Issues running databases with SQLAlchemy 1.4.42

[jda5]

Maybe I am being an idiot and have forgot to install a package but I am having the following issue:

I have created an API with FastAPI and databases. When I run my files in a Conda environment with SQLAlchemy 1.4.41 installed there are no issues, but when I switch to a Python Virtual Environment with version 1.4.42, I am returned the following error:

Traceback (most recent call last):
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 404, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/fastapi/routing.py", line 231, in app
    raw_response = await run_endpoint_function(
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
    return await dependant.call(**values)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/./app/endpoints/ingredient.py", line 33, in get_ingredient
    for id_, name in await db.fetch_all(query=SEARCH_INGREDIENT_QUERY, values={'search1': f"{query}%", 'search2': f"% {query}%"})
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/databases/core.py", line 142, in fetch_all
    return await connection.fetch_all(query, values)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/databases/core.py", line 250, in fetch_all
    return await self._connection.fetch_all(built_query)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/databases/backends/mysql.py", line 115, in fetch_all
    metadata = CursorResultMetaData(context, cursor.description)
  File "/Users/jacob/Documents/python/applications/gumbo/fastapi/venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 164, in __init__
    (
ValueError: not enough values to unpack (expected 5, got 4)

Looking at the file venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py on line 164 I can see that:

    def __init__(self, parent, cursor_description):
        context = parent.context
        dialect = context.dialect
        self._tuplefilter = None
        self._translated_indexes = None
        self.case_sensitive = dialect.case_sensitive
        self._safe_for_cache = False

        if context.result_column_struct:   
            (
                result_columns,
                cols_are_ordered,
                textual_ordered,
                ad_hoc_textual,
                loose_column_name_matching,
            ) = context.result_column_struct      # <---- ERROR IS HERE

I decided to print context.result_column_struct and found that it returned ([], True, False, False).

I am not really sure how to go about fixing this. If it helps the modules I have installed in the venv are listed below:

aiomysql==0.1.1
anyio==3.6.1
click==8.1.3
databases==0.6.1
fastapi==0.85.1
h11==0.14.0
httptools==0.5.0
idna==3.4
protobuf==3.20.1
pydantic==1.10.2
PyMySQL==1.0.2
python-dotenv==0.21.0
PyYAML==6.0
sniffio==1.3.0
SQLAlchemy==1.4.42
starlette==0.20.4
typing_extensions==4.4.0
uvicorn==0.18.3
uvloop==0.17.0
watchfiles==0.17.0
websockets==10.3

[jda5]

I fixed the above issue by downgrading SQLAlchemy to version 1.4.41. Might be worth looking into though?

[yetyetanotherusername]

FWIW I'm seeing the exact same issue on python 3.10.7.

[joniumGit]

Broken by this commit on SQLAlchemy:

sqlalchemy/sqlalchemy@c9af2eb

Could be fixed in databases by adding the new option to cursors:

databases/databases/backends/asyncmy.py

Lines 210 to 215 in 385f3fd

	execution_context.result_column_struct = (
	compiled._result_columns,
	compiled._ordered_columns,
	compiled._textual_ordered_columns,
	compiled._loose_column_name_matching,
	)

by changing it to this:

            execution_context.result_column_struct = (
                compiled._result_columns,
                compiled._ordered_columns,
                compiled._textual_ordered_columns,
                compiled._ad_hoc_textual,
                compiled._loose_column_name_matching,
            )

just like in the SQLAlchemy commit

[wiseaidev]

Same here. I was about to open an issue related to the recent SQLAlchemy release that introduced a breaking change in the engine. In my case, I am currently pinning the SQLAlchemy version to 1.4.1 until databases release a new version.

[zzzeek]

hey databases -

it's time to rearchitect databases to use SQLAlchemy's native asyncio feature. That way nothing breaks anymore. You can certainly have all those additional backends and all that still, just make real dialects for them.

It would be a lot of work to get it working but then you would be able to drop huge tracts of code that is currently reinventing what SQLAlchemy already does.

[zzzeek]

that or, at least get a travis / github action job that's testing against SQLAlchemy's current release.

[tomchristie]

[casually spoken (by me):]

"I don't know if there's a good reason for databases to keep on keeping on too much, now.
It filled a gap that doesn't exist anymore."

"I could be wrong."

[tomchristie]

Anyways, regarding this specific issue.

If anyone cares about this, then the first action would be to release a new version of databases that pins SQLAlchemy to a prior version.

[wiseaidev]

It filled a gap that doesn't exist anymore.

I just realized that when I stumbled across the textual-sql section.

[Kludex]

Plan for this:

1. Pin SQLAlchemy <=1.4.41.
2. Release 0.6.2.
3. Accept Fixes breaking changes in SQLAlchemy cursor #513 with the pin change to >=1.4.42.
4. Release 0.7.0.
5. Create a plan for Issues running databases with SQLAlchemy 1.4.42 #512 (comment).

[tomchristie]

Concur with steps 1 & 2.
Haven't reviewed steps 3, 4, 5.
(Over the horizon.)

[Casyfill]

for some reason I am still having same issue on SQLALchemy 1.4.41, Python 3.10
I believe it should be <=1.4.40 instead