FastAPI SQLAlchemy return data like dictionary

Learning FastAPI and SQLAlchemy with video tutorial. In video code working correct.

Code from tutorial:

@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()

When I using this code, I have error "Cannot convert dictionary update sequence element #0 to a sequence"

I tried:

return result.scalars().all()

But I get only id numbers, without another data from strings.

With chain:

@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)
    result = list(chain(*result))
    return result

I get correct results, but without column titles. This information is not fully understood by the user.

I get / I need (this like in video):

         /  [
[        /   {
 4       /    id: 4
 name    /    username: name
 25,     /    age: 25
 7       /   },
 name2   /   {
 31      /    id: 7
]        /    username: name2
         /    age:31
         /   }
         /  ]

How I can get full information from string of DB with column names? I need a list of dictionaries, I think.

I found a solution that helped me. Just changed "return":

@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 [dict(r._mapping) for r in result]

The reason your first example doesn't work is that a FastAPI doesn't know how to convert SQLAlchemy Row objects (which is what result.all() returns a list of). However, the idiomatic way in FastAPI is to define a Pydantic model for the response you want, and then it will use its BaseModel.from_orm method to extract data from your SQLAlchemy objects.

from pydantic import BaseModel

class OperationSchema(BaseModel):
    id: int
    username: str
    age: int

    class Config:
        orm_mode = True


@router.get("/", response_model=list[OperationSchema])
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()

This has the added advantage that the structure of your JSON response will be correct in the autoge of your OpenAPI documentation.

(For future readers, note that I'm using FastAPI 0.99.1 here, which doesn't yet support Pydantic 2.0, which is why I'm still using Pydantic 1.x)