Debian Codegraph @

Doc/library/sqlite3.rst

raw
1 :mod:`!sqlite3` --- DB-API 2.0 interface for SQLite databases
2 =============================================================
3
4 .. module:: sqlite3
5 :synopsis: A DB-API 2.0 implementation using SQLite 3.x.
6
7 .. sectionauthor:: Gerhard Häring <[email protected]>
8
9 **Source code:** :source:`Lib/sqlite3/`
10
11 .. Make sure we always doctest the tutorial with an empty database.
12
13 .. testsetup::
14
15 import sqlite3
16 src = sqlite3.connect(":memory:", isolation_level=None)
17 dst = sqlite3.connect("tutorial.db", isolation_level=None)
18 src.backup(dst)
19 src.close()
20 dst.close()
21 del src, dst
22
23 .. _sqlite3-intro:
24
25 SQLite is a C library that provides a lightweight disk-based database that
26 doesn't require a separate server process and allows accessing the database
27 using a nonstandard variant of the SQL query language. Some applications can use
28 SQLite for internal data storage. It's also possible to prototype an
29 application using SQLite and then port the code to a larger database such as
30 PostgreSQL or Oracle.
31
32 The :mod:`!sqlite3` module was written by Gerhard Häring. It provides an SQL interface
33 compliant with the DB-API 2.0 specification described by :pep:`249`, and
34 requires SQLite 3.15.2 or newer.
35
36 This document includes four main sections:
37
38 * :ref:`sqlite3-tutorial` teaches how to use the :mod:`!sqlite3` module.
39 * :ref:`sqlite3-reference` describes the classes and functions this module
40 defines.
41 * :ref:`sqlite3-howtos` details how to handle specific tasks.
42 * :ref:`sqlite3-explanation` provides in-depth background on
43 transaction control.
44
45 .. seealso::
46
47 https://www.sqlite.org
48 The SQLite web page; the documentation describes the syntax and the
49 available data types for the supported SQL dialect.
50
51 https://www.w3schools.com/sql/
52 Tutorial, reference and examples for learning SQL syntax.
53
54 :pep:`249` - Database API Specification 2.0
55 PEP written by Marc-André Lemburg.
56
57
58 .. We use the following practices for SQL code:
59 - UPPERCASE for keywords
60 - snake_case for schema
61 - single quotes for string literals
62 - singular for table names
63 - if needed, use double quotes for table and column names
64
65 .. _sqlite3-tutorial:
66
67 Tutorial
68 --------
69
70 In this tutorial, you will create a database of Monty Python movies
71 using basic :mod:`!sqlite3` functionality.
72 It assumes a fundamental understanding of database concepts,
73 including `cursors`_ and `transactions`_.
74
75 First, we need to create a new database and open
76 a database connection to allow :mod:`!sqlite3` to work with it.
77 Call :func:`sqlite3.connect` to create a connection to
78 the database :file:`tutorial.db` in the current working directory,
79 implicitly creating it if it does not exist:
80
81 .. testcode::
82
83 import sqlite3
84 con = sqlite3.connect("tutorial.db")
85
86 The returned :class:`Connection` object ``con``
87 represents the connection to the on-disk database.
88
89 In order to execute SQL statements and fetch results from SQL queries,
90 we will need to use a database cursor.
91 Call :meth:`con.cursor() <Connection.cursor>` to create the :class:`Cursor`:
92
93 .. testcode::
94
95 cur = con.cursor()
96
97 Now that we've got a database connection and a cursor,
98 we can create a database table ``movie`` with columns for title,
99 release year, and review score.
100 For simplicity, we can just use column names in the table declaration --
101 thanks to the `flexible typing`_ feature of SQLite,
102 specifying the data types is optional.
103 Execute the ``CREATE TABLE`` statement
104 by calling :meth:`cur.execute(...) <Cursor.execute>`:
105
106 .. testcode::
107
108 cur.execute("CREATE TABLE movie(title, year, score)")
109
110 .. Ideally, we'd use sqlite_schema instead of sqlite_master below,
111 but SQLite versions older than 3.33.0 do not recognise that variant.
112
113 We can verify that the new table has been created by querying
114 the ``sqlite_master`` table built-in to SQLite,
115 which should now contain an entry for the ``movie`` table definition
116 (see `The Schema Table`_ for details).
117 Execute that query by calling :meth:`cur.execute(...) <Cursor.execute>`,
118 assign the result to ``res``,
119 and call :meth:`res.fetchone() <Cursor.fetchone>` to fetch the resulting row:
120
121 .. doctest::
122
123 >>> res = cur.execute("SELECT name FROM sqlite_master")
124 >>> res.fetchone()
125 ('movie',)
126
127 We can see that the table has been created,
128 as the query returns a :class:`tuple` containing the table's name.
129 If we query ``sqlite_master`` for a non-existent table ``spam``,
130 :meth:`!res.fetchone` will return ``None``:
131
132 .. doctest::
133
134 >>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
135 >>> res.fetchone() is None
136 True
137
138 Now, add two rows of data supplied as SQL literals
139 by executing an ``INSERT`` statement,
140 once again by calling :meth:`cur.execute(...) <Cursor.execute>`:
141
142 .. testcode::
143
144 cur.execute("""
145 INSERT INTO movie VALUES
146 ('Monty Python and the Holy Grail', 1975, 8.2),
147 ('And Now for Something Completely Different', 1971, 7.5)
148 """)
149
150 The ``INSERT`` statement implicitly opens a transaction,
151 which needs to be committed before changes are saved in the database
152 (see :ref:`sqlite3-controlling-transactions` for details).
153 Call :meth:`con.commit() <Connection.commit>` on the connection object
154 to commit the transaction:
155
156 .. testcode::
157
158 con.commit()
159
160 We can verify that the data was inserted correctly
161 by executing a ``SELECT`` query.
162 Use the now-familiar :meth:`cur.execute(...) <Cursor.execute>` to
163 assign the result to ``res``,
164 and call :meth:`res.fetchall() <Cursor.fetchall>` to return all resulting rows:
165
166 .. doctest::
167
168 >>> res = cur.execute("SELECT score FROM movie")
169 >>> res.fetchall()
170 [(8.2,), (7.5,)]
171
172 The result is a :class:`list` of two :class:`!tuple`\s, one per row,
173 each containing that row's ``score`` value.
174
175 Now, insert three more rows by calling
176 :meth:`cur.executemany(...) <Cursor.executemany>`:
177
178 .. testcode::
179
180 data = [
181 ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
182 ("Monty Python's The Meaning of Life", 1983, 7.5),
183 ("Monty Python's Life of Brian", 1979, 8.0),
184 ]
185 cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
186 con.commit() # Remember to commit the transaction after executing INSERT.
187
188 Notice that ``?`` placeholders are used to bind ``data`` to the query.
189 Always use placeholders instead of :ref:`string formatting <tut-formatting>`
190 to bind Python values to SQL statements,
191 to avoid `SQL injection attacks`_
192 (see :ref:`sqlite3-placeholders` for more details).
193
194 We can verify that the new rows were inserted
195 by executing a ``SELECT`` query,
196 this time iterating over the results of the query:
197
198 .. doctest::
199
200 >>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
201 ... print(row)
202 (1971, 'And Now for Something Completely Different')
203 (1975, 'Monty Python and the Holy Grail')
204 (1979, "Monty Python's Life of Brian")
205 (1982, 'Monty Python Live at the Hollywood Bowl')
206 (1983, "Monty Python's The Meaning of Life")
207
208 Each row is a two-item :class:`tuple` of ``(year, title)``,
209 matching the columns selected in the query.
210
211 Finally, verify that the database has been written to disk
212 by calling :meth:`con.close() <Connection.close>`
213 to close the existing connection, opening a new one,
214 creating a new cursor, then querying the database:
215
216 .. doctest::
217
218 >>> con.close()
219 >>> new_con = sqlite3.connect("tutorial.db")
220 >>> new_cur = new_con.cursor()
221 >>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
222 >>> title, year = res.fetchone()
223 >>> print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')
224 The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975
225 >>> new_con.close()
226
227 You've now created an SQLite database using the :mod:`!sqlite3` module,
228 inserted data and retrieved values from it in multiple ways.
229
230 .. _SQL injection attacks: https://en.wikipedia.org/wiki/SQL_injection
231 .. _The Schema Table: https://www.sqlite.org/schematab.html
232 .. _cursors: https://en.wikipedia.org/wiki/Cursor_(databases)
233 .. _flexible typing: https://www.sqlite.org/flextypegood.html
234 .. _sqlite_master: https://www.sqlite.org/schematab.html
235 .. _transactions: https://en.wikipedia.org/wiki/Database_transaction
236
237 .. seealso::
238
239 * :ref:`sqlite3-howtos` for further reading:
240
241 * :ref:`sqlite3-placeholders`
242 * :ref:`sqlite3-adapters`
243 * :ref:`sqlite3-converters`
244 * :ref:`sqlite3-connection-context-manager`
245 * :ref:`sqlite3-howto-row-factory`
246
247 * :ref:`sqlite3-explanation` for in-depth background on transaction control.
248
249 .. _sqlite3-reference:
250
251 Reference
252 ---------
253
254 .. We keep the old sqlite3-module-contents ref to prevent breaking links.
255 .. _sqlite3-module-contents:
256
257 .. _sqlite3-module-functions:
258
259 Module functions
260 ^^^^^^^^^^^^^^^^
261
262 .. function:: connect(database, timeout=5.0, detect_types=0, \
263 isolation_level="DEFERRED", check_same_thread=True, \
264 factory=sqlite3.Connection, cached_statements=128, \
265 uri=False, *, \
266 autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)
267
268 Open a connection to an SQLite database.
269
270 :param database:
271 The path to the database file to be opened.
272 You can pass ``":memory:"`` to create an `SQLite database existing only
273 in memory <https://sqlite.org/inmemorydb.html>`_, and open a connection
274 to it.
275 :type database: :term:`path-like object`
276
277 :param float timeout:
278 How many seconds the connection should wait before raising
279 an :exc:`OperationalError` when a table is locked.
280 If another connection opens a transaction to modify a table,
281 that table will be locked until the transaction is committed.
282 Default five seconds.
283
284 :param int detect_types:
285 Control whether and how data types not
286 :ref:`natively supported by SQLite <sqlite3-types>`
287 are looked up to be converted to Python types,
288 using the converters registered with :func:`register_converter`.
289 Set it to any combination (using ``|``, bitwise or) of
290 :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`
291 to enable this.
292 Column names take precedence over declared types if both flags are set.
293 By default (``0``), type detection is disabled.
294
295 :param isolation_level:
296 Control legacy transaction handling behaviour.
297 See :attr:`Connection.isolation_level` and
298 :ref:`sqlite3-transaction-control-isolation-level` for more information.
299 Can be ``"DEFERRED"`` (default), ``"EXCLUSIVE"`` or ``"IMMEDIATE"``;
300 or ``None`` to disable opening transactions implicitly.
301 Has no effect unless :attr:`Connection.autocommit` is set to
302 :const:`~sqlite3.LEGACY_TRANSACTION_CONTROL` (the default).
303 :type isolation_level: str | None
304
305 :param bool check_same_thread:
306 If ``True`` (default), :exc:`ProgrammingError` will be raised
307 if the database connection is used by a thread
308 other than the one that created it.
309 If ``False``, the connection may be accessed in multiple threads;
310 write operations may need to be serialized by the user
311 to avoid data corruption.
312 See :attr:`threadsafety` for more information.
313
314 :param ~sqlite3.Connection factory:
315 A custom subclass of :class:`Connection` to create the connection with,
316 if not the default :class:`Connection` class.
317
318 :param int cached_statements:
319 The number of statements that :mod:`!sqlite3`
320 should internally cache for this connection, to avoid parsing overhead.
321 By default, 128 statements.
322
323 :param bool uri:
324 If set to ``True``, *database* is interpreted as a
325 :abbr:`URI (Uniform Resource Identifier)` with a file path
326 and an optional query string.
327 The scheme part *must* be ``"file:"``,
328 and the path can be relative or absolute.
329 The query string allows passing parameters to SQLite,
330 enabling various :ref:`sqlite3-uri-tricks`.
331
332 :param autocommit:
333 Control :pep:`249` transaction handling behaviour.
334 See :attr:`Connection.autocommit` and
335 :ref:`sqlite3-transaction-control-autocommit` for more information.
336 *autocommit* currently defaults to
337 :const:`~sqlite3.LEGACY_TRANSACTION_CONTROL`.
338 The default will change to ``False`` in a future Python release.
339 :type autocommit: bool
340
341 :rtype: ~sqlite3.Connection
342
343 .. audit-event:: sqlite3.connect database sqlite3.connect
344 .. audit-event:: sqlite3.connect/handle connection_handle sqlite3.connect
345
346 .. versionchanged:: 3.4
347 Added the *uri* parameter.
348
349 .. versionchanged:: 3.7
350 *database* can now also be a :term:`path-like object`, not only a string.
351
352 .. versionchanged:: 3.10
353 Added the ``sqlite3.connect/handle`` auditing event.
354
355 .. versionchanged:: 3.12
356 Added the *autocommit* parameter.
357
358 .. versionchanged:: 3.13
359 Positional use of the parameters *timeout*, *detect_types*,
360 *isolation_level*, *check_same_thread*, *factory*, *cached_statements*,
361 and *uri* is deprecated.
362 They will become keyword-only parameters in Python 3.15.
363
364 .. function:: complete_statement(statement)
365
366 Return ``True`` if the string *statement* appears to contain
367 one or more complete SQL statements.
368 No syntactic verification or parsing of any kind is performed,
369 other than checking that there are no unclosed string literals
370 and the statement is terminated by a semicolon.
371
372 For example:
373
374 .. doctest::
375
376 >>> sqlite3.complete_statement("SELECT foo FROM bar;")
377 True
378 >>> sqlite3.complete_statement("SELECT foo")
379 False
380
381 This function may be useful during command-line input
382 to determine if the entered text seems to form a complete SQL statement,
383 or if additional input is needed before calling :meth:`~Cursor.execute`.
384
385 See :func:`!runsource` in :source:`Lib/sqlite3/__main__.py`
386 for real-world use.
387
388 .. function:: enable_callback_tracebacks(flag, /)
389
390 Enable or disable callback tracebacks.
391 By default you will not get any tracebacks in user-defined functions,
392 aggregates, converters, authorizer callbacks etc. If you want to debug them,
393 you can call this function with *flag* set to ``True``. Afterwards, you
394 will get tracebacks from callbacks on :data:`sys.stderr`. Use ``False``
395 to disable the feature again.
396
397 .. note::
398
399 Errors in user-defined function callbacks are logged as unraisable exceptions.
400 Use an :func:`unraisable hook handler <sys.unraisablehook>` for
401 introspection of the failed callback.
402
403 .. function:: register_adapter(type, adapter, /)
404
405 Register an *adapter* :term:`callable` to adapt the Python type *type*
406 into an SQLite type.
407 The adapter is called with a Python object of type *type* as its sole
408 argument, and must return a value of a
409 :ref:`type that SQLite natively understands <sqlite3-types>`.
410
411 .. function:: register_converter(typename, converter, /)
412
413 Register the *converter* :term:`callable` to convert SQLite objects of type
414 *typename* into a Python object of a specific type.
415 The converter is invoked for all SQLite values of type *typename*;
416 it is passed a :class:`bytes` object and should return an object of the
417 desired Python type.
418 Consult the parameter *detect_types* of
419 :func:`connect` for information regarding how type detection works.
420
421 Note: *typename* and the name of the type in your query are matched
422 case-insensitively.
423
424
425 .. _sqlite3-module-constants:
426
427 Module constants
428 ^^^^^^^^^^^^^^^^
429
430 .. data:: LEGACY_TRANSACTION_CONTROL
431
432 Set :attr:`~Connection.autocommit` to this constant to select
433 old style (pre-Python 3.12) transaction control behaviour.
434 See :ref:`sqlite3-transaction-control-isolation-level` for more information.
435
436 .. data:: PARSE_DECLTYPES
437
438 Pass this flag value to the *detect_types* parameter of
439 :func:`connect` to look up a converter function using
440 the declared types for each column.
441 The types are declared when the database table is created.
442 :mod:`!sqlite3` will look up a converter function using the first word of the
443 declared type as the converter dictionary key.
444 For example:
445
446 .. code-block:: sql
447
448 CREATE TABLE test(
449 i integer primary key, ! will look up a converter named "integer"
450 p point, ! will look up a converter named "point"
451 n number(10) ! will look up a converter named "number"
452 )
453
454 This flag may be combined with :const:`PARSE_COLNAMES` using the ``|``
455 (bitwise or) operator.
456
457 .. note::
458
459 Generated fields (for example ``MAX(p)``) are returned as :class:`str`.
460 Use :const:`!PARSE_COLNAMES` to enforce types for such queries.
461
462 .. data:: PARSE_COLNAMES
463
464 Pass this flag value to the *detect_types* parameter of
465 :func:`connect` to look up a converter function by
466 using the type name, parsed from the query column name,
467 as the converter dictionary key.
468 The query column name must be wrapped in double quotes (``"``)
469 and the type name must be wrapped in square brackets (``[]``).
470
471 .. code-block:: sql
472
473 SELECT MAX(p) as "p [point]" FROM test; ! will look up converter "point"
474
475 This flag may be combined with :const:`PARSE_DECLTYPES` using the ``|``
476 (bitwise or) operator.
477
478 .. data:: SQLITE_OK
479 SQLITE_DENY
480 SQLITE_IGNORE
481
482 Flags that should be returned by the *authorizer_callback* :term:`callable`
483 passed to :meth:`Connection.set_authorizer`, to indicate whether:
484
485 * Access is allowed (:const:`!SQLITE_OK`),
486 * The SQL statement should be aborted with an error (:const:`!SQLITE_DENY`)
487 * The column should be treated as a ``NULL`` value (:const:`!SQLITE_IGNORE`)
488
489 .. data:: apilevel
490
491 String constant stating the supported DB-API level. Required by the DB-API.
492 Hard-coded to ``"2.0"``.
493
494 .. data:: paramstyle
495
496 String constant stating the type of parameter marker formatting expected by
497 the :mod:`!sqlite3` module. Required by the DB-API. Hard-coded to
498 ``"qmark"``.
499
500 .. note::
501
502 The ``named`` DB-API parameter style is also supported.
503
504 .. data:: sqlite_version
505
506 Version number of the runtime SQLite library as a :class:`string <str>`.
507
508 .. data:: sqlite_version_info
509
510 Version number of the runtime SQLite library as a :class:`tuple` of
511 :class:`integers <int>`.
512
513 .. data:: threadsafety
514
515 Integer constant required by the DB-API 2.0, stating the level of thread
516 safety the :mod:`!sqlite3` module supports. This attribute is set based on
517 the default `threading mode <https://sqlite.org/threadsafe.html>`_ the
518 underlying SQLite library is compiled with. The SQLite threading modes are:
519
520 1. **Single-thread**: In this mode, all mutexes are disabled and SQLite is
521 unsafe to use in more than a single thread at once.
522 2. **Multi-thread**: In this mode, SQLite can be safely used by multiple
523 threads provided that no single database connection is used
524 simultaneously in two or more threads.
525 3. **Serialized**: In serialized mode, SQLite can be safely used by
526 multiple threads with no restriction.
527
528 The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels
529 are as follows:
530
531 +------------------+----------------------+----------------------+-------------------------------+
532 | SQLite threading | :pep:`threadsafety | `SQLITE_THREADSAFE`_ | DB-API 2.0 meaning |
533 | mode | <0249#threadsafety>` | | |
534 +==================+======================+======================+===============================+
535 | single-thread | 0 | 0 | Threads may not share the |
536 | | | | module |
537 +------------------+----------------------+----------------------+-------------------------------+
538 | multi-thread | 1 | 2 | Threads may share the module, |
539 | | | | but not connections |
540 +------------------+----------------------+----------------------+-------------------------------+
541 | serialized | 3 | 1 | Threads may share the module, |
542 | | | | connections and cursors |
543 +------------------+----------------------+----------------------+-------------------------------+
544
545 .. _SQLITE_THREADSAFE: https://sqlite.org/compile.html#threadsafe
546
547 .. versionchanged:: 3.11
548 Set *threadsafety* dynamically instead of hard-coding it to ``1``.
549
550 .. data:: version
551
552 Version number of this module as a :class:`string <str>`.
553 This is not the version of the SQLite library.
554
555 .. deprecated-removed:: 3.12 3.14
556 This constant used to reflect the version number of the ``pysqlite``
557 package, a third-party library which used to upstream changes to
558 :mod:`!sqlite3`. Today, it carries no meaning or practical value.
559
560 .. data:: version_info
561
562 Version number of this module as a :class:`tuple` of :class:`integers <int>`.
563 This is not the version of the SQLite library.
564
565 .. deprecated-removed:: 3.12 3.14
566 This constant used to reflect the version number of the ``pysqlite``
567 package, a third-party library which used to upstream changes to
568 :mod:`!sqlite3`. Today, it carries no meaning or practical value.
569
570 .. _sqlite3-dbconfig-constants:
571
572 .. data:: SQLITE_DBCONFIG_DEFENSIVE
573 SQLITE_DBCONFIG_DQS_DDL
574 SQLITE_DBCONFIG_DQS_DML
575 SQLITE_DBCONFIG_ENABLE_FKEY
576 SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER
577 SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION
578 SQLITE_DBCONFIG_ENABLE_QPSG
579 SQLITE_DBCONFIG_ENABLE_TRIGGER
580 SQLITE_DBCONFIG_ENABLE_VIEW
581 SQLITE_DBCONFIG_LEGACY_ALTER_TABLE
582 SQLITE_DBCONFIG_LEGACY_FILE_FORMAT
583 SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE
584 SQLITE_DBCONFIG_RESET_DATABASE
585 SQLITE_DBCONFIG_TRIGGER_EQP
586 SQLITE_DBCONFIG_TRUSTED_SCHEMA
587 SQLITE_DBCONFIG_WRITABLE_SCHEMA
588
589 These constants are used for the :meth:`Connection.setconfig`
590 and :meth:`~Connection.getconfig` methods.
591
592 The availability of these constants varies depending on the version of SQLite
593 Python was compiled with.
594
595 .. versionadded:: 3.12
596
597 .. seealso::
598
599 https://www.sqlite.org/c3ref/c_dbconfig_defensive.html
600 SQLite docs: Database Connection Configuration Options
601
602
603 .. _sqlite3-connection-objects:
604
605 Connection objects
606 ^^^^^^^^^^^^^^^^^^
607
608 .. class:: Connection
609
610 Each open SQLite database is represented by a ``Connection`` object,
611 which is created using :func:`sqlite3.connect`.
612 Their main purpose is creating :class:`Cursor` objects,
613 and :ref:`sqlite3-controlling-transactions`.
614
615 .. seealso::
616
617 * :ref:`sqlite3-connection-shortcuts`
618 * :ref:`sqlite3-connection-context-manager`
619
620
621 .. versionchanged:: 3.13
622
623 A :exc:`ResourceWarning` is emitted if :meth:`close` is not called before
624 a :class:`!Connection` object is deleted.
625
626 An SQLite database connection has the following attributes and methods:
627
628 .. method:: cursor(factory=Cursor)
629
630 Create and return a :class:`Cursor` object.
631 The cursor method accepts a single optional parameter *factory*. If
632 supplied, this must be a :term:`callable` returning
633 an instance of :class:`Cursor` or its subclasses.
634
635 .. method:: blobopen(table, column, rowid, /, *, readonly=False, name="main")
636
637 Open a :class:`Blob` handle to an existing
638 :abbr:`BLOB (Binary Large OBject)`.
639
640 :param str table:
641 The name of the table where the blob is located.
642
643 :param str column:
644 The name of the column where the blob is located.
645
646 :param int rowid:
647 The row id where the blob is located.
648
649 :param bool readonly:
650 Set to ``True`` if the blob should be opened without write
651 permissions.
652 Defaults to ``False``.
653
654 :param str name:
655 The name of the database where the blob is located.
656 Defaults to ``"main"``.
657
658 :raises OperationalError:
659 When trying to open a blob in a ``WITHOUT ROWID`` table.
660
661 :rtype: Blob
662
663 .. note::
664
665 The blob size cannot be changed using the :class:`Blob` class.
666 Use the SQL function ``zeroblob`` to create a blob with a fixed size.
667
668 .. versionadded:: 3.11
669
670 .. method:: commit()
671
672 Commit any pending transaction to the database.
673 If :attr:`autocommit` is ``True``, or there is no open transaction,
674 this method does nothing.
675 If :attr:`!autocommit` is ``False``, a new transaction is implicitly
676 opened if a pending transaction was committed by this method.
677
678 .. method:: rollback()
679
680 Roll back to the start of any pending transaction.
681 If :attr:`autocommit` is ``True``, or there is no open transaction,
682 this method does nothing.
683 If :attr:`!autocommit` is ``False``, a new transaction is implicitly
684 opened if a pending transaction was rolled back by this method.
685
686 .. method:: close()
687
688 Close the database connection.
689 If :attr:`autocommit` is ``False``,
690 any pending transaction is implicitly rolled back.
691 If :attr:`!autocommit` is ``True`` or :data:`LEGACY_TRANSACTION_CONTROL`,
692 no implicit transaction control is executed.
693 Make sure to :meth:`commit` before closing
694 to avoid losing pending changes.
695
696 .. method:: execute(sql, parameters=(), /)
697
698 Create a new :class:`Cursor` object and call
699 :meth:`~Cursor.execute` on it with the given *sql* and *parameters*.
700 Return the new cursor object.
701
702 .. method:: executemany(sql, parameters, /)
703
704 Create a new :class:`Cursor` object and call
705 :meth:`~Cursor.executemany` on it with the given *sql* and *parameters*.
706 Return the new cursor object.
707
708 .. method:: executescript(sql_script, /)
709
710 Create a new :class:`Cursor` object and call
711 :meth:`~Cursor.executescript` on it with the given *sql_script*.
712 Return the new cursor object.
713
714 .. method:: create_function(name, narg, func, *, deterministic=False)
715
716 Create or remove a user-defined SQL function.
717
718 :param str name:
719 The name of the SQL function.
720
721 :param int narg:
722 The number of arguments the SQL function can accept.
723 If ``-1``, it may take any number of arguments.
724
725 :param func:
726 A :term:`callable` that is called when the SQL function is invoked.
727 The callable must return :ref:`a type natively supported by SQLite
728 <sqlite3-types>`.
729 Set to ``None`` to remove an existing SQL function.
730 :type func: :term:`callback` | None
731
732 :param bool deterministic:
733 If ``True``, the created SQL function is marked as
734 `deterministic <https://sqlite.org/deterministic.html>`_,
735 which allows SQLite to perform additional optimizations.
736
737 .. versionchanged:: 3.8
738 Added the *deterministic* parameter.
739
740 Example:
741
742 .. doctest::
743
744 >>> import hashlib
745 >>> def md5sum(t):
746 ... return hashlib.md5(t).hexdigest()
747 >>> con = sqlite3.connect(":memory:")
748 >>> con.create_function("md5", 1, md5sum)
749 >>> for row in con.execute("SELECT md5(?)", (b"foo",)):
750 ... print(row)
751 ('acbd18db4cc2f85cedef654fccc4a4d8',)
752 >>> con.close()
753
754 .. versionchanged:: 3.13
755
756 Passing *name*, *narg*, and *func* as keyword arguments is deprecated.
757 These parameters will become positional-only in Python 3.15.
758
759
760 .. method:: create_aggregate(name, n_arg, aggregate_class)
761
762 Create or remove a user-defined SQL aggregate function.
763
764 :param str name:
765 The name of the SQL aggregate function.
766
767 :param int n_arg:
768 The number of arguments the SQL aggregate function can accept.
769 If ``-1``, it may take any number of arguments.
770
771 :param aggregate_class:
772 A class must implement the following methods:
773
774 * ``step()``: Add a row to the aggregate.
775 * ``finalize()``: Return the final result of the aggregate as
776 :ref:`a type natively supported by SQLite <sqlite3-types>`.
777
778 The number of arguments that the ``step()`` method must accept
779 is controlled by *n_arg*.
780
781 Set to ``None`` to remove an existing SQL aggregate function.
782 :type aggregate_class: :term:`class` | None
783
784 Example:
785
786 .. testcode::
787
788 class MySum:
789 def __init__(self):
790 self.count = 0
791
792 def step(self, value):
793 self.count += value
794
795 def finalize(self):
796 return self.count
797
798 con = sqlite3.connect(":memory:")
799 con.create_aggregate("mysum", 1, MySum)
800 cur = con.execute("CREATE TABLE test(i)")
801 cur.execute("INSERT INTO test(i) VALUES(1)")
802 cur.execute("INSERT INTO test(i) VALUES(2)")
803 cur.execute("SELECT mysum(i) FROM test")
804 print(cur.fetchone()[0])
805
806 con.close()
807
808 .. testoutput::
809 :hide:
810
811 3
812
813 .. versionchanged:: 3.13
814
815 Passing *name*, *n_arg*, and *aggregate_class* as keyword arguments is deprecated.
816 These parameters will become positional-only in Python 3.15.
817
818
819 .. method:: create_window_function(name, num_params, aggregate_class, /)
820
821 Create or remove a user-defined aggregate window function.
822
823 :param str name:
824 The name of the SQL aggregate window function to create or remove.
825
826 :param int num_params:
827 The number of arguments the SQL aggregate window function can accept.
828 If ``-1``, it may take any number of arguments.
829
830 :param aggregate_class:
831 A class that must implement the following methods:
832
833 * ``step()``: Add a row to the current window.
834 * ``value()``: Return the current value of the aggregate.
835 * ``inverse()``: Remove a row from the current window.
836 * ``finalize()``: Return the final result of the aggregate as
837 :ref:`a type natively supported by SQLite <sqlite3-types>`.
838
839 The number of arguments that the ``step()`` and ``value()`` methods
840 must accept is controlled by *num_params*.
841
842 Set to ``None`` to remove an existing SQL aggregate window function.
843
844 :raises NotSupportedError:
845 If used with a version of SQLite older than 3.25.0,
846 which does not support aggregate window functions.
847
848 :type aggregate_class: :term:`class` | None
849
850 .. versionadded:: 3.11
851
852 Example:
853
854 .. testcode::
855
856 # Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc
857 class WindowSumInt:
858 def __init__(self):
859 self.count = 0
860
861 def step(self, value):
862 """Add a row to the current window."""
863 self.count += value
864
865 def value(self):
866 """Return the current value of the aggregate."""
867 return self.count
868
869 def inverse(self, value):
870 """Remove a row from the current window."""
871 self.count -= value
872
873 def finalize(self):
874 """Return the final value of the aggregate.
875
876 Any clean-up actions should be placed here.
877 """
878 return self.count
879
880
881 con = sqlite3.connect(":memory:")
882 cur = con.execute("CREATE TABLE test(x, y)")
883 values = [
884 ("a", 4),
885 ("b", 5),
886 ("c", 3),
887 ("d", 8),
888 ("e", 1),
889 ]
890 cur.executemany("INSERT INTO test VALUES(?, ?)", values)
891 con.create_window_function("sumint", 1, WindowSumInt)
892 cur.execute("""
893 SELECT x, sumint(y) OVER (
894 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
895 ) AS sum_y
896 FROM test ORDER BY x
897 """)
898 print(cur.fetchall())
899 con.close()
900
901 .. testoutput::
902 :hide:
903
904 [('a', 9), ('b', 12), ('c', 16), ('d', 12), ('e', 9)]
905
906 .. method:: create_collation(name, callable, /)
907
908 Create a collation named *name* using the collating function *callable*.
909 *callable* is passed two :class:`string <str>` arguments,
910 and it should return an :class:`integer <int>`:
911
912 * ``1`` if the first is ordered higher than the second
913 * ``-1`` if the first is ordered lower than the second
914 * ``0`` if they are ordered equal
915
916 The following example shows a reverse sorting collation:
917
918 .. testcode::
919
920 def collate_reverse(string1, string2):
921 if string1 == string2:
922 return 0
923 elif string1 < string2:
924 return 1
925 else:
926 return -1
927
928 con = sqlite3.connect(":memory:")
929 con.create_collation("reverse", collate_reverse)
930
931 cur = con.execute("CREATE TABLE test(x)")
932 cur.executemany("INSERT INTO test(x) VALUES(?)", [("a",), ("b",)])
933 cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse")
934 for row in cur:
935 print(row)
936 con.close()
937
938 .. testoutput::
939 :hide:
940
941 ('b',)
942 ('a',)
943
944 Remove a collation function by setting *callable* to ``None``.
945
946 .. versionchanged:: 3.11
947 The collation name can contain any Unicode character. Earlier, only
948 ASCII characters were allowed.
949
950
951 .. method:: interrupt()
952
953 Call this method from a different thread to abort any queries that might
954 be executing on the connection.
955 Aborted queries will raise an :exc:`OperationalError`.
956
957
958 .. method:: set_authorizer(authorizer_callback)
959
960 Register :term:`callable` *authorizer_callback* to be invoked
961 for each attempt to access a column of a table in the database.
962 The callback should return one of :const:`SQLITE_OK`,
963 :const:`SQLITE_DENY`, or :const:`SQLITE_IGNORE`
964 to signal how access to the column should be handled
965 by the underlying SQLite library.
966
967 The first argument to the callback signifies what kind of operation is to be
968 authorized. The second and third argument will be arguments or ``None``
969 depending on the first argument. The 4th argument is the name of the database
970 ("main", "temp", etc.) if applicable. The 5th argument is the name of the
971 inner-most trigger or view that is responsible for the access attempt or
972 ``None`` if this access attempt is directly from input SQL code.
973
974 Please consult the SQLite documentation about the possible values for the first
975 argument and the meaning of the second and third argument depending on the first
976 one. All necessary constants are available in the :mod:`!sqlite3` module.
977
978 Passing ``None`` as *authorizer_callback* will disable the authorizer.
979
980 .. versionchanged:: 3.11
981 Added support for disabling the authorizer using ``None``.
982
983 .. versionchanged:: 3.13
984 Passing *authorizer_callback* as a keyword argument is deprecated.
985 The parameter will become positional-only in Python 3.15.
986
987
988 .. method:: set_progress_handler(progress_handler, n)
989
990 Register :term:`callable` *progress_handler* to be invoked for every *n*
991 instructions of the SQLite virtual machine. This is useful if you want to
992 get called from SQLite during long-running operations, for example to update
993 a GUI.
994
995 If you want to clear any previously installed progress handler, call the
996 method with ``None`` for *progress_handler*.
997
998 Returning a non-zero value from the handler function will terminate the
999 currently executing query and cause it to raise a :exc:`DatabaseError`
1000 exception.
1001
1002 .. versionchanged:: 3.13
1003 Passing *progress_handler* as a keyword argument is deprecated.
1004 The parameter will become positional-only in Python 3.15.
1005
1006
1007 .. method:: set_trace_callback(trace_callback)
1008
1009 Register :term:`callable` *trace_callback* to be invoked
1010 for each SQL statement that is actually executed by the SQLite backend.
1011
1012 The only argument passed to the callback is the statement (as
1013 :class:`str`) that is being executed. The return value of the callback is
1014 ignored. Note that the backend does not only run statements passed to the
1015 :meth:`Cursor.execute` methods. Other sources include the
1016 :ref:`transaction management <sqlite3-controlling-transactions>` of the
1017 :mod:`!sqlite3` module and the execution of triggers defined in the current
1018 database.
1019
1020 Passing ``None`` as *trace_callback* will disable the trace callback.
1021
1022 .. note::
1023 Exceptions raised in the trace callback are not propagated. As a
1024 development and debugging aid, use
1025 :meth:`~sqlite3.enable_callback_tracebacks` to enable printing
1026 tracebacks from exceptions raised in the trace callback.
1027
1028 .. versionadded:: 3.3
1029
1030 .. versionchanged:: 3.13
1031 Passing *trace_callback* as a keyword argument is deprecated.
1032 The parameter will become positional-only in Python 3.15.
1033
1034
1035 .. method:: enable_load_extension(enabled, /)
1036
1037 Enable the SQLite engine to load SQLite extensions from shared libraries
1038 if *enabled* is ``True``;
1039 else, disallow loading SQLite extensions.
1040 SQLite extensions can define new functions,
1041 aggregates or whole new virtual table implementations. One well-known
1042 extension is the fulltext-search extension distributed with SQLite.
1043
1044 .. note::
1045
1046 The :mod:`!sqlite3` module is not built with loadable extension support by
1047 default, because some platforms (notably macOS) have SQLite
1048 libraries which are compiled without this feature.
1049 To get loadable extension support,
1050 you must pass the :option:`--enable-loadable-sqlite-extensions` option
1051 to :program:`configure`.
1052
1053 .. audit-event:: sqlite3.enable_load_extension connection,enabled sqlite3.Connection.enable_load_extension
1054
1055 .. versionadded:: 3.2
1056
1057 .. versionchanged:: 3.10
1058 Added the ``sqlite3.enable_load_extension`` auditing event.
1059
1060 .. We cannot doctest the load extension API, since there is no convenient
1061 way to skip it.
1062
1063 .. code-block::
1064
1065 con.enable_load_extension(True)
1066
1067 # Load the fulltext search extension
1068 con.execute("select load_extension('./fts3.so')")
1069
1070 # alternatively you can load the extension using an API call:
1071 # con.load_extension("./fts3.so")
1072
1073 # disable extension loading again
1074 con.enable_load_extension(False)
1075
1076 # example from SQLite wiki
1077 con.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)")
1078 con.executescript("""
1079 INSERT INTO recipe (name, ingredients) VALUES('broccoli stew', 'broccoli peppers cheese tomatoes');
1080 INSERT INTO recipe (name, ingredients) VALUES('pumpkin stew', 'pumpkin onions garlic celery');
1081 INSERT INTO recipe (name, ingredients) VALUES('broccoli pie', 'broccoli cheese onions flour');
1082 INSERT INTO recipe (name, ingredients) VALUES('pumpkin pie', 'pumpkin sugar flour butter');
1083 """)
1084 for row in con.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"):
1085 print(row)
1086
1087 .. method:: load_extension(path, /, *, entrypoint=None)
1088
1089 Load an SQLite extension from a shared library.
1090 Enable extension loading with :meth:`enable_load_extension` before
1091 calling this method.
1092
1093 :param str path:
1094
1095 The path to the SQLite extension.
1096
1097 :param entrypoint:
1098
1099 Entry point name.
1100 If ``None`` (the default),
1101 SQLite will come up with an entry point name of its own;
1102 see the SQLite docs `Loading an Extension`_ for details.
1103
1104 :type entrypoint: str | None
1105
1106 .. audit-event:: sqlite3.load_extension connection,path sqlite3.Connection.load_extension
1107
1108 .. versionadded:: 3.2
1109
1110 .. versionchanged:: 3.10
1111 Added the ``sqlite3.load_extension`` auditing event.
1112
1113 .. versionchanged:: 3.12
1114 Added the *entrypoint* parameter.
1115
1116 .. _Loading an Extension: https://www.sqlite.org/loadext.html#loading_an_extension
1117
1118 .. method:: iterdump(*, filter=None)
1119
1120 Return an :term:`iterator` to dump the database as SQL source code.
1121 Useful when saving an in-memory database for later restoration.
1122 Similar to the ``.dump`` command in the :program:`sqlite3` shell.
1123
1124 :param filter:
1125
1126 An optional ``LIKE`` pattern for database objects to dump, e.g. ``prefix_%``.
1127 If ``None`` (the default), all database objects will be included.
1128
1129 :type filter: str | None
1130
1131 Example:
1132
1133 .. testcode::
1134
1135 # Convert file example.db to SQL dump file dump.sql
1136 con = sqlite3.connect('example.db')
1137 with open('dump.sql', 'w') as f:
1138 for line in con.iterdump():
1139 f.write('%s\n' % line)
1140 con.close()
1141
1142 .. seealso::
1143
1144 :ref:`sqlite3-howto-encoding`
1145
1146 .. versionchanged:: 3.13
1147 Added the *filter* parameter.
1148
1149 .. method:: backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)
1150
1151 Create a backup of an SQLite database.
1152
1153 Works even if the database is being accessed by other clients
1154 or concurrently by the same connection.
1155
1156 :param ~sqlite3.Connection target:
1157 The database connection to save the backup to.
1158
1159 :param int pages:
1160 The number of pages to copy at a time.
1161 If equal to or less than ``0``,
1162 the entire database is copied in a single step.
1163 Defaults to ``-1``.
1164
1165 :param progress:
1166 If set to a :term:`callable`,
1167 it is invoked with three integer arguments for every backup iteration:
1168 the *status* of the last iteration,
1169 the *remaining* number of pages still to be copied,
1170 and the *total* number of pages.
1171 Defaults to ``None``.
1172 :type progress: :term:`callback` | None
1173
1174 :param str name:
1175 The name of the database to back up.
1176 Either ``"main"`` (the default) for the main database,
1177 ``"temp"`` for the temporary database,
1178 or the name of a custom database as attached using the
1179 ``ATTACH DATABASE`` SQL statement.
1180
1181 :param float sleep:
1182 The number of seconds to sleep between successive attempts
1183 to back up remaining pages.
1184
1185 Example 1, copy an existing database into another:
1186
1187 .. testcode::
1188
1189 def progress(status, remaining, total):
1190 print(f'Copied {total-remaining} of {total} pages...')
1191
1192 src = sqlite3.connect('example.db')
1193 dst = sqlite3.connect('backup.db')
1194 with dst:
1195 src.backup(dst, pages=1, progress=progress)
1196 dst.close()
1197 src.close()
1198
1199 .. testoutput::
1200 :hide:
1201
1202 Copied 0 of 0 pages...
1203
1204 Example 2, copy an existing database into a transient copy:
1205
1206 .. testcode::
1207
1208 src = sqlite3.connect('example.db')
1209 dst = sqlite3.connect(':memory:')
1210 src.backup(dst)
1211 dst.close()
1212 src.close()
1213
1214 .. versionadded:: 3.7
1215
1216 .. seealso::
1217
1218 :ref:`sqlite3-howto-encoding`
1219
1220 .. method:: getlimit(category, /)
1221
1222 Get a connection runtime limit.
1223
1224 :param int category:
1225 The `SQLite limit category`_ to be queried.
1226
1227 :rtype: int
1228
1229 :raises ProgrammingError:
1230 If *category* is not recognised by the underlying SQLite library.
1231
1232 Example, query the maximum length of an SQL statement
1233 for :class:`Connection` ``con`` (the default is 1000000000):
1234
1235 .. testsetup:: sqlite3.limits
1236
1237 import sqlite3
1238 con = sqlite3.connect(":memory:")
1239 con.setlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH, 1_000_000_000)
1240 con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 10)
1241
1242 .. doctest:: sqlite3.limits
1243
1244 >>> con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)
1245 1000000000
1246
1247 .. versionadded:: 3.11
1248
1249
1250 .. method:: setlimit(category, limit, /)
1251
1252 Set a connection runtime limit.
1253 Attempts to increase a limit above its hard upper bound are silently
1254 truncated to the hard upper bound. Regardless of whether or not the limit
1255 was changed, the prior value of the limit is returned.
1256
1257 :param int category:
1258 The `SQLite limit category`_ to be set.
1259
1260 :param int limit:
1261 The value of the new limit.
1262 If negative, the current limit is unchanged.
1263
1264 :rtype: int
1265
1266 :raises ProgrammingError:
1267 If *category* is not recognised by the underlying SQLite library.
1268
1269 Example, limit the number of attached databases to 1
1270 for :class:`Connection` ``con`` (the default limit is 10):
1271
1272 .. doctest:: sqlite3.limits
1273
1274 >>> con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1)
1275 10
1276 >>> con.getlimit(sqlite3.SQLITE_LIMIT_ATTACHED)
1277 1
1278
1279 .. testcleanup:: sqlite3.limits
1280
1281 con.close()
1282
1283 .. versionadded:: 3.11
1284
1285 .. _SQLite limit category: https://www.sqlite.org/c3ref/c_limit_attached.html
1286
1287
1288 .. method:: getconfig(op, /)
1289
1290 Query a boolean connection configuration option.
1291
1292 :param int op:
1293 A :ref:`SQLITE_DBCONFIG code <sqlite3-dbconfig-constants>`.
1294
1295 :rtype: bool
1296
1297 .. versionadded:: 3.12
1298
1299 .. method:: setconfig(op, enable=True, /)
1300
1301 Set a boolean connection configuration option.
1302
1303 :param int op:
1304 A :ref:`SQLITE_DBCONFIG code <sqlite3-dbconfig-constants>`.
1305
1306 :param bool enable:
1307 ``True`` if the configuration option should be enabled (default);
1308 ``False`` if it should be disabled.
1309
1310 .. versionadded:: 3.12
1311
1312 .. method:: serialize(*, name="main")
1313
1314 Serialize a database into a :class:`bytes` object. For an
1315 ordinary on-disk database file, the serialization is just a copy of the
1316 disk file. For an in-memory database or a "temp" database, the
1317 serialization is the same sequence of bytes which would be written to
1318 disk if that database were backed up to disk.
1319
1320 :param str name:
1321 The database name to be serialized.
1322 Defaults to ``"main"``.
1323
1324 :rtype: bytes
1325
1326 .. note::
1327
1328 This method is only available if the underlying SQLite library has the
1329 serialize API.
1330
1331 .. versionadded:: 3.11
1332
1333
1334 .. method:: deserialize(data, /, *, name="main")
1335
1336 Deserialize a :meth:`serialized <serialize>` database into a
1337 :class:`Connection`.
1338 This method causes the database connection to disconnect from database
1339 *name*, and reopen *name* as an in-memory database based on the
1340 serialization contained in *data*.
1341
1342 :param bytes data:
1343 A serialized database.
1344
1345 :param str name:
1346 The database name to deserialize into.
1347 Defaults to ``"main"``.
1348
1349 :raises OperationalError:
1350 If the database connection is currently involved in a read
1351 transaction or a backup operation.
1352
1353 :raises DatabaseError:
1354 If *data* does not contain a valid SQLite database.
1355
1356 :raises OverflowError:
1357 If :func:`len(data) <len>` is larger than ``2**63 - 1``.
1358
1359 .. note::
1360
1361 This method is only available if the underlying SQLite library has the
1362 deserialize API.
1363
1364 .. versionadded:: 3.11
1365
1366 .. attribute:: autocommit
1367
1368 This attribute controls :pep:`249`-compliant transaction behaviour.
1369 :attr:`!autocommit` has three allowed values:
1370
1371 * ``False``: Select :pep:`249`-compliant transaction behaviour,
1372 implying that :mod:`!sqlite3` ensures a transaction is always open.
1373 Use :meth:`commit` and :meth:`rollback` to close transactions.
1374
1375 This is the recommended value of :attr:`!autocommit`.
1376
1377 * ``True``: Use SQLite's `autocommit mode`_.
1378 :meth:`commit` and :meth:`rollback` have no effect in this mode.
1379
1380 * :data:`LEGACY_TRANSACTION_CONTROL`:
1381 Pre-Python 3.12 (non-:pep:`249`-compliant) transaction control.
1382 See :attr:`isolation_level` for more details.
1383
1384 This is currently the default value of :attr:`!autocommit`.
1385
1386 Changing :attr:`!autocommit` to ``False`` will open a new transaction,
1387 and changing it to ``True`` will commit any pending transaction.
1388
1389 See :ref:`sqlite3-transaction-control-autocommit` for more details.
1390
1391 .. note::
1392
1393 The :attr:`isolation_level` attribute has no effect unless
1394 :attr:`autocommit` is :data:`LEGACY_TRANSACTION_CONTROL`.
1395
1396 .. versionadded:: 3.12
1397
1398 .. attribute:: in_transaction
1399
1400 This read-only attribute corresponds to the low-level SQLite
1401 `autocommit mode`_.
1402
1403 ``True`` if a transaction is active (there are uncommitted changes),
1404 ``False`` otherwise.
1405
1406 .. versionadded:: 3.2
1407
1408 .. attribute:: isolation_level
1409
1410 Controls the :ref:`legacy transaction handling mode
1411 <sqlite3-transaction-control-isolation-level>` of :mod:`!sqlite3`.
1412 If set to ``None``, transactions are never implicitly opened.
1413 If set to one of ``"DEFERRED"``, ``"IMMEDIATE"``, or ``"EXCLUSIVE"``,
1414 corresponding to the underlying `SQLite transaction behaviour`_,
1415 :ref:`implicit transaction management
1416 <sqlite3-transaction-control-isolation-level>` is performed.
1417
1418 If not overridden by the *isolation_level* parameter of :func:`connect`,
1419 the default is ``""``, which is an alias for ``"DEFERRED"``.
1420
1421 .. note::
1422
1423 Using :attr:`autocommit` to control transaction handling is
1424 recommended over using :attr:`!isolation_level`.
1425 :attr:`!isolation_level` has no effect unless :attr:`autocommit` is
1426 set to :data:`LEGACY_TRANSACTION_CONTROL` (the default).
1427
1428 .. attribute:: row_factory
1429
1430 The initial :attr:`~Cursor.row_factory`
1431 for :class:`Cursor` objects created from this connection.
1432 Assigning to this attribute does not affect the :attr:`!row_factory`
1433 of existing cursors belonging to this connection, only new ones.
1434 Is ``None`` by default,
1435 meaning each row is returned as a :class:`tuple`.
1436
1437 See :ref:`sqlite3-howto-row-factory` for more details.
1438
1439 .. versionchanged:: 3.13.14
1440 Deleting the ``row_factory`` attribute is no longer allowed.
1441
1442 .. attribute:: text_factory
1443
1444 A :term:`callable` that accepts a :class:`bytes` parameter
1445 and returns a text representation of it.
1446 The callable is invoked for SQLite values with the ``TEXT`` data type.
1447 By default, this attribute is set to :class:`str`.
1448
1449 See :ref:`sqlite3-howto-encoding` for more details.
1450
1451 .. versionchanged:: 3.13.14
1452 Deleting the ``text_factory`` attribute is no longer allowed.
1453
1454 .. attribute:: total_changes
1455
1456 Return the total number of database rows that have been modified, inserted, or
1457 deleted since the database connection was opened.
1458
1459
1460 .. _sqlite3-cursor-objects:
1461
1462 Cursor objects
1463 ^^^^^^^^^^^^^^
1464
1465 A ``Cursor`` object represents a `database cursor`_
1466 which is used to execute SQL statements,
1467 and manage the context of a fetch operation.
1468 Cursors are created using :meth:`Connection.cursor`,
1469 or by using any of the :ref:`connection shortcut methods
1470 <sqlite3-connection-shortcuts>`.
1471
1472 Cursor objects are :term:`iterators <iterator>`,
1473 meaning that if you :meth:`~Cursor.execute` a ``SELECT`` query,
1474 you can simply iterate over the cursor to fetch the resulting rows:
1475
1476 .. testsetup:: sqlite3.cursor
1477
1478 import sqlite3
1479 con = sqlite3.connect(":memory:", isolation_level=None)
1480 cur = con.execute("CREATE TABLE data(t)")
1481 cur.execute("INSERT INTO data VALUES(1)")
1482
1483 .. testcode:: sqlite3.cursor
1484
1485 for row in cur.execute("SELECT t FROM data"):
1486 print(row)
1487
1488 .. testoutput:: sqlite3.cursor
1489 :hide:
1490
1491 (1,)
1492
1493 .. _database cursor: https://en.wikipedia.org/wiki/Cursor_(databases)
1494
1495 .. class:: Cursor
1496
1497 A :class:`Cursor` instance has the following attributes and methods.
1498
1499 .. index:: single: ? (question mark); in SQL statements
1500 .. index:: single: : (colon); in SQL statements
1501
1502 .. method:: execute(sql, parameters=(), /)
1503
1504 Execute a single SQL statement,
1505 optionally binding Python values using
1506 :ref:`placeholders <sqlite3-placeholders>`.
1507
1508 :param str sql:
1509 A single SQL statement.
1510
1511 :param parameters:
1512 Python values to bind to placeholders in *sql*.
1513 A :class:`!dict` if named placeholders are used.
1514 A :term:`!sequence` if unnamed placeholders are used.
1515 See :ref:`sqlite3-placeholders`.
1516 :type parameters: :class:`dict` | :term:`sequence`
1517
1518 :raises ProgrammingError:
1519 If *sql* contains more than one SQL statement.
1520
1521 If :attr:`~Connection.autocommit` is
1522 :data:`LEGACY_TRANSACTION_CONTROL`,
1523 :attr:`~Connection.isolation_level` is not ``None``,
1524 *sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
1525 and there is no open transaction,
1526 a transaction is implicitly opened before executing *sql*.
1527
1528 .. deprecated-removed:: 3.12 3.14
1529
1530 :exc:`DeprecationWarning` is emitted if
1531 :ref:`named placeholders <sqlite3-placeholders>` are used
1532 and *parameters* is a sequence instead of a :class:`dict`.
1533 Starting with Python 3.14, :exc:`ProgrammingError` will
1534 be raised instead.
1535
1536 Use :meth:`executescript` to execute multiple SQL statements.
1537
1538 .. method:: executemany(sql, parameters, /)
1539
1540 For every item in *parameters*,
1541 repeatedly execute the :ref:`parameterized <sqlite3-placeholders>`
1542 :abbr:`DML (Data Manipulation Language)` SQL statement *sql*.
1543
1544 Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
1545
1546 :param str sql:
1547 A single SQL DML statement.
1548
1549 :param parameters:
1550 An :term:`!iterable` of parameters to bind with
1551 the placeholders in *sql*.
1552 See :ref:`sqlite3-placeholders`.
1553 :type parameters: :term:`iterable`
1554
1555 :raises ProgrammingError:
1556 If *sql* contains more than one SQL statement,
1557 or is not a DML statement.
1558
1559 Example:
1560
1561 .. testcode:: sqlite3.cursor
1562
1563 rows = [
1564 ("row1",),
1565 ("row2",),
1566 ]
1567 # cur is an sqlite3.Cursor object
1568 cur.executemany("INSERT INTO data VALUES(?)", rows)
1569
1570 .. testcleanup:: sqlite3.cursor
1571
1572 con.close()
1573
1574 .. note::
1575
1576 Any resulting rows are discarded,
1577 including DML statements with `RETURNING clauses`_.
1578
1579 .. _RETURNING clauses: https://www.sqlite.org/lang_returning.html
1580
1581 .. deprecated-removed:: 3.12 3.14
1582
1583 :exc:`DeprecationWarning` is emitted if
1584 :ref:`named placeholders <sqlite3-placeholders>` are used
1585 and the items in *parameters* are sequences
1586 instead of :class:`dict`\s.
1587 Starting with Python 3.14, :exc:`ProgrammingError` will
1588 be raised instead.
1589
1590 .. method:: executescript(sql_script, /)
1591
1592 Execute the SQL statements in *sql_script*.
1593 If the :attr:`~Connection.autocommit` is
1594 :data:`LEGACY_TRANSACTION_CONTROL`
1595 and there is a pending transaction,
1596 an implicit ``COMMIT`` statement is executed first.
1597 No other implicit transaction control is performed;
1598 any transaction control must be added to *sql_script*.
1599
1600 *sql_script* must be a :class:`string <str>`.
1601
1602 Example:
1603
1604 .. testcode:: sqlite3.cursor
1605
1606 # cur is an sqlite3.Cursor object
1607 cur.executescript("""
1608 BEGIN;
1609 CREATE TABLE person(firstname, lastname, age);
1610 CREATE TABLE book(title, author, published);
1611 CREATE TABLE publisher(name, address);
1612 COMMIT;
1613 """)
1614
1615 .. method:: fetchone()
1616
1617 If :attr:`~Cursor.row_factory` is ``None``,
1618 return the next row query result set as a :class:`tuple`.
1619 Else, pass it to the row factory and return its result.
1620 Return ``None`` if no more data is available.
1621
1622
1623 .. method:: fetchmany(size=cursor.arraysize)
1624
1625 Return the next set of rows of a query result as a :class:`list`.
1626 Return an empty list if no more rows are available.
1627
1628 The number of rows to fetch per call is specified by the *size* parameter.
1629 If *size* is not given, :attr:`arraysize` determines the number of rows
1630 to be fetched.
1631 If fewer than *size* rows are available,
1632 as many rows as are available are returned.
1633
1634 Note there are performance considerations involved with the *size* parameter.
1635 For optimal performance, it is usually best to use the arraysize attribute.
1636 If the *size* parameter is used, then it is best for it to retain the same
1637 value from one :meth:`fetchmany` call to the next.
1638
1639 .. versionchanged:: 3.13.8
1640 Negative *size* values are rejected by raising :exc:`ValueError`.
1641
1642 .. method:: fetchall()
1643
1644 Return all (remaining) rows of a query result as a :class:`list`.
1645 Return an empty list if no rows are available.
1646 Note that the :attr:`arraysize` attribute can affect the performance of
1647 this operation.
1648
1649 .. method:: close()
1650
1651 Close the cursor now (rather than whenever ``__del__`` is called).
1652
1653 The cursor will be unusable from this point forward; a :exc:`ProgrammingError`
1654 exception will be raised if any operation is attempted with the cursor.
1655
1656 .. method:: setinputsizes(sizes, /)
1657
1658 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1659
1660 .. method:: setoutputsize(size, column=None, /)
1661
1662 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1663
1664 .. attribute:: arraysize
1665
1666 Read/write attribute that controls the number of rows returned by :meth:`fetchmany`.
1667 The default value is 1 which means a single row would be fetched per call.
1668
1669 .. versionchanged:: 3.13.8
1670 Negative values are rejected by raising :exc:`ValueError`.
1671
1672 .. attribute:: connection
1673
1674 Read-only attribute that provides the SQLite database :class:`Connection`
1675 belonging to the cursor. A :class:`Cursor` object created by
1676 calling :meth:`con.cursor() <Connection.cursor>` will have a
1677 :attr:`connection` attribute that refers to *con*:
1678
1679 .. doctest::
1680
1681 >>> con = sqlite3.connect(":memory:")
1682 >>> cur = con.cursor()
1683 >>> cur.connection == con
1684 True
1685 >>> con.close()
1686
1687 .. attribute:: description
1688
1689 Read-only attribute that provides the column names of the last query. To
1690 remain compatible with the Python DB API, it returns a 7-tuple for each
1691 column where the last six items of each tuple are ``None``.
1692
1693 It is set for ``SELECT`` statements without any matching rows as well.
1694
1695 .. attribute:: lastrowid
1696
1697 Read-only attribute that provides the row id of the last inserted row. It
1698 is only updated after successful ``INSERT`` or ``REPLACE`` statements
1699 using the :meth:`execute` method. For other statements, after
1700 :meth:`executemany` or :meth:`executescript`, or if the insertion failed,
1701 the value of ``lastrowid`` is left unchanged. The initial value of
1702 ``lastrowid`` is ``None``.
1703
1704 .. note::
1705 Inserts into ``WITHOUT ROWID`` tables are not recorded.
1706
1707 .. versionchanged:: 3.6
1708 Added support for the ``REPLACE`` statement.
1709
1710 .. attribute:: rowcount
1711
1712 Read-only attribute that provides the number of modified rows for
1713 ``INSERT``, ``UPDATE``, ``DELETE``, and ``REPLACE`` statements;
1714 is ``-1`` for other statements,
1715 including :abbr:`CTE (Common Table Expression)` queries.
1716 It is only updated by the :meth:`execute` and :meth:`executemany` methods,
1717 after the statement has run to completion.
1718 This means that any resulting rows must be fetched in order for
1719 :attr:`!rowcount` to be updated.
1720
1721 .. attribute:: row_factory
1722
1723 Control how a row fetched from this :class:`!Cursor` is represented.
1724 If ``None``, a row is represented as a :class:`tuple`.
1725 Can be set to the included :class:`sqlite3.Row`;
1726 or a :term:`callable` that accepts two arguments,
1727 a :class:`Cursor` object and the :class:`!tuple` of row values,
1728 and returns a custom object representing an SQLite row.
1729
1730 Defaults to what :attr:`Connection.row_factory` was set to
1731 when the :class:`!Cursor` was created.
1732 Assigning to this attribute does not affect
1733 :attr:`Connection.row_factory` of the parent connection.
1734
1735 See :ref:`sqlite3-howto-row-factory` for more details.
1736
1737 .. versionchanged:: 3.13.14
1738 Deleting the ``row_factory`` attribute is no longer allowed.
1739
1740
1741 .. The sqlite3.Row example used to be a how-to. It has now been incorporated
1742 into the Row reference. We keep the anchor here in order not to break
1743 existing links.
1744
1745 .. _sqlite3-columns-by-name:
1746 .. _sqlite3-row-objects:
1747
1748 Row objects
1749 ^^^^^^^^^^^
1750
1751 .. class:: Row
1752
1753 A :class:`!Row` instance serves as a highly optimized
1754 :attr:`~Connection.row_factory` for :class:`Connection` objects.
1755 It supports iteration, equality testing, :func:`len`,
1756 and :term:`mapping` access by column name and index.
1757
1758 Two :class:`!Row` objects compare equal
1759 if they have identical column names and values.
1760
1761 See :ref:`sqlite3-howto-row-factory` for more details.
1762
1763 .. method:: keys
1764
1765 Return a :class:`list` of column names as :class:`strings <str>`.
1766 Immediately after a query,
1767 it is the first member of each tuple in :attr:`Cursor.description`.
1768
1769 .. versionchanged:: 3.5
1770 Added support of slicing.
1771
1772
1773 .. _sqlite3-blob-objects:
1774
1775 Blob objects
1776 ^^^^^^^^^^^^
1777
1778 .. class:: Blob
1779
1780 .. versionadded:: 3.11
1781
1782 A :class:`Blob` instance is a :term:`file-like object`
1783 that can read and write data in an SQLite :abbr:`BLOB (Binary Large OBject)`.
1784 Call :func:`len(blob) <len>` to get the size (number of bytes) of the blob.
1785 Use indices and :term:`slices <slice>` for direct access to the blob data.
1786
1787 Use the :class:`Blob` as a :term:`context manager` to ensure that the blob
1788 handle is closed after use.
1789
1790 .. testcode::
1791
1792 con = sqlite3.connect(":memory:")
1793 con.execute("CREATE TABLE test(blob_col blob)")
1794 con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))")
1795
1796 # Write to our blob, using two write operations:
1797 with con.blobopen("test", "blob_col", 1) as blob:
1798 blob.write(b"hello, ")
1799 blob.write(b"world.")
1800 # Modify the first and last bytes of our blob
1801 blob[0] = ord("H")
1802 blob[-1] = ord("!")
1803
1804 # Read the contents of our blob
1805 with con.blobopen("test", "blob_col", 1) as blob:
1806 greeting = blob.read()
1807
1808 print(greeting) # outputs "b'Hello, world!'"
1809 con.close()
1810
1811 .. testoutput::
1812 :hide:
1813
1814 b'Hello, world!'
1815
1816 .. method:: close()
1817
1818 Close the blob.
1819
1820 The blob will be unusable from this point onward. An
1821 :class:`~sqlite3.Error` (or subclass) exception will be raised if any
1822 further operation is attempted with the blob.
1823
1824 .. method:: read(length=-1, /)
1825
1826 Read *length* bytes of data from the blob at the current offset position.
1827 If the end of the blob is reached, the data up to
1828 :abbr:`EOF (End of File)` will be returned. When *length* is not
1829 specified, or is negative, :meth:`~Blob.read` will read until the end of
1830 the blob.
1831
1832 .. method:: write(data, /)
1833
1834 Write *data* to the blob at the current offset. This function cannot
1835 change the blob length. Writing beyond the end of the blob will raise
1836 :exc:`ValueError`.
1837
1838 .. method:: tell()
1839
1840 Return the current access position of the blob.
1841
1842 .. method:: seek(offset, origin=os.SEEK_SET, /)
1843
1844 Set the current access position of the blob to *offset*. The *origin*
1845 argument defaults to :const:`os.SEEK_SET` (absolute blob positioning).
1846 Other values for *origin* are :const:`os.SEEK_CUR` (seek relative to the
1847 current position) and :const:`os.SEEK_END` (seek relative to the blob’s
1848 end).
1849
1850
1851 PrepareProtocol objects
1852 ^^^^^^^^^^^^^^^^^^^^^^^
1853
1854 .. class:: PrepareProtocol
1855
1856 The PrepareProtocol type's single purpose is to act as a :pep:`246` style
1857 adaption protocol for objects that can :ref:`adapt themselves
1858 <sqlite3-conform>` to :ref:`native SQLite types <sqlite3-types>`.
1859
1860
1861 .. _sqlite3-exceptions:
1862
1863 Exceptions
1864 ^^^^^^^^^^
1865
1866 The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
1867
1868 .. exception:: Warning
1869
1870 This exception is not currently raised by the :mod:`!sqlite3` module,
1871 but may be raised by applications using :mod:`!sqlite3`,
1872 for example if a user-defined function truncates data while inserting.
1873 ``Warning`` is a subclass of :exc:`Exception`.
1874
1875 .. exception:: Error
1876
1877 The base class of the other exceptions in this module.
1878 Use this to catch all errors with one single :keyword:`except` statement.
1879 ``Error`` is a subclass of :exc:`Exception`.
1880
1881 If the exception originated from within the SQLite library,
1882 the following two attributes are added to the exception:
1883
1884 .. attribute:: sqlite_errorcode
1885
1886 The numeric error code from the
1887 `SQLite API <https://sqlite.org/rescode.html>`_
1888
1889 .. versionadded:: 3.11
1890
1891 .. attribute:: sqlite_errorname
1892
1893 The symbolic name of the numeric error code
1894 from the `SQLite API <https://sqlite.org/rescode.html>`_
1895
1896 .. versionadded:: 3.11
1897
1898 .. exception:: InterfaceError
1899
1900 Exception raised for misuse of the low-level SQLite C API.
1901 In other words, if this exception is raised, it probably indicates a bug in the
1902 :mod:`!sqlite3` module.
1903 ``InterfaceError`` is a subclass of :exc:`Error`.
1904
1905 .. exception:: DatabaseError
1906
1907 Exception raised for errors that are related to the database.
1908 This serves as the base exception for several types of database errors.
1909 It is only raised implicitly through the specialised subclasses.
1910 ``DatabaseError`` is a subclass of :exc:`Error`.
1911
1912 .. exception:: DataError
1913
1914 Exception raised for errors caused by problems with the processed data,
1915 like numeric values out of range, and strings which are too long.
1916 ``DataError`` is a subclass of :exc:`DatabaseError`.
1917
1918 .. exception:: OperationalError
1919
1920 Exception raised for errors that are related to the database's operation,
1921 and not necessarily under the control of the programmer.
1922 For example, the database path is not found,
1923 or a transaction could not be processed.
1924 ``OperationalError`` is a subclass of :exc:`DatabaseError`.
1925
1926 .. exception:: IntegrityError
1927
1928 Exception raised when the relational integrity of the database is affected,
1929 e.g. a foreign key check fails. It is a subclass of :exc:`DatabaseError`.
1930
1931 .. exception:: InternalError
1932
1933 Exception raised when SQLite encounters an internal error.
1934 If this is raised, it may indicate that there is a problem with the runtime
1935 SQLite library.
1936 ``InternalError`` is a subclass of :exc:`DatabaseError`.
1937
1938 .. exception:: ProgrammingError
1939
1940 Exception raised for :mod:`!sqlite3` API programming errors,
1941 for example supplying the wrong number of bindings to a query,
1942 or trying to operate on a closed :class:`Connection`.
1943 ``ProgrammingError`` is a subclass of :exc:`DatabaseError`.
1944
1945 .. exception:: NotSupportedError
1946
1947 Exception raised in case a method or database API is not supported by the
1948 underlying SQLite library. For example, setting *deterministic* to
1949 ``True`` in :meth:`~Connection.create_function`, if the underlying SQLite library
1950 does not support deterministic functions.
1951 ``NotSupportedError`` is a subclass of :exc:`DatabaseError`.
1952
1953
1954 .. _sqlite3-types:
1955
1956 SQLite and Python types
1957 ^^^^^^^^^^^^^^^^^^^^^^^
1958
1959 SQLite natively supports the following types: ``NULL``, ``INTEGER``,
1960 ``REAL``, ``TEXT``, ``BLOB``.
1961
1962 The following Python types can thus be sent to SQLite without any problem:
1963
1964 +-------------------------------+-------------+
1965 | Python type | SQLite type |
1966 +===============================+=============+
1967 | ``None`` | ``NULL`` |
1968 +-------------------------------+-------------+
1969 | :class:`int` | ``INTEGER`` |
1970 +-------------------------------+-------------+
1971 | :class:`float` | ``REAL`` |
1972 +-------------------------------+-------------+
1973 | :class:`str` | ``TEXT`` |
1974 +-------------------------------+-------------+
1975 | :class:`bytes` | ``BLOB`` |
1976 +-------------------------------+-------------+
1977
1978
1979 This is how SQLite types are converted to Python types by default:
1980
1981 +-------------+----------------------------------------------+
1982 | SQLite type | Python type |
1983 +=============+==============================================+
1984 | ``NULL`` | ``None`` |
1985 +-------------+----------------------------------------------+
1986 | ``INTEGER`` | :class:`int` |
1987 +-------------+----------------------------------------------+
1988 | ``REAL`` | :class:`float` |
1989 +-------------+----------------------------------------------+
1990 | ``TEXT`` | depends on :attr:`~Connection.text_factory`, |
1991 | | :class:`str` by default |
1992 +-------------+----------------------------------------------+
1993 | ``BLOB`` | :class:`bytes` |
1994 +-------------+----------------------------------------------+
1995
1996 The type system of the :mod:`!sqlite3` module is extensible in two ways: you can
1997 store additional Python types in an SQLite database via
1998 :ref:`object adapters <sqlite3-adapters>`,
1999 and you can let the :mod:`!sqlite3` module convert SQLite types to
2000 Python types via :ref:`converters <sqlite3-converters>`.
2001
2002
2003 .. _sqlite3-default-converters:
2004
2005 Default adapters and converters (deprecated)
2006 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2007
2008 .. note::
2009
2010 The default adapters and converters are deprecated as of Python 3.12.
2011 Instead, use the :ref:`sqlite3-adapter-converter-recipes`
2012 and tailor them to your needs.
2013
2014 The deprecated default adapters and converters consist of:
2015
2016 * An adapter for :class:`datetime.date` objects to :class:`strings <str>` in
2017 `ISO 8601`_ format.
2018 * An adapter for :class:`datetime.datetime` objects to strings in
2019 ISO 8601 format.
2020 * A converter for :ref:`declared <sqlite3-converters>` "date" types to
2021 :class:`datetime.date` objects.
2022 * A converter for declared "timestamp" types to
2023 :class:`datetime.datetime` objects.
2024 Fractional parts will be truncated to 6 digits (microsecond precision).
2025
2026 .. note::
2027
2028 The default "timestamp" converter ignores UTC offsets in the database and
2029 always returns a naive :class:`datetime.datetime` object. To preserve UTC
2030 offsets in timestamps, either leave converters disabled, or register an
2031 offset-aware converter with :func:`register_converter`.
2032
2033 .. deprecated:: 3.12
2034
2035 .. _ISO 8601: https://en.wikipedia.org/wiki/ISO_8601
2036
2037
2038 .. _sqlite3-cli:
2039
2040 Command-line interface
2041 ^^^^^^^^^^^^^^^^^^^^^^
2042
2043 The :mod:`!sqlite3` module can be invoked as a script,
2044 using the interpreter's :option:`-m` switch,
2045 in order to provide a simple SQLite shell.
2046 The argument signature is as follows::
2047
2048 python -m sqlite3 [-h] [-v] [filename] [sql]
2049
2050 Type ``.quit`` or CTRL-D to exit the shell.
2051
2052 .. program:: python -m sqlite3 [-h] [-v] [filename] [sql]
2053
2054 .. option:: -h, --help
2055
2056 Print CLI help.
2057
2058 .. option:: -v, --version
2059
2060 Print underlying SQLite library version.
2061
2062 .. versionadded:: 3.12
2063
2064
2065 .. _sqlite3-howtos:
2066
2067 How-to guides
2068 -------------
2069
2070 .. _sqlite3-placeholders:
2071
2072 How to use placeholders to bind values in SQL queries
2073 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2074
2075 SQL operations usually need to use values from Python variables. However,
2076 beware of using Python's string operations to assemble queries, as they
2077 are vulnerable to `SQL injection attacks`_. For example, an attacker can simply
2078 close the single quote and inject ``OR TRUE`` to select all rows::
2079
2080 >>> # Never do this -- insecure!
2081 >>> symbol = input()
2082 ' OR TRUE; --
2083 >>> sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
2084 >>> print(sql)
2085 SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
2086 >>> cur.execute(sql)
2087
2088 Instead, use the DB-API's parameter substitution. To insert a variable into a
2089 query string, use a placeholder in the string, and substitute the actual values
2090 into the query by providing them as a :class:`tuple` of values to the second
2091 argument of the cursor's :meth:`~Cursor.execute` method.
2092
2093 An SQL statement may use one of two kinds of placeholders:
2094 question marks (qmark style) or named placeholders (named style).
2095 For the qmark style, *parameters* must be a
2096 :term:`sequence` whose length must match the number of placeholders,
2097 or a :exc:`ProgrammingError` is raised.
2098 For the named style, *parameters* must be
2099 an instance of a :class:`dict` (or a subclass),
2100 which must contain keys for all named parameters;
2101 any extra items are ignored.
2102 Here's an example of both styles:
2103
2104 .. testcode::
2105
2106 con = sqlite3.connect(":memory:")
2107 cur = con.execute("CREATE TABLE lang(name, first_appeared)")
2108
2109 # This is the named style used with executemany():
2110 data = (
2111 {"name": "C", "year": 1972},
2112 {"name": "Fortran", "year": 1957},
2113 {"name": "Python", "year": 1991},
2114 {"name": "Go", "year": 2009},
2115 )
2116 cur.executemany("INSERT INTO lang VALUES(:name, :year)", data)
2117
2118 # This is the qmark style used in a SELECT query:
2119 params = (1972,)
2120 cur.execute("SELECT * FROM lang WHERE first_appeared = ?", params)
2121 print(cur.fetchall())
2122 con.close()
2123
2124 .. testoutput::
2125 :hide:
2126
2127 [('C', 1972)]
2128
2129 .. note::
2130
2131 :pep:`249` numeric placeholders are *not* supported.
2132 If used, they will be interpreted as named placeholders.
2133
2134
2135 .. _sqlite3-adapters:
2136
2137 How to adapt custom Python types to SQLite values
2138 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2139
2140 SQLite supports only a limited set of data types natively.
2141 To store custom Python types in SQLite databases, *adapt* them to one of the
2142 :ref:`Python types SQLite natively understands <sqlite3-types>`.
2143
2144 There are two ways to adapt Python objects to SQLite types:
2145 letting your object adapt itself, or using an *adapter callable*.
2146 The latter will take precedence above the former.
2147 For a library that exports a custom type,
2148 it may make sense to enable that type to adapt itself.
2149 As an application developer, it may make more sense to take direct control by
2150 registering custom adapter functions.
2151
2152
2153 .. _sqlite3-conform:
2154
2155 How to write adaptable objects
2156 """"""""""""""""""""""""""""""
2157
2158 Suppose we have a :class:`!Point` class that represents a pair of coordinates,
2159 ``x`` and ``y``, in a Cartesian coordinate system.
2160 The coordinate pair will be stored as a text string in the database,
2161 using a semicolon to separate the coordinates.
2162 This can be implemented by adding a ``__conform__(self, protocol)``
2163 method which returns the adapted value.
2164 The object passed to *protocol* will be of type :class:`PrepareProtocol`.
2165
2166 .. testcode::
2167
2168 class Point:
2169 def __init__(self, x, y):
2170 self.x, self.y = x, y
2171
2172 def __conform__(self, protocol):
2173 if protocol is sqlite3.PrepareProtocol:
2174 return f"{self.x};{self.y}"
2175
2176 con = sqlite3.connect(":memory:")
2177 cur = con.cursor()
2178
2179 cur.execute("SELECT ?", (Point(4.0, -3.2),))
2180 print(cur.fetchone()[0])
2181 con.close()
2182
2183 .. testoutput::
2184 :hide:
2185
2186 4.0;-3.2
2187
2188
2189 How to register adapter callables
2190 """""""""""""""""""""""""""""""""
2191
2192 The other possibility is to create a function that converts the Python object
2193 to an SQLite-compatible type.
2194 This function can then be registered using :func:`register_adapter`.
2195
2196 .. testcode::
2197
2198 class Point:
2199 def __init__(self, x, y):
2200 self.x, self.y = x, y
2201
2202 def adapt_point(point):
2203 return f"{point.x};{point.y}"
2204
2205 sqlite3.register_adapter(Point, adapt_point)
2206
2207 con = sqlite3.connect(":memory:")
2208 cur = con.cursor()
2209
2210 cur.execute("SELECT ?", (Point(1.0, 2.5),))
2211 print(cur.fetchone()[0])
2212 con.close()
2213
2214 .. testoutput::
2215 :hide:
2216
2217 1.0;2.5
2218
2219
2220 .. _sqlite3-converters:
2221
2222 How to convert SQLite values to custom Python types
2223 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2224
2225 Writing an adapter lets you convert *from* custom Python types *to* SQLite
2226 values.
2227 To be able to convert *from* SQLite values *to* custom Python types,
2228 we use *converters*.
2229
2230 Let's go back to the :class:`!Point` class. We stored the x and y coordinates
2231 separated via semicolons as strings in SQLite.
2232
2233 First, we'll define a converter function that accepts the string as a parameter
2234 and constructs a :class:`!Point` object from it.
2235
2236 .. note::
2237
2238 Converter functions are **always** passed a :class:`bytes` object,
2239 no matter the underlying SQLite data type.
2240
2241 .. testcode::
2242
2243 def convert_point(s):
2244 x, y = map(float, s.split(b";"))
2245 return Point(x, y)
2246
2247 We now need to tell :mod:`!sqlite3` when it should convert a given SQLite value.
2248 This is done when connecting to a database, using the *detect_types* parameter
2249 of :func:`connect`. There are three options:
2250
2251 * Implicit: set *detect_types* to :const:`PARSE_DECLTYPES`
2252 * Explicit: set *detect_types* to :const:`PARSE_COLNAMES`
2253 * Both: set *detect_types* to
2254 ``sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES``.
2255 Column names take precedence over declared types.
2256
2257 The following example illustrates the implicit and explicit approaches:
2258
2259 .. testcode::
2260
2261 class Point:
2262 def __init__(self, x, y):
2263 self.x, self.y = x, y
2264
2265 def __repr__(self):
2266 return f"Point({self.x}, {self.y})"
2267
2268 def adapt_point(point):
2269 return f"{point.x};{point.y}"
2270
2271 def convert_point(s):
2272 x, y = list(map(float, s.split(b";")))
2273 return Point(x, y)
2274
2275 # Register the adapter and converter
2276 sqlite3.register_adapter(Point, adapt_point)
2277 sqlite3.register_converter("point", convert_point)
2278
2279 # 1) Parse using declared types
2280 p = Point(4.0, -3.2)
2281 con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
2282 cur = con.execute("CREATE TABLE test(p point)")
2283
2284 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2285 cur.execute("SELECT p FROM test")
2286 print("with declared types:", cur.fetchone()[0])
2287 cur.close()
2288 con.close()
2289
2290 # 2) Parse using column names
2291 con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
2292 cur = con.execute("CREATE TABLE test(p)")
2293
2294 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2295 cur.execute('SELECT p AS "p [point]" FROM test')
2296 print("with column names:", cur.fetchone()[0])
2297 cur.close()
2298 con.close()
2299
2300 .. testoutput::
2301 :hide:
2302
2303 with declared types: Point(4.0, -3.2)
2304 with column names: Point(4.0, -3.2)
2305
2306
2307 .. _sqlite3-adapter-converter-recipes:
2308
2309 Adapter and converter recipes
2310 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2311
2312 This section shows recipes for common adapters and converters.
2313
2314 .. testcode::
2315
2316 import datetime as dt
2317 import sqlite3
2318
2319 def adapt_date_iso(val):
2320 """Adapt datetime.date to ISO 8601 date."""
2321 return val.isoformat()
2322
2323 def adapt_datetime_iso(val):
2324 """Adapt datetime.datetime to timezone-naive ISO 8601 date."""
2325 return val.replace(tzinfo=None).isoformat()
2326
2327 def adapt_datetime_epoch(val):
2328 """Adapt datetime.datetime to Unix timestamp."""
2329 return int(val.timestamp())
2330
2331 sqlite3.register_adapter(dt.date, adapt_date_iso)
2332 sqlite3.register_adapter(dt.datetime, adapt_datetime_iso)
2333 sqlite3.register_adapter(dt.datetime, adapt_datetime_epoch)
2334
2335 def convert_date(val):
2336 """Convert ISO 8601 date to datetime.date object."""
2337 return dt.date.fromisoformat(val.decode())
2338
2339 def convert_datetime(val):
2340 """Convert ISO 8601 datetime to datetime.datetime object."""
2341 return dt.datetime.fromisoformat(val.decode())
2342
2343 def convert_timestamp(val):
2344 """Convert Unix epoch timestamp to datetime.datetime object."""
2345 return dt.datetime.fromtimestamp(int(val))
2346
2347 sqlite3.register_converter("date", convert_date)
2348 sqlite3.register_converter("datetime", convert_datetime)
2349 sqlite3.register_converter("timestamp", convert_timestamp)
2350
2351 .. testcode::
2352 :hide:
2353
2354 when = dt.datetime(2019, 5, 18, 15, 17, 8, 123456)
2355
2356 assert adapt_date_iso(when.date()) == "2019-05-18"
2357 assert convert_date(b"2019-05-18") == when.date()
2358
2359 assert adapt_datetime_iso(when) == "2019-05-18T15:17:08.123456"
2360 assert convert_datetime(b"2019-05-18T15:17:08.123456") == when
2361
2362 # Using current time as fromtimestamp() returns local date/time.
2363 # Dropping microseconds as adapt_datetime_epoch truncates fractional second part.
2364 now = dt.datetime.now().replace(microsecond=0)
2365 current_timestamp = int(now.timestamp())
2366
2367 assert adapt_datetime_epoch(now) == current_timestamp
2368 assert convert_timestamp(str(current_timestamp).encode()) == now
2369
2370
2371 .. _sqlite3-connection-shortcuts:
2372
2373 How to use connection shortcut methods
2374 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2375
2376 Using the :meth:`~Connection.execute`,
2377 :meth:`~Connection.executemany`, and :meth:`~Connection.executescript`
2378 methods of the :class:`Connection` class, your code can
2379 be written more concisely because you don't have to create the (often
2380 superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
2381 objects are created implicitly and these shortcut methods return the cursor
2382 objects. This way, you can execute a ``SELECT`` statement and iterate over it
2383 directly using only a single call on the :class:`Connection` object.
2384
2385 .. testcode::
2386
2387 # Create and fill the table.
2388 con = sqlite3.connect(":memory:")
2389 con.execute("CREATE TABLE lang(name, first_appeared)")
2390 data = [
2391 ("C++", 1985),
2392 ("Objective-C", 1984),
2393 ]
2394 con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)", data)
2395
2396 # Print the table contents
2397 for row in con.execute("SELECT name, first_appeared FROM lang"):
2398 print(row)
2399
2400 print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows")
2401
2402 # close() is not a shortcut method and it's not called automatically;
2403 # the connection object should be closed manually
2404 con.close()
2405
2406 .. testoutput::
2407 :hide:
2408
2409 ('C++', 1985)
2410 ('Objective-C', 1984)
2411 I just deleted 2 rows
2412
2413
2414 .. _sqlite3-connection-context-manager:
2415
2416 How to use the connection context manager
2417 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2418
2419 A :class:`Connection` object can be used as a context manager that
2420 automatically commits or rolls back open transactions when leaving the body of
2421 the context manager.
2422 If the body of the :keyword:`with` statement finishes without exceptions,
2423 the transaction is committed.
2424 If this commit fails,
2425 or if the body of the ``with`` statement raises an uncaught exception,
2426 the transaction is rolled back.
2427 If :attr:`~Connection.autocommit` is ``False``,
2428 a new transaction is implicitly opened after committing or rolling back.
2429
2430 If there is no open transaction upon leaving the body of the ``with`` statement,
2431 or if :attr:`~Connection.autocommit` is ``True``,
2432 the context manager does nothing.
2433
2434 .. note::
2435 The context manager neither implicitly opens a new transaction
2436 nor closes the connection. If you need a closing context manager, consider
2437 using :meth:`contextlib.closing`.
2438
2439 .. testcode::
2440
2441 con = sqlite3.connect(":memory:")
2442 con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
2443
2444 # Successful, con.commit() is called automatically afterwards
2445 with con:
2446 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2447
2448 # con.rollback() is called after the with block finishes with an exception,
2449 # the exception is still raised and must be caught
2450 try:
2451 with con:
2452 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2453 except sqlite3.IntegrityError:
2454 print("couldn't add Python twice")
2455
2456 # Connection object used as context manager only commits or rollbacks transactions,
2457 # so the connection object should be closed manually
2458 con.close()
2459
2460 .. testoutput::
2461 :hide:
2462
2463 couldn't add Python twice
2464
2465
2466 .. _sqlite3-uri-tricks:
2467
2468 How to work with SQLite URIs
2469 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2470
2471 Some useful URI tricks include:
2472
2473 * Open a database in read-only mode:
2474
2475 .. doctest::
2476
2477 >>> con = sqlite3.connect("file:tutorial.db?mode=ro", uri=True)
2478 >>> con.execute("CREATE TABLE readonly(data)")
2479 Traceback (most recent call last):
2480 OperationalError: attempt to write a readonly database
2481 >>> con.close()
2482
2483 * Do not implicitly create a new database file if it does not already exist;
2484 will raise :exc:`~sqlite3.OperationalError` if unable to create a new file:
2485
2486 .. doctest::
2487
2488 >>> con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
2489 Traceback (most recent call last):
2490 OperationalError: unable to open database file
2491
2492
2493 * Create a shared named in-memory database:
2494
2495 .. testcode::
2496
2497 db = "file:mem1?mode=memory&cache=shared"
2498 con1 = sqlite3.connect(db, uri=True)
2499 con2 = sqlite3.connect(db, uri=True)
2500 with con1:
2501 con1.execute("CREATE TABLE shared(data)")
2502 con1.execute("INSERT INTO shared VALUES(28)")
2503 res = con2.execute("SELECT data FROM shared")
2504 assert res.fetchone() == (28,)
2505
2506 con1.close()
2507 con2.close()
2508
2509 More information about this feature, including a list of parameters,
2510 can be found in the `SQLite URI documentation`_.
2511
2512 .. _SQLite URI documentation: https://www.sqlite.org/uri.html
2513
2514
2515 .. _sqlite3-howto-row-factory:
2516
2517 How to create and use row factories
2518 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2519
2520 By default, :mod:`!sqlite3` represents each row as a :class:`tuple`.
2521 If a :class:`!tuple` does not suit your needs,
2522 you can use the :class:`sqlite3.Row` class
2523 or a custom :attr:`~Cursor.row_factory`.
2524
2525 While :attr:`!row_factory` exists as an attribute both on the
2526 :class:`Cursor` and the :class:`Connection`,
2527 it is recommended to set :class:`Connection.row_factory`,
2528 so all cursors created from the connection will use the same row factory.
2529
2530 :class:`!Row` provides indexed and case-insensitive named access to columns,
2531 with minimal memory overhead and performance impact over a :class:`!tuple`.
2532 To use :class:`!Row` as a row factory,
2533 assign it to the :attr:`!row_factory` attribute:
2534
2535 .. doctest::
2536
2537 >>> con = sqlite3.connect(":memory:")
2538 >>> con.row_factory = sqlite3.Row
2539
2540 Queries now return :class:`!Row` objects:
2541
2542 .. doctest::
2543
2544 >>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius")
2545 >>> row = res.fetchone()
2546 >>> row.keys()
2547 ['name', 'radius']
2548 >>> row[0] # Access by index.
2549 'Earth'
2550 >>> row["name"] # Access by name.
2551 'Earth'
2552 >>> row["RADIUS"] # Column names are case-insensitive.
2553 6378
2554 >>> con.close()
2555
2556 .. note::
2557
2558 The ``FROM`` clause can be omitted in the ``SELECT`` statement, as in the
2559 above example. In such cases, SQLite returns a single row with columns
2560 defined by expressions, e.g. literals, with the given aliases
2561 ``expr AS alias``.
2562
2563 You can create a custom :attr:`~Cursor.row_factory`
2564 that returns each row as a :class:`dict`, with column names mapped to values:
2565
2566 .. testcode::
2567
2568 def dict_factory(cursor, row):
2569 fields = [column[0] for column in cursor.description]
2570 return {key: value for key, value in zip(fields, row)}
2571
2572 Using it, queries now return a :class:`!dict` instead of a :class:`!tuple`:
2573
2574 .. doctest::
2575
2576 >>> con = sqlite3.connect(":memory:")
2577 >>> con.row_factory = dict_factory
2578 >>> for row in con.execute("SELECT 1 AS a, 2 AS b"):
2579 ... print(row)
2580 {'a': 1, 'b': 2}
2581 >>> con.close()
2582
2583 The following row factory returns a :term:`named tuple`:
2584
2585 .. testcode::
2586
2587 from collections import namedtuple
2588
2589 def namedtuple_factory(cursor, row):
2590 fields = [column[0] for column in cursor.description]
2591 cls = namedtuple("Row", fields)
2592 return cls._make(row)
2593
2594 :func:`!namedtuple_factory` can be used as follows:
2595
2596 .. doctest::
2597
2598 >>> con = sqlite3.connect(":memory:")
2599 >>> con.row_factory = namedtuple_factory
2600 >>> cur = con.execute("SELECT 1 AS a, 2 AS b")
2601 >>> row = cur.fetchone()
2602 >>> row
2603 Row(a=1, b=2)
2604 >>> row[0] # Indexed access.
2605 1
2606 >>> row.b # Attribute access.
2607 2
2608 >>> con.close()
2609
2610 With some adjustments, the above recipe can be adapted to use a
2611 :class:`~dataclasses.dataclass`, or any other custom class,
2612 instead of a :class:`~collections.namedtuple`.
2613
2614
2615 .. _sqlite3-howto-encoding:
2616
2617 How to handle non-UTF-8 text encodings
2618 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2619
2620 By default, :mod:`!sqlite3` uses :class:`str` to adapt SQLite values
2621 with the ``TEXT`` data type.
2622 This works well for UTF-8 encoded text, but it might fail for other encodings
2623 and invalid UTF-8.
2624 You can use a custom :attr:`~Connection.text_factory` to handle such cases.
2625
2626 Because of SQLite's `flexible typing`_, it is not uncommon to encounter table
2627 columns with the ``TEXT`` data type containing non-UTF-8 encodings,
2628 or even arbitrary data.
2629 To demonstrate, let's assume we have a database with ISO-8859-2 (Latin-2)
2630 encoded text, for example a table of Czech-English dictionary entries.
2631 Assuming we now have a :class:`Connection` instance :py:data:`!con`
2632 connected to this database,
2633 we can decode the Latin-2 encoded text using this :attr:`~Connection.text_factory`:
2634
2635 .. testcode::
2636
2637 con.text_factory = lambda data: str(data, encoding="latin2")
2638
2639 For invalid UTF-8 or arbitrary data in stored in ``TEXT`` table columns,
2640 you can use the following technique, borrowed from the :ref:`unicode-howto`:
2641
2642 .. testcode::
2643
2644 con.text_factory = lambda data: str(data, errors="surrogateescape")
2645
2646 .. note::
2647
2648 The :mod:`!sqlite3` module API does not support strings
2649 containing surrogates.
2650
2651 .. seealso::
2652
2653 :ref:`unicode-howto`
2654
2655
2656 .. _sqlite3-explanation:
2657
2658 Explanation
2659 -----------
2660
2661 .. _sqlite3-transaction-control:
2662 .. _sqlite3-controlling-transactions:
2663
2664 Transaction control
2665 ^^^^^^^^^^^^^^^^^^^
2666
2667 :mod:`!sqlite3` offers multiple methods of controlling whether,
2668 when and how database transactions are opened and closed.
2669 :ref:`sqlite3-transaction-control-autocommit` is recommended,
2670 while :ref:`sqlite3-transaction-control-isolation-level`
2671 retains the pre-Python 3.12 behaviour.
2672
2673 .. _sqlite3-transaction-control-autocommit:
2674
2675 Transaction control via the ``autocommit`` attribute
2676 """"""""""""""""""""""""""""""""""""""""""""""""""""
2677
2678 The recommended way of controlling transaction behaviour is through
2679 the :attr:`Connection.autocommit` attribute,
2680 which should preferably be set using the *autocommit* parameter
2681 of :func:`connect`.
2682
2683 It is suggested to set *autocommit* to ``False``,
2684 which implies :pep:`249`-compliant transaction control.
2685 This means:
2686
2687 * :mod:`!sqlite3` ensures that a transaction is always open,
2688 so :func:`connect`, :meth:`Connection.commit`, and :meth:`Connection.rollback`
2689 will implicitly open a new transaction
2690 (immediately after closing the pending one, for the latter two).
2691 :mod:`!sqlite3` uses ``BEGIN DEFERRED`` statements when opening transactions.
2692 * Transactions should be committed explicitly using :meth:`!commit`.
2693 * Transactions should be rolled back explicitly using :meth:`!rollback`.
2694 * An implicit rollback is performed if the database is
2695 :meth:`~Connection.close`-ed with pending changes.
2696
2697 Set *autocommit* to ``True`` to enable SQLite's `autocommit mode`_.
2698 In this mode, :meth:`Connection.commit` and :meth:`Connection.rollback`
2699 have no effect.
2700 Note that SQLite's autocommit mode is distinct from
2701 the :pep:`249`-compliant :attr:`Connection.autocommit` attribute;
2702 use :attr:`Connection.in_transaction` to query
2703 the low-level SQLite autocommit mode.
2704
2705 Set *autocommit* to :data:`LEGACY_TRANSACTION_CONTROL`
2706 to leave transaction control behaviour to the
2707 :attr:`Connection.isolation_level` attribute.
2708 See :ref:`sqlite3-transaction-control-isolation-level` for more information.
2709
2710
2711 .. _sqlite3-transaction-control-isolation-level:
2712
2713 Transaction control via the ``isolation_level`` attribute
2714 """""""""""""""""""""""""""""""""""""""""""""""""""""""""
2715
2716 .. note::
2717
2718 The recommended way of controlling transactions is via the
2719 :attr:`~Connection.autocommit` attribute.
2720 See :ref:`sqlite3-transaction-control-autocommit`.
2721
2722 If :attr:`Connection.autocommit` is set to
2723 :data:`LEGACY_TRANSACTION_CONTROL` (the default),
2724 transaction behaviour is controlled using
2725 the :attr:`Connection.isolation_level` attribute.
2726 Otherwise, :attr:`!isolation_level` has no effect.
2727
2728 If the connection attribute :attr:`~Connection.isolation_level`
2729 is not ``None``,
2730 new transactions are implicitly opened before
2731 :meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
2732 ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements;
2733 for other statements, no implicit transaction handling is performed.
2734 Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
2735 to respectively commit and roll back pending transactions.
2736 You can choose the underlying `SQLite transaction behaviour`_ —
2737 that is, whether and what type of ``BEGIN`` statements :mod:`!sqlite3`
2738 implicitly executes –
2739 via the :attr:`~Connection.isolation_level` attribute.
2740
2741 If :attr:`~Connection.isolation_level` is set to ``None``,
2742 no transactions are implicitly opened at all.
2743 This leaves the underlying SQLite library in `autocommit mode`_,
2744 but also allows the user to perform their own transaction handling
2745 using explicit SQL statements.
2746 The underlying SQLite library autocommit mode can be queried using the
2747 :attr:`~Connection.in_transaction` attribute.
2748
2749 The :meth:`~Cursor.executescript` method implicitly commits
2750 any pending transaction before execution of the given SQL script,
2751 regardless of the value of :attr:`~Connection.isolation_level`.
2752
2753 .. versionchanged:: 3.6
2754 :mod:`!sqlite3` used to implicitly commit an open transaction before DDL
2755 statements. This is no longer the case.
2756
2757 .. versionchanged:: 3.12
2758 The recommended way of controlling transactions is now via the
2759 :attr:`~Connection.autocommit` attribute.
2760
2761 .. _autocommit mode:
2762 https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
2763
2764 .. _SQLite transaction behaviour:
2765 https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions
2766
2767 .. testcleanup::
2768
2769 import os
2770 os.remove("backup.db")
2771 os.remove("dump.sql")
2772 os.remove("example.db")
2773 os.remove("tutorial.db")