[Python-checkins] gh-94017: Improve clarity of sqlite3 transaction handling docs (GH-94320)
miss-islington
webhook-mailer at python.org
Wed Jul 6 17:07:45 EDT 2022
https://github.com/python/cpython/commit/7e3b6affa0b52eb13d1244d80d907c305d38ed70
commit: 7e3b6affa0b52eb13d1244d80d907c305d38ed70
branch: 3.10
author: Miss Islington (bot) <31488909+miss-islington at users.noreply.github.com>
committer: miss-islington <31488909+miss-islington at users.noreply.github.com>
date: 2022-07-06T14:07:36-07:00
summary:
gh-94017: Improve clarity of sqlite3 transaction handling docs (GH-94320)
Co-authored-by: Alex Waygood <Alex.Waygood at Gmail.com>
Co-authored-by: CAM Gerlach <CAM.Gerlach at Gerlach.CAM>
(cherry picked from commit 760b8cf0c887fbc5191611a7e7d4b8c0c4f15edc)
Co-authored-by: Erlend Egeberg Aasland <erlend.aasland at innova.no>
files:
M Doc/library/sqlite3.rst
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index b0be6761d7185..ac0d03062079c 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -360,14 +360,24 @@ Connection Objects
.. attribute:: isolation_level
- Get or set the current default isolation level. :const:`None` for autocommit mode or
- one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
- :ref:`sqlite3-controlling-transactions` for a more detailed explanation.
+ This attribute controls the :ref:`transaction handling
+ <sqlite3-controlling-transactions>` performed by ``sqlite3``.
+ If set to :const:`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.
+
+ If not overridden by the *isolation_level* parameter of :func:`connect`,
+ the default is ``""``, which is an alias for ``"DEFERRED"``.
.. attribute:: in_transaction
+ This read-only attribute corresponds to the low-level SQLite
+ `autocommit mode`_.
+
:const:`True` if a transaction is active (there are uncommitted changes),
- :const:`False` otherwise. Read-only attribute.
+ :const:`False` otherwise.
.. versionadded:: 3.2
@@ -695,7 +705,7 @@ Cursor Objects
.. method:: execute(sql[, parameters])
- Executes an SQL statement. Values may be bound to the statement using
+ Execute an SQL statement. Values may be bound to the statement using
:ref:`placeholders <sqlite3-placeholders>`.
:meth:`execute` will only execute a single SQL statement. If you try to execute
@@ -703,13 +713,19 @@ Cursor Objects
:meth:`executescript` if you want to execute multiple SQL statements with one
call.
+ If :attr:`~Connection.isolation_level` is not :const:`None`,
+ *sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
+ and there is no open transaction,
+ a transaction is implicitly opened before executing *sql*.
+
.. method:: executemany(sql, seq_of_parameters)
- Executes a :ref:`parameterized <sqlite3-placeholders>` SQL command
+ Execute a :ref:`parameterized <sqlite3-placeholders>` SQL command
against all parameter sequences or mappings found in the sequence
- *seq_of_parameters*. The :mod:`sqlite3` module also allows using an
+ *seq_of_parameters*. It is also possible to use an
:term:`iterator` yielding parameters instead of a sequence.
+ Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
.. literalinclude:: ../includes/sqlite3/executemany_1.py
@@ -720,12 +736,13 @@ Cursor Objects
.. method:: executescript(sql_script)
- This is a nonstandard convenience method for executing multiple SQL statements
- at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
- gets as a parameter. This method disregards :attr:`isolation_level`; any
- transaction control must be added to *sql_script*.
+ Execute multiple SQL statements at once.
+ If there is a pending transaciton,
+ an implicit ``COMMIT`` statement is executed first.
+ No other implicit transaction control is performed;
+ any transaction control must be added to *sql_script*.
- *sql_script* can be an instance of :class:`str`.
+ *sql_script* must be a :class:`string <str>`.
Example:
@@ -1183,38 +1200,43 @@ This section shows recipes for common adapters and converters.
Controlling Transactions
------------------------
-The underlying ``sqlite3`` library operates in ``autocommit`` mode by default,
-but the Python :mod:`sqlite3` module by default does not.
-
-``autocommit`` mode means that statements that modify the database take effect
-immediately. A ``BEGIN`` or ``SAVEPOINT`` statement disables ``autocommit``
-mode, and a ``COMMIT``, a ``ROLLBACK``, or a ``RELEASE`` that ends the
-outermost transaction, turns ``autocommit`` mode back on.
-
-The Python :mod:`sqlite3` module by default issues a ``BEGIN`` statement
-implicitly before a Data Modification Language (DML) statement (i.e.
-``INSERT``/``UPDATE``/``DELETE``/``REPLACE``).
-
-You can control which kind of ``BEGIN`` statements :mod:`sqlite3` implicitly
-executes via the *isolation_level* parameter to the :func:`connect`
-call, or via the :attr:`isolation_level` property of connections.
-If you specify no *isolation_level*, a plain ``BEGIN`` is used, which is
-equivalent to specifying ``DEFERRED``. Other possible values are ``IMMEDIATE``
-and ``EXCLUSIVE``.
-
-You can disable the :mod:`sqlite3` module's implicit transaction management by
-setting :attr:`isolation_level` to ``None``. This will leave the underlying
-``sqlite3`` library operating in ``autocommit`` mode. You can then completely
-control the transaction state by explicitly issuing ``BEGIN``, ``ROLLBACK``,
-``SAVEPOINT``, and ``RELEASE`` statements in your code.
-
-Note that :meth:`~Cursor.executescript` disregards
-:attr:`isolation_level`; any transaction control must be added explicitly.
+The ``sqlite3`` module does not adhere to the transaction handling recommended
+by :pep:`249`.
+
+If the connection attribute :attr:`~Connection.isolation_level`
+is not :const:`None`,
+new transactions are implicitly opened before
+:meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
+``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements.
+Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
+to respectively commit and roll back pending transactions.
+You can choose the underlying `SQLite transaction behaviour`_ —
+that is, whether and what type of ``BEGIN`` statements ``sqlite3``
+implicitly executes –
+via the :attr:`~Connection.isolation_level` attribute.
+
+If :attr:`~Connection.isolation_level` is set to :const:`None`,
+no transactions are implicitly opened at all.
+This leaves the underlying SQLite library in `autocommit mode`_,
+but also allows the user to perform their own transaction handling
+using explicit SQL statements.
+The underlying SQLite library autocommit mode can be queried using the
+:attr:`~Connection.in_transaction` attribute.
+
+The :meth:`~Cursor.executescript` method implicitly commits
+any pending transaction before execution of the given SQL script,
+regardless of the value of :attr:`~Connection.isolation_level`.
.. versionchanged:: 3.6
:mod:`sqlite3` used to implicitly commit an open transaction before DDL
statements. This is no longer the case.
+.. _autocommit mode:
+ https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
+
+.. _SQLite transaction behaviour:
+ https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions
+
Using :mod:`sqlite3` efficiently
--------------------------------
More information about the Python-checkins
mailing list