Nested schema is not being picked up from model/database, despite the relationship existing and the data being there #7597
-
First check
DescriptionI have posted this on stackoverflow: https://stackoverflow.com/questions/61724245/fastapi-is-not-picking-up-a-nested-schema-despite-the-data-being-there-in-the-db but here it is again for those who can't follow the link: I get an error when trying to return data stored in a relationship between two models. More info below:
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")
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: AddressRelevant call in @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 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 It seems to think that the address data isn't there. I thought this would be something to do with Please, what am I missing? |
Beta Was this translation helpful? Give feedback.
Replies: 4 comments
-
|
Ok so I updated class Company(CompanyBase):
incorporated : datetime.date
address: Addressto class Company(CompanyBase):
incorporated : datetime.date
address: List[Address]and it works! Hooray. However, I don't want it to display as a list as there will never be more than one address. Is there some other way I can define it? How come I am just getting into FastAPI and love it so far, I just want to make sure I completely understand what's going on behind the scenes. |
Beta Was this translation helpful? Give feedback.
-
|
Wow, it is now also incredibly slow when I try to load multiple companies and their addresses. Is there a better way to structure my |
Beta Was this translation helpful? Give feedback.
-
|
The problem turned out to be in my relationships in models. The SQLAlchemy docs helped me understand it (https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-one) Essentially, in the Company mode, I had to add 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}')>"Marking as closed now. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for reporting back and closing the issue @ChrisKneller 👍 |
Beta Was this translation helpful? Give feedback.
The problem turned out to be in my relationships in models. The SQLAlchemy docs helped me understand it (https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-one)
Essentially, in the Company mode, I had to add
uselist=Falsein the relationship.Marking as closed now.