PythonとCSVファイルで レコード一括INSERT

12758 ワード
MySQL Python CSV pokemon Python テキストリンク

概要

pythonを用いて
csvファイルの各行から値を読み取り
DBへinsertさせていきたい。
また、insertした後にテーブルのレコード状態を確認したいので
select結果もファイル出力させたい。

テーブル情報

テーブル名はpokevalueです。

pokevalue
mysql> desc pokevalue;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(1024) | YES  |     | NULL    |       |
| H     | int(11)       | YES  |     | NULL    |       |
| A     | int(11)       | YES  |     | NULL    |       |
| B     | int(11)       | YES  |     | NULL    |       |
| C     | int(11)       | YES  |     | NULL    |       |
| D     | int(11)       | YES  |     | NULL    |       |
| S     | int(11)       | YES  |     | NULL    |       |
| T     | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

input

以下の書式で、insertしたい値を記載した状態のcsvファイル

・対象テーブルのcolumnを先頭行に記載
・2行目以降はcolumn毎にinsertしたい値を記載

pokevalue_insert.csv
id,name,H,A,B,C,D,S,T
1,フシギダネ,45,49,49,65,65,45,318
2,フシギソウ,60,62,63,80,80,60,405
3,フシギバナ,80,82,83,100,100,80,525

output

・summary.log(select結果が吐かれる)

実装

sql_test.py(サブモジュール)

sql_test.py
import pymysql
import pymysql.cursors
class MySQL:
conn = pymysql.connect(host=XXXX,
                    user=XXXX,
                    password=XXXX,
                    db=XXXX,
                    charset='utf8mb4'
                    )
    # select
    def query(stmt, *args):
        try:
            conn.ping()
            with conn.cursor() as cursor:
                cursor.execute(stmt, (args))
                data = cursor.fetchall()
                return data
        finally:
            conn.close()
            cursor.close()

    # insert
    def ins_query(stmt, *args):
        try:
            conn.ping()
            with conn.cursor() as cursor:
                cursor.execute(stmt, (args))
                data = cursor.fetchall()
        finally:
            conn.commit()
            conn.close()
            cursor.close()
            return True

メインモジュールからquery()かins_query()を呼ばれます。
前者の場合はselect結果を返し
後者の場合はinsertするといった単純な作りです。



app.py(メインモジュール)

app.py
import csv
from sql_test import MySQL

csv_file = open("./pokevalue_insert.csv", "r", encoding="ms932", errors="", newline="")

f = csv.DictReader(csv_file)
for row in f:
    ins_main = 'INSERT INTO pokevalue\
        (id, name, H, A, B, C, D, S, T)\
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)'

    MySQL.ins_query(ins_main, row["id"], row["name"], row["H"], row["A"], row["B"], row["C"], row["D"], row["S"], row["T"])

    sel_main = 'SELECT * FROM pokevalue\
        WHERE id=%s AND name=%s AND H=%s AND A=%s AND B=%s AND C=%s AND D=%s AND S=%s AND T=%s'

    sel_value = MySQL.query(sel_main, row["id"], row["name"], row["H"], row["A"], row["B"], row["C"], row["D"], row["S"], row["T"])
    with open('summary.log', mode='a') as log:
            log.write("{0}{1}".format(str(sel_value), "\n"))

csvファイルはDictReaderによって辞書型で開き、キーを指定して変数に格納できる形にしました。
その為、csvファイルから読み取った行のカラムを指定して読み込むには
row[カラム名]あるいはrow.get(カラム名)のように指定してやります。
(もしかすると単にreaderで読み取って、forをネストしながらinsert文組み立てた方が良かった気もしますが今回はこれで。)

その後はins_mainにINSERT文を記述します。(VALUESの中身は可変なので変数%sとします)
そしてins_queryへの引数に、ins_mainとcsvファイルから読み取った値を渡してあげれば、無事テーブルにレコードがINSERTされます。(selectも同様の構造で書いています)
最後にselect結果をlogファイルに追記しています。

app.py実行結果


summary.log
((1, 'フシギダネ', 45, 49, 49, 65, 65, 45, 318),)
((2, 'フシギソウ', 60, 62, 63, 80, 80, 60, 405),)
((3, 'フシギバナ', 80, 82, 83, 100, 100, 80, 525),)
pokevalue
mysql> select * from pokevalue;
+------+------------------------------------------+------+------+------+------+------+------+------+
| id   | name                                     | H    | A    | B    | C    | D    | S    | T    |
+------+------------------------------------------+------+------+------+------+------+------+------+
|    1 | フシギダネ                               |   45 |   49 |   49 |   65 |   65 |   45 |  318 |
|    2 | フシギソウ                               |   60 |   62 |   63 |   80 |   80 |   60 |  405 |
|    3 | フシギバナ                               |   80 |   82 |   83 |  100 |  100 |   80 |  525 |

参考記事

pythonでのcsvファイルの読み込み