Stripp database response to fit in schema in fastapi

Question

I started to build a wepapp with fastapi and Vue. I started at the backend and tried to get some data from a database. As ORM I use SQLAlchemy. What I am tring to achive is to strip down the DB response to some specific columns.

I am fairly new to the world of webapplications, I will also appreciate some good resources. I find it kind of hard to get started in this topic. Despite there being alt of Tutorials, they just cover how to start a basic site and leave the rest to the docs. But I you are not used to the terminology, its quite easy to get lost there.

Anyway, I have this test setup:

My model for the db is

class System(Base):
__tablename__ = 'system'

id = Column(BIGINT(20), primary_key=True)
name = Column(String(200), nullable=False)
type = Column(String(200), nullable=False)
installed_power = Column(Float(asdecimal=True), nullable=False)
date_of_installation = Column(
    DateTime, nullable=False, server_default=text("current_timestamp()"))
last_changed = Column(DateTime, nullable=False, server_default=text(
    "current_timestamp() ON UPDATE current_timestamp()"))
site_id = Column(ForeignKey('site.id'), nullable=False, index=True)

site = relationship('Site')

and my schema is

class System(BaseModel):        
id: int
name: str
type: str
installed_power: int

In my main.py I am doing this

@app.get("/system", response_model=schemas.System)
def get_systems(db: Session = Depends(get_db)):
    query = crud.get_system(db)
    return query.all()

This dose not work. The error says pydantic.error_wrappers.ValidationError: 1 validation error for System response value is not a valid dict (type=type_error.dict) If I add all db columns to the schema it works obviously

I also tried something like this, but this did not work either.

@app.get("/system", response_model=schemas.System)
def get_systems(db: Session = Depends(get_db)):
    query = crud.get_system(db)
    return query.all()
    res = []
    for row in query:
        system = schemas.System()
        system.id = row.id
        system.name = row.name
        system.type = row.type
        system.installed_power = row.installed_power
        res.append(system)
    return res

Answer 1

WITHOUT orm_mode

In your first example, your response_model expects a schema (System), but you send it query.all() which returns a list containing classes from the model you get. (SQLAlchemy does not return a dictionary, which is what pydantic expects by default).

So, at this point, you have to make a choice, either your endpoint must return ONE object, in which case you should not use query.all() but something like query.one_or_none().

Or you want to return a list of objects and your response_model should be:

from typing import List

@app.get("/system", response_model=List[schemas.System])

All that remains is to format your data so that it corresponds to your schema.

for version with only one data:

@app.get("/system", response_model=schemas.System)
def get_systems(db: Session = Depends(get_db)):
    query = crud.get_system(db).one_or_none()
    return schemas.System(id=query.id,name= query.name, type=query.type, installed_power=query.installed_power)

for version with multiple datas:

from typing import List

def make_response_systems(query):
    result = []
    for data in query:
        result.append(schemas.System(id=query.id,name= query.name, type=query.type, installed_power=query.installed_power))
    return result

@app.get("/system", response_model=List[schemas.System])
def get_systems(db: Session = Depends(get_db)):
    query = crud.get_system(db).all()
    return make_response_systems(query)

There are more aesthetic ways to do this. But I think the example above is a good way to understand how it works.

For the rest, you can look at the orm_mode of pydantics models, which you can find in the FastAPI documentation.

To learn, the FastAPI documentation is very complete and easy to access.

WITH orm_mode

Pydantic's orm_mode will tell the Pydantic model to read the data even if it is not a dict, but an ORM model (or any other arbitrary object with attributes).

class System(BaseModel):        
    id: int
    name: str
    type: str
    installed_power: int

    class Config:
        orm_mode = True

@app.get("/system", response_model=schemas.System)
def get_systems(db: Session = Depends(get_db)):
    query = crud.get_system(db).one_or_none()
    return query

Documentation fastapi orm_mode : https://fastapi.tiangolo.com/tutorial/sql-databases/?h=orm_mode#use-pydantics-orm_mode

Answer 2

As per pydantic docs

ORM Mode (aka Arbitrary Class Instances) Pydantic models can be created from arbitrary class instances to support models that map to ORM objects. To do this: The Config property orm_mode must be set to True. The special constructor from_orm must be used to create the model instance.

we need to add orm_mode to the schema config.

class System(BaseModel):        
  id: int
  name: str
  type: str
  installed_power: int
  
  class Config:
    orm_mode = True

Reference: https://pydantic-docs.helpmanual.io/usage/models/#orm-mode-aka-arbitrary-class-instances