pythonからMySQLに書き込みを行う時、いつもハマる。
今回こそハマりどころメモしておく。

実施内容のイメージ

Pythonを使ってローカルのcsvをローカルにたてたMySQLやGoogle Cloud PLatformのMySQLにデータを書き込む。

環境

Python3
mysql-connector
GCPのCloud SQL(MySQL)(今回は)
接続方法やSSL化については今回は省略。純粋にSQL文のハマりどころについてです。

書き込む際のコード

main.py

# -*- coding: utf-8 -*-
import os
import sys
import time
import glob
import shutil
import datetime
import logging
import traceback
import pandas as pd
import mysql.connector
import ssl
ssl.match_hostname = lambda cert, hostname: True
                
def insert_sql():
    schema = 'hogehoge2'#db名のこと
    connection = mysql.connector.connect(
                        port="3306",#基本はこのポートを使うことが多い
                        host='**.**.**.**',#gcpのIP
                        user='hogehoge',
                        password='fugafuda',
                        db=schema,
                        charset='utf8',
                        ssl_ca="./cloudstorage_cert/server-ca.pem",#証明書を使いたいので今回はこのフォルダにおいて指定
                        ssl_cert="./cloudstorage_cert/client-cert.pem",
                        ssl_key="./cloudstorage_cert/client-key.pem"
    )
    
    df = pd.read_csv(filename,engine="python")
    for r in range(df.shape[0]):
        cur = connection.cursor()
        sql = "insert into schema.table (col1,col2,col3,col4) values ('%s','%s',%s,%s);"%(
                    datetime.datetime.now(),
                    str(datetime.datetime.now()),
                    100,
                    1.5
                )
        print(sql)
        cur.execute(sql)
        cur.close()
        connection.commit()
    connection.close()
    print("done")

if __name__ == '__main__':
    insert_sql()

注意ポイント

1.テーブルを指定するとき、ちゃんとスキーマ（db）を記載する

my.sql

ダメな例
insert into mytable (datetime,col2,col3,col4) values ('2020-10-20 10:39:13.252105','2020-10-20 10:39:13.252105',100,1.5);
いい例
insert into myshema.mytable (datetime,col2,col3,col4) values ('2020-10-20 10:39:13.252105','2020-10-20 10:39:13.252105',100,1.5);

2. 文字列や時間など数字ではないものは、SQL文の中でシングルクオートで囲む

example.py

# ダメな例
sql = "insert into myshema.mytable (datetime,col2,col3,col4) values (%s,%s,%s,%s);"%(
                    temp.loc[r,"DateTime"],
                    str(temp.loc[r,"DateTime"]),
                    100,
                    1.5
                    )
# いい例
sql = "insert into myshema.mytable (datetime,col2,col3,col4) values ('%s','%s',%s,%s);"%(
                    temp.loc[r,"DateTime"],
                    str(temp.loc[r,"DateTime"]),
                    100,
                    1.5
                    )

3. connectionに対してcommit()しないとデータが反映されない

connection.close()は忘れないのだが、connection.commit()を忘れてデータが更新されないことがある。cur.commit()はしない。connectionでcommit()することに注意。

以上のあたりでよくハマる。sql文の最後の;はあってもなくても動作した