Merge "Surface driver connection object when using a proxied dialect"

5 files changed, 125 insertions, 9 deletions

diff --git a/doc/build/changelog/unreleased_14/6832.rst b/doc/build/changelog/unreleased_14/6832.rst
new file mode 100644
index 000000000..fcc5b6f6e
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/6832.rst
@@ -0,0 +1,28 @@
+.. change::
+    :tags: engine, asyncio
+    :tickets: 6832
+
+    Improve the interface used by adapted drivers, like the asyncio ones,
+    to access the actual connection object returned by the driver.
+
+    The :class:`._ConnectionFairy` object has two new attributes:
+
+    * :attr:`._ConnectionFairy.dbapi_connection` always represents a DBAPI
+      compatible object. For pep-249 drivers, this is the DBAPI connection as
+      it always has been, previously accessed under the ``.connection``
+      attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249
+      interface, the returned object will normally be a SQLAlchemy adaption
+      object called :class:`_engine.AdaptedConnection`.
+    * :attr:`._ConnectionFairy.driver_connection` always represents the actual
+      connection object maintained by the third party pep-249 DBAPI or async
+      driver in use. For standard pep-249 DBAPIs, this will always be the same
+      object as that of the ``dbapi_connection``. For an asyncio driver, it
+      will be the underlying asyncio-only connection object.
+
+    The ``.connection`` attribute remains available and is now a legacy alias
+    of ``.dbapi_connection``.
+
+    .. seealso::
+
+        :ref:`faq_dbapi_connection`
+
diff --git a/doc/build/core/connections.rst b/doc/build/core/connections.rst
index ac9ee717f..52197a795 100644
--- a/doc/build/core/connections.rst
+++ b/doc/build/core/connections.rst
@@ -1743,6 +1743,12 @@ needed and they also vary highly dependent on the type of DBAPI in use,
 so in any case the direct DBAPI calling pattern is always there for those
 cases where it is needed.
 
+.. seealso::
+
+    :ref:`faq_dbapi_connection` - includes additional details about how
+    the DBAPI connection is accessed as well as the "driver" connection
+    when using asyncio drivers.
+
 Some recipes for DBAPI connection use follow.
 
 .. _stored_procedures:
diff --git a/doc/build/core/internals.rst b/doc/build/core/internals.rst
index 34bf0407c..074acc798 100644
--- a/doc/build/core/internals.rst
+++ b/doc/build/core/internals.rst
@@ -49,3 +49,5 @@ Some key internal constructs are listed here.
     :members:
 
 
+.. autoclass:: sqlalchemy.engine.AdaptedConnection
+    :members:
diff --git a/doc/build/core/pooling.rst b/doc/build/core/pooling.rst
index 0c01d3f45..878a9ccab 100644
--- a/doc/build/core/pooling.rst
+++ b/doc/build/core/pooling.rst
@@ -154,6 +154,7 @@ to disable under the following conditions:
 * If the pool itself doesn't maintain a connection after it's checked in,
   such as when using :class:`.NullPool`, the behavior can be disabled.
 * Otherwise, it must be ensured that:
+
   * the application ensures that all :class:`_engine.Connection`
     objects are explicitly closed out using a context manager (i.e. ``with``
     block) or a ``try/finally`` style block
@@ -518,7 +519,7 @@ are three general approaches to this:
     def checkout(dbapi_connection, connection_record, connection_proxy):
         pid = os.getpid()
         if connection_record.info['pid'] != pid:
-            connection_record.connection = connection_proxy.connection = None
+            connection_record.dbapi_connection = connection_proxy.dbapi_connection = None
             raise exc.DisconnectionError(
                     "Connection record belongs to pid %s, "
                     "attempting to check out in pid %s" %
diff --git a/doc/build/faq/connections.rst b/doc/build/faq/connections.rst
index 79e8804c1..1bee24c32 100644
--- a/doc/build/faq/connections.rst
+++ b/doc/build/faq/connections.rst
@@ -406,28 +406,107 @@ current SQLAlchemy versions.
 
     :ref:`pysqlite_threading_pooling` - info on PySQLite's behavior.
 
+.. _faq_dbapi_connection:
+
 How do I get at the raw DBAPI connection when using an Engine?
 --------------------------------------------------------------
 
 With a regular SA engine-level Connection, you can get at a pool-proxied
 version of the DBAPI connection via the :attr:`_engine.Connection.connection` attribute on
 :class:`_engine.Connection`, and for the really-real DBAPI connection you can call the
-:attr:`.ConnectionFairy.connection` attribute on that - but there should never be any need to access
-the non-pool-proxied DBAPI connection, as all methods are proxied through::
+:attr:`._ConnectionFairy.dbapi_connection` attribute on that.  On regular sync drivers
+there is usually no need to access the non-pool-proxied DBAPI connection,
+as all methods are proxied through::
 
     engine = create_engine(...)
     conn = engine.connect()
-    conn.connection.<do DBAPI things>
-    cursor_obj = conn.connection.cursor(<DBAPI specific arguments..>)
+
+    # pep-249 style ConnectionFairy connection pool proxy object
+    connection_fairy = conn.connection
+
+    # typically to run statements one would get a cursor() from this
+    # object
+    cursor_obj = connection_fairy.cursor()
+    # ... work with cursor_obj
+
+    # to bypass "connection_fairy", such as to set attributes on the
+    # unproxied pep-249 DBAPI connection, use .dbapi_connection
+    raw_dbapi_connection = connection_fairy.dbapi_connection
+
+    # the same thing is available as .driver_connection (more on this
+    # in the next section)
+    also_raw_dbapi_connection = connection_fairy.driver_connection
+
+.. versionchanged:: 1.4.24  Added the
+   :attr:`._ConnectionFairy.dbapi_connection` attribute,
+   which supersedes the previous
+   :attr:`._ConnectionFairy.connection` attribute which still remains
+   available; this attribute always provides a pep-249 synchronous style
+   connection object.  The :attr:`._ConnectionFairy.driver_connection`
+   attribute is also added which will always refer to the real driver-level
+   connection regardless of what API it presents.
+
+Accessing the underlying connnection for an asyncio driver
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+When an asyncio driver is in use, there are two changes to the above
+scheme.  The first is that when using an :class:`_asyncio.AsyncConnection`,
+the :class:`._ConnectionFairy` must be accessed using the awaitable method
+:meth:`_asyncio.AsyncConnection.get_raw_connection`.   The
+returned :class:`._ConnectionFairy` in this case retains a sync-style
+pep-249 usage pattern, and the :attr:`._ConnectionFairy.dbapi_connection`
+attribute refers to a
+a SQLAlchemy-adapted connection object which adapts the asyncio
+connection to a sync style pep-249 API, in other words there are *two* levels
+of proxying going on when using an asyncio driver.   The actual asyncio connection
+is available from the :class:`._ConnectionFairy.driver_connection` attribute.
+To restate the previous example in terms of asyncio looks like::
+
+    async def main():
+        engine = create_async_engine(...)
+        conn = await engine.connect()
+
+        # pep-249 style ConnectionFairy connection pool proxy object
+        # presents a sync interface
+        connection_fairy = await conn.get_raw_connection()
+
+        # beneath that proxy is a second proxy which adapts the
+        # asyncio driver into a pep-249 connection object, accessible
+        # via .dbapi_connection as is the same with a sync API
+        sqla_sync_conn = connection_fairy.dbapi_connection
+
+        # the really-real innermost driver connection is available
+        # from the .driver_connection attribute
+        raw_asyncio_connection = connection_fairy.driver_connection
+
+        # work with raw asyncio connection
+        result = await raw_asyncio_connection.execute(...)
+
+.. versionchanged:: 1.4.24  Added the
+   :attr:`._ConnectionFairy.dbapi_connection`
+   and :attr:`._ConnectionFairy.driver_connection` attributes to allow access
+   to pep-249 connections, pep-249 adaption layers, and underlying driver
+   connections using a consistent interface.
+
+When using asyncio drivers, the above "DBAPI" connection is actually a
+SQLAlchemy-adapted form of connection which presents a synchronous-style
+pep-249 style API.  To access the actual
+asyncio driver connection, which will present the original asyncio API
+of the driver in use, this can be accessed via the
+:attr:`._ConnectionFairy.driver_connection` attribute of
+:class:`._ConnectionFairy`.
+For a standard pep-249 driver, :attr:`._ConnectionFairy.dbapi_connection`
+and :attr:`._ConnectionFairy.driver_connection` are synonymous.
 
 You must ensure that you revert any isolation level settings or other
 operation-specific settings on the connection back to normal before returning
 it to the pool.
 
-As an alternative to reverting settings, you can call the :meth:`_engine.Connection.detach` method on
-either :class:`_engine.Connection` or the proxied connection, which will de-associate
-the connection from the pool such that it will be closed and discarded
-when :meth:`_engine.Connection.close` is called::
+As an alternative to reverting settings, you can call the
+:meth:`_engine.Connection.detach` method on either :class:`_engine.Connection`
+or the proxied connection, which will de-associate the connection from the pool
+such that it will be closed and discarded when :meth:`_engine.Connection.close`
+is called::
 
     conn = engine.connect()
     conn.detach()  # detaches the DBAPI connection from the connection pool