sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres when adding connection via UI

[Bee2A]

Apache Airflow Provider(s)

postgres

Versions of Apache Airflow Providers

apache-airflow-providers-postgres==6.2.0

Apache Airflow version

2.11.0

Operating System

Debian

Deployment

Docker-Compose

Deployment details

FROM apache/airflow:2.11.0-python3.12

What happened

1. Adding a new Posgtres-Connection via the UI and chosing "Postgres" as the Connection Type

2. Retrieving the URI for this connection in a Task with: uri =Connection.get_connection_from_secrets('Test-Postgres').get_uri() results in: 'postgres://my_user:my_pw@my_host:5432/my_db'
3. Using the generated URI ends up in the following exception, because it starts with 'postgres://' and thus is not supported by SQLAlchemy 1.4+
4. Exception:
   File "", line 2, in create_engine
   File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
   return fn(*args, **kwargs)
   ^^^^^^^^^^^^^^^^^^^
   File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 518, in create_engine
   entrypoint = u._get_entrypoint()
   ^^^^^^^^^^^^^^^^^^^
   File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/url.py", line 662, in _get_entrypoint
   cls = registry.load(name)
   ^^^^^^^^^^^^^^^^^^^
   File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 343, in load
   raise exc.NoSuchModuleError(
   sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres

What you think should happen instead

A URI for a Postgres-connection added via the UI should either result in:
'postgresql://my_user:my_pw@my_host:5432/my_db'

or

'postgresql+psycopg2://my_user:my_pw@my_host:5432/my_db'

How to reproduce

Add a Postgres-Connection via UI and use the retrieved uri for a db-connection

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[potiuk]

See the note here:
https://airflow.apache.org/docs/apache-airflow/stable/howto/connection.html#generating-a-connection-uri

The get_uri() method return the connection URI in Airflow format, not a SQLAlchemy-compatible URI. if you need a SQLAlchemy-compatible URI for database connections, use sqlalchemy_url property instead.

If you find any place where it is mentioned that getUri returns SQLAlchemy URI - can you please submit a PR correcting that and copying information above?