Merge "fully support isolation_level parameter in base dialect" into main

5 files changed, 130 insertions, 36 deletions

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 efcd2b530..f9a65a0f8 100644
--- a/lib/sqlalchemy/engine/create.py
+++ b/lib/sqlalchemy/engine/create.py
@@ -206,34 +206,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`
@@ -595,6 +593,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 48b7e35a0..7150f386c 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -911,6 +911,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`
@@ -982,6 +987,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.