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>

14 files changed, 537 insertions, 62 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
 ---------
 
diff --git a/Include/internal/pycore_global_objects_fini_generated.h b/Include/internal/pycore_global_objects_fini_generated.h
index 59001dc..8883bbb 100644
--- a/Include/internal/pycore_global_objects_fini_generated.h
+++ b/Include/internal/pycore_global_objects_fini_generated.h
@@ -783,6 +783,7 @@ _PyStaticObjects_CheckRefcnt(PyInterpreterState *interp) {
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(as_integer_ratio));
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(attribute));
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(authorizer_callback));
+    _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(autocommit));
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(b));
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(backtick));
     _PyStaticObject_CheckRefcnt((PyObject *)&_Py_ID(base));
diff --git a/Include/internal/pycore_global_strings.h b/Include/internal/pycore_global_strings.h
index bf83bc4..1227142 100644
--- a/Include/internal/pycore_global_strings.h
+++ b/Include/internal/pycore_global_strings.h
@@ -269,6 +269,7 @@ struct _Py_global_strings {
         STRUCT_FOR_ID(as_integer_ratio)
         STRUCT_FOR_ID(attribute)
         STRUCT_FOR_ID(authorizer_callback)
+        STRUCT_FOR_ID(autocommit)
         STRUCT_FOR_ID(b)
         STRUCT_FOR_ID(backtick)
         STRUCT_FOR_ID(base)
diff --git a/Include/internal/pycore_runtime_init_generated.h b/Include/internal/pycore_runtime_init_generated.h
index c937b0f..e7fba8d 100644
--- a/Include/internal/pycore_runtime_init_generated.h
+++ b/Include/internal/pycore_runtime_init_generated.h
@@ -775,6 +775,7 @@ extern "C" {
     INIT_ID(as_integer_ratio), \
     INIT_ID(attribute), \
     INIT_ID(authorizer_callback), \
+    INIT_ID(autocommit), \
     INIT_ID(b), \
     INIT_ID(backtick), \
     INIT_ID(base), \
diff --git a/Include/internal/pycore_unicodeobject_generated.h b/Include/internal/pycore_unicodeobject_generated.h
index 026945f..ada0485 100644
--- a/Include/internal/pycore_unicodeobject_generated.h
+++ b/Include/internal/pycore_unicodeobject_generated.h
@@ -444,6 +444,8 @@ _PyUnicode_InitStaticStrings(void) {
     PyUnicode_InternInPlace(&string);
     string = &_Py_ID(authorizer_callback);
     PyUnicode_InternInPlace(&string);
+    string = &_Py_ID(autocommit);
+    PyUnicode_InternInPlace(&string);
     string = &_Py_ID(b);
     PyUnicode_InternInPlace(&string);
     string = &_Py_ID(backtick);
diff --git a/Lib/test/test_sqlite3/test_transactions.py b/Lib/test/test_sqlite3/test_transactions.py
index 9c3d19e..5d211dd 100644
--- a/Lib/test/test_sqlite3/test_transactions.py
+++ b/Lib/test/test_sqlite3/test_transactions.py
@@ -22,9 +22,11 @@
 
 import unittest
 import sqlite3 as sqlite
+from contextlib import contextmanager
 
 from test.support import LOOPBACK_TIMEOUT
 from test.support.os_helper import TESTFN, unlink
+from test.support.script_helper import assert_python_ok
 
 from test.test_sqlite3.test_dbapi import memory_database
 
@@ -366,5 +368,176 @@ class IsolationLevelPostInit(unittest.TestCase):
         self.assertEqual(self.traced, [self.QUERY])
 
 
+class AutocommitAttribute(unittest.TestCase):
+    """Test PEP 249-compliant autocommit behaviour."""
+    legacy = sqlite.LEGACY_TRANSACTION_CONTROL
+
+    @contextmanager
+    def check_stmt_trace(self, cx, expected, reset=True):
+        try:
+            traced = []
+            cx.set_trace_callback(lambda stmt: traced.append(stmt))
+            yield
+        finally:
+            self.assertEqual(traced, expected)
+            if reset:
+                cx.set_trace_callback(None)
+
+    def test_autocommit_default(self):
+        with memory_database() as cx:
+            self.assertEqual(cx.autocommit,
+                             sqlite.LEGACY_TRANSACTION_CONTROL)
+
+    def test_autocommit_setget(self):
+        dataset = (
+            True,
+            False,
+            sqlite.LEGACY_TRANSACTION_CONTROL,
+        )
+        for mode in dataset:
+            with self.subTest(mode=mode):
+                with memory_database(autocommit=mode) as cx:
+                    self.assertEqual(cx.autocommit, mode)
+                with memory_database() as cx:
+                    cx.autocommit = mode
+                    self.assertEqual(cx.autocommit, mode)
+
+    def test_autocommit_setget_invalid(self):
+        msg = "autocommit must be True, False, or.*LEGACY"
+        for mode in "a", 12, (), None:
+            with self.subTest(mode=mode):
+                with self.assertRaisesRegex(ValueError, msg):
+                    sqlite.connect(":memory:", autocommit=mode)
+
+    def test_autocommit_disabled(self):
+        expected = [
+            "SELECT 1",
+            "COMMIT",
+            "BEGIN",
+            "ROLLBACK",
+            "BEGIN",
+        ]
+        with memory_database(autocommit=False) as cx:
+            self.assertTrue(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected):
+                cx.execute("SELECT 1")
+                cx.commit()
+                cx.rollback()
+
+    def test_autocommit_disabled_implicit_rollback(self):
+        expected = ["ROLLBACK"]
+        with memory_database(autocommit=False) as cx:
+            self.assertTrue(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected, reset=False):
+                cx.close()
+
+    def test_autocommit_enabled(self):
+        expected = ["CREATE TABLE t(t)", "INSERT INTO t VALUES(1)"]
+        with memory_database(autocommit=True) as cx:
+            self.assertFalse(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected):
+                cx.execute("CREATE TABLE t(t)")
+                cx.execute("INSERT INTO t VALUES(1)")
+                self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_enabled_txn_ctl(self):
+        for op in "commit", "rollback":
+            with self.subTest(op=op):
+                with memory_database(autocommit=True) as cx:
+                    meth = getattr(cx, op)
+                    self.assertFalse(cx.in_transaction)
+                    with self.check_stmt_trace(cx, []):
+                        meth()  # expect this to pass silently
+                        self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_disabled_then_enabled(self):
+        expected = ["COMMIT"]
+        with memory_database(autocommit=False) as cx:
+            self.assertTrue(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected):
+                cx.autocommit = True  # should commit
+                self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_enabled_then_disabled(self):
+        expected = ["BEGIN"]
+        with memory_database(autocommit=True) as cx:
+            self.assertFalse(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected):
+                cx.autocommit = False  # should begin
+                self.assertTrue(cx.in_transaction)
+
+    def test_autocommit_explicit_then_disabled(self):
+        expected = ["BEGIN DEFERRED"]
+        with memory_database(autocommit=True) as cx:
+            self.assertFalse(cx.in_transaction)
+            with self.check_stmt_trace(cx, expected):
+                cx.execute("BEGIN DEFERRED")
+                cx.autocommit = False  # should now be a no-op
+                self.assertTrue(cx.in_transaction)
+
+    def test_autocommit_enabled_ctx_mgr(self):
+        with memory_database(autocommit=True) as cx:
+            # The context manager is a no-op if autocommit=True
+            with self.check_stmt_trace(cx, []):
+                with cx:
+                    self.assertFalse(cx.in_transaction)
+                self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_disabled_ctx_mgr(self):
+        expected = ["COMMIT", "BEGIN"]
+        with memory_database(autocommit=False) as cx:
+            with self.check_stmt_trace(cx, expected):
+                with cx:
+                    self.assertTrue(cx.in_transaction)
+                self.assertTrue(cx.in_transaction)
+
+    def test_autocommit_compat_ctx_mgr(self):
+        expected = ["BEGIN ", "INSERT INTO T VALUES(1)", "COMMIT"]
+        with memory_database(autocommit=self.legacy) as cx:
+            cx.execute("create table t(t)")
+            with self.check_stmt_trace(cx, expected):
+                with cx:
+                    self.assertFalse(cx.in_transaction)
+                    cx.execute("INSERT INTO T VALUES(1)")
+                    self.assertTrue(cx.in_transaction)
+                self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_enabled_executescript(self):
+        expected = ["BEGIN", "SELECT 1"]
+        with memory_database(autocommit=True) as cx:
+            with self.check_stmt_trace(cx, expected):
+                self.assertFalse(cx.in_transaction)
+                cx.execute("BEGIN")
+                cx.executescript("SELECT 1")
+                self.assertTrue(cx.in_transaction)
+
+    def test_autocommit_disabled_executescript(self):
+        expected = ["SELECT 1"]
+        with memory_database(autocommit=False) as cx:
+            with self.check_stmt_trace(cx, expected):
+                self.assertTrue(cx.in_transaction)
+                cx.executescript("SELECT 1")
+                self.assertTrue(cx.in_transaction)
+
+    def test_autocommit_compat_executescript(self):
+        expected = ["BEGIN", "COMMIT", "SELECT 1"]
+        with memory_database(autocommit=self.legacy) as cx:
+            with self.check_stmt_trace(cx, expected):
+                self.assertFalse(cx.in_transaction)
+                cx.execute("BEGIN")
+                cx.executescript("SELECT 1")
+                self.assertFalse(cx.in_transaction)
+
+    def test_autocommit_disabled_implicit_shutdown(self):
+        # The implicit ROLLBACK should not call back into Python during
+        # interpreter tear-down.
+        code = """if 1:
+            import sqlite3
+            cx = sqlite3.connect(":memory:", autocommit=False)
+            cx.set_trace_callback(print)
+        """
+        assert_python_ok("-c", code, PYTHONIOENCODING="utf-8")
+
+
 if __name__ == "__main__":
     unittest.main()
diff --git a/Misc/NEWS.d/next/Library/2022-06-14-22-46-05.gh-issue-83638.73xfGK.rst b/Misc/NEWS.d/next/Library/2022-06-14-22-46-05.gh-issue-83638.73xfGK.rst
new file mode 100644
index 0000000..3edbf95
--- /dev/null
+++ b/Misc/NEWS.d/next/Library/2022-06-14-22-46-05.gh-issue-83638.73xfGK.rst
@@ -0,0 +1,6 @@
+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>`.
+Patch by Erlend E. Aasland.
diff --git a/Modules/_sqlite/clinic/connection.c.h b/Modules/_sqlite/clinic/connection.c.h
index e7e7870..1f9841c 100644
--- a/Modules/_sqlite/clinic/connection.c.h
+++ b/Modules/_sqlite/clinic/connection.c.h
@@ -13,7 +13,8 @@ pysqlite_connection_init_impl(pysqlite_Connection *self, PyObject *database,
                               double timeout, int detect_types,
                               const char *isolation_level,
                               int check_same_thread, PyObject *factory,
-                              int cache_size, int uri);
+                              int cache_size, int uri,
+                              enum autocommit_mode autocommit);
 
 static int
 pysqlite_connection_init(PyObject *self, PyObject *args, PyObject *kwargs)
@@ -21,14 +22,14 @@ pysqlite_connection_init(PyObject *self, PyObject *args, PyObject *kwargs)
     int return_value = -1;
     #if defined(Py_BUILD_CORE) && !defined(Py_BUILD_CORE_MODULE)
 
-    #define NUM_KEYWORDS 8
+    #define NUM_KEYWORDS 9
     static struct {
         PyGC_Head _this_is_not_used;
         PyObject_VAR_HEAD
         PyObject *ob_item[NUM_KEYWORDS];
     } _kwtuple = {
         .ob_base = PyVarObject_HEAD_INIT(&PyTuple_Type, NUM_KEYWORDS)
-        .ob_item = { &_Py_ID(database), &_Py_ID(timeout), &_Py_ID(detect_types), &_Py_ID(isolation_level), &_Py_ID(check_same_thread), &_Py_ID(factory), &_Py_ID(cached_statements), &_Py_ID(uri), },
+        .ob_item = { &_Py_ID(database), &_Py_ID(timeout), &_Py_ID(detect_types), &_Py_ID(isolation_level), &_Py_ID(check_same_thread), &_Py_ID(factory), &_Py_ID(cached_statements), &_Py_ID(uri), &_Py_ID(autocommit), },
     };
     #undef NUM_KEYWORDS
     #define KWTUPLE (&_kwtuple.ob_base.ob_base)
@@ -37,14 +38,14 @@ pysqlite_connection_init(PyObject *self, PyObject *args, PyObject *kwargs)
     #  define KWTUPLE NULL
     #endif  // !Py_BUILD_CORE
 
-    static const char * const _keywords[] = {"database", "timeout", "detect_types", "isolation_level", "check_same_thread", "factory", "cached_statements", "uri", NULL};
+    static const char * const _keywords[] = {"database", "timeout", "detect_types", "isolation_level", "check_same_thread", "factory", "cached_statements", "uri", "autocommit", NULL};
     static _PyArg_Parser _parser = {
         .keywords = _keywords,
         .fname = "Connection",
         .kwtuple = KWTUPLE,
     };
     #undef KWTUPLE
-    PyObject *argsbuf[8];
+    PyObject *argsbuf[9];
     PyObject * const *fastargs;
     Py_ssize_t nargs = PyTuple_GET_SIZE(args);
     Py_ssize_t noptargs = nargs + (kwargs ? PyDict_GET_SIZE(kwargs) : 0) - 1;
@@ -56,6 +57,7 @@ pysqlite_connection_init(PyObject *self, PyObject *args, PyObject *kwargs)
     PyObject *factory = (PyObject*)clinic_state()->ConnectionType;
     int cache_size = 128;
     int uri = 0;
+    enum autocommit_mode autocommit = LEGACY_TRANSACTION_CONTROL;
 
     fastargs = _PyArg_UnpackKeywords(_PyTuple_CAST(args)->ob_item, nargs, kwargs, NULL, &_parser, 1, 8, 0, argsbuf);
     if (!fastargs) {
@@ -121,12 +123,24 @@ pysqlite_connection_init(PyObject *self, PyObject *args, PyObject *kwargs)
             goto skip_optional_pos;
         }
     }
-    uri = PyObject_IsTrue(fastargs[7]);
-    if (uri < 0) {
-        goto exit;
+    if (fastargs[7]) {
+        uri = PyObject_IsTrue(fastargs[7]);
+        if (uri < 0) {
+            goto exit;
+        }
+        if (!--noptargs) {
+            goto skip_optional_pos;
+        }
     }
 skip_optional_pos:
-    return_value = pysqlite_connection_init_impl((pysqlite_Connection *)self, database, timeout, detect_types, isolation_level, check_same_thread, factory, cache_size, uri);
+    if (!noptargs) {
+        goto skip_optional_kwonly;
+    }
+    if (!autocommit_converter(fastargs[8], &autocommit)) {
+        goto exit;
+    }
+skip_optional_kwonly:
+    return_value = pysqlite_connection_init_impl((pysqlite_Connection *)self, database, timeout, detect_types, isolation_level, check_same_thread, factory, cache_size, uri, autocommit);
 
 exit:
     return return_value;
@@ -1518,4 +1532,4 @@ exit:
 #ifndef DESERIALIZE_METHODDEF
     #define DESERIALIZE_METHODDEF
 #endif /* !defined(DESERIALIZE_METHODDEF) */
-/*[clinic end generated code: output=beef3eac690a1f88 input=a9049054013a1b77]*/
+/*[clinic end generated code: output=20e929a7a7d62a01 input=a9049054013a1b77]*/
diff --git a/Modules/_sqlite/connection.c b/Modules/_sqlite/connection.c
index ceb77bb..2854c1b 100644
--- a/Modules/_sqlite/connection.c
+++ b/Modules/_sqlite/connection.c
@@ -92,6 +92,30 @@ isolation_level_converter(PyObject *str_or_none, const char **result)
     return 1;
 }
 
+static int
+autocommit_converter(PyObject *val, enum autocommit_mode *result)
+{
+    if (Py_IsTrue(val)) {
+        *result = AUTOCOMMIT_ENABLED;
+        return 1;
+    }
+    if (Py_IsFalse(val)) {
+        *result = AUTOCOMMIT_DISABLED;
+        return 1;
+    }
+    if (PyLong_Check(val) &&
+        PyLong_AsLong(val) == LEGACY_TRANSACTION_CONTROL)
+    {
+        *result = AUTOCOMMIT_LEGACY;
+        return 1;
+    }
+
+    PyErr_SetString(PyExc_ValueError,
+        "autocommit must be True, False, or "
+        "sqlite3.LEGACY_TRANSACTION_CONTROL");
+    return 0;
+}
+
 #define clinic_state() (pysqlite_get_state_by_type(Py_TYPE(self)))
 #include "clinic/connection.c.h"
 #undef clinic_state
@@ -132,13 +156,38 @@ new_statement_cache(pysqlite_Connection *self, pysqlite_state *state,
     return res;
 }
 
+static inline int
+connection_exec_stmt(pysqlite_Connection *self, const char *sql)
+{
+    int rc;
+    Py_BEGIN_ALLOW_THREADS
+    int len = (int)strlen(sql) + 1;
+    sqlite3_stmt *stmt;
+    rc = sqlite3_prepare_v2(self->db, sql, len, &stmt, NULL);
+    if (rc == SQLITE_OK) {
+        (void)sqlite3_step(stmt);
+        rc = sqlite3_finalize(stmt);
+    }
+    Py_END_ALLOW_THREADS
+
+    if (rc != SQLITE_OK) {
+        (void)_pysqlite_seterror(self->state, self->db);
+        return -1;
+    }
+    return 0;
+}
+
 /*[python input]
 class IsolationLevel_converter(CConverter):
     type = "const char *"
     converter = "isolation_level_converter"
 
+class Autocommit_converter(CConverter):
+    type = "enum autocommit_mode"
+    converter = "autocommit_converter"
+
 [python start generated code]*/
-/*[python end generated code: output=da39a3ee5e6b4b0d input=cbcfe85b253061c2]*/
+/*[python end generated code: output=da39a3ee5e6b4b0d input=bc2aa6c7ba0c5f8f]*/
 
 // NB: This needs to be in sync with the sqlite3.connect docstring
 /*[clinic input]
@@ -152,6 +201,8 @@ _sqlite3.Connection.__init__ as pysqlite_connection_init
     factory: object(c_default='(PyObject*)clinic_state()->ConnectionType') = ConnectionType
     cached_statements as cache_size: int = 128
     uri: bool = False
+    *
+    autocommit: Autocommit(c_default='LEGACY_TRANSACTION_CONTROL') = sqlite3.LEGACY_TRANSACTION_CONTROL
 [clinic start generated code]*/
 
 static int
@@ -159,8 +210,9 @@ pysqlite_connection_init_impl(pysqlite_Connection *self, PyObject *database,
                               double timeout, int detect_types,
                               const char *isolation_level,
                               int check_same_thread, PyObject *factory,
-                              int cache_size, int uri)
-/*[clinic end generated code: output=839eb2fee4293bda input=b8ce63dc6f70a383]*/
+                              int cache_size, int uri,
+                              enum autocommit_mode autocommit)
+/*[clinic end generated code: output=cba057313ea7712f input=b21abce28ebcd304]*/
 {
     if (PySys_Audit("sqlite3.connect", "O", database) < 0) {
         return -1;
@@ -227,6 +279,7 @@ pysqlite_connection_init_impl(pysqlite_Connection *self, PyObject *database,
     self->state = state;
     self->detect_types = detect_types;
     self->isolation_level = isolation_level;
+    self->autocommit = autocommit;
     self->check_same_thread = check_same_thread;
     self->thread_ident = PyThread_get_thread_ident();
     self->statement_cache = statement_cache;
@@ -256,6 +309,10 @@ pysqlite_connection_init_impl(pysqlite_Connection *self, PyObject *database,
     }
 
     self->initialized = 1;
+
+    if (autocommit == AUTOCOMMIT_DISABLED) {
+        (void)connection_exec_stmt(self, "BEGIN");
+    }
     return 0;
 
 error:
@@ -322,9 +379,32 @@ free_callback_contexts(pysqlite_Connection *self)
 }
 
 static void
+remove_callbacks(sqlite3 *db)
+{
+#ifdef HAVE_TRACE_V2
+    sqlite3_trace_v2(db, SQLITE_TRACE_STMT, 0, 0);
+#else
+    sqlite3_trace(db, 0, (void*)0);
+#endif
+    sqlite3_progress_handler(db, 0, 0, (void *)0);
+    (void)sqlite3_set_authorizer(db, NULL, NULL);
+}
+
+static void
 connection_close(pysqlite_Connection *self)
 {
     if (self->db) {
+        if (self->autocommit == AUTOCOMMIT_DISABLED &&
+            !sqlite3_get_autocommit(self->db))
+        {
+            /* If close is implicitly called as a result of interpreter
+             * tear-down, we must not call back into Python. */
+            if (_Py_IsFinalizing()) {
+                remove_callbacks(self->db);
+            }
+            (void)connection_exec_stmt(self, "ROLLBACK");
+        }
+
         free_callback_contexts(self);
 
         sqlite3 *db = self->db;
@@ -538,24 +618,21 @@ pysqlite_connection_commit_impl(pysqlite_Connection *self)
         return NULL;
     }
 
-    if (!sqlite3_get_autocommit(self->db)) {
-        int rc;
-
-        Py_BEGIN_ALLOW_THREADS
-        sqlite3_stmt *statement;
-        rc = sqlite3_prepare_v2(self->db, "COMMIT", 7, &statement, NULL);
-        if (rc == SQLITE_OK) {
-            (void)sqlite3_step(statement);
-            rc = sqlite3_finalize(statement);
+    if (self->autocommit == AUTOCOMMIT_LEGACY) {
+        if (!sqlite3_get_autocommit(self->db)) {
+            if (connection_exec_stmt(self, "COMMIT") < 0) {
+                return NULL;
+            }
         }
-        Py_END_ALLOW_THREADS
-
-        if (rc != SQLITE_OK) {
-            (void)_pysqlite_seterror(self->state, self->db);
+    }
+    else if (self->autocommit == AUTOCOMMIT_DISABLED) {
+        if (connection_exec_stmt(self, "COMMIT") < 0) {
+            return NULL;
+        }
+        if (connection_exec_stmt(self, "BEGIN") < 0) {
             return NULL;
         }
     }
-
     Py_RETURN_NONE;
 }
 
@@ -575,25 +652,21 @@ pysqlite_connection_rollback_impl(pysqlite_Connection *self)
         return NULL;
     }
 
-    if (!sqlite3_get_autocommit(self->db)) {
-        int rc;
-
-        Py_BEGIN_ALLOW_THREADS
-        sqlite3_stmt *statement;
-        rc = sqlite3_prepare_v2(self->db, "ROLLBACK", 9, &statement, NULL);
-        if (rc == SQLITE_OK) {
-            (void)sqlite3_step(statement);
-            rc = sqlite3_finalize(statement);
+    if (self->autocommit == AUTOCOMMIT_LEGACY) {
+        if (!sqlite3_get_autocommit(self->db)) {
+            if (connection_exec_stmt(self, "ROLLBACK") < 0) {
+                return NULL;
+            }
         }
-        Py_END_ALLOW_THREADS
-
-        if (rc != SQLITE_OK) {
-            (void)_pysqlite_seterror(self->state, self->db);
+    }
+    else if (self->autocommit == AUTOCOMMIT_DISABLED) {
+        if (connection_exec_stmt(self, "ROLLBACK") < 0) {
+            return NULL;
+        }
+        if (connection_exec_stmt(self, "BEGIN") < 0) {
             return NULL;
         }
-
     }
-
     Py_RETURN_NONE;
 }
 
@@ -2270,6 +2343,48 @@ getlimit_impl(pysqlite_Connection *self, int category)
 }
 
 
+static PyObject *
+get_autocommit(pysqlite_Connection *self, void *Py_UNUSED(ctx))
+{
+    if (!pysqlite_check_thread(self) || !pysqlite_check_connection(self)) {
+        return NULL;
+    }
+    if (self->autocommit == AUTOCOMMIT_ENABLED) {
+        Py_RETURN_TRUE;
+    }
+    if (self->autocommit == AUTOCOMMIT_DISABLED) {
+        Py_RETURN_FALSE;
+    }
+    return PyLong_FromLong(LEGACY_TRANSACTION_CONTROL);
+}
+
+static int
+set_autocommit(pysqlite_Connection *self, PyObject *val, void *Py_UNUSED(ctx))
+{
+    if (!pysqlite_check_thread(self) || !pysqlite_check_connection(self)) {
+        return -1;
+    }
+    if (!autocommit_converter(val, &self->autocommit)) {
+        return -1;
+    }
+    if (self->autocommit == AUTOCOMMIT_ENABLED) {
+        if (!sqlite3_get_autocommit(self->db)) {
+            if (connection_exec_stmt(self, "COMMIT") < 0) {
+                return -1;
+            }
+        }
+    }
+    else if (self->autocommit == AUTOCOMMIT_DISABLED) {
+        if (sqlite3_get_autocommit(self->db)) {
+            if (connection_exec_stmt(self, "BEGIN") < 0) {
+                return -1;
+            }
+        }
+    }
+    return 0;
+}
+
+
 static const char connection_doc[] =
 PyDoc_STR("SQLite database connection object.");
 
@@ -2277,6 +2392,7 @@ static PyGetSetDef connection_getset[] = {
     {"isolation_level",  (getter)pysqlite_connection_get_isolation_level, (setter)pysqlite_connection_set_isolation_level},
     {"total_changes",  (getter)pysqlite_connection_get_total_changes, (setter)0},
     {"in_transaction",  (getter)pysqlite_connection_get_in_transaction, (setter)0},
+    {"autocommit",  (getter)get_autocommit, (setter)set_autocommit},
     {NULL}
 };
 
diff --git a/Modules/_sqlite/connection.h b/Modules/_sqlite/connection.h
index 629fe3d..1df9206 100644
--- a/Modules/_sqlite/connection.h
+++ b/Modules/_sqlite/connection.h
@@ -39,6 +39,12 @@ typedef struct _callback_context
     pysqlite_state *state;
 } callback_context;
 
+enum autocommit_mode {
+    AUTOCOMMIT_LEGACY = LEGACY_TRANSACTION_CONTROL,
+    AUTOCOMMIT_ENABLED = 1,
+    AUTOCOMMIT_DISABLED = 0,
+};
+
 typedef struct
 {
     PyObject_HEAD
@@ -51,6 +57,7 @@ typedef struct
 
     /* NULL for autocommit, otherwise a string with the isolation level */
     const char *isolation_level;
+    enum autocommit_mode autocommit;
 
     /* 1 if a check should be performed for each API call if the connection is
      * used from the same thread it was created in */
diff --git a/Modules/_sqlite/cursor.c b/Modules/_sqlite/cursor.c
index cbf4718..7844b6e 100644
--- a/Modules/_sqlite/cursor.c
+++ b/Modules/_sqlite/cursor.c
@@ -855,7 +855,8 @@ _pysqlite_query_execute(pysqlite_Cursor* self, int multiple, PyObject* operation
 
     /* We start a transaction implicitly before a DML statement.
        SELECT is the only exception. See #9924. */
-    if (self->connection->isolation_level
+    if (self->connection->autocommit == AUTOCOMMIT_LEGACY
+        && self->connection->isolation_level
         && self->statement->is_dml
         && sqlite3_get_autocommit(self->connection->db))
     {
@@ -1033,7 +1034,9 @@ pysqlite_cursor_executescript_impl(pysqlite_Cursor *self,
 
     // Commit if needed
     sqlite3 *db = self->connection->db;
-    if (!sqlite3_get_autocommit(db)) {
+    if (self->connection->autocommit == AUTOCOMMIT_LEGACY
+        && !sqlite3_get_autocommit(db))
+    {
         int rc = SQLITE_OK;
 
         Py_BEGIN_ALLOW_THREADS
diff --git a/Modules/_sqlite/module.c b/Modules/_sqlite/module.c
index 707aae1..6db3d51 100644
--- a/Modules/_sqlite/module.c
+++ b/Modules/_sqlite/module.c
@@ -46,7 +46,8 @@ module _sqlite3
 PyDoc_STRVAR(module_connect_doc,
 "connect($module, /, database, timeout=5.0, detect_types=0,\n"
 "        isolation_level='', check_same_thread=True,\n"
-"        factory=ConnectionType, cached_statements=128, uri=False)\n"
+"        factory=ConnectionType, cached_statements=128, uri=False, *,\n"
+"        autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)\n"
 "--\n"
 "\n"
 "Opens a connection to the SQLite database file database.\n"
@@ -706,6 +707,10 @@ module_exec(PyObject *module)
         goto error;
     }
 
+    if (PyModule_AddIntMacro(module, LEGACY_TRANSACTION_CONTROL) < 0) {
+        goto error;
+    }
+
     int threadsafety = get_threadsafety(state);
     if (threadsafety < 0) {
         goto error;
diff --git a/Modules/_sqlite/module.h b/Modules/_sqlite/module.h
index 7deba22..daa2209 100644
--- a/Modules/_sqlite/module.h
+++ b/Modules/_sqlite/module.h
@@ -26,6 +26,8 @@
 #define PY_SSIZE_T_CLEAN
 #include "Python.h"
 
+#define LEGACY_TRANSACTION_CONTROL -1
+
 #define PYSQLITE_VERSION "2.6.0"
 #define MODULE_NAME "sqlite3"