SQLAlchemyはPythonでよく利用されるORMです。ですが、実際に使ってみたところ、リレーションシップの使い方にくせがあり、苦戦しました。特にSELECT時の挙動が分かりにくかったので、調査したことをまとめておくことにしました。
本記事で使用しているバージョンは下記です。
fastapi==0.79.0
uvicorn==0.18.2
SQLAlchemy==1.4.39
PyMySQL==1.0.2
alembic==1.8.1
1対多
1対多は、1つの親に対して複数の子がいるパターンです。
子テーブルが親テーブルのIDを外部キーとして持ちます。
テーブル定義
親テーブルの定義はこのようになります。
from models.child1 import Child1 # noqa: F401
class Parent1(Base):
__tablename__ = "parent1_table"
id = Column(Integer, primary_key=True)
children = relationship("Child1", backref="parent")
name = Column(String(255))
子テーブルの定義はこのようになります。
class Child1(Base):
__tablename__ = "child1_table"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent1_table.id"))
name = Column(String(255))
公式ドキュメントでは親テーブルと子テーブルを1つのファイルに記載しています。ただ、可読性向上のために1テーブル1ファイルにしたくて、親テーブルと子テーブルのファイルを分けています。
ファイルを分割した場合、いくつかポイントがあります。
1つ目のポイントとして、parent.pyでrelationshipに指定したChildをimportする必要があります。また、linterで警告が出るので無視する設定にしておきます。
２つ目のポイントとして、双方向のリレーションを自動的に組むために、backrefを利用します。backrefの値はアクセス時に利用したい属性名を指定します。双方向のリレーションを自動的に組むにはbackrefを利用するほかにback_populatesを利用する方法もあります。公式ドキュメントによると、backrefよりもback_populatesを使うほうがよさそうです。ただ、ファイルを分割した場合、back_populatesだと循環参照エラーになり、解決できなかったため、backrefを利用しています。
双方向のリレーションとは何かの説明についてはこちらの記事が分かりやすいです。
INSERT
データの追加はこのように書けます。
@router.post("/pair1", tags=["1対多"])
def create_pair1(db: Session = Depends(get_db)):
parent = models_parent1.Parent1(
id=1, children=[models_child1.Child1(id=1, name="child1")], name="parent1"
)
db.add(parent)
db.commit()
return
SELECT
query()には検索条件に応じて親子どちらかのmodelのみ指定します。そうすることで、pydanticの型に直接変換できます。親子両方のmodelを指定するとpydanticの型に変換するのが難しくなります。query()で両方のmodelを指定していなくてもpydanticのmodelで属性名として追加しておけば2つのテーブルの情報がレスポンスボディに入ります。
親テーブルの検索条件を指定する場合
クエリはこのように書けます。
@router.get("/pair1-by-parent-id", response_model=schemas_parent1.Parent1, tags=["1対多"])
def get_pair1_by_parent_id(db: Session = Depends(get_db)):
parent = (
db.query(models_parent1.Parent1)
# db.query(models_parent1.Parent1, models_child1.Child1)
# Child1を指定しないと戻り値の型は<class 'models.parent1.Parent1>
# Child1を指定しないと戻り値の型は<class 'sqlalchemy.engine.row.Row'>
# Child1を指定しないほうがpydanticの型に直接変換できて簡単
# Child1を指定しない場合はjoinは無意味なので指定しない
# .join(models_child1.Child1, models_parent1.Parent1.children)
.filter(models_parent1.Parent1.id == 1).one()
)
return parent
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
from schemas.child1 import Child1
class Parent1(BaseModel):
id: int
name: str
children: list[Child1]
class Config:
orm_mode = True
class Child1(BaseModel):
id: int
name: str
class Config:
orm_mode = True
実際に発行されるSQLは下記のようになります。親テーブルの情報と子テーブルの情報を別々に取得しています。それが許容できない場合は別の方法で実装してください。
SELECT parent1_table.id AS parent1_table_id, parent1_table.name AS parent1_table_name FROM parent1_table WHERE parent1_table.id = 1;
SELECT child1_table.id AS child1_table_id, child1_table.parent_id AS child1_table_parent_id, child1_table.name AS child1_table_name FROM child1_table WHERE 1 = child1_table.parent_id;
子テーブルの検索条件を指定する場合
クエリはこのように書けます。
@router.get("/pair1-by-child-id", response_model=schemas_child4.Child4, tags=["1対多"])
def get_pair1_by_child_id(db: Session = Depends(get_db)):
child = db.query(models_child1.Child1).filter(models_child1.Child1.id == 1).one()
return child
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
class Parent4(BaseModel):
id: int
name: str
class Config:
orm_mode = True
from schemas.parent4 import Parent4
class Child4(BaseModel):
id: int
name: str
parent: Parent4
class Config:
orm_mode = True
多対1
多対1は、1つの子に対して複数の親がいるパターンです。
親テーブルが子テーブルのIDを外部キーとして持ちます。
テーブル定義
親テーブルの定義はこのようになります。
from models.child2 import Child2 # noqa: F401
class Parent2(Base):
__tablename__ = "parent2_table"
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey("child2_table.id"))
child = relationship("Child2", backref="parents")
name = Column(String(255))
子テーブルの定義はこのようになります。
class Child2(Base):
__tablename__ = "child2_table"
id = Column(Integer, primary_key=True)
name = Column(String(255))
ポイントは１対多のときと同じです。
INSERT
データの追加はこのように書けます。
@router.post("/pair2", tags=["多対1"])
def create_pair2(db: Session = Depends(get_db)):
parent2 = models_parent2.Parent2(
id=1, child=models_child2.Child2(id=1, name="child2"), name="parent2"
)
db.add(parent2)
db.commit()
return
SELECT
親テーブルの検索条件を指定する場合
クエリはこのように書けます。
@router.get("/pair2-by-parent-id", response_model=schemas_parent2.Parent2, tags=["多対1"])
def get_pair2_by_parent_id(db: Session = Depends(get_db)):
parent = (
db.query(models_parent2.Parent2)
# Child2を指定しないほうがpydanticの型に直接変換できて簡単
# db.query(models_parent2.Parent2, models_child2.Child2)
.filter(models_parent2.Parent2.id == 1).one()
)
return parent
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
from schemas.child2 import Child2
class Parent2(BaseModel):
id: int
name: str
child: Child2
class Config:
orm_mode = True
class Child2(BaseModel):
id: int
name: str
class Config:
orm_mode = True
子テーブルの検索条件を指定する場合
クエリはこのように書けます。
@router.get(
"/pair2-by-child-id", response_model=schemas_child5.Child5, tags=["多対1"]
)
def get_pair2_by_child_id(db: Session = Depends(get_db)):
child = db.query(models_child2.Child2).filter(models_child2.Child2.id == 1).one()
return child
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
class Parent5(BaseModel):
id: int
name: str
class Config:
orm_mode = True
from schemas.parent5 import Parent5
class Child5(BaseModel):
id: int
name: str
parents: list[Parent5]
class Config:
orm_mode = True
多対多
多対多は、1つの子に対して複数の親、1つの親に対して複数の子がいるパターンです。
テーブル定義
親テーブルの定義はこのようになります。
from models.child3 import Child3 # noqa: F401
from models.association import association_table # noqa: F401
class Parent3(Base):
__tablename__ = "left_table"
id = Column(Integer, primary_key=True)
children = relationship("Child3", secondary=association_table, backref="parents")
name = Column(String(255))
子テーブルの定義はこのようになります。
class Child3(Base):
__tablename__ = "right_table"
id = Column(Integer, primary_key=True)
name = Column(String(255))
中間テーブルの定義はこのようになります。
association_table = Table(
"association_table",
Base.metadata,
Column("left_id", ForeignKey("left_table.id"), primary_key=True),
Column("right_id", ForeignKey("right_table.id"), primary_key=True),
)
INSERT
データの追加はこのように書けます。
@router.post("/pair3", tags=["多対多"])
def create_pair3(db: Session = Depends(get_db)):
# 中間テーブルにも値が入る
parent = models_parent3.Parent3(
id=1, children=[models_child3.Child3(id=1, name="child3")], name="parent3"
)
db.add(parent)
db.commit()
return
SELECT
親テーブルの検索条件を指定する場合
@router.get("/pair3-by-parent-id", response_model=schemas_parent3.Parent3, tags=["多対多"])
def get_pair3_by_parent_id(db: Session = Depends(get_db)):
parent = (
db.query(models_parent3.Parent3).filter(models_parent3.Parent3.id == 1).one()
)
return parent
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
from schemas.child3 import Child3
class Parent3(BaseModel):
id: int
name: str
children: list[Child3]
class Config:
orm_mode = True
class Child3(BaseModel):
id: int
name: str
class Config:
orm_mode = True
実際に発行されるSQLは下記のようになります。WHERE句に結合条件が記載されています。それが許容できない場合は別の方法で実装してください。
SELECT left_table.id AS left_table_id, left_table.name AS left_table_name FROM left_table WHERE left_table.id = 1;
SELECT right_table.id AS right_table_id, right_table.name AS right_table_name FROM right_table, association_table WHERE 1 = association_table.left_id AND right_table.id = association_table.right_id;
子テーブルの検索条件を指定する場合
@router.get("/pair3-by-child-id", response_model=schemas_child6.Child6, tags=["多対多"])
def get_pair3_by_child_id(db: Session = Depends(get_db)):
child = db.query(models_child3.Child3).filter(models_child3.Child3.id == 1).one()
return child
pydanticのモデルはこちら。backrefで指定した値を属性名にします。
class Parent6(BaseModel):
id: int
name: str
class Config:
orm_mode = True
class Child6(BaseModel):
id: int
name: str
parents: list[Parent6]
class Config:
orm_mode = True
また、中間テーブルのカラムがある場合は、Association Objectを使います。
最後に
全体のソースコードはこちらにあります。