FastAPI SQLAlchemy return data like dictionary

Question

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.

Answer 1

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]