FastAPI is not picking up a nested schema despite the data being there in the DB/model

Viewed 5204

I get an error when trying to return data stored in a relationship between two models. More info below:

models.py (relevant models are Company and 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

Relevant call in 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()

Now as far as I can tell from the docs I have got everything set up fine. When I remove address: Address from the Company schema this call will return the correct company/companies without the address data.

I have checked that the relevant address data is associated with the company model by testing

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

So I know that the data is in the addresses table, the relationship is set up correctly, and the model and schema works before trying to include the address data in the result. However when I try to access http://127.0.0.1:8000/companies?number=12544331 I get Internal Server Error and the following error message:

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)

It seems to think that the address data isn't there. I thought this would be something to do with orm_mode in schemas.py as there is lazyloading but I have orm_mode set to True to avoid this (see https://fastapi.tiangolo.com/tutorial/sql-databases/#technical-details-about-orm-mode).

Please, what am I missing?

1 Answers

It turns out it was due to my relationship definition in models.py. The SQLAlchemy docs explained it here: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-one

Adjusted code:

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

Notice the used of uselist=False

Related