csvファイルを pandas で読み込んで MySQL 8.0.24 に書き込んでみる

目的

・Windows 10に MySQL Community Server 8.0.24 をインストール済
・pandas でcsvファイルを読み込む
・python + mysql-connector-python を使ったサンプルコードを書いてみる
・対象データは郵便番号検索の17ISHIKA.CSV
※単にpandas.read_csvを使ったコードを書いてみたかっただけともいう
※pandasでKEN_ALL.CSVを取り込むだけだと11秒位だったので、そのままSQLに流したらどうかを試したかったという
※KEN_ALL.CSVは12万行超えだけど、トータルそう変わらなかった気も

書き込み用テーブルのSQL

SET character_set_client = utf8mb4;
CREATE TABLE zipcode(
	seq nchar(8) 		not null,
	prefcode nchar(3) 	null,
	kubuncode nchar(8) 	null,
	postal5 nchar(5) 	null,
	postal nchar(8) 	null,
	prefkana nchar(20) 	null,
	citykana nchar(40) 	null,
	addrkana nchar(80) 	null,
	prefkanji nchar(20) null,
	citykanji nchar(40) null,
	addrkanji nchar(80) null,
	flg1 int null,
	flg2 int null,
	flg3 int null,
	flg4 int null,
	flg5 int null,
	flg6 int null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

事前準備

Windows Powershell にて以下を実行する
zipcode.zipcodeを作成する
※zipcodeスキーマを作成後、zipcodeテーブルを作成する

PS C:\> mysql -u root -p
Enter password: ****

mysql> CREATE SCHEMA zipcode;

mysql> use zipcode
Database changed

※テーブル作成用SQLを流す

mysql> SHOW TABLES;
+-------------------+
| Tables_in_zipcode |
+-------------------+
| zipcode           |
+-------------------+

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zipcode            |
+--------------------+

pandas & mysql-connector-python をインストールする

PS C:\> python -V
Python 3.9.5

PS C:\> pip install pandas
Successfully installed pandas-1.2.4

PS > pip3 install mysql-connector-python

pd.read_csvの留意点

import pandas as pd

postal = pd.read_csv("test.CSV", header=None, encoding='cp932')
print(postal)

postal = pd.read_csv("test.CSV", header=None, encoding='cp932', dtype='object')
print(postal)

※ dtypeを指定しないと数字のみの文字列が数値として判別する
※ "060  " -> 60
※ test.CSVの内容
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ",～

     0   1       2        3             4   ... 10 11 12 13  14
0  1101  60  600000  ﾎｯｶｲﾄﾞｳ  ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ  ...  0  0  0  0   0

      0      1        2        3             4   ... 10 11 12 13 14
0  01101  060    0600000  ﾎｯｶｲﾄﾞｳ  ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ  ...  0  0  0  0  0

サンプルコード

# coding:utf-8
# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode

import pandas as pd
import mysql.connector as mydb

# コネクションの作成
# アカウントの作成＆権限付与が必要
conn = mydb.connect(
    host='192.168.5.xxx',
    port='3306',
    user='demo',
    password='demo',
    database='zipcode'
)

count = 0
postal = pd.read_csv("17ISHIKA.CSV", header=None, encoding='cp932', dtype='object')

# autocommit しない
conn.autocommit = False
cur = conn.cursor()
cur.execute('TRUNCATE TABLE ZIPCODE;')

for ary in postal.values.tolist():
    count = count + 1
    sql = 'INSERT INTO ZIPCODE (' \
        + '  SEQ'       \
        + ', PREFCODE'  \
        + ', KUBUNCODE' \
        + ', POSTAL5'   \
        + ', POSTAL'    \
        + ', PREFKANA'  \
        + ', CITYKANA'  \
        + ', ADDRKANA'  \
        + ', PREFKANJI' \
        + ', CITYKANJI' \
        + ', ADDRKANJI' \
        + ', FLG1' \
        + ', FLG2' \
        + ', FLG3' \
        + ', FLG4' \
        + ', FLG5' \
        + ', FLG6' \
        + ') VALUES ('  \
        + '  \'' + str(count).rjust(8, '0') + '\'' \
        + ', \'' + str(ary[0])[:2] + '\'' \
        + ', \'' + str(ary[0]).strip() + '\'' \
        + ', \'' + str(ary[1]).strip() + '\'' \
        + ', \'' + str(ary[2]) + '\'' \
        + ', \'' + ary[3] + '\'' \
        + ', \'' + ary[4] + '\'' \
        + ', \'' + ary[5] + '\'' \
        + ', \'' + ary[6] + '\'' \
        + ', \'' + ary[7] + '\'' \
        + ', \'' + ary[8] + '\'' \
        + ',   ' + str(ary[9])   \
        + ',   ' + str(ary[10])  \
        + ',   ' + str(ary[11])  \
        + ',   ' + str(ary[12])  \
        + ',   ' + str(ary[13])  \
        + ',   ' + str(ary[14])  \
        + ');'
    # print(sql)
    cur.execute(sql)

conn.commit()
# 挿入したデータの件数を確認する
# cursor = conn.cursor()
cur.execute("SELECT COUNT(*) FROM ZIPCODE;")
row = cur.fetchone()
if row:
    print(row)

cur.close()
conn.close()

参考にしたサイトはこちら

Pythonでmysql-connector-pythonでDB処理＋トランザクションの動作検証
MySQLの文字コードをutf8mb4に変更
pandas.DataFrame, SeriesとPython標準のリストを相互に変換
pandasでデータを読み込むときに気を付けること(dtypeの指定)
ユーザーの作成
ユーザーに設定できる権限の種類と一覧