FastAPI中AppUser与Family表关联配置问题求助

我是FastAPI新手，创建了AppUser和Family两个SQLAlchemy模型及对应的Pydantic Schema，现在需要实现两个需求：

1. 将关联家庭的字段设置为非空
2. 返回关联的Family表数据（期望是数组格式）

现有代码 #

SQLAlchemy 模型 #

# AppUser
class AppUser(Base):
    __tablename__ = 'Users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)
    password = Column(String)
    birth = Column(Integer)
    age = Column(Integer)
    gender = Column(String)
    quest_role = Column(Boolean)
    family_id = Column(Integer, ForeignKey('Families.id'))
    last_login = Column(DateTime)
    createdAt = Column(DateTime)
    updatedAt = Column(DateTime)
    publishedAt = Column(DateTime)

    family = relationship("Family", back_populates="users")

# Families
class Family(Base):
    __tablename__ = 'Families'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    createdAt = Column(DateTime)
    updatedAt = Column(DateTime)

    users = relationship("AppUser", back_populates="family")

Pydantic Schema #

# =================================Family===========================================
# Create Family Schema (Pydantic Model)
class FamilyCreate(BaseModel):
    name: str
    users: int

# Complete Family Schema (Pydantic Model)
class Family(BaseModel):
    id: int
    name: str
    createdAt: datetime = None
    updatedAt: datetime = None

    class Config:
        orm_mode = True

# =================================App_User=========================================== 
# Create appUser Schema (Pydantic Model)
class AppUserCreate(BaseModel):
    name: str
    email: str
    password: str
    birth: int
    age: int
    gender: str
    quest_role: bool

# Complete appUser Schema (Pydantic Model)
class AppUser(BaseModel):
    id: int
    name: str
    email: str
    password: str
    birth: int
    age: int
    gender: str
    quest_role: bool
    family_id: List[Family] = None
    last_login: datetime = None
    createdAt: datetime = None
    updatedAt: datetime = None
    publishedAt: datetime = None

    class Config:
        orm_mode = True

当前输出 #

[
  {
    "id": 1,
    "name": "string",
    "email": "string",
    "password": "string",
    "birth": 0,
    "age": 0,
    "gender": "string",
    "quest_role": true,
    "family_id": null,
    "last_login": "2023-06-23T14:10:34.120964",
    "createdAt": "2023-06-23T14:10:34.120964",
    "updatedAt": "2023-06-23T14:10:34.120964",
    "publishedAt": "2023-06-23T14:10:34.120964"
  }
]

期望输出 #

[
  {
    "id": 0,
    "name": "string",
    "email": "string",
    "password": "string",
    "birth": 0,
    "age": 0,
    "gender": "string",
    "quest_role": true,
    "family_id": [
      {
        "id": 0,
        "name": "string",
        "createdAt": "2023-06-23T06:56:22.730Z",
        "updatedAt": "2023-06-23T06:56:22.730Z"
      }
    ],
    "last_login": "2023-06-23T06:56:22.730Z",
    "createdAt": "2023-06-23T06:56:22.730Z",
    "updatedAt": "2023-06-23T06:56:22.730Z",
    "publishedAt": "2023-06-23T06:56:22.730Z"
  }
]

解决方案 #

1. 调整关联关系模型 #

从期望输出看，你需要一个用户对应多个家庭的多对多关系，当前模型是多对一（一个用户属于一个家庭），需先修改模型：

多对多模型修改

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

# 多对多中间表
user_family_association = Table(
    'user_family', Base.metadata,
    Column('user_id', Integer, ForeignKey('Users.id'), primary_key=True),
    Column('family_id', Integer, ForeignKey('Families.id'), primary_key=True)
)

class AppUser(Base):
    __tablename__ = 'Users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)
    password = Column(String)
    birth = Column(Integer)
    age = Column(Integer)
    gender = Column(String)
    quest_role = Column(Boolean)
    last_login = Column(DateTime)
    createdAt = Column(DateTime)
    updatedAt = Column(DateTime)
    publishedAt = Column(DateTime)

    # 替换原有单家庭关联，改为多家庭关联
    families = relationship("Family", secondary=user_family_association, back_populates="users")

class Family(Base):
    __tablename__ = 'Families'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    createdAt = Column(DateTime)
    updatedAt = Column(DateTime)

    # 替换原有单用户关联，改为多用户关联
    users = relationship("AppUser", secondary=user_family_association, back_populates="families")

如果实际需求是一个用户属于一个家庭（多对一），只需修改family_id字段为非空：

family_id = Column(Integer, ForeignKey('Families.id'), nullable=False)

2. 修正Pydantic Schema #

多对多场景

调整AppUser的Schema，把family_id: List[Family] = None改为非空的家庭列表字段（命名建议用families，若坚持用family_id也可）：

# Complete appUser Schema (Pydantic Model)
class AppUser(BaseModel):
    id: int
    name: str
    email: str
    password: str
    birth: int
    age: int
    gender: str
    quest_role: bool
    families: List[Family]  # 非空，返回关联的家庭列表
    last_login: datetime = None
    createdAt: datetime = None
    updatedAt: datetime = None
    publishedAt: datetime = None

    class Config:
        orm_mode = True

多对一场景

调整AppUser的Schema，明确family_id为非空，并添加关联的家庭对象：

# Complete appUser Schema (Pydantic Model)
class AppUser(BaseModel):
    id: int
    name: str
    email: str
    password: str
    birth: int
    age: int
    gender: str
    quest_role: bool
    family_id: int  # 非空，去掉默认值
    family: Family  # 返回关联的家庭对象
    last_login: datetime = None
    createdAt: datetime = None
    updatedAt: datetime = None
    publishedAt: datetime = None

    class Config:
        orm_mode = True

3. 查询时加载关联数据 #

FastAPI查询时需显式加载关联模型，否则SQLAlchemy不会自动查询关联表，导致返回空值：

多对多场景

from sqlalchemy.orm import selectinload
from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session
from .models import AppUser
from .schemas import AppUser as AppUserSchema
from .database import get_db

app = FastAPI()

@app.get("/users/", response_model=List[AppUserSchema])
def get_users(db: Session = Depends(get_db)):
    # 加载关联的families数据
    users = db.query(AppUser).options(selectinload(AppUser.families)).all()
    return users

多对一场景

@app.get("/users/", response_model=List[AppUserSchema])
def get_users(db: Session = Depends(get_db)):
    users = db.query(AppUser).options(selectinload(AppUser.family)).all()
    return users

4. 数据插入处理（多对多场景） #

创建用户时需关联已有的家庭：

@app.post("/users/", response_model=AppUserSchema)
def create_user(user_data: AppUserCreate, family_ids: List[int], db: Session = Depends(get_db)):
    db_user = AppUser(**user_data.dict())
    # 根据ID查询家庭并关联
    families = db.query(Family).filter(Family.id.in_(family_ids)).all()
    db_user.families = families
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

内容的提问来源于stack exchange，提问作者Kid