Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings

Commit 760b8cf

Browse filesBrowse files
Erlend Egeberg AaslandAlexWaygoodCAM-Gerlach
authored
gh-94017: Improve clarity of sqlite3 transaction handling docs (#94320)
Co-authored-by: Alex Waygood <Alex.Waygood@Gmail.com> Co-authored-by: CAM Gerlach <CAM.Gerlach@Gerlach.CAM>
1 parent 14fea6b commit 760b8cf
Copy full SHA for 760b8cf

File tree

Expand file treeCollapse file tree

1 file changed

+61
-39
lines changed
Filter options
Expand file treeCollapse file tree

1 file changed

+61
-39
lines changed

‎Doc/library/sqlite3.rst

Copy file name to clipboardExpand all lines: Doc/library/sqlite3.rst
+61-39Lines changed: 61 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -406,14 +406,24 @@ Connection Objects
406406

407407
.. attribute:: isolation_level
408408

409-
Get or set the current default isolation level. :const:`None` for autocommit mode or
410-
one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
411-
:ref:`sqlite3-controlling-transactions` for a more detailed explanation.
409+
This attribute controls the :ref:`transaction handling
410+
<sqlite3-controlling-transactions>` performed by ``sqlite3``.
411+
If set to :const:`None`, transactions are never implicitly opened.
412+
If set to one of ``"DEFERRED"``, ``"IMMEDIATE"``, or ``"EXCLUSIVE"``,
413+
corresponding to the underlying `SQLite transaction behaviour`_,
414+
implicit :ref:`transaction management
415+
<sqlite3-controlling-transactions>` is performed.
416+
417+
If not overridden by the *isolation_level* parameter of :func:`connect`,
418+
the default is ``""``, which is an alias for ``"DEFERRED"``.
412419

413420
.. attribute:: in_transaction
414421

422+
This read-only attribute corresponds to the low-level SQLite
423+
`autocommit mode`_.
424+
415425
:const:`True` if a transaction is active (there are uncommitted changes),
416-
:const:`False` otherwise. Read-only attribute.
426+
:const:`False` otherwise.
417427

418428
.. versionadded:: 3.2
419429

@@ -868,21 +878,27 @@ Cursor Objects
868878

869879
.. method:: execute(sql[, parameters])
870880

871-
Executes an SQL statement. Values may be bound to the statement using
881+
Execute an SQL statement. Values may be bound to the statement using
872882
:ref:`placeholders <sqlite3-placeholders>`.
873883

874884
:meth:`execute` will only execute a single SQL statement. If you try to execute
875885
more than one statement with it, it will raise a :exc:`ProgrammingError`. Use
876886
:meth:`executescript` if you want to execute multiple SQL statements with one
877887
call.
878888

889+
If :attr:`~Connection.isolation_level` is not :const:`None`,
890+
*sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
891+
and there is no open transaction,
892+
a transaction is implicitly opened before executing *sql*.
893+
879894

880895
.. method:: executemany(sql, seq_of_parameters)
881896

882-
Executes a :ref:`parameterized <sqlite3-placeholders>` SQL command
897+
Execute a :ref:`parameterized <sqlite3-placeholders>` SQL command
883898
against all parameter sequences or mappings found in the sequence
884-
*seq_of_parameters*. The :mod:`sqlite3` module also allows using an
899+
*seq_of_parameters*. It is also possible to use an
885900
:term:`iterator` yielding parameters instead of a sequence.
901+
Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
886902

887903
.. literalinclude:: ../includes/sqlite3/executemany_1.py
888904

@@ -893,12 +909,13 @@ Cursor Objects
893909

894910
.. method:: executescript(sql_script)
895911

896-
This is a nonstandard convenience method for executing multiple SQL statements
897-
at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
898-
gets as a parameter. This method disregards :attr:`isolation_level`; any
899-
transaction control must be added to *sql_script*.
912+
Execute multiple SQL statements at once.
913+
If there is a pending transaciton,
914+
an implicit ``COMMIT`` statement is executed first.
915+
No other implicit transaction control is performed;
916+
any transaction control must be added to *sql_script*.
900917

901-
*sql_script* can be an instance of :class:`str`.
918+
*sql_script* must be a :class:`string <str>`.
902919

903920
Example:
904921

@@ -1425,38 +1442,43 @@ This section shows recipes for common adapters and converters.
14251442
Controlling Transactions
14261443
------------------------
14271444

1428-
The underlying ``sqlite3`` library operates in ``autocommit`` mode by default,
1429-
but the Python :mod:`sqlite3` module by default does not.
1430-
1431-
``autocommit`` mode means that statements that modify the database take effect
1432-
immediately. A ``BEGIN`` or ``SAVEPOINT`` statement disables ``autocommit``
1433-
mode, and a ``COMMIT``, a ``ROLLBACK``, or a ``RELEASE`` that ends the
1434-
outermost transaction, turns ``autocommit`` mode back on.
1435-
1436-
The Python :mod:`sqlite3` module by default issues a ``BEGIN`` statement
1437-
implicitly before a Data Modification Language (DML) statement (i.e.
1438-
``INSERT``/``UPDATE``/``DELETE``/``REPLACE``).
1439-
1440-
You can control which kind of ``BEGIN`` statements :mod:`sqlite3` implicitly
1441-
executes via the *isolation_level* parameter to the :func:`connect`
1442-
call, or via the :attr:`isolation_level` property of connections.
1443-
If you specify no *isolation_level*, a plain ``BEGIN`` is used, which is
1444-
equivalent to specifying ``DEFERRED``. Other possible values are ``IMMEDIATE``
1445-
and ``EXCLUSIVE``.
1446-
1447-
You can disable the :mod:`sqlite3` module's implicit transaction management by
1448-
setting :attr:`isolation_level` to ``None``. This will leave the underlying
1449-
``sqlite3`` library operating in ``autocommit`` mode. You can then completely
1450-
control the transaction state by explicitly issuing ``BEGIN``, ``ROLLBACK``,
1451-
``SAVEPOINT``, and ``RELEASE`` statements in your code.
1452-
1453-
Note that :meth:`~Cursor.executescript` disregards
1454-
:attr:`isolation_level`; any transaction control must be added explicitly.
1445+
The ``sqlite3`` module does not adhere to the transaction handling recommended
1446+
by :pep:`249`.
1447+
1448+
If the connection attribute :attr:`~Connection.isolation_level`
1449+
is not :const:`None`,
1450+
new transactions are implicitly opened before
1451+
:meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
1452+
``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements.
1453+
Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
1454+
to respectively commit and roll back pending transactions.
1455+
You can choose the underlying `SQLite transaction behaviour`_ —
1456+
that is, whether and what type of ``BEGIN`` statements ``sqlite3``
1457+
implicitly executes –
1458+
via the :attr:`~Connection.isolation_level` attribute.
1459+
1460+
If :attr:`~Connection.isolation_level` is set to :const:`None`,
1461+
no transactions are implicitly opened at all.
1462+
This leaves the underlying SQLite library in `autocommit mode`_,
1463+
but also allows the user to perform their own transaction handling
1464+
using explicit SQL statements.
1465+
The underlying SQLite library autocommit mode can be queried using the
1466+
:attr:`~Connection.in_transaction` attribute.
1467+
1468+
The :meth:`~Cursor.executescript` method implicitly commits
1469+
any pending transaction before execution of the given SQL script,
1470+
regardless of the value of :attr:`~Connection.isolation_level`.
14551471

14561472
.. versionchanged:: 3.6
14571473
:mod:`sqlite3` used to implicitly commit an open transaction before DDL
14581474
statements. This is no longer the case.
14591475

1476+
.. _autocommit mode:
1477+
https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
1478+
1479+
.. _SQLite transaction behaviour:
1480+
https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions
1481+
14601482

14611483
Using :mod:`sqlite3` efficiently
14621484
--------------------------------

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.