[PyMySQL] ValueError : unsupported format character 'Y' (0x59)

ValueError : unsupported format character 'Y' (0x59)
pymysqlライブラリを利用してSQLを実行する際に起こることがあるこのエラー。
今回はこの unsupported format character 'Y' の原因と対策について紹介する。

エラー現象

pd.read_sql メソッド実行時に DATE_FORMAT(column, '%Y-%m-%d') のような %Y-%m-%d を含むselect文を実行すると unsupported format character 'Y' (0x59) のエラーになってしまう。

print("Connect to DB")
conn = pymysql.connect(user="root", password="", host="localhost", database="test")

print("Select data")
sql = "SELECT DATE_FORMAT(col_datetime, '%Y-%m-%d') FROM test.table_datetime where col_date = %s"
df_datetime = pd.read_sql(sql, conn, params=["2019-11-18"])
print(df_datetime)
# unsupported format character 'Y' (0x59)

unsupported format character 'Y' を直訳すると「サポートされていないフォーマットの文字'Y'」となるので %Y-%m-%d の部分が怪しい。

エラー原因と対策

ではエラーの原因が何なのかというと、pymysqlが %Y-%m-%d を %s のような 後でパラメータ値で置換する部分 として認識してしまう為、「サポートされていないフォーマット」としてエラーになってしまう。

ターミナルやSQLを実行するためのアプリ上では DATE_FORMAT(column, '%Y-%m-%d') は問題無く実行できるので、この手の「PythonからSQLを実行する場合のお作法」に起因するエラーは気づきにくい。

エラーを回避する為にはSQL中の %Y-%m-%d を一旦 %s とし、後で代入するパラメータ側に%Y-%m-%d を指定する。

sql = "SELECT DATE_FORMAT(col_datetime, %s) FROM test.table_datetime where col_date = %s"
df_datetime = pd.read_sql(sql, conn, params=["%Y-%m-%d","2019-11-18"])
print(df_datetime)
#   DATE_FORMAT(col_datetime, '%Y-%m-%d')
# 0                            2019-11-18
# 1                            2019-11-18

ちなみにSQL中に %Y-%m-%d のみ存在する場合はエラーにはならない。
つまり %Y-%m-%d と %s が混在していると %Y をSQLではなくパラメータとして認識してしまうので処理できずにエラーになる。

sql = "SELECT DATE_FORMAT(col_datetime, '%Y-%m-%d') FROM test.table_datetime"
df_datetime = pd.read_sql(sql, conn)
print(df_datetime)
#   DATE_FORMAT(col_datetime, '%Y-%m-%d')
# 0                            2019-11-18
# 1                            2019-11-18

まとめ

• unsupported format character 'Y' (0x59) が発生する原因は、SQL中に %Y と %s が混在しているから
• 回避方法はSQL中の %Y も一旦 %s とし、pd.read_sql 等のSQL実行時に　params=["%Y-%m-%d"] で指定する。