[Python-checkins] gh-83638: Add sqlite3.Connection.autocommit for PEP 249 compliant behaviour (#93823)
erlend-aasland
webhook-mailer at python.org
Sat Nov 12 17:44:47 EST 2022
https://github.com/python/cpython/commit/c95f554a408f76f96c14c006ebe8a0d3d3b40765
commit: c95f554a408f76f96c14c006ebe8a0d3d3b40765
branch: main
author: Erlend E. Aasland <erlend.aasland at protonmail.com>
committer: erlend-aasland <erlend.aasland at protonmail.com>
date: 2022-11-12T23:44:41+01:00
summary:
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 at Gmail.com>
Co-authored-by: C.A.M. Gerlach <CAM.Gerlach at Gerlach.CAM>
Co-authored-by: Géry Ogam <gery.ogam at gmail.com>
files:
A Misc/NEWS.d/next/Library/2022-06-14-22-46-05.gh-issue-83638.73xfGK.rst
M Doc/library/sqlite3.rst
M Doc/whatsnew/3.12.rst
M Include/internal/pycore_global_objects_fini_generated.h
M Include/internal/pycore_global_strings.h
M Include/internal/pycore_runtime_init_generated.h
M Include/internal/pycore_unicodeobject_generated.h
M Lib/test/test_sqlite3/test_transactions.py
M Modules/_sqlite/clinic/connection.c.h
M Modules/_sqlite/connection.c
M Modules/_sqlite/connection.h
M Modules/_sqlite/cursor.c
M Modules/_sqlite/module.c
M Modules/_sqlite/module.h
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 88914e811d19..1681fc49e9f1 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 ead2a9e718a9..e5782ad6322f 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 59001dc95092..8883bbbfb44e 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 bf83bc45a51f..1227142b6aaf 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 c937b0f9f4e4..e7fba8db16fd 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 026945f1e342..ada0485c2e25 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 9c3d19e79bd9..5d211dd47b0b 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 @@ def test_isolation_level_none(self):
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 000000000000..3edbf95213ed
--- /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 e7e78707ee8d..1f9841c368b3 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 @@ getlimit(pysqlite_Connection *self, PyObject *arg)
#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 ceb77bbf8420..2854c1b5c31b 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:
@@ -321,10 +378,33 @@ free_callback_contexts(pysqlite_Connection *self)
set_callback_context(&self->authorizer_ctx, NULL);
}
+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 629fe3d3a95a..1df92065a587 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 cbf4718365fe..7844b6e26cdb 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 707aae160ab4..6db3d51fd202 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 7deba22ffec1..daa22091d38a 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"
More information about the Python-checkins
mailing list