Problem return columns or entifies with sqlalchemy v2.0

[dxeox]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the FastAPI documentation, with the integrated search.
• I already searched in Google "How to X in FastAPI" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to FastAPI but to Pydantic.
• I already checked if it is not related to FastAPI but to Swagger UI.
• I already checked if it is not related to FastAPI but to ReDoc.

Commit to Help

• I commit to help with one of those options 👆

Example Code

router.py

from fastapi import APIRouter, Depends, HTTPException,status

import database
from proveedores import repository, schemas
from sqlalchemy.orm import Session
from functions import normalizar_respuesta

router = APIRouter(
    prefix="/proveedores",
    tags=['proveedores']
)


@router.get('/ver_proveedores')
def ver_proveedores(db: Session = Depends(database.get_db)):
    proveedores = repository.find_all(db)
    return proveedores


//////////////////////////////////////////////////////////////////////////////////////////
model.py

class Proveedores(Base):
    __tablename__ = 'proveedores'

    id_proveedor = Column(Integer,
                          primary_key=True,
                          server_default=text("nextval('proveedores_id_proveedor_seq'::regclass)"))
    nombre_proveedor = Column(String(50), nullable=False)
    representante_proveedor = Column(String(50), nullable=False)
    ciudad_proveedor = Column(String(50), nullable=False)
    ruc_proveedor = Column(String(13), nullable=False, unique=True)
    direccion_proveedor = Column(String(100), nullable=False)
    telefono_proveedor = Column(String(13), nullable=False)
    correo_proveedor = Column(String(50), nullable=False)
    banco_proveedor = Column(String(50), nullable=False)
    tipoCuenta_proveedor = Column(Enum('Corriente', 'Ahorros', name='dominio_tipo_cuenta_proveedor'), nullable=False)
    numeroCuenta_proveedor = Column(String(13), nullable=False)
    estado_proveedor = Column(Boolean, nullable=False)
    fechaActualizacion_proveedor = Column(DateTime, nullable=False)
    fechaBorrado_proveedor = Column(DateTime)

///////////////////////////////////////////////////////////////////////////////////////
repository.py

import datetime

from fastapi import HTTPException, status
from sqlalchemy import or_, select
from sqlalchemy.orm import Session

from models import Proveedores as modelProveedores
from . import schemas


def find_all(db: Session):
    proveedores = db.query(modelProveedores).all()
    if not proveedores:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail='No existen proveedores')
    else:
        return proveedores

***************************************************************************************
Return server:

Traceback (most recent call last):
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/encoders.py", line 152, in jsonable_encoder
    data = dict(obj)
ValueError: dictionary update sequence element #0 has length 8; 2 is required

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/encoders.py", line 157, in jsonable_encoder
    data = vars(obj)
TypeError: vars() argument must have __dict__ attribute

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 428, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/applications.py", line 276, in __call__
    await super().__call__(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/middleware/cors.py", line 83, in __call__
    await self.app(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
    raise exc
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/routing.py", line 276, in handle
    await self.app(scope, receive, send)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/starlette/routing.py", line 66, in app
    response = await func(request)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/routing.py", line 255, in app
    content = await serialize_response(
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/routing.py", line 152, in serialize_response
    return jsonable_encoder(response_content)
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/encoders.py", line 131, in jsonable_encoder
    jsonable_encoder(
  File "/home/dxeox/Desarrollos/COMPUNEX/BACKEND/Comercio/micro_comercio/venv/lib/python3.10/site-packages/fastapi/encoders.py", line 160, in jsonable_encoder
    raise ValueError(errors) from e
ValueError: [ValueError('dictionary update sequence element #0 has length 8; 2 is required'), TypeError('vars() argument must have __dict__ attribute')]

Description

When I make a request I get the following error on the server:

ValueError: [ValueError('dictionary update sequence element #0 has length 8; 2 is required'), TypeError('vars() argument must have dict attribute')]

Checking why this happens is since version 2.0 since I tested the same project and had no error with version 1.4. Any solution please?
My solution was to transform with the "_asdict()" method in the encoders.py file, but I don't know if it will be the most optimal since it seems that in cases when you ask for a column, sqlalchemy returns a tuple.

Operating System

Linux

Operating System Details

No response

FastAPI Version

0.96

Python Version

3.10

Additional Context

No response

[jgould22]

You can use a mixin to add an asdict method like what is described here https://stackoverflow.com/questions/73146024/sqlalchemy-method-to-get-orm-object-as-dict

I would however recommend against it as you generally don't want to return the model directly.

When you do that there is the chance that you will inadvertently change the response whenever your model changes and this can break api comparability. Your current model there only has integers and strings but it would be annoying if a value that is not so trivially converted to json were to be added like bytes (though you could handle that in your asdict mixin as mentioned).

[chrisK824]

Hi @dxeox , have you tried to cast that sqlachemy result into a list like that?
proveedores = list(db.query(modelProveedores).all())

[dxeox]

Hi, yes I tried but it didn't work

[dxeox]

One solution i found is to replace the following line in fastapi's encoders.py file:

replace this:
try:
data = dict(obj)
for:
try:
data = dict(obj._asdict())

[kufelkamil]

I had the same issue, change
proveedores = db.query(modelProveedores).all()
to
proveedores = db.query(modelProveedores).mappings().all()

source: https://stackoverflow.com/a/58660606/2704705

[Vasili-K]

Thanks, worked for me