psycopg2 isolation_level with create_engine()

300 views
Skip to first unread message

gordon.d...@gmail.com

unread,
May 30, 2021, 10:02:49 AM5/30/21
to sqlalchemy-devel
From my reading of the docs at …


… I would expect this to work

import psycopg2
import sqlalchemy as sa

print(sa.__version__)  # 1.4.17
print(psycopg2.__version__)  # 2.8.6 (dt dec pq3 ext lo64)

connection_uri = "postgresql+psycopg2://scott:tiger@localhost/test?isolation_level=AUTOCOMMIT"
engine = sa.create_engine(
    connection_uri,
    # future=True,
)

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS x")

However, I get

Traceback (most recent call last):
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    self._dec_overflow()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get
    return self._create_connection()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/psycopg2/__init__.py", line 126, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/psycopg2/extensions.py", line 175, in make_dsn
    parse_dsn(dsn)
psycopg2.ProgrammingError: invalid dsn: invalid connection option "isolation_level"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/gord/git/alembic-gerrit/gord_test/gord_test.py", line 15, in <module>
    with engine.begin() as conn:
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2994, in begin
    conn = self.connect(close_with_result=close_with_result)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3215, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2068, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    self._dec_overflow()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get
    return self._create_connection()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/psycopg2/__init__.py", line 126, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)
  File "/home/gord/git/alembic-gerrit/venv/lib/python3.8/site-packages/psycopg2/extensions.py", line 175, in make_dsn
    parse_dsn(dsn)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "isolation_level"

(Background on this error at: http://sqlalche.me/e/14/f405)

Have we broken something?

Mike Bayer

unread,
May 30, 2021, 12:05:20 PM5/30/21
to sqlalche...@googlegroups.com


On Sun, May 30, 2021, at 10:02 AM, gordon.d...@gmail.com wrote:
From my reading of the docs at …


these are referred towards as "keyword arguments" that are accepted by create_engine() directly, they are not part of the URL query string; the URL query string args are parameters that go directly to the DBAPI: https://docs.sqlalchemy.org/en/14/core/engines.html#special-keyword-arguments-passed-to-dbapi-connect

so in your example:


e = create_engine("...", isolation_level="AUTOCOMMIT")


certainly this section and others like it should include more clarification that there are two types of dialect-specific arguments, those accepted by the psycopg2 **dialect** and those accepted by the psycopg2 **dbapi**.

Additionally, the "isolation_level" argument documented there is kind of in limbo right now as most of our documented isolation level use now refers to the "isolation_level" execution option:


that is, create_engine(..., isoaltion_level="autocommit") is sort of useful but not what we document, and issue https://github.com/sqlalchemy/sqlalchemy/issues/6342 is set up to declare isolation_level as "legacy". but im not totally sure we should do that.  we can perhaps rename it "default_isolation_level".    but in any case, keeping it around as you can see adds to what is already a confusing set of configurational layers that nonetheless are all needed.




--
You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.

gordon.d...@gmail.com

unread,
May 30, 2021, 12:44:01 PM5/30/21
to sqlalchemy-devel
Okay, thanks. I was somewhat misled because

connection_uri = "postgresql+psycopg2://scott:tiger@localhost/test?sslmode=require"

actually does work. When I try that against my PostgreSQL 11 Docker instance the connection does get to the point where pg rejects it:

> sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server does not support SSL, but SSL was required

I was hoping that ?isolation_level=AUTOCOMMIT would work because I have some Alembic tests that are failing with CockroachDB (but work with PostgreSQL) and I've narrowed it down to CrDB seeming to want a commit after (some!) ALTER TABLE x ALTER COLUMN statements. I thought that if I could enable autocommit from the connection URI then I could at least see if that made a difference. I can confirm that it does seem to affect the behaviour when I do it via kwarg to create_engine():

import psycopg2
import sqlalchemy as sa

print(sa.__version__)  # 1.4.17
print(psycopg2.__version__)  # 2.8.6 (dt dec pq3 ext lo64)

# connection_uri = "postgresql+psycopg2://scott:tiger@localhost/test"
connection_uri = "cockroachdb+psycopg2://root@localhost:26257/defaultdb"
print(connection_uri)
engine = sa.create_engine(
    connection_uri,
    # future=True,
    # isolation_level="AUTOCOMMIT",
)

# try to mimic test_modify_nullable_to_non() in tests/test_op.py

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS x")
with engine.begin() as conn:
    conn.exec_driver_sql("CREATE TABLE x (columnname varchar(10))")

with engine.begin() as conn:
    insp = sa.inspect(engine)
    col_info = insp.get_columns("x")[0]
    print(f"before ALTER TABLE: nullable is {col_info['nullable']}")
    conn.execute(sa.text("ALTER TABLE x ALTER COLUMN columnname SET NOT NULL"))
    insp = sa.inspect(engine)
    col_info = insp.get_columns("x")[0]
    print(f" after ALTER TABLE: nullable is {col_info['nullable']}")

gives me

before ALTER TABLE: nullable is True
 after ALTER TABLE: nullable is True

but if I uncomment the `isolation_level="AUTOCOMMIT"` I get 

before ALTER TABLE: nullable is True
 after ALTER TABLE: nullable is False

Mike Bayer

unread,
May 30, 2021, 1:01:32 PM5/30/21
to sqlalche...@googlegroups.com
not sure if this is helpful but alembic also includes its own autocommit API for use in migrations:

gordon.d...@gmail.com

unread,
May 30, 2021, 1:54:34 PM5/30/21
to sqlalchemy-devel
Okay, thanks! I think we're on to something here. In "tests/test_op.py" if I modify

    def test_modify_nullable_to_non(self):
        self._run_alter_col({}, {"nullable": False})

to be

    def test_modify_nullable_to_non(self):
        with self.op.get_context().autocommit_block():
            self._run_alter_col({}, {"nullable": False})

then the test passes for both PostgreSQL and CockroachDB.

I suspect that you may not be too keen on just changing the test for everybody, so I suppose that either

1. We could add one or more requirements in Alembic and have the test(s) only use autocommit when the requirement indicates that it is necessary.

2. Or perhaps I could override the test for the CrDB dialect similar to what we do for SQLA suite tests? (But that depends on the tests being visible if somebody just has `pip install alembic`, hence my question re: an Alembic "test suite" on Gitter a couple of days ago).

Mike Bayer

unread,
May 30, 2021, 2:12:12 PM5/30/21
to 'Terrence Brannon' via sqlalchemy-devel
tests within Alembic that do DB work are either testing that the DB operations deliver certain DDL /DML to the database, *or* the tests are specific to how transactions are scoped.    So if we are looking to begin to export some tests to be acceptable for 3rd party, I think it might be fine if the general concept of "backend_requires_autocommit_for_ddl" is introduced, perhaps wtihin requirements.     In the case of this specific test, everything runs through fixtures.py -> _run_alter_col() so the "autocommit" could be set up there, or more generally in this thing we have called "with sqla_compat._ensure_scope_for_ddl(self.conn):", which could perhaps be generalized to consult requirements within the test suite.

this is all based on the assumption that the test suite is more or less testing the same thing if we turn all DDL into "autocommit" for some backends which I htink is fine since lots of backends don't even have transactional DDL and are "autocommit" anyway.
Reply all
Reply to author
Forward
0 new messages