使用Python Databases库从MySQL执行fetch_all时触发ValueError报错

调用database.fetch_all(query)时持续触发以下异常：

Traceback (most recent call last):
  ...
  File "c:\users\nikhi\desktop\upwork\srinath srinath\api_project\venv\lib\site-packages\databases\backends\mysql.py", line 115, in fetch_all
    metadata = CursorResultMetaData(context, cursor.description)
  File "c:\users\nikhi\desktop\upwork\srinath srinath\api_project\venv\lib\site-packages\sqlalchemy\engine\cursor.py", line 164, in __init__
    (
ValueError: not enough values to unpack (expected 5, got 4)

使用的代码如下：

from typing import List

import databases
import sqlalchemy
from fastapi import FastAPI
from pydantic import BaseModel

database = databases.Database(DATABASE_URL)

metadata = sqlalchemy.MetaData()

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("text", sqlalchemy.String(100)),
    sqlalchemy.Column("completed", sqlalchemy.Boolean),
)


engine = sqlalchemy.create_engine(
    DATABASE_URL
)
metadata.create_all(engine)

app = FastAPI()


@app.on_event("startup")
async def startup():
    await database.connect()


@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()


@app.get("/notes/", response_model=List[Note])
async def read_notes():
    query = notes.select()
    return await database.fetch_all(query=query)

问题原因及解决办法 #

该错误源于databases库与SQLAlchemy版本不兼容——SQLAlchemy 2.0+版本的游标描述返回字段数量，和databases库预期的不匹配。

解决方式二选一：

1. 降级SQLAlchemy到1.4.x系列，执行命令：

   pip install sqlalchemy==1.4.49
   

2. 升级databases库到最新兼容版本，执行命令：

   pip install --upgrade databases
   

额外检查：确保DATABASE_URL使用了正确的异步驱动

• MySQL需用mysql+aiomysql://user:password@host/database，且已安装aiomysql：pip install aiomysql
• PostgreSQL需用postgresql+asyncpg://user:password@host/database，且已安装asyncpg：pip install asyncpg

内容的提问来源于stack exchange，提问作者N. Yeshikhin