Incompatible with SQLAlchemy 2.0.0

[vladalexeev]

Describe the bug

The library doesn't work with newly issued SQLAlchemy 2.0.0.

To Reproduce

Just import create_engine

from aiopg.sa import create_engine

Expected behavior

Expected successful import of create_engine

Logs/tracebacks

my_package/db.py:7: in <module>
    from aiopg.sa import create_engine
.venv/lib/python3.7/site-packages/aiopg/sa/__init__.py:3: in <module>
    from .engine import Engine, create_engine
.venv/lib/python3.7/site-packages/aiopg/sa/engine.py:16: in <module>
    raise ImportError("aiopg.sa requires sqlalchemy")
E   ImportError: aiopg.sa requires sqlalchemy

Python Version

Python 3.7.3

aiopg Version

Name: aiopg
Version: 1.4.0
Summary: Postgres integration with asyncio.
Home-page: https://aiopg.readthedocs.io
Author: Andrew Svetlov
Author-email: andrew.svetlov@gmail.com
License: BSD
Location: /root/builds/-tRNszhp/0/m7/py3-services/m7-documents/.venv/lib/python3.7/site-packages
Requires: async-timeout, psycopg2-binary

OS

Debian 10

Additional context

No response

Code of Conduct

• [X] I agree to follow the aio-libs Code of Conduct

[Havrushchyk]

Still reproduced -> Ubuntu 22.04 -> sqlalchemy '2.0.3', Python 3.10.6, aiopg 1.4.0

[nicholasrigo93]

Seeing the same. Problem comes from trying to import this:

try:
    from sqlalchemy.dialects.postgresql.psycopg2 import (
        PGCompiler_psycopg2,
        PGDialect_psycopg2,
    )
except ImportError:  # pragma: no cover
    raise ImportError("aiopg.sa requires sqlalchemy")

Here: https://github.com/aio-libs/aiopg/blob/ae2617d7e50ed78f24ad7fb13833614780309596/aiopg/sa/engine.py#L10

But the PGCompiler_psycopg2 object doesn't exist anymore in this script https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/postgresql/psycopg2.py

[alphavector]

I was able to solve this for me with the following patch

def sa_patch():
    """
    sqlalchemy patch to make aiopg.sa work correctly
    """
    import sqlalchemy
    from sqlalchemy.dialects.postgresql import base, psycopg2

    class PGDialect_psycopg2(psycopg2.PGDialect_psycopg2):
        case_sensitive = True
        description_encoding = None

    setattr(sqlalchemy.dialects.postgresql.psycopg2, 'PGCompiler_psycopg2', base.PGCompiler)
    setattr(sqlalchemy.dialects.postgresql.psycopg2, 'PGDialect_psycopg2', PGDialect_psycopg2)

And this function must be called every time before importing

sa_patch()
import aiopg.sa

[athamana]

Are there any news for this issue? I tried alphavector's solution and it worked but official support for sqlalchemy v2 would be much appreciated. Are there any plans for this ?

[Pliner]

Hello,

In the long run, it is better to upgrade(from my point of view) to sqlalchemy native async support, which was introduced in sqlalchemy v1.4 (initially, asyncpg was the only one supported) and continued in sqlachemy v2. Currently, sqlalchemy v2 has built-in async support of asyncpg and psycopg v3.

Aiopg was vital before sqlachemy began to support async, but now it doesn't make much sense for further support.

I am happy to merge any compatibility patches with sqlalchemy v2, but there are no activities from my side to support it.

[Pliner]

@asvetlov https://github.com/aio-libs/aiopg/issues/798#issuecomment-934256346 seems to have the same opinion on that.

[Pliner]

Resolved by #901