FastAPI中基于SQLModel实现三表关联的嵌套响应模型

下面是针对Host、Binary、Map_Products_All三张表的嵌套响应实现步骤，直接用代码示例说明：

1. 确认SQLAlchemy数据库模型关联 #

先确保三张表的ORM关联关系正确定义：

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Host(Base):
    __tablename__ = "host"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    binaries = relationship("Binary", back_populates="host")  # 一对多关联Binary

class Binary(Base):
    __tablename__ = "binary"
    id = Column(Integer, primary_key=True, index=True)
    filename = Column(String, index=True)
    host_id = Column(Integer, ForeignKey("host.id"))
    product_id = Column(Integer, ForeignKey("map_products_all.id"))
    
    host = relationship("Host", back_populates="binaries")  # 反向关联Host
    product = relationship("Map_Products_All", back_populates="binaries")  # 多对一关联产品表

class Map_Products_All(Base):
    __tablename__ = "map_products_all"
    id = Column(Integer, primary_key=True, index=True)
    product_name = Column(String, index=True)
    version = Column(String)
    binaries = relationship("Binary", back_populates="product")  # 反向关联Binary

2. 定义嵌套结构的Pydantic响应模型 #

从最内层的产品表开始，逐层向上嵌套：

from pydantic import BaseModel
from typing import List, Optional

# 产品表响应模型
class MapProductsAllResp(BaseModel):
    id: int
    product_name: str
    version: Optional[str] = None

    class Config:
        orm_mode = True  # 允许直接从ORM模型转换

# Binary响应模型，包含关联的产品信息
class BinaryResp(BaseModel):
    id: int
    filename: str
    product: MapProductsAllResp  # 嵌套产品模型

    class Config:
        orm_mode = True

# Host响应模型，包含带产品信息的Binary列表
class HostResp(BaseModel):
    id: int
    name: str
    binaries: List[BinaryResp]  # 嵌套Binary列表

    class Config:
        orm_mode = True

3. 实现接口查询（预加载关联数据） #

使用SQLAlchemy的joinedload预加载所有关联数据，避免N+1查询问题：

from fastapi import FastAPI, Depends

app = FastAPI()

# 数据库会话依赖
def get_db():
    db = Session()
    try:
        yield db
    finally:
        db.close()

@app.get("/hosts/", response_model=List[HostResp])
def fetch_all_hosts(db: Session = Depends(get_db)):
    # 预加载Host -> Binary -> Map_Products_All的关联数据
    hosts = db.query(Host).options(
        joinedload(Host.binaries).joinedload(Binary.product)
    ).all()
    return hosts

关键说明 #

• orm_mode = True是核心：让Pydantic可以直接解析SQLAlchemy的ORM实例，不需要手动转换字典
• joinedload一次性加载所有层级的关联数据，避免多次查询数据库，提升接口性能
• 嵌套模型的层级必须和数据库的关联关系严格对应，确保数据能正确映射

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