How to handle aggregated query results with SQLAlchemy, pydantic and FastAPI

I would like to create an API that returns the aggregated values for each department as a response. I am new to FastAPI and pydantic and would appreciate any support. It seems the part where the query results are mapped in pydantic is not working well.

get method in main.py

@app.get("/api/{client_id}", response_model=List[schemas.Overview])
def read_overview(client_id: str, db: Session = Depends(get_db)):
    db_overview = crud.get_overview(db, client_id=client_id)
    if db_overview is None:
        raise HTTPException(status_code=404, detail="Overview not found")
    return db_overview

crud.py Get the data corresponding to the three department codes, 1000001, 1000002, and 2000001, aggregated by client_id and deptCode.

from sqlalchemy.orm import Session
from sqlalchemy import func
from . import models, schemas
import datetime

def get_overview(db: Session, client_id: str):
    text = '1000001,1000002,2000001'
    depts = text.split(',')
    tbl = models.Overview
       overview = db.query(tbl.client_id, tbl.deptCode, tbl.deptName, func.sum(tbl.count), func.sum(tbl.item1), func.sum(tbl.item2), func.sum(tbl.item3), func.sum(tbl.item4), func.sum(tbl.item5), func.sum(tbl.item6)).\
        filter(tbl.client_id==client_id). \
        filter(tbl.deptCode.in_(depts)). \
        group_by(tbl.client_id, tbl.deptCode, tbl.deptName).all()
    # print(“overview”, overview)
    return overview

If I check the result retrieved by QUERY with print(), I can see that three rows have been retrieved as expected.

Result - overview on terminal by using print()

[('C012345', '1000001', 'A地域', Decimal('25'), Decimal('7'), Decimal('0'), Decimal('2'), Decimal('0'), Decimal('0'), Decimal('0')), ('C012345', '1000002', 'Z地域', Decimal('55'), Decimal('15'), Decimal('2'), Decimal('12'), Decimal('0'), Decimal('0'), Decimal('0')), ('C012345', '2000001', 'あ小学校', Decimal('15'), Decimal('5'), Decimal('0'), Decimal('2'), Decimal('0'), Decimal('0'), Decimal('0'))]

However, I get the error message "value is not a valid integer" and "field required", resulting in an error as follows. Error

  File "/Users/junya/mr2edu_server/venv/lib/python3.9/site-packages/fastapi/routing.py", line 137, in serialize_response
    raise ValidationError(errors, field.type_)
pydantic.error_wrappers.ValidationError: 21 validation errors for Overview
response -> 0 -> count
  value is not a valid integer (type=type_error.integer)
response -> 0 -> item1
  field required (type=value_error.missing)
response -> 0 -> item2
  field required (type=value_error.missing)
response -> 0 -> item3
  field required (type=value_error.missing)
response -> 0 -> item4
  field required (type=value_error.missing)
response -> 0 -> item5
  field required (type=value_error.missing)
response -> 0 -> item6
  field required (type=value_error.missing)

…. continued ...

I also show schemas.py and models.py.

schemas.py

from pydantic import BaseModel
import datetime

class Overview(BaseModel):
    client_id: str
    deptCode: str
    deptName: str
    count: int
    item1: int
    item2: int
    item3: int
    item4: int
    item5: int
    item6: int

    class Config:
        orm_mode = True

models.py

from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.orm import relationship

from .database import Base

class Overview(Base):
    __tablename__ = "overview"
    id = Column(String, primary_key=True, index=True)
    client_id = Column(String)
    date = Column(Date)
    deptCode = Column(String)
    deptName = Column(String)
    level = Column(Integer)
    count = Column(Integer)
    item1 = Column(Integer)
    item2 = Column(Integer)
    item3 = Column(Integer)
    item4 = Column(Integer)
    item5 = Column(Integer)
    item6 = Column(Integer)

from Recent Questions - Stack Overflow https://ift.tt/3kAqtn7
https://ift.tt/eA8V8J