Creating an SQLAlchemy engine based on psycopg3

Question

I need to upgrade the following code to some equivalent code based on psycopg version 3:

import psycopg2
from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://', creator=connector)

This psycopg2 URL worked like a charm, but:

import psycopg # v3.1
from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg://', creator=connector)

(I also tried the 'psycopg3' word without success)

returns:

Traceback (most recent call last):

  File "/tmp/ipykernel_1032556/253047102.py", line 1, in <cell line: 1>
    engine = create_engine('postgresql+psycopg://', creator=connector)

  File "<string>", line 2, in create_engine

  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)

  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/create.py", line 534, in create_engine
    entrypoint = u._get_entrypoint()

  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/url.py", line 661, in _get_entrypoint
    cls = registry.load(name)

  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/langhelpers.py", line 343, in load
    raise exc.NoSuchModuleError(

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg

So, how to properly create an SQLAlchemy engine based on psycopg (v3.x)?

My sqlalchemy version is: '1.4.35' (tried version 1.4.40 but face an AttributeError: module 'sqlalchemy' has no attribute 'dialects' error).

psycopg3 doc: https://www.psycopg.org/psycopg3/docs/api/
sqlalchemy doc: https://docs.sqlalchemy.org/en/14/core/engines.html

Garnettgarnette

it isn't supported yet, and will only be availble in the yet to be released sqlalchemy 2.0. see github.com/sqlalchemy/sqlalchemy/issues/6842 – Weathered

This is possible as of SQLAlchemy 2.0.x which just recently released. If you migrate to 2.0 you should be able to use Psycopg3. See release notes here: docs.sqlalchemy.org/en/20/changelog/… – Bluebeard

Answer 1

Just an update to the current answer: Sqlalchemy 2.0 has been released and it does support psycopg3. You need to upgrade to 2.0 to use it.

Note the connection string will have to be changed from postgresql to postgresql+psycopg or SqlAlchemy will (at time of writing) try using psycopg2.

See docs here for more info: https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg

Donndonna

assuming sqla 2 and psycopg3 - how is it meant to be used ? – Everyplace

docs.sqlalchemy.org/en/20/dialects/… – Donndonna

Answer 2

SQLAlchemy does not support psycopg3 in the 1.4.x series. The part after the + symbol is a package inside the sqlalchemy.dialects.postgresql.{engine} where engine is psycopg2, asyncpg or something other.

Other dialects work the same way.

But there is no psycopg nor psycopg3 driver in the 1.4.x series.

Thistledown