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