gh-83638: Add sqlite3.Connection.autocommit for PEP 249 compliant behaviour (#93823)

Introduce the autocommit attribute to Connection and the autocommit
parameter to connect() for PEP 249-compliant transaction handling.

Co-authored-by: Alex Waygood <Alex.Waygood@Gmail.com>
Co-authored-by: C.A.M. Gerlach <CAM.Gerlach@Gerlach.CAM>
Co-authored-by: Géry Ogam <gery.ogam@gmail.com>

2 files changed, 161 insertions, 17 deletions

diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 88914e8..1681fc4 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -258,7 +258,8 @@ Module functions
 .. function:: connect(database, timeout=5.0, detect_types=0, \
                       isolation_level="DEFERRED", check_same_thread=True, \
                       factory=sqlite3.Connection, cached_statements=128, \
-                      uri=False)
+                      uri=False, \*, \
+                      autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)
 
    Open a connection to an SQLite database.
 
@@ -290,11 +291,12 @@ Module functions
        By default (``0``), type detection is disabled.
 
    :param isolation_level:
-       The :attr:`~Connection.isolation_level` of the connection,
-       controlling whether and how transactions are implicitly opened.
+       See :attr:`Connection.isolation_level` and
+       :ref:`sqlite3-transaction-control-isolation-level` for more information.
        Can be ``"DEFERRED"`` (default), ``"EXCLUSIVE"`` or ``"IMMEDIATE"``;
        or ``None`` to disable opening transactions implicitly.
-       See :ref:`sqlite3-controlling-transactions` for more.
+       Has no effect unless :attr:`Connection.autocommit` is set to
+       :data:`~sqlite3.LEGACY_TRANSACTION_CONTROL` (the default).
    :type isolation_level: str | None
 
    :param bool check_same_thread:
@@ -321,6 +323,14 @@ Module functions
        The query string allows passing parameters to SQLite,
        enabling various :ref:`sqlite3-uri-tricks`.
 
+   :param autocommit:
+       See :attr:`Connection.autocommit` and
+       :ref:`sqlite3-transaction-control-autocommit` for more information.
+       *autocommit* currently defaults to
+       :data:`~sqlite3.LEGACY_TRANSACTION_CONTROL`.
+       The default will change to ``False`` in a future Python release.
+   :type autocommit: bool
+
    :rtype: Connection
 
    .. audit-event:: sqlite3.connect database sqlite3.connect
@@ -335,6 +345,9 @@ Module functions
    .. versionadded:: 3.10
       The ``sqlite3.connect/handle`` auditing event.
 
+   .. versionadded:: 3.12
+      The *autocommit* parameter.
+
 .. function:: complete_statement(statement)
 
    Return ``True`` if the string *statement* appears to contain
@@ -418,6 +431,12 @@ Module functions
 Module constants
 ^^^^^^^^^^^^^^^^
 
+.. data:: LEGACY_TRANSACTION_CONTROL
+
+   Set :attr:`~Connection.autocommit` to this constant to select
+   old style (pre-Python 3.12) transaction control behaviour.
+   See :ref:`sqlite3-transaction-control-isolation-level` for more information.
+
 .. data:: PARSE_COLNAMES
 
    Pass this flag value to the *detect_types* parameter of
@@ -616,18 +635,27 @@ Connection objects
    .. method:: commit()
 
       Commit any pending transaction to the database.
-      If there is no open transaction, this method is a no-op.
+      If :attr:`autocommit` is ``True``, or there is no open transaction,
+      this method does nothing.
+      If :attr:`!autocommit` is ``False``, a new transaction is implicitly
+      opened if a pending transaction was committed by this method.
 
    .. method:: rollback()
 
       Roll back to the start of any pending transaction.
-      If there is no open transaction, this method is a no-op.
+      If :attr:`autocommit` is ``True``, or there is no open transaction,
+      this method does nothing.
+      If :attr:`!autocommit` is ``False``, a new transaction is implicitly
+      opened if a pending transaction was rolled back by this method.
 
    .. method:: close()
 
       Close the database connection.
-      Any pending transaction is not committed implicitly;
-      make sure to :meth:`commit` before closing
+      If :attr:`autocommit` is ``False``,
+      any pending transaction is implicitly rolled back.
+      If :attr:`!autocommit` is ``True`` or :data:`LEGACY_TRANSACTION_CONTROL`,
+      no implicit transaction control is executed.
+      Make sure to :meth:`commit` before closing
       to avoid losing pending changes.
 
    .. method:: execute(sql, parameters=(), /)
@@ -1224,6 +1252,38 @@ Connection objects
 
       .. versionadded:: 3.11
 
+   .. attribute:: autocommit
+
+      This attribute controls :pep:`249`-compliant transaction behaviour.
+      :attr:`!autocommit` has three allowed values:
+
+      * ``False``: Select :pep:`249`-compliant transaction behaviour,
+        implying that :mod:`!sqlite3` ensures a transaction is always open.
+        Use :meth:`commit` and :meth:`rollback` to close transactions.
+
+        This is the recommended value of :attr:`!autocommit`.
+
+      * ``True``: Use SQLite's `autocommit mode`_.
+        :meth:`commit` and :meth:`rollback` have no effect in this mode.
+
+      * :data:`LEGACY_TRANSACTION_CONTROL`:
+        Pre-Python 3.12 (non-:pep:`249`-compliant) transaction control.
+        See :attr:`isolation_level` for more details.
+
+        This is currently the default value of :attr:`!autocommit`.
+
+      Changing :attr:`!autocommit` to ``False`` will open a new transaction,
+      and changing it to ``True`` will commit any pending transaction.
+
+      See :ref:`sqlite3-transaction-control-autocommit` for more details.
+
+      .. note::
+
+         The :attr:`isolation_level` attribute has no effect unless
+         :attr:`autocommit` is :data:`LEGACY_TRANSACTION_CONTROL`.
+
+      .. versionadded:: 3.12
+
    .. attribute:: in_transaction
 
       This read-only attribute corresponds to the low-level SQLite
@@ -1236,17 +1296,24 @@ Connection objects
 
    .. attribute:: isolation_level
 
-      This attribute controls the :ref:`transaction handling
-      <sqlite3-controlling-transactions>` performed by :mod:`!sqlite3`.
+      Controls the :ref:`legacy transaction handling mode
+      <sqlite3-transaction-control-isolation-level>` of :mod:`!sqlite3`.
       If set to ``None``, transactions are never implicitly opened.
       If set to one of ``"DEFERRED"``, ``"IMMEDIATE"``, or ``"EXCLUSIVE"``,
       corresponding to the underlying `SQLite transaction behaviour`_,
-      implicit :ref:`transaction management
-      <sqlite3-controlling-transactions>` is performed.
+      :ref:`implicit transaction management
+      <sqlite3-transaction-control-isolation-level>` is performed.
 
       If not overridden by the *isolation_level* parameter of :func:`connect`,
       the default is ``""``, which is an alias for ``"DEFERRED"``.
 
+      .. note::
+
+         Using :attr:`autocommit` to control transaction handling is
+         recommended over using :attr:`!isolation_level`.
+         :attr:`!isolation_level` has no effect unless :attr:`autocommit` is
+         set to :data:`LEGACY_TRANSACTION_CONTROL` (the default).
+
    .. attribute:: row_factory
 
       A callable that accepts two arguments,
@@ -1375,7 +1442,9 @@ Cursor objects
       :meth:`executescript` if you want to execute multiple SQL statements with one
       call.
 
-      If :attr:`~Connection.isolation_level` is not ``None``,
+      If :attr:`~Connection.autocommit` is
+      :data:`LEGACY_TRANSACTION_CONTROL`,
+      :attr:`~Connection.isolation_level` is not ``None``,
       *sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
       and there is no open transaction,
       a transaction is implicitly opened before executing *sql*.
@@ -1403,7 +1472,9 @@ Cursor objects
    .. method:: executescript(sql_script, /)
 
       Execute the SQL statements in *sql_script*.
-      If there is a pending transaction,
+      If the :attr:`~Connection.autocommit` is
+      :data:`LEGACY_TRANSACTION_CONTROL`
+      and there is a pending transaction,
       an implicit ``COMMIT`` statement is executed first.
       No other implicit transaction control is performed;
       any transaction control must be added to *sql_script*.
@@ -2202,9 +2273,12 @@ the transaction is committed.
 If this commit fails,
 or if the body of the ``with`` statement raises an uncaught exception,
 the transaction is rolled back.
+If :attr:`~Connection.autocommit` is ``False``,
+a new transaction is implicitly opened after committing or rolling back.
 
 If there is no open transaction upon leaving the body of the ``with`` statement,
-the context manager is a no-op.
+or if :attr:`~Connection.autocommit` is ``True``,
+the context manager does nothing.
 
 .. note::
 
@@ -2289,13 +2363,72 @@ can be found in the `SQLite URI documentation`_.
 Explanation
 -----------
 
+.. _sqlite3-transaction-control:
 .. _sqlite3-controlling-transactions:
 
 Transaction control
 ^^^^^^^^^^^^^^^^^^^
 
-The :mod:`!sqlite3` module does not adhere to the transaction handling recommended
-by :pep:`249`.
+:mod:`!sqlite3` offers multiple methods of controlling whether,
+when and how database transactions are opened and closed.
+:ref:`sqlite3-transaction-control-autocommit` is recommended,
+while :ref:`sqlite3-transaction-control-isolation-level`
+retains the pre-Python 3.12 behaviour.
+
+.. _sqlite3-transaction-control-autocommit:
+
+Transaction control via the ``autocommit`` attribute
+""""""""""""""""""""""""""""""""""""""""""""""""""""
+
+The recommended way of controlling transaction behaviour is through
+the :attr:`Connection.autocommit` attribute,
+which should preferrably be set using the *autocommit* parameter
+of :func:`connect`.
+
+It is suggested to set *autocommit* to ``False``,
+which implies :pep:`249`-compliant transaction control.
+This means:
+
+* :mod:`!sqlite3` ensures that a transaction is always open,
+  so :meth:`Connection.commit` and :meth:`Connection.rollback`
+  will implicitly open a new transaction immediately after closing
+  the pending one.
+  :mod:`!sqlite3` uses ``BEGIN DEFERRED`` statements when opening transactions.
+* Transactions should be committed explicitly using :meth:`!commit`.
+* Transactions should be rolled back explicitly using :meth:`!rollback`.
+* An implicit rollback is performed if the database is
+  :meth:`~Connection.close`-ed with pending changes.
+
+Set *autocommit* to ``True`` to enable SQLite's `autocommit mode`_.
+In this mode, :meth:`Connection.commit` and :meth:`Connection.rollback`
+have no effect.
+Note that SQLite's autocommit mode is distinct from
+the :pep:`249`-compliant :attr:`Connection.autocommit` attribute;
+use :attr:`Connection.in_transaction` to query
+the low-level SQLite autocommit mode.
+
+Set *autocommit* to :data:`LEGACY_TRANSACTION_CONTROL`
+to leave transaction control behaviour to the
+:attr:`Connection.isolation_level` attribute.
+See :ref:`sqlite3-transaction-control-isolation-level` for more information.
+
+
+.. _sqlite3-transaction-control-isolation-level:
+
+Transaction control via the ``isolation_level`` attribute
+"""""""""""""""""""""""""""""""""""""""""""""""""""""""""
+
+.. note::
+
+   The recommended way of controlling transactions is via the
+   :attr:`~Connection.autocommit` attribute.
+   See :ref:`sqlite3-transaction-control-autocommit`.
+
+If :attr:`Connection.autocommit` is set to
+:data:`LEGACY_TRANSACTION_CONTROL` (the default),
+transaction behaviour is controlled using
+the :attr:`Connection.isolation_level` attribute.
+Otherwise, :attr:`!isolation_level` has no effect.
 
 If the connection attribute :attr:`~Connection.isolation_level`
 is not ``None``,
@@ -2326,6 +2459,10 @@ regardless of the value of :attr:`~Connection.isolation_level`.
    :mod:`!sqlite3` used to implicitly commit an open transaction before DDL
    statements.  This is no longer the case.
 
+.. versionchanged:: 3.12
+   The recommended way of controlling transactions is now via the
+   :attr:`~Connection.autocommit` attribute.
+
 .. _autocommit mode:
    https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
 
diff --git a/Doc/whatsnew/3.12.rst b/Doc/whatsnew/3.12.rst
index ead2a9e..e5782ad 100644
--- a/Doc/whatsnew/3.12.rst
+++ b/Doc/whatsnew/3.12.rst
@@ -263,6 +263,13 @@ sqlite3
 * Add a :ref:`command-line interface <sqlite3-cli>`.
   (Contributed by Erlend E. Aasland in :gh:`77617`.)
 
+* Add the :attr:`~sqlite3.Connection.autocommit` attribute
+  to :class:`~sqlite3.Connection`
+  and the *autocommit* parameter to :func:`~sqlite3.connect`
+  to control :pep:`249`-compliant
+  :ref:`transaction handling <sqlite3-transaction-control-autocommit>`.
+  (Contributed by Erlend E. Aasland in :gh:`83638`.)
+
 threading
 ---------