@@ -25,6 +25,15 @@ The sqlite3 module was written by Gerhard Häring. It provides an SQL interface
25
25
compliant with the DB-API 2.0 specification described by :pep: `249 `, and
26
26
requires SQLite 3.7.15 or newer.
27
27
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
+
28
37
29
38
.. _sqlite3-tutorial :
30
39
@@ -136,10 +145,15 @@ both styles:
136
145
PEP written by Marc-André Lemburg.
137
146
138
147
148
+ .. _sqlite3-reference :
149
+
150
+ Reference
151
+ ---------
152
+
139
153
.. _sqlite3-module-contents :
140
154
141
155
Module functions and constants
142
- ------------------------------
156
+ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
143
157
144
158
145
159
.. data :: apilevel
@@ -375,8 +389,8 @@ Module functions and constants
375
389
376
390
.. _sqlite3-connection-objects :
377
391
378
- Connection Objects
379
- ------------------
392
+ Connection objects
393
+ ^^^^^^^^^^^^^^^^^^
380
394
381
395
.. class :: Connection
382
396
@@ -773,8 +787,8 @@ Connection Objects
773
787
774
788
.. _sqlite3-cursor-objects :
775
789
776
- Cursor Objects
777
- --------------
790
+ Cursor objects
791
+ ^^^^^^^^^^^^^^
778
792
779
793
A ``Cursor `` object represents a `database cursor `_
780
794
which is used to execute SQL statements,
@@ -950,8 +964,8 @@ Cursor Objects
950
964
951
965
.. _sqlite3-row-objects :
952
966
953
- Row Objects
954
- -----------
967
+ Row objects
968
+ ^^^^^^^^^^^
955
969
956
970
.. class :: Row
957
971
@@ -1013,8 +1027,8 @@ Now we plug :class:`Row` in::
1013
1027
35.14
1014
1028
1015
1029
1016
- PrepareProtocol Objects
1017
- -----------------------
1030
+ PrepareProtocol objects
1031
+ ^^^^^^^^^^^^^^^^^^^^^^^
1018
1032
1019
1033
.. class :: PrepareProtocol
1020
1034
@@ -1026,7 +1040,7 @@ PrepareProtocol Objects
1026
1040
.. _sqlite3-exceptions :
1027
1041
1028
1042
Exceptions
1029
- ----------
1043
+ ^^^^^^^^^^
1030
1044
1031
1045
The exception hierarchy is defined by the DB-API 2.0 (:pep: `249 `).
1032
1046
@@ -1101,11 +1115,7 @@ The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
1101
1115
.. _sqlite3-types :
1102
1116
1103
1117
SQLite and Python types
1104
- -----------------------
1105
-
1106
-
1107
- Introduction
1108
- ^^^^^^^^^^^^
1118
+ ^^^^^^^^^^^^^^^^^^^^^^^
1109
1119
1110
1120
SQLite natively supports the following types: ``NULL ``, ``INTEGER ``,
1111
1121
``REAL ``, ``TEXT ``, ``BLOB ``.
@@ -1145,10 +1155,18 @@ This is how SQLite types are converted to Python types by default:
1145
1155
+-------------+----------------------------------------------+
1146
1156
1147
1157
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 :
1151
1165
1166
+ How-to guides
1167
+ -------------
1168
+
1169
+ .. _sqlite3-adapters :
1152
1170
1153
1171
Using adapters to store custom Python types in SQLite databases
1154
1172
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
@@ -1192,6 +1210,8 @@ This function can then be registered using :func:`register_adapter`.
1192
1210
.. literalinclude :: ../includes/sqlite3/adapter_point_2.py
1193
1211
1194
1212
1213
+ .. _sqlite3-converters :
1214
+
1195
1215
Converting SQLite values to custom Python types
1196
1216
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1197
1217
@@ -1264,7 +1284,7 @@ timestamp converter.
1264
1284
1265
1285
.. _sqlite3-adapter-converter-recipes :
1266
1286
1267
- Adapter and Converter Recipes
1287
+ Adapter and converter recipes
1268
1288
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1269
1289
1270
1290
This section shows recipes for common adapters and converters.
@@ -1307,83 +1327,6 @@ This section shows recipes for common adapters and converters.
1307
1327
sqlite3.register_converter("timestamp", convert_timestamp)
1308
1328
1309
1329
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
-
1387
1330
.. _sqlite3-connection-shortcuts :
1388
1331
1389
1332
Using connection shortcut methods
@@ -1401,6 +1344,8 @@ directly using only a single call on the :class:`Connection` object.
1401
1344
.. literalinclude :: ../includes/sqlite3/shortcut_methods.py
1402
1345
1403
1346
1347
+ .. _sqlite3-columns-by-name :
1348
+
1404
1349
Accessing columns by name instead of by index
1405
1350
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1406
1351
@@ -1436,3 +1381,82 @@ the context manager is a no-op.
1436
1381
nor closes the connection.
1437
1382
1438
1383
.. 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