mysql-connector-python でcommit()が失敗するケース

前提

使用バージョン

• python: 3.6.6 (Anaconda)
• mysql-connector: 8.0.12 (Anaconda公式)

テーブル構成

• IDと名前の2つだけのカラム.
• 既存で1レコードだけデータがある状態とする.

MariaDB [mydb]> desc tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

MariaDB [mydb]> select * from tbl;
+------+------+
| id   | name |
+------+------+
|    1 | shio |
+------+------+

DBへの接続方法

• 接続情報を辞書型変数で宣言して利用する.
• 変数connでデータベースへの接続を確立する.

import mysql.connector

# DB接続情報
config = {
    'host': 'myserver',
    'user': 'myusername',
    'password': 'mypassword',
    'database': 'mydb'
}
conn = mysql.connector.connect(**config) # DBへのコネクション確立
print(conn.is_connected()) # -> True

本題

DB書き込みが失敗する場合をケース別に．

ケース1: コネクション切断後にクエリ実行する場合

(当たり前)

print(conn.is_connected()) # -> True
cursor = conn.cursor() # カーソルを定義

# 実行クエリ1
query = '''INSERT INTO tbl(id, name) VALUES(2, 'koro') ; '''
cursor.execute(query) # クエリ実行
conn.commit() # コミット. (2, koro) のデータは問題なく書き込める．
conn.close() # ここでDBへのコネクション切断
print(conn.is_connected()) # -> False

# 実行クエリ2
query = '''INSERT INTO tbl(id, name) VALUES(3, 'haku') ; '''
cursor.execute(query) # -> Traceback: Lost connection to MySQL server at 'my_server:3306', system error: Connection not available.
# conn.commit()

同じようにカーソルを閉じた後にもクエリ実行できない

print(conn.is_connected()) # -> True
cursor = conn.cursor() # カーソルを定義

# 実行クエリ1
query = '''INSERT INTO tbl(id, name) VALUES(2, 'koro') ; '''
cursor.execute(query) # クエリ実行
conn.commit() # コミット. この場合も(2, koro) のデータは問題なく書き込める．
cursor.close() # カーソルのclose
print(conn.is_connected()) # -> True

# 実行クエリ2
query = '''INSERT INTO tbl(id, name) VALUES(3, 'haku') ; '''
cursor.execute(query) # Traceback: Cursor is not connected
#conn.commit()

コネクションやカーソルのclose()は最後に行えば良いだけの話．
疑問なのはcursor.close()の使い所．
conn.close()ではなくあえてじゃなくてcursor.close()を使うメリットがあるケースって？？

ケース2: SET句で変数を使う場合

こっちが本題

print(conn.is_connected()) # -> True
cursor = conn.cursor() # カーソルを定義

# 実行クエリ
query = '''
    SET @name = 'lemo';
    INSERT INTO tbl(id, name) VALUES(2, @name) ;
    '''
cursor.execute(query) # クエリ実行
conn.commit() # Traceback: Commands out of sync; you can't run this command now

Commands out of sync; you can't run this command now
(= コマンドは同期されていません。このコマンドは現在実行できません)

なんじゃこりゃ．
普通にGUIツールやコマンドラインで実行したら上手くいくクエリ文なのに．
公式チュートリアルでも特に触れられてない気がする． Issue行き?
select結果を後の代入に使いたい場合， python側の変数に持たせないといけなくてなんだかな〜と．

おわり

ケース別と言いつつ大してネタは無いという．
他のケースや指摘あればコメントください．