FastAPI+SQLAlchemyでリレーションシップを使いこなす

SQLAlchemyはPythonでよく利用されるORMです。ですが、実際に使ってみたところ、リレーションシップの使い方にくせがあり、苦戦しました。特にSELECT時の挙動が分かりにくかったので、調査したことをまとめておくことにしました。

本記事で使用しているバージョンは下記です。

```requirements.txt
fastapi==0.79.0
uvicorn==0.18.2
SQLAlchemy==1.4.39
PyMySQL==1.0.2
alembic==1.8.1
```

# 1対多
1対多は、1つの親に対して複数の子がいるパターンです。
子テーブルが親テーブルのIDを外部キーとして持ちます。

## テーブル定義

親テーブルの定義はこのようになります。

```python:models/parent1.py
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))
```

子テーブルの定義はこのようになります。

```python:models/child1.py
class Child1(Base):
    __tablename__ = "child1_table"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parent1_table.id"))
    name = Column(String(255))
```

[公式ドキュメント](https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html)では親テーブルと子テーブルを1つのファイルに記載しています。ただ、可読性向上のために1テーブル1ファイルにしたくて、親テーブルと子テーブルのファイルを分けています。

ファイルを分割した場合、いくつかポイントがあります。
1つ目のポイントとして、parent.pyでrelationshipに指定したChildをimportする必要があります。また、linterで警告が出るので無視する設定にしておきます。
２つ目のポイントとして、双方向のリレーションを自動的に組むために、backrefを利用します。backrefの値はアクセス時に利用したい属性名を指定します。双方向のリレーションを自動的に組むにはbackrefを利用するほかにback_populatesを利用する方法もあります。[公式ドキュメント](https://docs.sqlalchemy.org/en/14/orm/backref.html
)によると、backrefよりもback_populatesを使うほうがよさそうです。ただ、ファイルを分割した場合、back_populatesだと循環参照エラーになり、解決できなかったため、backrefを利用しています。
双方向のリレーションとは何かの説明についてはこちらの記事が分かりやすいです。

https://qiita.com/1234224576/items/ba66838b32b99cce51d2

## INSERT

データの追加はこのように書けます。

```python:router/pair1.py
@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つのテーブルの情報がレスポンスボディに入ります。

### 親テーブルの検索条件を指定する場合

クエリはこのように書けます。

```python:router/pair1.py
@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で指定した値を属性名にします。

```python:schemas/parent1.py
from schemas.child1 import Child1

class Parent1(BaseModel):
    id: int
    name: str
    children: list[Child1]

    class Config:
        orm_mode = True
```

```python:schemas/child1.py
class Child1(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

実際に発行されるSQLは下記のようになります。親テーブルの情報と子テーブルの情報を別々に取得しています。それが許容できない場合は別の方法で実装してください。

```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;
```

### 子テーブルの検索条件を指定する場合

クエリはこのように書けます。

```python:router/pair1.py
@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で指定した値を属性名にします。


```python:schemas/parent4.py
class Parent4(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

```python:schemas/child4.py
from schemas.parent4 import Parent4


class Child4(BaseModel):
    id: int
    name: str
    parent: Parent4

    class Config:
        orm_mode = True
```

# 多対1

多対1は、1つの子に対して複数の親がいるパターンです。
親テーブルが子テーブルのIDを外部キーとして持ちます。

## テーブル定義

親テーブルの定義はこのようになります。

```python:models/parent2.py
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))
```

子テーブルの定義はこのようになります。

```python:models/child2.py
class Child2(Base):
    __tablename__ = "child2_table"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
```

ポイントは１対多のときと同じです。

## INSERT

データの追加はこのように書けます。

```python:router/pair2.py
@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

### 親テーブルの検索条件を指定する場合

クエリはこのように書けます。

```python:router/pair2.py
@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で指定した値を属性名にします。

```python:schemas/parent2.py
from schemas.child2 import Child2


class Parent2(BaseModel):
    id: int
    name: str
    child: Child2

    class Config:
        orm_mode = True
```

```python:schemas/child2.py
class Child2(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

### 子テーブルの検索条件を指定する場合

クエリはこのように書けます。

```python:router/pair2.py
@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で指定した値を属性名にします。

```python:schemas/parent5.py
class Parent5(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

```python:schemas/child5.py
from schemas.parent5 import Parent5


class Child5(BaseModel):
    id: int
    name: str
    parents: list[Parent5]

    class Config:
        orm_mode = True
```

# 多対多

多対多は、1つの子に対して複数の親、1つの親に対して複数の子がいるパターンです。

## テーブル定義

親テーブルの定義はこのようになります。

```python:models/parent3.py
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))
```

子テーブルの定義はこのようになります。

```python:models/child3.py
class Child3(Base):
    __tablename__ = "right_table"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
```

中間テーブルの定義はこのようになります。

```python:models/association.py
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

データの追加はこのように書けます。

```python:router/pair3.py
@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

### 親テーブルの検索条件を指定する場合

```python:router/pair3.py
@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で指定した値を属性名にします。

```python:schemas/parent3.py
from schemas.child3 import Child3


class Parent3(BaseModel):
    id: int
    name: str
    children: list[Child3]

    class Config:
        orm_mode = True
```

```python:schemas/child3.py
class Child3(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

実際に発行されるSQLは下記のようになります。WHERE句に結合条件が記載されています。それが許容できない場合は別の方法で実装してください。

```sql
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;
```

### 子テーブルの検索条件を指定する場合

```python:router/pair3.py
@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で指定した値を属性名にします。

```python:schemas/parent6.py
class Parent6(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True
```

```python:schemas/child6.py
class Child6(BaseModel):
    id: int
    name: str
    parents: list[Parent6]

    class Config:
        orm_mode = True
```

また、中間テーブルのカラムがある場合は、[Association Object](https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object)を使います。

# 最後に
全体のソースコードはこちらにあります。

https://github.com/shimi7o/fastapi-sqlalchemy-sample