8000 [3.10] gh-94635: Add Reference, How-to, and Explanation headings to s… · python/cpython@6765a78 · GitHub
[go: up one dir, main page]

Skip to content

Commit 6765a78

Browse files
[3.10] gh-94635: Add Reference, How-to, and Explanation headings to sqlite3 docs (GH-94636) (#95483)
Co-authored-by: CAM Gerlach <CAM.Gerlach@Gerlach.CAM>. (cherry picked from commit 6c439b9) Co-authored-by: Erlend Egeberg Aasland <erlend.aasland@protonmail.com> * Fix refs
1 parent 356ed4c commit 6765a78

File tree

1 file changed

+120
-96
lines changed

1 file changed

+120
-96
lines changed

Doc/library/sqlite3.rst

Lines changed: 120 additions & 96 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,15 @@ The sqlite3 module was written by Gerhard Häring. It provides an SQL interface
2525
compliant with the DB-API 2.0 specification described by :pep:`249`, and
2626
requires SQLite 3.7.15 or newer.
2727

28+
This document includes four main sections:
29+
30+
* :ref:`sqlite3-tutorial` teaches how to use the sqlite3 module.
31+
* :ref:`sqlite3-reference` describes the classes and functions this module
32+
defines.
33+
* :ref:`sqlite3-howtos` details how to handle specific tasks.
34+
* :ref:`sqlite3-explanation` provides in-depth background on
35+
transaction control.
36+
2837

2938
.. _sqlite3-tutorial:
3039

@@ -136,10 +145,15 @@ both styles:
136145
PEP written by Marc-André Lemburg.
137146

138147

148+
.. _sqlite3-reference:
149+
150+
Reference
151+
---------
152+
139153
.. _sqlite3-module-contents:
140154

141155
Module functions and constants
142-
------------------------------
156+
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
143157

144158

145159
.. data:: apilevel
@@ -375,8 +389,8 @@ Module functions and constants
375389

376390
.. _sqlite3-connection-objects:
377391

378-
Connection Objects
379-
------------------
392+
Connection objects
393+
^^^^^^^^^^^^^^^^^^
380394

381395
.. class:: Connection
382396

@@ -773,8 +787,8 @@ Connection Objects
773787

774788
.. _sqlite3-cursor-objects:
775789

776-
Cursor Objects
777-
--------------
790+
Cursor objects
791+
^^^^^^^^^^^^^^
778792

779793
A ``Cursor`` object represents a `database cursor`_
780794
which is used to execute SQL statements,
@@ -950,8 +964,8 @@ Cursor Objects
950964

951965
.. _sqlite3-row-objects:
952966

953-
Row Objects
954-
-----------
967+
Row objects
968+
^^^^^^^^^^^
955969

956970
.. class:: Row
957971

@@ -1013,8 +1027,8 @@ Now we plug :class:`Row` in::
10131027
35.14
10141028

10151029

1016-
PrepareProtocol Objects
1017-
-----------------------
1030+
PrepareProtocol objects
1031+
^^^^^^^^^^^^^^^^^^^^^^^
10181032

10191033
.. class:: PrepareProtocol
10201034

@@ -1026,7 +1040,7 @@ PrepareProtocol Objects
10261040
.. _sqlite3-exceptions:
10271041

10281042
Exceptions
1029-
----------
1043+
^^^^^^^^^^
10301044

10311045
The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
10321046

@@ -1101,11 +1115,7 @@ The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
11011115
.. _sqlite3-types:
11021116

11031117
SQLite and Python types
1104-
-----------------------
1105-
1106-
1107-
Introduction
1108-
^^^^^^^^^^^^
1118+
^^^^^^^^^^^^^^^^^^^^^^^
11091119

11101120
SQLite natively supports the following types: ``NULL``, ``INTEGER``,
11111121
``REAL``, ``TEXT``, ``BLOB``.
@@ -1145,10 +1155,18 @@ This is how SQLite types are converted to Python types by default:
11451155
+-------------+----------------------------------------------+
11461156

11471157
The type system of the :mod:`sqlite3` module is extensible in two ways: you can
1148-
store additional Python types in an SQLite database via object adaptation, and
1149-
you can let the :mod:`sqlite3` module convert SQLite types to different Python
1150-
types via converters.
1158+
store additional Python types in an SQLite database via
1159+
:ref:`object adapters <sqlite3-adapters>`,
1160+
and you can let the ``sqlite3`` module convert SQLite types to
1161+
Python types via :ref:`converters <sqlite3-converters>`.
1162+
1163+
1164+
.. _sqlite3-howtos:
11511165

1166+
How-to guides
1167+
-------------
1168+
1169+
.. _sqlite3-adapters:
11521170

11531171
Using adapters to store custom Python types in SQLite databases
11541172
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
@@ -1192,6 +1210,8 @@ This function can then be registered using :func:`register_adapter`.
11921210
.. literalinclude:: ../includes/sqlite3/adapter_point_2.py
11931211

11941212

1213+
.. _sqlite3-converters:
1214+
11951215
Converting SQLite values to custom Python types
11961216
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
11971217

@@ -1264,7 +1284,7 @@ timestamp converter.
12641284

12651285
.. _sqlite3-adapter-converter-recipes:
12661286

1267-
Adapter and Converter Recipes
1287+
Adapter and converter recipes
12681288
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
12691289

12701290
This section shows recipes for common adapters and converters.
@@ -1307,83 +1327,6 @@ This section shows recipes for common adapters and converters.
13071327
sqlite3.register_converter("timestamp", convert_timestamp)
13081328
13091329
1310-
.. _sqlite3-controlling-transactions:
1311-
1312-
Controlling Transactions
1313-
------------------------
1314-
1315-
The ``sqlite3`` module does not adhere to the transaction handling recommended
1316-
by :pep:`249`.
1317-
1318-
If the connection attribute :attr:`~Connection.isolation_level`
1319-
is not :const:`None`,
1320-
new transactions are implicitly opened before
1321-
:meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
1322-
``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements.
1323-
Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
1324-
to respectively commit and roll back pending transactions.
1325-
You can choose the underlying `SQLite transaction behaviour`_ —
1326-
that is, whether and what type of ``BEGIN`` statements ``sqlite3``
1327-
implicitly executes –
1328-
via the :attr:`~Connection.isolation_level` attribute.
1329-
1330-
If :attr:`~Connection.isolation_level` is set to :const:`None`,
1331-
no transactions are implicitly opened at all.
1332-
This leaves the underlying SQLite library in `autocommit mode`_,
1333-
but also allows the user to perform their own transaction handling
1334-
using explicit SQL statements.
1335-
The underlying SQLite library autocommit mode can be queried using the
1336-
:attr:`~Connection.in_transaction` attribute.
1337-
1338-
The :meth:`~Cursor.executescript` method implicitly commits
1339-
any pending transaction before execution of the given SQL script,
1340-
regardless of the value of :attr:`~Connection.isolation_level`.
1341-
1342-
.. versionchanged:: 3.6
1343-
:mod:`sqlite3` used to implicitly commit an open transaction before DDL
1344-
statements. This is no longer the case.
1345-
1346-
.. _autocommit mode:
1347-
https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
1348-
1349-
.. _SQLite transaction behaviour:
1350-
https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions
1351-
1352-
1353-
.. _sqlite3-uri-tricks:
1354-
1355-
SQLite URI tricks
1356-
-----------------
1357-
1358-
Some useful URI tricks include:
1359-
1360-
* Open a database in read-only mode::
1361-
1362-
con = sqlite3.connect("file:template.db?mode=ro", uri=True)
1363-
1364-
* Do not implicitly create a new database file if it does not already exist;
1365-
will raise :exc:`~sqlite3.OperationalError` if unable to create a new file::
1366-
1367-
con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
1368-
1369-
* Create a shared named in-memory database::
1370-
1371-
con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
1372-
con2 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
1373-
con1.execute("create table t(t)")
1374-
con1.execute("insert into t values(28)")
1375-
con1.commit()
1376-
rows = con2.execute("select * from t").fetchall()
1377-
1378-
More information about this feature, including a list of parameters,
1379-
can be found in the `SQLite URI documentation`_.
1380-
1381-
.. _SQLite URI documentation: https://www.sqlite.org/uri.html
1382-
1383-
Using :mod:`sqlite3` efficiently
1384< 1241 /code>-
--------------------------------
1385-
1386-
13871330
.. _sqlite3-connection-shortcuts:
13881331

13891332
Using connection shortcut methods
@@ -1401,6 +1344,8 @@ directly using only a single call on the :class:`Connection` object.
14011344
.. literalinclude:: ../includes/sqlite3/shortcut_methods.py
14021345

14031346

1347+
.. _sqlite3-columns-by-name:
1348+
14041349
Accessing columns by name instead of by index
14051350
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
14061351

@@ -1436,3 +1381,82 @@ the context manager is a no-op.
14361381
nor closes the connection.
14371382

14381383
.. literalinclude:: ../includes/sqlite3/ctx_manager.py
1384+
1385+
1386+
.. _sqlite3-uri-tricks:
1387+
1388+
Working with SQLite URIs
1389+
^^^^^^^^^^^^^^^^^^^^^^^^
1390+
1391+
Some useful URI tricks include:
1392+
1393+
* Open a database in read-only mode::
1394+
1395+
con = sqlite3.connect("file:template.db?mode=ro", uri=True)
1396+
1397+
* Do not implicitly create a new database file if it does not already exist;
1398+
will raise :exc:`~sqlite3.OperationalError` if unable to create a new file::
1399+
1400+
con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
1401+
1402+
* Create a shared named in-memory database::
1403+
1404+
con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
1405+
con2 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
1406+
con1.execute("create table t(t)")
1407+
con1.execute("insert into t values(28)")
1408+
con1.commit()
1409+
rows = con2.execute("select * from t").fetchall()
1410+
1411+
More information about this feature, including a list of parameters,
1412+
can be found in the `SQLite URI documentation`_.
1413+
1414+
.. _SQLite URI documentation: https://www.sqlite.org/uri.html
1415+
1416+
1417+
.. _sqlite3-explanation:
1418+
1419+
Explanation
1420+
-----------
1421+
1422+
.. _sqlite3-controlling-transactions:
1423+
1424+
Transaction control
1425+
^^^^^^^^^^^^^^^^^^^
1426+
1427+
The ``sqlite3`` module does not adhere to the transaction handling recommended
1428+
by :pep:`249`.
1429+
1430+
If the connection attribute :attr:`~Connection.isolation_level`
1431+
is not :const:`None`,
1432+
new transactions are implicitly opened before
1433+
:meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
1434+
``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements.
1435+
Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
1436+
to respectively commit and roll back pending transactions.
1437+
You can choose the underlying `SQLite transaction behaviour`_ —
1438+
that is, whether and what type of ``BEGIN`` statements ``sqlite3``
1439+
implicitly executes –
1440+
via the :attr:`~Connection.isolation_level` attribute.
1441+
1442+
If :attr:`~Connection.isolation_level` is set to :const:`None`,
1443+
no transactions are implicitly opened at all.
1444+
This leaves the underlying SQLite library in `autocommit mode`_,
1445+
but also allows the user to perform their own transaction handling
1446+
using explicit SQL statements.
1447+
The underlying SQLite library autocommit mode can be queried using the
1448+
:attr:`~Connection.in_transaction` attribute.
1449+
1450+
The :meth:`~Cursor.executescript` method implicitly commits
1451+
any pending transaction before execution of the given SQL script,
1452+
regardless of the value of :attr:`~Connection.isolation_level`.
1453+
1454+
.. versionchanged:: 3.6
1455+
:mod:`sqlite3` used to implicitly commit an open transaction before DDL
1456+
statements. This is no longer the case.
1457+
1458+
.. _autocommit mode:
1459+
https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
1460+
1461+
.. _SQLite transaction behaviour:
1462+
https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions

0 commit comments

Comments
 (0)
0