Fastapi pydantic validation: return items grouped by day

Question

I have a FastAPI application. I would like to provide an overview per day of the total steps someone is doing.

The database table contains following information:

id  activity     client_ip    duration  steps   date
--------------------------------------------------------------
8   Activity2    A.B.C.D      1         200     2021-12-11 13:45:00.887810
7   Activity1    A.B.C.D      1         100     2021-12-11 13:45:00.887810
6   Activity1    A.B.C.D      1         100     2021-12-10 13:43:58.887698
5   Activity2    K.L.M.N      2         400     2021-12-10 13:42:57.061950
4   Activity1    K.L.M.N      1         100     2021-12-09 13:34:36.951690
3   Activity4    A.B.C.D      23        500     2021-12-09 12:33:46.405082
2   Activity1    K.L.M.N      12        1200    2021-12-09 12:33:33.989297
1   Activity1    A.B.C.D      10        1000    2021-12-09 12:33:33.989297

I want to write a SQLAlchemy query that returns the total amount of steps (across all activities) per day per client. So for client A.B.C.D, it would look like:

day          sum
----------------
2021-12-11   300
2021-12-10   100
2021-12-09   1500

To achieve this, I have written the below query:

def total_steps_per_day_per_client_ip(db, client_ip):
    entries = (
        db.query(func.strftime("%Y-%m-%d", models.Entry.date), func.sum(models.Entry.total_steps).label("sum"))
        .filter_by(client_ip=client_ip)
        .group_by(func.strftime("%Y-%m-%d", models.Entry.date))
    ).all()

    return entries

This function returns [('2021-12-09', 150), ('2021-12-10', 100), ('2021-12-11', 300)]

I would like to return the below json object to my Vue client:

{
   day: 2021-12-11
   total_steps: 300
},
{
   day: 2021-12-10
   total_steps: 100
},
{
   day: 2021-12-09
   total_steps: 150
}

Therefore I have the following Fastapi route:

@app.get("/steps",response_model=schemas.TotalSteps)
async def steps(request: Request, db: Session = Depends(get_db)):
    totalsteps_perday = total_steps_per_day_per_client_ip(db, request.client.host)
    return totalsteps_perday

and in the schema's file I have defined TotalSteps as follows:

class TotalSteps(BaseModel):
    day: str
    totalsteps: int

When I hit the /steps URL, I'm getting pydantic validation errors:

pydantic.error_wrappers.ValidationError: 2 validation errors for TotalSteps 
response -> day   
  field required (type=value_error.missing) 
response -> totalsteps   
  field required (type=value_error.missing)

Maybe this question is related: https://stackoverflow.com/questions/2828248/sqlalchemy-returns-tuple-not-dictionary — Hernán Alarcón, Dec 11 '21 at 22:14

score 0 · Answer 1 · answered Dec 12 '21 at 12:02

All you need is to add proper label() to each func in your query:

    entries = (
        db.query(
            func.strftime("%Y-%m-%d", models.Entry.date).label("day"), 
            func.sum(models.Entry.total_steps).label("totalsteps"))
        .filter_by(client_ip=client_ip)
        .group_by(func.strftime("%Y-%m-%d", models.Entry.date))
    ).all()

FastAPI internally converts each result row to dict based on the labels or the column names by default and with your code it results in:

{
   'date': '2021-12-11',
   'sum': 300,
}

That's why pydantic model gives errors regarding missing fields

but you need it to be actually:

{
   'day': '2021-12-11',
   'totalsteps': 300,
}

The Fool · Answer 2 · 2021-12-12T12:54:28.970

You can provide a List as response model. I think that's what you want here.

from typing import List
@app.get("/steps", response_model=List[schemas.TotalSteps])

If you now return a list of data from the handler, FastAPI will know how to serialize it into a JSON array.

You may also need to take additional configuration steps for your model

from pydantic import BaseModel, Field
from datetime import date

class TotalSteps(BaseModel):
    date: date = Field(..., alias='day')
    sum: int = Field(..., alias='total_steps')

   class Config:
        allow_population_by_field_name = True

Or you use directly the correct labels in your query function.

Fastapi pydantic validation: return items grouped by day

2 Answers2