目次
SQLAlchemyでSqlite3を触っていると、Pythonのリストをそのままカラムに入れたい場面が出てきます。タグの集合、ユーザーの趣味、簡易な配列データなど、わざわざ別テーブルに正規化するほどでもないけれど、文字列の中に「,」区切りで詰めるのは後で扱いに困る、というやつです。
自分も同じところで一度詰まったので、SQLAlchemy 2.0 系の書き方に合わせて整理し直しました。
SQLite側の前提：JSON型と JSON1 拡張
SQLiteには真の「リスト型」「配列型」はありません。値は TEXT INTEGER REAL BLOB NULL の5種類だけで、Pythonのリストをそのまま格納できる箱は用意されていません。
そのうえで、リストを扱う実用的なやり方が2つあります。
- 文字列カラム（
Text）にJSON文字列として詰め、Python側でjson.dumps/json.loadsする - SQLAlchemyの
JSON型カラムを使い、シリアライズ・デシリアライズをORMに任せる
後者の JSON 型は、SQLite側で見ると結局は TEXT カラムにJSONを格納している形になります。違いは「JSONとして読み書きする責務をORMが持つか、自分で持つか」です。
ここで重要なのが、SQLite 3.38.0（2022-02-22リリース）以降、JSON関数がデフォルトで組み込まれた点です[1]。それ以前はビルドオプション扱いで使えない環境もありましたが、いまの環境ならまず使えると考えてよくなりました。これにより、JSON配列の要素を json_each や json_extract でSQL側から検索できます。後段でこの活用例も載せます。
SQLAlchemy 2.0 系で書き直す：JSON 型 + Mapped[list[str]]
ここからはコード。SQLAlchemy 2.0以降は declarative_base() ではなく DeclarativeBase を継承し、カラム宣言も Column(...) ではなく Mapped[...] 型ヒント + mapped_column(...) を使う書き方が推奨されています[2]。
リストを扱うカラムは JSON 型で宣言します。
from sqlalchemy import JSON, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
hobbies: Mapped[list[str]] = mapped_column(JSON)
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session:
session.add(User(name="John Doe", hobbies=["reading", "swimming", "coding"]))
session.commit()
user = session.scalar(select(User).where(User.name == "John Doe"))
print(user.hobbies) # ['reading', 'swimming', 'coding']
print(type(user.hobbies)) # <class 'list'>
この書き方なら、属性に代入したリストはSQLAlchemyが自動でJSON文字列にして書き込み、取り出すときも自動でPythonのリストに戻してくれます。json.dumps / json.loads を自分で呼ぶ必要はありません。
session.query(...).filter_by(...) ではなく session.scalar(select(...).where(...)) を使っているのも2.0スタイルの推奨パターンです。Query API はまだ動きますがレガシー扱いで、新規コードでは select() ベースに揃えるのが筋です。
Mutableの落とし穴
JSON 型は便利ですが、1つだけ落とし穴があります。取り出したリストを「その場で .append() して commit()」しても、変更が検知されず保存されないことがあります。
user.hobbies.append("painting")
session.commit() # 反映されないことがある
理由は、SQLAlchemyが変更検知を「属性への代入」で判定しているためで、リストの中身を破壊的に書き換えても気付けないからです。安全なのは新しいリストを代入し直すこと。
user.hobbies = user.hobbies + ["painting"]
session.commit()
変更検知を内部でやってほしい場合は MutableList.as_mutable(JSON) を使う手もありますが、まずは「リストごと差し替える」運用に寄せておくと事故が減ります。
旧スタイル（Text + 手動JSON）も知っておく価値はある
JSON 型が使えない事情がある場合（古い環境、サードパーティのスキーマと噛み合わせる必要がある等）、Text カラムに手動でJSON文字列を入れる旧来の書き方も残ります。
import json
from sqlalchemy import String, Text, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
hobbies_raw: Mapped[str] = mapped_column("hobbies", Text)
@property
def hobbies(self) -> list[str]:
return json.loads(self.hobbies_raw)
@hobbies.setter
def hobbies(self, value: list[str]) -> None:
self.hobbies_raw = json.dumps(value)
JSON 型を使う場合と比べると、
- カラム宣言とアクセサが分かれる
- 取得側で
json.loadsを都度呼ぶコストが発生する - 入れ忘れ・取り出し忘れのバグが出やすい
というデメリットがあるので、特別な事情がない限りは JSON 型に寄せたほうが楽です。
SQL側からリストの中身を検索する
JSON 型でリストを格納しておくと、SQLite 3.38以降では SQL側から配列の要素にアクセスできます。たとえば「coding が趣味に入っているユーザーを全部出す」は json_each でこう書けます。
from sqlalchemy import text
with Session(engine) as session:
rows = session.execute(
text(
"""
SELECT DISTINCT users.name
FROM users, json_each(users.hobbies)
WHERE json_each.value = :target
"""
),
{"target": "coding"},
).all()
print(rows) # [('John Doe',)]
json_each(users.hobbies) がJSON配列の各要素を行として展開し、その value カラムで絞り込んでいます。「特定の要素を含む行を全部欲しい」のような検索を、アプリ側に取り出してから for で回さずSQLで済ませられます。
正規化されたタグテーブルを別に切る前段の選択肢として、「JSON 型 + json_each 検索」は十分機能します。検索性能を本格的に要求するならインデックス付きの別テーブルに正規化したほうがいいですが、用途次第ではこの構成のままで足ります。
JSON関数の全体像は公式リファレンスにまとまっているので、json_extract で特定インデックスを取り出したい、json_array_length で要素数を見たい、といった用途は[1:1]を参照してください。
経験者にとっては当たり前のことかもしれませんが、SQLAlchemyを触り始めて「Pythonのリストってどう入れるの」で止まったときに、いまならこの構成からスタートしておけば後で大きく書き換えずに済みます。