Python基礎：ファイルを読み込んでデータベースに登録する

はじめに

今回も「Pythonからデータベースを操作する」の続きです。PythonからSQL文を発行する方法を学んだので、データベースにデータを連続で登録する、条件に合致したデータを抽出することができるようになりました。今回は、手元にあるファイルを読み込んで、データベースに登録する方法を扱います。

前回までの内容は以下を参考にしてください。

Python基礎：データベースを操作する

はじめに前回に引き続き、データtベースを扱います。今回は、直接操作するのではなく、PythonからMySQLを操作してみます。PythonからのMySQLの操作には、mysqlclientを利用します。まずはこれを導入するところから始...

Python初心者向け：データベースからデータを抽出する基本を解説

Python初心者でもわかるようにMySQLに接続し、指定した条件に合致するデータを抽出する方法を基本から解説していきます。データの抽出は、select文でしたね。PythonからはどのようにSQL文を実行するのかを実際に確認してみましょう。

今回は、問題を解きながら考えていくことにします。

問題

１．
test_dbというデータベースを作成して、そこに次のような構造のテーブルtest_tableを作ってください。
　name（最大15文字の文字列）
　zoo（最大15文字の文字列）
　favorite（最大10文字の文字列）
　weight（整数）

２．
次の記述があるテキストファイル「test.txt」を読み込んで1行ずつ「test_table」に挿入するプログラムを作ってください。

（test.txt）
　らいおん,旭山動物園,にく,250
　きりん,上野動物園,はっぱ,400
　ぱんだ,上野動物園,ささ,120
　らくだ,こどもどうぶつえん,みず,200

３．
DB「test_db」のテーブル「test_table」にアクセスして、
　①体重の平均
　②上野動物園の体重の平均
を出力するプログラムをつくってください。

解答

データベースをつくる

これは前回扱いましたね。ちょうどよい復習になります。まずは、MySQLにログインしましょう。次のようにします。

mysql -h localhost -u root -p

すると、パスワードが求めれられるので、ご自身のパスワードを入力してください。ログイン出来たら、データベースを作るには、「create database」でしたね。今回作りたいデータベースは、「test_db」だったので次のようにします。

create database test_db;

うまくいってそうですね。次のようにしてデータベースを確認することができます。

ちゃんと「test_db」が作成されていることが確認できます。

テーブルをつくる

次にテーブルを作ります。テーブル名は「test_table」でした。まずは、作ったデータベースに入る必要があるので、「use testdb」とするのでした。

このように「Database changed」と表示されればOKです。ここにテーブルを作っていきます。テーブルの作成は、「create table」でしたね。

create table test_table(name varchar(15), zoo varchar(15), favorite varchar(10),weight int);

「Query OK」と表示されればOKです。これでテーブルができました。

ファイルを読み込むプログラムをつくる

次にファイルを読み込むプログラムを作っていきましょう。まずはプログラム名を決めます。ここでは、「zoo.py」としましょう。読み込むファイルと同じフォルダにこのファイルを作ります。

このファイルで必要なライブラリの読み込み、データベースとの接続・・・と必要な処理を書いていきます。

(zoo.py)
# ライブラリの読み込み
import MySQLdb

# データベースとの接続
conn=MySQLdb.connect(host='localhost',user='root',passwd='XXXX',db='test_db',charset='utf8')
cursor=conn.cursor()

# ファイルを読み込んで書き込む
with open('test.txt',encoding='utf-8') as fh:
    for line in fh:
        line=line.rstrip()
        items=line.split(',')
        sql="insert into test_table(name,zoo,favorite,weight) values('%s','%s','%s',%s)"%(items[0],items[1],items[2],items[3])
        cursor.execute(sql)
    conn.commit()
    conn.close()

「zoo.py」の中にコメントを記載しているので、ここでは解説は不要でしょう。わからない箇所は、過去のブログを確認してみてください。

ここまでできたら、次を試してみましょう。

python zoo.py

エラーが表示されなければ、処理は成功しています。実際にデータが登録されているか、MySQLを確認してみましょう。MySQLにログインして、利用する出たベースをしていします。そこで、対象のテーブルを指定してselectすると確認できます。

このように表示されれば成功となります。

平均を求める

次に体重の平均を求めていきましょう。ここでは、「どうぶつたち全体の平均体重」と、「上野動物園のどうぶつたちの平均体重」が求められているので、最終的に次のように表示されるプログラムを作ることにしましょう。

どうぶつたち全体の平均体重は〇〇で、上野動物園のどうぶつたちの平均体重は〇〇です。

これを「zoo_weight.py」というプログラムにまとめましょう。まずは、どうつぶたち全体の平均体重をもとめてみましょう。

import MySQLdb

conn=MySQLdb.connect(host='localhost',user='root',passwd='XXX',db='test_db',charset='utf8')
cursor=conn.cursor()

sql_1="select weight from test_table"
cursor.execute(sql_1)
result=cursor.fetchall()

total=0
for item in result:
    total+=item[0] # 項目は1つでもタプルで返ってきているのでこのように指定

weight_average=total/len(result)

print(weight_average)

これをターミナルで実行してみると、以下のようになります。

ここまでうまくいってそうですね。続いて、上野動物園のどうぶつたいの平均体重も求めてしまいましょう。次のように書き換えます。

import MySQLdb

conn=MySQLdb.connect(host='localhost',user='root',passwd='XXX',db='test_db',charset='utf8')
cursor=conn.cursor()

sql_1="select weight from test_table"
cursor.execute(sql_1)
result=cursor.fetchall()

total=0
for item in result:
    total+=item[0] # 項目は1つでもタプルで返ってきているのでこのように指定

weight_average=total/len(result)

sql_2="select weight from test_table where zoo='上野動物園'"
cursor.execute(sql_2)
result=cursor.fetchall()

total_ueno=0
for item in result:
    total_ueno+=item[0]

ueno_weight_average=total_ueno/len(result)

print('どうぶつたち全体の平均体重は%.02fで、上野動物園のどうぶつたちの平均体重は%.02fです。'%(weight_average,ueno_weight_average))

これを実行すると、次のようになります。