diff options
Diffstat (limited to 'lib/sqlalchemy')
21 files changed, 290 insertions, 226 deletions
diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py index 9661015ad..411985b5d 100644 --- a/lib/sqlalchemy/connectors/pyodbc.py +++ b/lib/sqlalchemy/connectors/pyodbc.py @@ -172,6 +172,9 @@ class PyODBCConnector(Connector): ] ) + def get_isolation_level_values(self, dbapi_conn): + return super().get_isolation_level_values(dbapi_conn) + ["AUTOCOMMIT"] + def set_isolation_level(self, connection, level): # adjust for ConnectionFairy being present # allows attribute set e.g. "connection.autocommit = True" diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 0077f7fa1..974cae4f7 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2725,7 +2725,6 @@ class MSDialect(default.DefaultDialect): query_timeout=None, use_scope_identity=True, schema_name="dbo", - isolation_level=None, deprecate_large_types=None, json_serializer=None, json_deserializer=None, @@ -2748,7 +2747,6 @@ class MSDialect(default.DefaultDialect): super(MSDialect, self).__init__(**opts) - self.isolation_level = isolation_level self._json_serializer = json_serializer self._json_deserializer = json_deserializer @@ -2771,14 +2769,10 @@ class MSDialect(default.DefaultDialect): ] ) + def get_isolation_level_values(self, dbapi_conn): + return list(self._isolation_lookup) + 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 TRANSACTION ISOLATION LEVEL %s" % level) cursor.close() @@ -2833,16 +2827,6 @@ class MSDialect(default.DefaultDialect): self._setup_version_attributes() self._setup_supports_nvarchar_max(connection) - 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 - def _setup_version_attributes(self): if self.server_version_info[0] not in list(range(8, 17)): util.warn( diff --git a/lib/sqlalchemy/dialects/mssql/pymssql.py b/lib/sqlalchemy/dialects/mssql/pymssql.py index b559384ba..18bee1890 100644 --- a/lib/sqlalchemy/dialects/mssql/pymssql.py +++ b/lib/sqlalchemy/dialects/mssql/pymssql.py @@ -125,6 +125,9 @@ class MSDialect_pymssql(MSDialect): else: return False + def get_isolation_level_values(self, dbapi_conn): + return super().get_isolation_level_values(dbapi_conn) + ["AUTOCOMMIT"] + def set_isolation_level(self, connection, level): if level == "AUTOCOMMIT": connection.autocommit(True) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 5382e00db..af2d7bdbb 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2385,7 +2385,6 @@ class MySQLDialect(default.DefaultDialect): def __init__( self, - isolation_level=None, json_serializer=None, json_deserializer=None, is_mariadb=None, @@ -2393,49 +2392,20 @@ class MySQLDialect(default.DefaultDialect): ): kwargs.pop("use_ansiquotes", None) # legacy default.DefaultDialect.__init__(self, **kwargs) - self.isolation_level = isolation_level self._json_serializer = json_serializer self._json_deserializer = json_deserializer self._set_mariadb(is_mariadb, None) - 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): + return ( "SERIALIZABLE", "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", - ] - ) - - def set_isolation_level(self, connection, level): - level = level.replace("_", " ") - - # adjust for ConnectionFairy being present - # allows attribute set e.g. "connection.autocommit = True" - # to work properly - if hasattr(connection, "dbapi_connection"): - connection = connection.dbapi_connection - - self._set_isolation_level(connection, level) + ) - def _set_isolation_level(self, connection, level): - 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() + def set_isolation_level(self, dbapi_conn, level): + cursor = dbapi_conn.cursor() cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level) cursor.execute("COMMIT") cursor.close() diff --git a/lib/sqlalchemy/dialects/mysql/mysqldb.py b/lib/sqlalchemy/dialects/mysql/mysqldb.py index dfe719c28..1e57c779d 100644 --- a/lib/sqlalchemy/dialects/mysql/mysqldb.py +++ b/lib/sqlalchemy/dialects/mysql/mysqldb.py @@ -308,23 +308,22 @@ class MySQLDialect_mysqldb(MySQLDialect): else: return cset_name() - _isolation_lookup = set( - [ + def get_isolation_level_values(self, dbapi_conn): + return ( "SERIALIZABLE", "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "AUTOCOMMIT", - ] - ) + ) - def _set_isolation_level(self, connection, level): + def set_isolation_level(self, dbapi_conn, level): if level == "AUTOCOMMIT": - connection.autocommit(True) + dbapi_conn.autocommit(True) else: - connection.autocommit(False) - super(MySQLDialect_mysqldb, self)._set_isolation_level( - connection, level + dbapi_conn.autocommit(False) + super(MySQLDialect_mysqldb, self).set_isolation_level( + dbapi_conn, level ) diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 229a54b95..9846a65bd 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -1573,10 +1573,8 @@ class OracleDialect(default.DefaultDialect): # use the default return None - _isolation_lookup = ["READ COMMITTED", "SERIALIZABLE"] - - def get_isolation_level(self, connection): - raise NotImplementedError("implemented by cx_Oracle dialect") + def get_isolation_level_values(self, dbapi_conn): + return ["READ COMMITTED", "SERIALIZABLE"] def get_default_isolation_level(self, dbapi_conn): try: @@ -1586,9 +1584,6 @@ class OracleDialect(default.DefaultDialect): except: return "READ COMMITTED" - def set_isolation_level(self, connection, level): - raise NotImplementedError("implemented by cx_Oracle dialect") - def has_table(self, connection, table_name, schema=None): self._ensure_has_table_connection(connection) diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 23f619a12..2cfcb0e5c 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -1024,6 +1024,9 @@ class OracleDialect_cx_oracle(OracleDialect): return result + def get_isolation_level_values(self, dbapi_conn): + return super().get_isolation_level_values(dbapi_conn) + ["AUTOCOMMIT"] + def set_isolation_level(self, connection, level): if hasattr(connection, "dbapi_connection"): dbapi_connection = connection.dbapi_connection 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): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index dc8425859..6c22c8ef3 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1815,7 +1815,6 @@ class SQLiteDialect(default.DefaultDialect): preparer = SQLiteIdentifierPreparer ischema_names = ischema_names colspecs = colspecs - isolation_level = None construct_arguments = [ ( @@ -1856,7 +1855,6 @@ class SQLiteDialect(default.DefaultDialect): ) def __init__( self, - isolation_level=None, native_datetime=False, json_serializer=None, json_deserializer=None, @@ -1865,7 +1863,6 @@ class SQLiteDialect(default.DefaultDialect): **kwargs ): default.DefaultDialect.__init__(self, **kwargs) - self.isolation_level = isolation_level if _json_serializer: json_serializer = _json_serializer @@ -1918,22 +1915,12 @@ class SQLiteDialect(default.DefaultDialect): {"READ UNCOMMITTED": 1, "SERIALIZABLE": 0} ) + def get_isolation_level_values(self, dbapi_conn): + return list(self._isolation_lookup) + def set_isolation_level(self, connection, level): - try: - isolation_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, - ) + isolation_level = self._isolation_lookup[level] + cursor = connection.cursor() cursor.execute("PRAGMA read_uncommitted = %d" % isolation_level) cursor.close() @@ -1960,16 +1947,6 @@ class SQLiteDialect(default.DefaultDialect): else: assert False, "Unknown isolation level %s" % value - 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 - @reflection.cache def get_schema_names(self, connection, **kw): s = "PRAGMA database_list" diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py index 10912e0d5..45a35be65 100644 --- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py +++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py @@ -504,8 +504,6 @@ class SQLiteDialect_pysqlite(SQLiteDialect): ) def on_connect(self): - connect = super(SQLiteDialect_pysqlite, self).on_connect() - def regexp(a, b): if b is None: return None @@ -524,13 +522,6 @@ class SQLiteDialect_pysqlite(SQLiteDialect): fns = [set_regexp] - if self.isolation_level is not None: - - def iso_level(conn): - self.set_isolation_level(conn, self.isolation_level) - - fns.append(iso_level) - def connect(conn): for fn in fns: fn(conn) diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py index ef6282525..24f8a8a87 100644 --- a/lib/sqlalchemy/engine/base.py +++ b/lib/sqlalchemy/engine/base.py @@ -2392,9 +2392,6 @@ class Engine(ConnectionEventsTarget, log.Identified): * The logging configuration and logging_name is copied from the parent :class:`_engine.Engine`. - .. TODO: the below autocommit link will have a more specific ref - for the example in an upcoming commit - The intent of the :meth:`_engine.Engine.execution_options` method is to implement schemes where multiple :class:`_engine.Engine` objects refer to the same connection pool, but are differentiated @@ -2404,7 +2401,7 @@ class Engine(ConnectionEventsTarget, log.Identified): :class:`_engine.Engine` has a lower :term:`isolation level` setting configured or is even transaction-disabled using "autocommit". An example of this - configuration is at :ref:`dbapi_autocommit`. + configuration is at :ref:`dbapi_autocommit_multiple`. Another example is one that uses a custom option ``shard_id`` which is consumed by an event diff --git a/lib/sqlalchemy/engine/characteristics.py b/lib/sqlalchemy/engine/characteristics.py index c00bff40d..2543f591b 100644 --- a/lib/sqlalchemy/engine/characteristics.py +++ b/lib/sqlalchemy/engine/characteristics.py @@ -50,7 +50,7 @@ class IsolationLevelCharacteristic(ConnectionCharacteristic): dialect.reset_isolation_level(dbapi_conn) def set_characteristic(self, dialect, dbapi_conn, value): - dialect.set_isolation_level(dbapi_conn, value) + dialect._assert_and_set_isolation_level(dbapi_conn, value) def get_characteristic(self, dialect, dbapi_conn): return dialect.get_isolation_level(dbapi_conn) diff --git a/lib/sqlalchemy/engine/create.py b/lib/sqlalchemy/engine/create.py index e6da1d8e6..5932bfb9b 100644 --- a/lib/sqlalchemy/engine/create.py +++ b/lib/sqlalchemy/engine/create.py @@ -201,34 +201,32 @@ def create_engine(url, **kwargs): should **always be set to True**. Some SQLAlchemy features will fail to function properly if this flag is set to ``False``. - :param isolation_level: this string parameter is interpreted by various - dialects in order to affect the transaction isolation level of the - database connection. The parameter essentially accepts some subset of - these string arguments: ``"SERIALIZABLE"``, ``"REPEATABLE READ"``, - ``"READ COMMITTED"``, ``"READ UNCOMMITTED"`` and ``"AUTOCOMMIT"``. - Behavior here varies per backend, and - individual dialects should be consulted directly. - - Note that the isolation level can also be set on a - per-:class:`_engine.Connection` basis as well, using the + :param isolation_level: optional string name of an isolation level + which will be set on all new connections unconditionally. + Isolation levels are typically some subset of the string names + ``"SERIALIZABLE"``, ``"REPEATABLE READ"``, + ``"READ COMMITTED"``, ``"READ UNCOMMITTED"`` and ``"AUTOCOMMIT"`` + based on backend. + + The :paramref:`_sa.create_engine.isolation_level` parameter is + in contrast to the :paramref:`.Connection.execution_options.isolation_level` - feature. + execution option, which may be set on an individual + :class:`.Connection`, as well as the same parameter passed to + :meth:`.Engine.execution_options`, where it may be used to create + multiple engines with different isolation levels that share a common + connection pool and dialect. + + .. versionchanged:: 2.0 The + :paramref:`_sa.create_engine.isolation_level` + parameter has been generalized to work on all dialects which support + the concept of isolation level, and is provided as a more succinct, + up front configuration switch in contrast to the execution option + which is more of an ad-hoc programmatic option. .. seealso:: - :attr:`_engine.Connection.default_isolation_level` - - view default level - - :paramref:`.Connection.execution_options.isolation_level` - - set per :class:`_engine.Connection` isolation level - - :ref:`SQLite Transaction Isolation <sqlite_isolation_level>` - - :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>` - - :ref:`MySQL Transaction Isolation <mysql_isolation_level>` - - :ref:`session_transaction_isolation` - for the ORM + :ref:`dbapi_autocommit` :param json_deserializer: for dialects that support the :class:`_types.JSON` @@ -594,6 +592,10 @@ def create_engine(url, **kwargs): event.listen(pool, "connect", on_connect) + builtin_on_connect = dialect._builtin_onconnect() + if builtin_on_connect: + event.listen(pool, "connect", builtin_on_connect) + def first_connect(dbapi_connection, connection_record): c = base.Connection( engine, diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index d670cf231..9a138e69e 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -239,6 +239,7 @@ class DefaultDialect(interfaces.Dialect): self, encoding="utf-8", paramstyle=None, + isolation_level=None, dbapi=None, implicit_returning=None, supports_native_boolean=None, @@ -251,12 +252,6 @@ class DefaultDialect(interfaces.Dialect): **kwargs ): - if not getattr(self, "ported_sqla_06", True): - util.warn( - "The %s dialect is not yet ported to the 0.6 format" - % self.name - ) - if server_side_cursors: if not self.supports_server_side_cursors: raise exc.ArgumentError( @@ -279,6 +274,7 @@ class DefaultDialect(interfaces.Dialect): self.implicit_returning = implicit_returning self.positional = self.paramstyle in ("qmark", "format", "numeric") self.identifier_preparer = self.preparer(self) + self._on_connect_isolation_level = isolation_level self.type_compiler = self.type_compiler(self) if supports_native_boolean is not None: self.supports_native_boolean = supports_native_boolean @@ -345,6 +341,18 @@ class DefaultDialect(interfaces.Dialect): except ImportError: pass + def _builtin_onconnect(self): + if self._on_connect_isolation_level is not None: + + def builtin_connect(dbapi_conn, conn_rec): + self._assert_and_set_isolation_level( + dbapi_conn, self._on_connect_isolation_level + ) + + return builtin_connect + else: + return None + def initialize(self, connection): try: self.server_version_info = self._get_server_version_info( @@ -573,11 +581,51 @@ class DefaultDialect(interfaces.Dialect): def is_disconnect(self, e, connection, cursor): return False + @util.memoized_instancemethod + def _gen_allowed_isolation_levels(self, dbapi_conn): + + try: + raw_levels = list(self.get_isolation_level_values(dbapi_conn)) + except NotImplementedError: + return None + else: + normalized_levels = [ + level.replace("_", " ").upper() for level in raw_levels + ] + if raw_levels != normalized_levels: + raise ValueError( + f"Dialect {self.name!r} get_isolation_level_values() " + f"method should return names as UPPERCASE using spaces, " + f"not underscores; got " + f"{sorted(set(raw_levels).difference(normalized_levels))}" + ) + return tuple(normalized_levels) + + def _assert_and_set_isolation_level(self, dbapi_conn, level): + level = level.replace("_", " ").upper() + + _allowed_isolation_levels = self._gen_allowed_isolation_levels( + dbapi_conn + ) + if ( + _allowed_isolation_levels + and level not in _allowed_isolation_levels + ): + raise exc.ArgumentError( + f"Invalid value {level!r} for isolation_level. " + f"Valid isolation levels for {self.name!r} are " + f"{', '.join(_allowed_isolation_levels)}" + ) + + self.set_isolation_level(dbapi_conn, level) + def reset_isolation_level(self, dbapi_conn): # default_isolation_level is read from the first connection # after the initial set of 'isolation_level', if any, so is # the configured default of this dialect. - self.set_isolation_level(dbapi_conn, self.default_isolation_level) + self._assert_and_set_isolation_level( + dbapi_conn, self.default_isolation_level + ) def normalize_name(self, name): if name is None: diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py index 38d2c7a57..fbdf9b829 100644 --- a/lib/sqlalchemy/engine/interfaces.py +++ b/lib/sqlalchemy/engine/interfaces.py @@ -919,6 +919,11 @@ class Dialect(object): isolation level facilities; these APIs should be preferred for most typical use cases. + If the dialect also implements the + :meth:`.Dialect.get_isolation_level_values` method, then the given + level is guaranteed to be one of the string names within that sequence, + and the method will not need to anticipate a lookup failure. + .. seealso:: :meth:`_engine.Connection.get_isolation_level` @@ -990,6 +995,48 @@ class Dialect(object): """ raise NotImplementedError() + def get_isolation_level_values(self, dbapi_conn): + """return a sequence of string isolation level names that are accepted + by this dialect. + + The available names should use the following conventions: + + * use UPPERCASE names. isolation level methods will accept lowercase + names but these are normalized into UPPERCASE before being passed + along to the dialect. + * separate words should be separated by spaces, not underscores, e.g. + ``REPEATABLE READ``. isolation level names will have underscores + converted to spaces before being passed along to the dialect. + * The names for the four standard isolation names to the extent that + they are supported by the backend should be ``READ UNCOMMITTED`` + ``READ COMMITTED``, ``REPEATABLE READ``, ``SERIALIZABLE`` + * if the dialect supports an autocommit option it should be provided + using the isolation level name ``AUTOCOMMIT``. + * Other isolation modes may also be present, provided that they + are named in UPPERCASE and use spaces not underscores. + + This function is used so that the default dialect can check that + a given isolation level parameter is valid, else raises an + :class:`_exc.ArgumentError`. + + A DBAPI connection is passed to the method, in the unlikely event that + the dialect needs to interrogate the connection itself to determine + this list, however it is expected that most backends will return + a hardcoded list of values. If the dialect supports "AUTOCOMMIT", + that value should also be present in the sequence returned. + + The method raises ``NotImplementedError`` by default. If a dialect + does not implement this method, then the default dialect will not + perform any checking on a given isolation level value before passing + it onto the :meth:`.Dialect.set_isolation_level` method. This is + to allow backwards-compatibility with third party dialects that may + not yet be implementing this method. + + .. versionadded:: 2.0 + + """ + raise NotImplementedError() + @classmethod def get_dialect_cls(cls, url): """Given a URL, return the :class:`.Dialect` that will be used. diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py index c30fdf823..3aa2649f4 100644 --- a/lib/sqlalchemy/testing/assertions.py +++ b/lib/sqlalchemy/testing/assertions.py @@ -384,9 +384,13 @@ def _expect_raises(except_cls, msg=None, check_context=False): ec.error = err success = True if msg is not None: - assert re.search( - msg, util.text_type(err), re.UNICODE - ), "%r !~ %s" % (msg, err) + # I'm often pdbing here, and "err" above isn't + # in scope, so assign the string explicitly + error_as_string = util.text_type(err) + assert re.search(msg, error_as_string, re.UNICODE), "%r !~ %s" % ( + msg, + error_as_string, + ) if check_context and not are_we_already_in_a_traceback: _assert_proper_exception_context(err) print(util.text_type(err).encode("utf-8")) diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index 4cc431bb7..a3d277c50 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -20,6 +20,7 @@ import sys from . import exclusions from . import only_on +from .. import create_engine from .. import util from ..pool import QueuePool @@ -873,6 +874,59 @@ class SuiteRequirements(Requirements): ] } """ + with config.db.connect() as conn: + + try: + supported = conn.dialect.get_isolation_level_values( + conn.connection.dbapi_connection + ) + except NotImplementedError: + return None + else: + return { + "default": conn.dialect.default_isolation_level, + "supported": supported, + } + + @property + def get_isolation_level_values(self): + """target dialect supports the + :meth:`_engine.Dialect.get_isolation_level_values` + method added in SQLAlchemy 2.0. + + """ + + def go(config): + with config.db.connect() as conn: + try: + conn.dialect.get_isolation_level_values( + conn.connection.dbapi_connection + ) + except NotImplementedError: + return False + else: + return True + + return exclusions.only_if(go) + + @property + def dialect_level_isolation_level_param(self): + """test that the dialect allows the 'isolation_level' argument + to be handled by DefaultDialect""" + + def go(config): + try: + e = create_engine( + config.db.url, isolation_level="READ COMMITTED" + ) + except: + return False + else: + return ( + e.dialect._on_connect_isolation_level == "READ COMMITTED" + ) + + return exclusions.only_if(go) @property def json_type(self): diff --git a/lib/sqlalchemy/testing/suite/test_dialect.py b/lib/sqlalchemy/testing/suite/test_dialect.py index b3e43aad0..28fd99876 100644 --- a/lib/sqlalchemy/testing/suite/test_dialect.py +++ b/lib/sqlalchemy/testing/suite/test_dialect.py @@ -8,6 +8,7 @@ from .. import eq_ from .. import fixtures from .. import ne_ from .. import provide_metadata +from ..assertions import expect_raises_message from ..config import requirements from ..provision import set_default_schema_on_connection from ..schema import Column @@ -140,6 +141,48 @@ class IsolationLevelTest(fixtures.TestBase): levels["default"], ) + @testing.requires.get_isolation_level_values + def test_invalid_level_execution_option(self, connection_no_trans): + """test for the new get_isolation_level_values() method""" + + connection = connection_no_trans + with expect_raises_message( + exc.ArgumentError, + "Invalid value '%s' for isolation_level. " + "Valid isolation levels for '%s' are %s" + % ( + "FOO", + connection.dialect.name, + ", ".join( + requirements.get_isolation_levels(config)["supported"] + ), + ), + ): + connection.execution_options(isolation_level="FOO") + + @testing.requires.get_isolation_level_values + @testing.requires.dialect_level_isolation_level_param + def test_invalid_level_engine_param(self, testing_engine): + """test for the new get_isolation_level_values() method + and support for the dialect-level 'isolation_level' parameter. + + """ + + eng = testing_engine(options=dict(isolation_level="FOO")) + with expect_raises_message( + exc.ArgumentError, + "Invalid value '%s' for isolation_level. " + "Valid isolation levels for '%s' are %s" + % ( + "FOO", + eng.dialect.name, + ", ".join( + requirements.get_isolation_levels(config)["supported"] + ), + ), + ): + eng.connect() + class AutocommitIsolationTest(fixtures.TablesTest): |