将SQLAlchemy模型转换为自定义Pydantic模型的实现方案

需求说明 #

现有SQLAlchemy模型，需为Program生成包含id和本地化名称name的Pydantic模型，其中name字段对应Program.name关联的Localization下的Translation文本内容，最终响应格式如下：

{
    "id": 1,
    "name": "My localized name"
}

现有SQLAlchemy模型 #

from sqlalchemy import ForeignKey, Mapped, mapped_column
from sqlalchemy.orm import DeclarativeBase, relationship
from typing import List

class Base(DeclarativeBase):
    pass

class Program(Base):
    __tablename__ = "programs"

    id: Mapped[int] = mapped_column(primary_key=True)
    name_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    name: Mapped["Localization"] = relationship(foreign_keys="Program.name_id")

class Language(Base):
    __tablename__ = "languages"

    id: Mapped[str] = mapped_column(primary_key=True)
    name: Mapped[int] = mapped_column()

class Localization(Base):
    __tablename__ = "localizations"

    id: Mapped[int] = mapped_column(primary_key=True)
    translations: Mapped[List["Translation"]] = relationship(back_populates="localization")

class Translation(Base):
    __tablename__ = "translations"

    id: Mapped[int] = mapped_column(primary_key=True)
    localization_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    localization: Mapped["Localization"] = relationship(back_populates="translations")
    text: Mapped[str] = mapped_column()
    language_id: Mapped[str] = mapped_column(ForeignKey("languages.id"))

解决方案 #

可以通过Pydantic的计算字段或字段验证器实现，以下是两种实用方案：

方案1：使用Pydantic v2的computed_field（推荐） #

直接在Pydantic模型中定义计算属性，自动从SQLAlchemy实例提取本地化文本：

from pydantic import BaseModel, computed_field

class ProgramResponse(BaseModel):
    id: int

    @computed_field
    def name(self) -> str:
        # 示例：筛选英文翻译，可根据需求调整语言逻辑
        translation = next((t for t in self.name.translations if t.language_id == 'en'), None)
        return translation.text if translation else ""

    class Config:
        orm_mode = True  # 支持直接从SQLAlchemy ORM实例转换

方案2：使用字段验证器 #

适合需要更灵活逻辑的场景（比如处理空值、多语言切换）：

from pydantic import BaseModel, field_validator, ValidationInfo

class ProgramResponse(BaseModel):
    id: int
    name: str

    @field_validator('name', mode='before')
    def get_localized_name(cls, _, info: ValidationInfo):
        localization = info.data.get('name')
        if not localization or not localization.translations:
            return ""
        # 示例：取第一个翻译文本，可替换为语言筛选逻辑
        return localization.translations[0].text

    class Config:
        orm_mode = True

使用示例 #

从SQLAlchemy查询实例后，直接转换为Pydantic模型：

# 假设db是SQLAlchemy会话
program = db.query(Program).options(
    # 预加载关联数据，避免N+1查询
    joinedload(Program.name).joinedload(Localization.translations)
).first()

response = ProgramResponse.from_orm(program)
print(response.model_dump())
# 输出: {'id': 1, 'name': 'My localized name'}

关键注意事项 #

• 预加载关联数据：查询时需用joinedload或selectinload加载Localization和Translation，避免频繁数据库查询。
• 多语言支持：可在模型中添加language_id参数，或通过上下文传递当前语言，动态筛选对应翻译文本。

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