From af1b91626f63e00e11d07ad378d23198abc7f91f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 6 Nov 2021 13:00:43 -0400 Subject: fully support isolation_level parameter in base dialect Generalized the :paramref:`_sa.create_engine.isolation_level` parameter to the base dialect so that it is no longer dependent on individual dialects to be present. This parameter sets up the "isolation level" setting to occur for all new database connections as soon as they are created by the connection pool, where the value then stays set without being reset on every checkin. The :paramref:`_sa.create_engine.isolation_level` parameter is essentially equivalent in functionality to using the :paramref:`_engine.Engine.execution_options.isolation_level` parameter via :meth:`_engine.Engine.execution_options` for an engine-wide setting. The difference is in that the former setting assigns the isolation level just once when a connection is created, the latter sets and resets the given level on each connection checkout. Fixes: #6342 Change-Id: Id81d6b1c1a94371d901ada728a610696e09e9741 --- lib/sqlalchemy/dialects/postgresql/asyncpg.py | 17 +++-------- lib/sqlalchemy/dialects/postgresql/base.py | 39 ++++---------------------- lib/sqlalchemy/dialects/postgresql/pg8000.py | 24 +++++++--------- lib/sqlalchemy/dialects/postgresql/psycopg2.py | 24 +++------------- 4 files changed, 24 insertions(+), 80 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py index 28374ed60..fe1f9fd5a 100644 --- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py +++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py @@ -933,20 +933,11 @@ class PGDialect_asyncpg(PGDialect): "SERIALIZABLE": "serializable", } - def set_isolation_level(self, connection, level): - try: - level = self._isolation_lookup[level.replace("_", " ")] - except KeyError as err: - util.raise_( - exc.ArgumentError( - "Invalid value '%s' for isolation_level. " - "Valid isolation levels for %s are %s" - % (level, self.name, ", ".join(self._isolation_lookup)) - ), - replace_context=err, - ) + def get_isolation_level_values(self, dbapi_conn): + return list(self._isolation_lookup) - connection.set_isolation_level(level) + def set_isolation_level(self, connection, level): + connection.set_isolation_level(self._isolation_lookup[level]) def set_readonly(self, connection, value): connection.readonly = value diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index a00c26e87..7d86ccd01 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -3148,7 +3148,6 @@ class PGDialect(default.DefaultDialect): preparer = PGIdentifierPreparer execution_ctx_cls = PGExecutionContext inspector = PGInspector - isolation_level = None implicit_returning = True full_returning = True @@ -3195,19 +3194,9 @@ class PGDialect(default.DefaultDialect): _supports_create_index_concurrently = True _supports_drop_index_concurrently = True - def __init__( - self, - isolation_level=None, - json_serializer=None, - json_deserializer=None, - **kwargs - ): + def __init__(self, json_serializer=None, json_deserializer=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) - # the isolation_level parameter to the PGDialect itself is legacy. - # still works however the execution_options method is the one that - # is documented. - self.isolation_level = isolation_level self._json_deserializer = json_deserializer self._json_serializer = json_serializer @@ -3247,33 +3236,17 @@ class PGDialect(default.DefaultDialect): ) self.supports_identity_columns = self.server_version_info >= (10,) - def on_connect(self): - if self.isolation_level is not None: - - def connect(conn): - self.set_isolation_level(conn, self.isolation_level) - - return connect - else: - return None - - _isolation_lookup = set( - [ + def get_isolation_level_values(self, dbapi_conn): + # note the generic dialect doesn't have AUTOCOMMIT, however + # all postgresql dialects should include AUTOCOMMIT. + return ( "SERIALIZABLE", "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", - ] - ) + ) def set_isolation_level(self, connection, level): - level = level.replace("_", " ") - if level not in self._isolation_lookup: - raise exc.ArgumentError( - "Invalid value '%s' for isolation_level. " - "Valid isolation levels for %s are %s" - % (level, self.name, ", ".join(self._isolation_lookup)) - ) cursor = connection.cursor() cursor.execute( "SET SESSION CHARACTERISTICS AS TRANSACTION " diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index a94f9dcdb..324007e7e 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -437,6 +437,15 @@ class PGDialect_pg8000(PGDialect): # connection was closed normally return "connection is closed" in str(e) + def get_isolation_level_values(self, dbapi_conn): + return ( + "AUTOCOMMIT", + "READ COMMITTED", + "READ UNCOMMITTED", + "REPEATABLE READ", + "SERIALIZABLE", + ) + def set_isolation_level(self, connection, level): level = level.replace("_", " ") @@ -446,7 +455,7 @@ class PGDialect_pg8000(PGDialect): if level == "AUTOCOMMIT": connection.autocommit = True - elif level in self._isolation_lookup: + else: connection.autocommit = False cursor = connection.cursor() cursor.execute( @@ -455,12 +464,6 @@ class PGDialect_pg8000(PGDialect): ) cursor.execute("COMMIT") cursor.close() - else: - raise exc.ArgumentError( - "Invalid value '%s' for isolation_level. " - "Valid isolation levels for %s are %s or AUTOCOMMIT" - % (level, self.name, ", ".join(self._isolation_lookup)) - ) def set_readonly(self, connection, value): cursor = connection.cursor() @@ -562,13 +565,6 @@ class PGDialect_pg8000(PGDialect): fns.append(on_connect) - if self.isolation_level is not None: - - def on_connect(conn): - self.set_isolation_level(conn, self.isolation_level) - - fns.append(on_connect) - if self._json_deserializer: def on_connect(conn): diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index aadd11059..f62830a0d 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -741,20 +741,11 @@ class PGDialect_psycopg2(PGDialect): "SERIALIZABLE": extensions.ISOLATION_LEVEL_SERIALIZABLE, } - def set_isolation_level(self, connection, level): - try: - level = self._isolation_lookup[level.replace("_", " ")] - except KeyError as err: - util.raise_( - exc.ArgumentError( - "Invalid value '%s' for isolation_level. " - "Valid isolation levels for %s are %s" - % (level, self.name, ", ".join(self._isolation_lookup)) - ), - replace_context=err, - ) + def get_isolation_level_values(self, dbapi_conn): + return list(self._isolation_lookup) - connection.set_isolation_level(level) + def set_isolation_level(self, connection, level): + connection.set_isolation_level(self._isolation_lookup[level]) def set_readonly(self, connection, value): connection.readonly = value @@ -798,13 +789,6 @@ class PGDialect_psycopg2(PGDialect): fns.append(on_connect) - if self.isolation_level is not None: - - def on_connect(conn): - self.set_isolation_level(conn, self.isolation_level) - - fns.append(on_connect) - if self.dbapi and self.use_native_uuid: def on_connect(conn): -- cgit v1.2.1