将SQLAlchemy模型高效转换为JSON：API序列化策略深度解析

import json
from sqlalchemy import ForeignKey, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
from sqlalchemy_serializer import SerializerMixin

# 定义基础模型，并继承SerializerMixin
class Base(DeclarativeBase, SerializerMixin):
    pass

# 定义项目模型
class Project(Base):
    __tablename__ = "projects"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

# 定义用户模型
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    # 定义与项目的关系
    projects: Mapped[list[Project]] = relationship(backref="owner")
    # 序列化规则：停止对projects关联的owner进行递归，避免循环引用
    serialize_rules = ('-projects.owner',)

# 数据库初始化与会话创建
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
session_maker = sessionmaker(bind=engine)

with session_maker() as session:
    # 创建用户和项目数据
    user = User(name="User1")
    user.projects.append(Project(name="Project 1"))
    user.projects.append(Project(name="Project 2"))

    session.add(user)
    session.commit()
    session.refresh(user)

    # 将用户模型序列化为JSON
    print(json.dumps(user.to_dict()))

{"id": 1, "projects": [{"id": 1, "name": "Project 1", "owner_id": 1}, {"id": 2, "name": "Project 2", "owner_id": 1}], "name": "User1"}

from sqlalchemy import ForeignKey, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
from pydantic import BaseModel, ConfigDict

# SQLAlchemy基础模型
class Base(DeclarativeBase):
    pass

# SQLAlchemy模型定义
class Project(Base):
    __tablename__ = "projects"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    projects: Mapped[list[Project]] = relationship(backref="owner")

# Pydantic模型定义（用于API输出）
class ProjectScheme(BaseModel):
    # 允许从任意对象属性读取数据
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str

class UserScheme(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str
    # 嵌套Pydantic模型以处理关系
    projects: list[ProjectScheme]

# 数据库初始化与会话创建
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
session_maker = sessionmaker(bind=engine)

with session_maker() as session:
    # 创建用户和项目数据
    user = User(name="User1")
    user.projects.append(Project(name="Project 1"))
    user.projects.append(Project(name="Project 2"))

    session.add(user)
    session.commit()
    session.refresh(user)

    # 使用Pydantic模型验证并序列化SQLAlchemy对象
    user_json = UserScheme.model_validate(user).model_dump_json()
    print(user_json)

{"id":1,"name":"User1","projects":[{"name":"Project 1","id":1},{"name":"Project 2","id":2}]}

from typing import Optional
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import SQLModel, Field, Relationship

# 定义项目基础模型（Pydantic部分）
class ProjectBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

# 定义项目数据库模型（SQLAlchemy部分，继承ProjectBase）
class Project(ProjectBase, table=True):
    __tablename__ = "projects"
    owner_id: Optional[int] = Field(default=None, foreign_key="users.id")
    # 定义与User的关系
    owner: "User" = Relationship(back_populates="projects")

# 定义用户基础模型（Pydantic部分）
class UserBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

# 定义用户数据库模型（SQLAlchemy部分，继承UserBase）
class User(UserBase, table=True):
    __tablename__ = "users"
    # 定义与Project的关系
    projects: list[Project] = Relationship(back_populates="owner")

# 定义用户输出模型（Pydantic部分，用于API响应，只包含需要输出的字段和关系）
class UserOutput(UserBase):
    projects: list[ProjectBase] = [] # 使用ProjectBase避免循环，并控制输出深度

# 数据库初始化与会话创建
engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
session_maker = sessionmaker(bind=engine)

with session_maker() as session:
    # 创建用户和项目数据
    user = User(name="User1")
    user.projects.append(Project(name="Project 1"))
    user.projects.append(Project(name="Project 2"))

    session.add(user)
    session.commit()
    session.refresh(user)

    # 使用UserOutput模型验证并序列化SQLModel对象
    print(UserOutput.model_validate(user).model_dump_json())

{"id":1,"name":"User1","projects":[{"name":"Project 1","id":1},{"name":"Project 2","id":2}]}