Pythonによるテーブルの作成とデータ入力

今日は、『実践Pythonによるデータベース入門』の5.3節―テーブルの作成とデータ入力―で学んだことをアウトプットします。

【例題5.3】

下記仕様要求を実現するPythonプログラムを作成せよ。
仕様要求
（1）MySQLサーバーのsampledbへの接続を行う。
（2）SQL命令「テーブルの作成」を実行する。
（3）SQL命令「テーブルにデータ入力する」を実行する。
（4）SQL命令「テーブルのデータを表示する」を実行する。
（5）（4）の結果を画面に表示する
（6）サーバーにコミットする
（7）接続を切断する

【解答1】

SQL命令文を1行ずつ投入していきます。

# データベースにテーブル作成
import mysql.connector

# コネクターを作成
config = {
   'user': 'sampleuser',
   'password': 'hogehoge',
   'host': 'localhost',
   'database': 'sampledb',
}

dbconnector = mysql.connector.connect(**config)

# 接続できているかどうか確認
if dbconnector.is_connected():
   print('データベースへの接続が成功しました。')
else:
   print('データベースへの接続が失敗しました。')
   exit(1)

# cursorオブジェクトの生成
cursor = dbconnector.cursor(buffered=True)

# 属性 id, name, gender, ageを持つテーブルを作成、データ入力、問い合わせ
cursor.execute("DROP TABLE IF EXISTS memberlist;")
cursor.execute("CREATE TABLE memberlist ( mid INt, name CHAR(20), gender CHAR(10),\
               age INT, PRIMARY KEY (mid) );")
cursor.execute("INSERT INTO memberlist VALUES ( 1, '田中花子', '女', 28);")
cursor.execute("INSERT INTO memberlist VALUES ( 2, '山田聡', '男', 32);")
cursor.execute("INSERT INTO memberlist VALUES ( 3, '佐藤美那子', '女', 21);")
cursor.execute("INSERT INTO memberlist VALUES ( 4, '鈴木博', '男', 25);")
# コミットする
dbconnector.commit()

# 問い合わせ
cursor.execute("SELECT * FROM memberlist;")
# すべてのデータを取得、表示
tuples = cursor.fetchall()
print("テーブル memberlistのデータ:")
for tpl in tuples:
   print(tpl)

# 接続を切断
dbconnector.close()

実行結果は以下のようになりました。

データベースへの接続が成功しました。
テーブル memberlistのデータ:
(1, '田中花子', '女', 28)
(2, '山田聡', '男', 32)
(3, '佐藤美那子', '女', 21)
(4, '鈴木博', '男', 25)

【解答2】解答1の改良版。

改良点1：複数行のSQL命令文をまとめて投入。
改良点2：データを以下のようにcsvファイルに保存し、そこから読み込んで、データベースのテーブルに書き込みます。

# データベースにテーブル作成
# SQL命令文をまとめて実行
# csvファイルからデータを読み込み

import mysql.connector
import csv

# コネクターを作成
config = {
   'user': 'sampleuser',
   'password': 'hogehoge',
   'host': 'localhost',
   'database': 'sampledb',
}

dbconnector = mysql.connector.connect(**config)

# 接続できているかどうか確認
if dbconnector.is_connected():
   print('データベースへの接続が成功しました。')
else:
   print('データベースへの接続が失敗しました。')
   exit(1)

# cursorオブジェクトの生成
cursor = dbconnector.cursor(buffered=True)

# 属性 id, name, gender, ageを持つテーブルを作成、データ入力
operation = (
           "DROP TABLE IF EXISTS newlist;"
           "CREATE TABLE newlist ( mid INT, name CHAR(20), gender CHAR(10), age INT, PRIMARY KEY (mid) );"
)

# SQL命令文を実行
results = cursor.execute(operation, multi=True)
for res in results:
   print(res)

operation = ("INSERT INTO newlist VALUES (%s, %s, %s, %s);")
seqs = []
with open('newlistdata.csv', encoding='utf-8') as f:
   reader = csv.reader(f)
   for row in reader:
       tuple = (int(row[0]), row[1], row[2], int(row[3]))
       seqs.append(tuple)

# executemany()で実行する
cursor.executemany(operation, seqs)
# コミットする
dbconnector.commit()

# 問い合わせ
cursor.execute("SELECT * FROM newlist;")
# 問い合わせ結果のすべてのデータを取得、表示
tuples = cursor.fetchall()
print("テーブル newlistのデータ:")
for tpl in tuples:
   print(tpl)

# 接続を切断
dbconnector.close()

実行結果は以下のようになりました。

データベースへの接続が成功しました。
MySQLCursorBuffered: DROP TABLE IF EXISTS newlist
MySQLCursorBuffered: CREATE TABLE newlist ( mid INT, name CHA..
テーブル newlistのデータ:
(1, ' 田中花子', ' 女', 28)
(2, ' 山田聡', ' 男', 32)
(3, ' 佐藤美那子', ' 女', 21)
(4, ' 鈴木博', ' 男', 25)

【解答2】の方法ですと、テーブルのデータを変更するとき、プログラムのソースコードを変更することなく、データファイルのみの変更で対応できるようになりますね。