【SQLAlchemy】MySQL：fillterで大文字小文字を区別して取得

ちょっとハマったのでメモ。

割とよく使いそうなんだけどSQLAlchemyではあまり情報が見つからなかったので、数日後には忘れているだろう自分とどこかの誰かのために備忘録として残しておきます。

【問題】MySQLは大文字小文字の区別をしない

例えばUserテーブルのusernameカラムがユーザー毎に一意の値（文字列）を持っていて、そのusernameは半角英数字の大文字小文字が使用可能。その上で同じusername文字列でも大文字小文字の違いがある場合、それらは別々のユーザーとしたい、というようなケースだとします。

文字にすると難しいですが、要はusername「taro」と「Taro」は別のuserとしたいという良くあるお話。

このとき以下のようなクエリで取得できそうですが…実はこれだと「taro」と「Taro」は区別されません。これはSQLAlchemyというかそもそものMySQLの仕様なのでしょうがない。

def get_user(db: Session, username: str):
    return db.query(models.User).filter(models.User.username == username).first()

【補足：コードについて】
dbはsqlalchemy.ormのsessionmakerから生成されたセッション、modelsはテーブルが定義されているモジュールとします。基本的な構成は参考リンク内と同様、詳細は以下リンク先をご確認ください。
（参考：SQL (Relational) Databases – FastAPI）

【解決方法】func.binary()を使用する

こういうときはfuncをimportし、binary関数（おそらく通常のMySQLでいうところのBINARY演算子を付与するのと同様のはず）を使用すると良さげです。

# import追加忘れずに
from sqlalchemy import func

def get_user(db: Session, username: str):
    return db.query(models.User).filter(models.User.username == func.binary(username)).first()

こうすることで「taro」と「Taro」は区別できます。また「TaRo」や「tarO」は結果が対象ユーザーが見つからずNoneが返ってくるようになる。

# import追加忘れずに
from sqlalchemy import func

def get_user(db: Session, username: str):
    return db.query(models.User).filter(models.User.username == func.binary(username)).first()

console.log(get_user(db=db, username='TaRo'))
# -> None
console.log(get_user(db=db, username='tarO'))
# -> None

これで同じ文字列でも大文字小文字違えば違うusernameとして扱うことができます。

新しいユーザーが作成されるときget_user()で使いたいusernameと同じ文字列で大文字小文字の組み合わせが違うusrenameが既に存在するかを確認→存在しない（重複しない）のでOK！というようなことができますね。

参考記事

how to force a filter() to match exact cases (case sensitivity) in sqlalchemy? – stackoverflow

MySQLで大文字小文字を区別させる – DayByDay