11

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

swiss_knight
  • 5,787
  • 8
  • 50
  • 92
  • 4
    it isn't supported yet, and will only be availble in the yet to be released sqlalchemy 2.0. see https://github.com/sqlalchemy/sqlalchemy/issues/6842 – toppk Sep 04 '22 at 00:44
  • 1
    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: https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#dialect-support-for-psycopg-3-a-k-a-psycopg – Jhirschibar Feb 15 '23 at 15:21

2 Answers2

7

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

saolof
  • 1,097
  • 1
  • 15
  • 12
4

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.

Fran
  • 181
  • 8