🫠

SQLAlchemy w/ PyMySQL で collation_connection に任意の collation を設定したい！

2023/12/08に公開

MySQL

Python

SQLAlchemy

tech

tl;dr

SQLAlchemy は MySQL サーバとのコネクションを確立するタイミングで SET NAMES <charset> を (COLLATE の指定せず) 誰の許可もなしに勝手に発行しやがる困ったちゃん である
- このふざけた挙動のせいで、サーバの collation_connection に utf8mb4_0900_bin を設定していても結果的に無視されてしまう
これを解消しようとして、create_engine() の connect_args パラメータによって collation を指定するステートメントを PyMySQL レベルのコネクション確立時に発行するようにしても、SQLAlchemy による SET NAMES の発行タイミングの方が後 なので有効に働かない
SQLAlchemy 2.0 系でも有効に働くワークアラウンドとして、PoolEvents.connect のイベントをインスタンスレベルで listen し、そのイベントハンドラにて SET NAMES を改めて発行する方法がある

SQLAlchemy で MySQL サーバに接続する際の困った挙動

MySQL を普段から扱っている人であればみなさんご存知であるように、プログラミング言語を問わず MySQL サーバに接続してクエリを発行するアプリケーションを書くときはコネクションにおける charset や collation が期待どおりになっているかを常に気にする必要があります。特に collation に関わる有名な事例として、以下の「🍣=🍺」が挙げられるかと思います。

MySQL のサーバには接続時の charset/collation を決める character_set_connection や collation_connection といった設定項目が存在しているので、これらをあらかじめ都合のよい値に変更しておくことでアプリケーション側のデータベース接続時の設定の手間を省くこともできます。しかし、この記事で取り上げる SQLAlchemy はこの collation_connection の設定に影響を及ぼす困った挙動をしてくれます。

具体的には以下の discussion で言及されているように、SQLAlchemy は MySQL サーバに接続する際の一連の処理において SET NAMES <charset> ステートメントを COLLATE を指定せずに自動的に発行する実装になっているのですが、これが原因となり、MySQL サーバ側で設定していた collation_connection が結果として無視されてしまう挙動が生じてしまいます。

この discussion の投稿者が言及しているように、create_engine() の connect_args パラメータに

{
  "init_command": "SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin"
}

のような値を指定して PyMySQL 側でコネクションが確立したときに発行するステートメントを仕込んでおいたとしても、SQLAlchemy 側で発行される SET NAMES の方が後に発行されるので、この方法では残念ながら解決とはなりません。

ワークアラウンド

ではどうすればよいかというと、discussion の回答で提示されているように PoolEvents.connect のイベントを listen し、そのイベントハンドラで別途 SET NAMES などのステートメントを発行する方法が有効なワークアラウンドとして挙げられます。なおこの discussion 自体は SQLAlchemy 1.4.x の頃のものではありますが、現行バージョン (2.0.x) でも同様に利用できることを確認しています。

なお注意点として、以下のコードにあるようにイベントハンドラは create_engine() で生成された特定のインスタンスに対して 設定する必要があります。すべてのインスタンスに対して PoolEvents.connect のイベントハンドラを仕掛けようとして @event.listens_for(Engine, "connect") みたいに記述すると、イベントハンドラ側の SET NAMES ステートメントが先に発行され結局 SQLAlchemy 側のステートメントが後から発行されてしまうので、問題の解決にはなりません。

# pip install sqlalchemy pymysql

from sqlalchemy import create_engine, event, text, Engine


def main() -> None:
    engine: Engine = create_engine("mysql+pymysql://user:pw@host/")

    # ここで @event.listens_for(Engine, "connect") のように指定してしまうと、
    # SQLAlchemy 側の SET NAMES が後に発行されてしまうのでうまくいかない
    # 必ず create_engine() で生成したインスタンスに対してイベントハンドラを仕掛けなければならない
    @event.listens_for(engine, "connect")
    def overwrite_charset_and_collation(dbapi_conn, conn_rec) -> None:
        with dbapi_conn.cursor() as cursor:
            cursor.execute("SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin")

    # collation_connection を確認する
    with engine.connect() as conn:
        (result,) = conn.execute(text("SHOW VARIABLES LIKE 'collation_connection'"))
        print(result)


if __name__ == "__main__":
    main()

tl;dr

SQLAlchemy で MySQL サーバに接続する際の困った挙動

ワークアラウンド

Discussion