如何使用fastAPI、Pydantic和SQLAlchemy正确设置一对多双向关系

Question

我用的是全栈-fastapi-postgresql，fastapi 0 . 54 . 1版，pydantic版。

我不知道如何设置pydantic，因此它可以在SQLAlchemy中正确地使用多对一双向关系。出于某种原因，我当前的实现会以最大递归错误破坏堆栈。

我知道github的一个讨论，来自_orm()应该在加载SQLAlchemy双向关系时检测循环，而不是吹栈。我相信这是密切相关的，但我不能从中获得任何有用的东西。

任何帮助将不胜感激。

来自 fastAPI 的错误消息：

文件“/usr/local/lib/python 3.7/site-packages/fastapi/encoders . py”，第113行，在JSON able _ encoder sqlalchemy _ safe = sqlalchemy _ safe，文件“/usr/local/lib/python 3.7/site-packages/fastapi/encoders . py”，第166行，在JSON able _ encoder sqlalchemy _ safe = sqlalchemy _ safe，文件“/usr/local/lib/python 3.7/site-packages/fastapi/encoders . py”中

模型：

app/模型/公司. py

from typing import TYPE_CHECKING
from sqlalchemy import Boolean, Column, Integer, String
from sqlalchemy.orm import relationship
from app.db.base_class import Base

if TYPE_CHECKING:
    from .company import Company  # noqa: F401

class Company(Base):
    id = Column(Integer, primary_key=True, index=True)
    enabled = Column(Boolean(), default=True)
    logourl = Column(String, index=True)
    name = Column(String, index=True)
    users = relationship("User", back_populates="company")

应用/模型/用户.py

from typing import TYPE_CHECKING

from sqlalchemy import Column, ForeignKey, Integer, String, Boolean, PrimaryKeyConstraint,UniqueConstraint, DateTime    
from sqlalchemy.orm import relationship

from app.db.base_class import Base

if TYPE_CHECKING:
    from .job import Job  # noqa: F401
    from .company import Company  # noqa: F401


class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    full_name = Column(String, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    is_active = Column(Boolean(), default=True)
    is_superuser = Column(Boolean(), default=False)
    company_id = Column(Integer, ForeignKey("company.id"))
    company = relationship("Company", back_populates="users")

架构：

app/schemas/user.py

from typing import Optional, Any
from pydantic import BaseModel, EmailStr


# Shared properties
class UserBase(BaseModel):
    email: Optional[EmailStr] = None
    is_active: Optional[bool] = True
    is_superuser: bool = False
    full_name: Optional[str] = None
    company_id: Optional[int] = None
    company: Optional[Any] = None


# Properties to receive via API on creation
class UserCreate(UserBase):
    email: EmailStr
    password: str
    company_id: int

# Properties to receive via API on update
class UserUpdate(UserBase):
    password: Optional[str] = None
    company_id: Optional[int] = None


class UserInDBBase(UserBase):
    id: Optional[int] = None

    class Config:
        orm_mode = True


# Additional properties to return via API
class User(UserInDBBase):
    pass


# Additional properties stored in DB
class UserInDB(UserInDBBase):
    hashed_password: str

app/schemas/company.py

from typing import Optional, List
from pydantic import BaseModel
from .user import User


# Shared properties
class CompanyBase(BaseModel):
    enabled: Optional[bool] = None
    logourl: Optional[str] = None
    name: Optional[str] = None
    users: List[User] = None


# Properties to receive on Company creation
class CompanyCreate(CompanyBase):
    name: str


# Properties to receive on Company update
class CompanyUpdate(CompanyBase):
    pass


# Properties shared by models stored in DB
class CompanyInDBBase(CompanyBase):
    id: int
    name: str

    class Config:
        orm_mode = True


# Properties to return to client
class Company(CompanyInDBBase):
    pass


# Properties properties stored in DB
class CompanyInDB(CompanyInDBBase):
    pass

Answer 1

您正在尝试让user架构包含公司，但也希望公司架构包含user，这意味着当您检索特定公司时，例如，其用户将包含同一公司，而该公司将再次包含所有用户，因此存在递归问题。

当检索到用户或company时，您需要决定要显示什么数据，当您只想检索特定的 时，检索公司的完整数据可能没有任何用处。

从UserBase架构中删除公司应该就足够了。