FastAPI+SQLAlchemy返回查询结果触发ValueError问题求助

使用FastAPI+SQLAlchemy开发时，查询逻辑能正常输出数据，但返回给前端时触发序列化错误。

核心查询代码 #

def get_events_by_month(db: Session, month: int):    
    sq = db.query(models.Events.title, models.Events.start, over(func.row_number(), partition_by=models.Events.start).label('rn')).subquery()
    print(sq)
    q = db.query(sq.c.title, sq.c.start).where(sq.c.rn <= 3).all()
    print(q)
    return q

注：print(sq)输出正确的查询语句，print(q)能打印出期望的元组列表，但return q时触发报错。

报错信息 #

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/encoders.py", line 322, in jsonable_encoder
    data = dict(obj)
ValueError: dictionary update sequence element #0 has length 38; 2 is required

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/encoders.py", line 327, 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/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/uvicorn/protocols/http/httptools_impl.py", line 411, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 69, in __call__
    return await self.app(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/applications.py", line 1054, in __call__
    await super().__call__(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/applications.py", line 123, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/middleware/errors.py", line 186, in __call__
    raise exc
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/middleware/errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/middleware/cors.py", line 83, in __call__
    await self.app(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 62, in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/routing.py", line 758, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/routing.py", line 778, in app
    await route.handle(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/routing.py", line 299, in handle
    await self.app(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/routing.py", line 79, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/starlette/routing.py", line 74, in app
    response = await func(request)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/routing.py", line 296, in app
    content = await serialize_response(
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/routing.py", line 180, in serialize_response
    return jsonable_encoder(response_content)
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/encoders.py", line 301, in jsonable_encoder
    jsonable_encoder(
  File "/home/mortyar/Desktop/coding projects/calendar-next14-fastapi/backend/lib/python3.10/site-packages/fastapi/encoders.py", line 330, in jsonable_encoder
    raise ValueError(errors) from e
ValueError: [ValueError('dictionary update sequence element #0 has length 38; 2 is required'), TypeError('vars() argument must have __dict__ attribute')]

已尝试的解决方法 #

• 手动将元组转换为字典，未成功
• 定义响应模型但未生效：

class EventsBase(BaseModel):
    title: Optional[str]
    start: Optional[date]

关联代码 #

端点定义 #

@app.get("/events/{month}")
def get_events_by_month(month: int, db: Session = Depends(get_db)):
    return crud.get_events_by_month(db, month)

数据库模型 #

class Events(Base):
    __tablename__ = "events"
    __table_args__ = (UniqueConstraint("start", "url", name="event"),)

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column((VARCHAR(300)))
    start = Column(DATE, index=True, nullable=False)
    end = Column(DATE)
    registry = Column(VARCHAR(50), index=True)
    city = Column(VARCHAR(50))
    location = Column(VARCHAR(10), index=True)
    discipline = Column(VARCHAR(50), index=True)
    url = Column(VARCHAR(500), nullable=False)

解决方案 #

问题本质是SQLAlchemy子查询返回的Row对象无法被FastAPI的jsonable_encoder正确序列化，以下是三种可行的解决方式：

方式一：手动转换为字典列表 #

修改查询函数，将结果逐个转换为字典：

def get_events_by_month(db: Session, month: int):    
    sq = db.query(models.Events.title, models.Events.start, over(func.row_number(), partition_by=models.Events.start).label('rn')).subquery()
    q = db.query(sq.c.title, sq.c.start).where(sq.c.rn <= 3).all()
    return [{"title": item.title, "start": item.start} for item in q]

方式二：正确配置响应模型 #

在端点中明确指定响应模型，并给模型添加ORM兼容配置：

from pydantic import BaseModel, Optional
from datetime import date

class EventsBase(BaseModel):
    title: Optional[str]
    start: Optional[date]

    class Config:
        orm_mode = True  # SQLAlchemy 2.0+也可使用from_attributes=True

# 修改端点，指定响应模型为列表类型
@app.get("/events/{month}", response_model=list[EventsBase])
def get_events_by_month(month: int, db: Session = Depends(get_db)):
    return crud.get_events_by_month(db, month)

方式三：使用SQLAlchemy内置方法转换 #

利用Row对象的_asdict()方法直接转换为字典：

def get_events_by_month(db: Session, month: int):    
    sq = db.query(models.Events.title, models.Events.start, over(func.row_number(), partition_by=models.Events.start).label('rn')).subquery()
    q = db.query(sq.c.title, sq.c.start).where(sq.c.rn <= 3).all()
    return [item._asdict() for item in q]

内容的提问来源于stack exchange，提问作者corgipower