PythonのMySQLdbで日本語の入ったクエリを投げる際のUnicodeEncodeError回避

概要

PythonからMySQLへとinsert文を投げたい時があります。
MySQLdbパッケージを使ってクエリを投げる際に、クエリに日本語が入っているとUnicodeEncodeErrorが出てしまいます。
これはMySQLdb.connect()の引数に"use_unicode=True"、"charset="utf8""というオプションを追加すると回避できるようです。

状況

こういったMySQLのデータベースがあるとします。

mysql> desc test;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| test_id   | int(11)     | NO   | PRI | NULL    |       |
| test_text | varchar(64) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Pythonからtest_textというカラムに文字列を追加したいという気持ちが生まれました。
普通にMySQLdbパッケージを使ってinsert文を投げてみます。

mysqltest.py

import MySQLdb
 
try: 
    conn = MySQLdb.connect(
    host=host,
    db=dbname,
    port=port,
    user=user,
    passwd=password
    )

    cur = conn.cursor()

    query = "insert into test values('1','aaa')"

    cur.execute(query)
    
except:
    cur.close()
    conn.close()

cur.close()
conn.commit()
conn.close()

アルファベット文字列なら普通にできます。

mysql> select * from test;
+---------+-----------+
| test_id | test_text |
+---------+-----------+
|       1 | aaa       |
+---------+-----------+

しかしながら日本語の文字列だと…。

mysqltest.py

(省略)
    query = "insert into test values('2','あああ')"

    cur.execute(query)
(省略)

Traceback (most recent call last):
  File "mysqltest.py", line 30, in <module>
    conn.commit()
_mysql_exceptions.OperationalError: (2006, '')

といったエラーが出ます。
これはtry文での例外をキャッチした後にconn.commit()をしようとして出たエラーのようです。
このスタックトレースではよくわからんのでexcept文が実行されたタイミングでスタックトレースを出してみましょう。

mysqltest.py

import MySQLdb
import traceback

try: 
    conn = MySQLdb.connect(
    host=host,
    db=dbname,
    port=port,
    user=user,
    passwd=password
    )

    cur = conn.cursor()

    query = "insert into test values('2','あああ')"

    cur.execute(query)

except:
    cur.close()
    conn.close()
    print(traceback.format_exc()) 

cur.close()
conn.commit()
conn.close()

するとこのように出てきます。

Traceback (most recent call last):
  File "mysqltest.py", line 25, in <module>
    cur.execute(query)
  File "C:\path\to\anaconda\lib\site-packages\MySQLdb\cursors.py", line 248, in execute
    query = query.encode(db.encoding, 'surrogateescape')
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 29-31: ordinal not in range(256)

latin-1という文字コードでクエリがエンコードできないという怒られが発生しています。
どうやらMySQLdbパッケージはデフォルトでlatin-1を使ってエンコードするようです。どうして？

対策

こちらを参考にすると、MySQLdb.connect()の引数でエンコード方法を指定できるようです。
"use_unicode=True"と"charset="utf8""を引数に与えてやると、

mysqltest.py

import MySQLdb
import traceback

try: 
    conn = MySQLdb.connect(
    host=host,
    db=dbname,
    port=port,
    user=user,
    passwd=password,
    use_unicode=True,
    charset="utf8"
    )

    cur = conn.cursor()

    query = "insert into test values('2','あああ')"

    cur.execute(query)

except:
    cur.close()
    conn.close()
    print(traceback.format_exc()) 

cur.close()
conn.commit()
conn.close()

クエリが無事utf-8でエンコードされ、上手くいきました。

mysql> select * from test;
+---------+-----------+
| test_id | test_text |
+---------+-----------+
|       1 | aaa       |
|       2 | あああ    |
+---------+-----------+