SQLAlchemy模型到JSON _大数据知识库

我正在为前端和后端之间的通信做一个API，在服务器端，我让python获取sqlalchemy模型并将其发送回去，问题是我找不到一种方法将它们转换为JSON，这样我就可以将它们发送回去。
我确实看到了How to serialize SqlAlchemy result to JSON?，但是这个线程已经有12年的历史了。我试着把这个模型输出为字典，就像这个建议的那样;

class User:
   def as_dict(self):
       return {c.name: getattr(self, c.name) for c in self.__table__.columns}

字符串
但是我的模型是继承的，它只输出模型直接拥有的列（字段），它不输出我需要的继承列。
我如何读取一个sqlalchemy模型对象并将其输出为JSON及其所有继承字段？

有几种方法可以做到这一点：

1）使用SQLAlchemy-serializer mixin

import json
from sqlalchemy import ForeignKey, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
from sqlalchemy_serializer import 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")
    serialize_rules = ('-projects.owner',)  # Specify where to stop recursion

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)

    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"}

型

2）使用Pydantic

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

class Base(DeclarativeBase):
    pass

# SQLAlchemy models
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 models
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
    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)

    user_dict = UserScheme.model_validate(user).model_dump_json()

    print(user_dict)

型
输出量：

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

型

3）使用SQLModel

使用SQLModel，您可以减少模型中的冗余：

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

class ProjectBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

class Project(ProjectBase, table=True):
    __tablename__="projects"
    owner_id: Optional[int] = Field(default=None, foreign_key="users.id")
    owner: "User" = Relationship(back_populates="projects")

class UserBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

class User(UserBase, table=True):
    __tablename__="users"
    projects: list[Project] = Relationship(back_populates="owner")

class UserOutput(UserBase):
    projects: list[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)

    print(UserOutput.model_validate(user).model_dump_json())

型
输出量：

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

型

SQLAlchemy模型到JSON

1条答案

相关问题

热门标签

最新问答