FastAPI SQLAlchemy cannot convert dictionary update sequence element #0 to a sequence

Question

I'm trying to return list of operations and getting error

@router.get("/")
async def get_specific_operations(operation_type: str, session: AsyncSession = Depends(get_async_session)):
    query = select(operation).where(operation.c.type == operation_type)
    result = await session.execute(query)
    return result.all()

Error:

ValueError: [TypeError('cannot convert dictionary update sequence element #0 to a sequence'), TypeError('vars() argument must have __dict__ attribute')]

Answer 1

For some simple cases, if you want to return a list of Pydantic models, just use response_model:

response_model receives the same type you would declare for a Pydantic model field, so, it can be a Pydantic model, but it can also be, e.g. a list of Pydantic models, like List[Item].

Just like @sergei klinov mentioned.

For other cases, such as returning a JSONResponse that contains the query results from SQLAlchemy (sqlmodel):

e.g., I used the below code (sqlmodel) to return custom data to the frontend for datatables:

results = session.exec(statement).all()
head= ["headerName1", "headerName2", "headerName3"]
data = {
        "head": [{"title": column} for column in head],
        "rows": [list(result) for result in results]
       }

in the above snippet code, in order to convert the SQLAlchemy Sequence object (results), we can use a List Comprehension (or a for loop) and convert the type to list using list(result).

For cases such as dynamic Pydantic models that are defined at runtime (e.g. within the endpoint), or if you only want to return certain columns:

e.g., In my case (sqlmodel), I only want to select certain columns (fields) and return them with some manipulation.

    results = session.exec(statement).all()
    if results:
        data = []
        for r in results:
            data.append({
                "full_name": f"{r.firstname} {r.lastname}",
                "value": r.value,
                "x": r.x,
                "y": r.y,
            }
            )
        return JSONResponse(content=data, status_code=200)

You can access the object's attributes and append to the list where you want to return.

Note: I am using sqlmodel, as @Eugene said, replace .all() to .scalars().all(), see the difference here in the sqlmodel official documentation