python - 尽管数据库/模型中存在数据，FastAPI 并未选择嵌套模式

Question

尝试返回存储在两个模型之间的关系中的数据时出现错误。更多信息如下:
models.py(相关模型为 Company 和 Address )

from datetime import datetime

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column, Integer, String, Date, Sequence, ForeignKey, DateTime

try:
    from .functions import to_camelcase
except:
    from functions import to_camelcase


Base = declarative_base()


class ToDictMixin(object):
    def to_dict(self, camelcase=True):
        if camelcase:
            return {to_camelcase(column.key): getattr(self, attr) for attr, column in self.__mapper__.c.items()}
        else:
            return {column.key: getattr(self, attr) for attr, column in self.__mapper__.c.items()}


class TimestampMixin(object):
    record_created = Column('record_created', DateTime, default=datetime.now())


class Company(Base, ToDictMixin, TimestampMixin):
    __tablename__ = 'companies'

    number = Column(Integer, primary_key=True)
    name = Column(String)
    incorporated = Column(Date)

    address = relationship("Address", back_populates="occupier")

    def __repr__(self):
        return f"<Company(number='{self.number}', name='{self.name}', incorporated='{self.incorporated.isoformat}')>"


class Address(Base, ToDictMixin, TimestampMixin):
    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)
    address_line1 = Column(String)
    address_line2 = Column(String)
    address_line3 = Column(String)
    po_box = Column(String)
    post_town = Column(String)
    county = Column(String)
    postcode = Column(String)
    country = Column(String)
    occupier_id = Column(Integer, ForeignKey("companies.number"))

    occupier = relationship("Company", back_populates="address")

schemas.py

import datetime

from pydantic import BaseModel, BaseConfig
from typing import List

from functions import to_camelcase


class APIBase(BaseModel):
    class Config(BaseConfig):
        orm_mode = True
        alias_generator = to_camelcase
        allow_population_by_field_name = True


class AddressBase(APIBase):
    address_line1 : str
    postcode: str


class AddressCreate(AddressBase):
    pass


class Address(AddressBase):
    address_line2 : str
    address_line3 : str
    po_box : str
    post_town : str
    county : str
    postcode : str
    country : str


class CompanyBase(APIBase):
    number: int
    name: str


class CompanyCreate(CompanyBase):
    incorporated : datetime.date


class Company(CompanyBase):
    incorporated : datetime.date
    address: Address

main.py 中的相关调用:

@app.get("/companies", response_model=List[schemas.Company])
def get_companies(
    year: int = None, month: int = None, day: int = None, number: int = None, 
    name: str = None, db: Session = Depends(get_db)):

    name = name.upper() if name else None

    arguments = locals()
    arguments.pop("db")

    if not any(arguments.values()):
        return None

    myquery = db.query(models.Company)
    datedict = {}

    for key, value in arguments.items():
        if key == "number" and datedict:
            myquery = crud.get_company_by_date(db, **datedict)

        if not value:
            continue

        if key == 'year' or key == 'month' or key == 'day':
            datedict[key] = value
        else:
            myquery = crud.filter_query(myquery, **{key:value})

    return myquery.all()

现在，据我从文档中可以看出，我已经把一切都设置好了。当我从 Company 架构中删除 address: Address 时，此调用将返回正确的公司/公司，而没有地址数据。

我已经通过测试检查相关地址数据是否与公司模型相关联

>>> x = SESSION.query(Company).filter_by(number=12544331).one_or_none()
>>> x.address[0].address_line1
'4 VICTORIA COURT'

所以我知道数据在地址表中，关系设置正确，模型和模式在尝试将地址数据包含在结果中之前工作。但是，当我尝试访问 http://127.0.0.1:8000/companies?number=12544331 时，我收到内部服务器错误和以下错误消息:

INFO:     127.0.0.1:50928 - "GET /companies?number=12544331 HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 384, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\fastapi\applications.py", line 149, in __call__
    await super().__call__(scope, receive, send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\applications.py", line 102, in __call__
    await self.middleware_stack(scope, receive, send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc from None
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc from None
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\routing.py", line 550, in __call__
    await route.handle(scope, receive, send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\starlette\routing.py", line 41, in app
    response = await func(request)
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\fastapi\routing.py", line 204, in app
    response_data = await serialize_response(
  File "c:\users\admin\google~1\python\new_co~1\env\lib\site-packages\fastapi\routing.py", line 126, in serialize_response
    raise ValidationError(errors, field.type_)
pydantic.error_wrappers.ValidationError: 8 validation errors for Company
response -> 0 -> address -> addressLine1
  field required (type=value_error.missing)
response -> 0 -> address -> postcode
  field required (type=value_error.missing)
response -> 0 -> address -> addressLine2
  field required (type=value_error.missing)
response -> 0 -> address -> addressLine3
  field required (type=value_error.missing)
response -> 0 -> address -> poBox
  field required (type=value_error.missing)
response -> 0 -> address -> postTown
  field required (type=value_error.missing)
response -> 0 -> address -> county
  field required (type=value_error.missing)
response -> 0 -> address -> country
  field required (type=value_error.missing)

似乎认为地址数据不存在。我认为这与 orm_mode 中的 schemas.py 有关系，因为有延迟加载，但我将 orm_mode 设置为 True 以避免这种情况(请参阅 https://fastapi.tiangolo.com/tutorial/sql-databases/#technical-details-about-orm-mode )。

请问，我错过了什么？

Answer 1

事实证明这是由于我在 models.py 中的关系定义。 SQLAlchemy 文档在这里解释了它:https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-one

调整后的代码:

class Company(Base, ToDictMixin, TimestampMixin):
    __tablename__ = 'companies'

    number = Column(Integer, primary_key=True)
    name = Column(String)
    incorporated = Column(Date)

    address = relationship("Address", uselist=False, back_populates="occupier")

    def __repr__(self):
        return f"<Company(number='{self.number}', name='{self.name}', incorporated='{self.incorporated.isoformat}')>"

注意 uselist=False 的使用