Stripp database response to fit in schema in fastapi

user_15948948 0
Viewed 438

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
fastapi sqlalchemy web-applications python
2 Answers

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

user_129309580

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

user_48599100
Related