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 .. versionchanged:: 3.14.6
1424 Deleting the ``row_factory`` attribute is no longer allowed.
1425
1426 .. attribute:: text_factory
1427
1428 A :term:`callable` that accepts a :class:`bytes` parameter
1429 and returns a text representation of it.
1430 The callable is invoked for SQLite values with the ``TEXT`` data type.
1431 By default, this attribute is set to :class:`str`.
1432
1433 See :ref:`sqlite3-howto-encoding` for more details.
1434
1435 .. versionchanged:: 3.14.6
1436 Deleting the ``text_factory`` attribute is no longer allowed.
1437
1438 .. attribute:: total_changes
1439
1440 Return the total number of database rows that have been modified, inserted, or
1441 deleted since the database connection was opened.
1442
1443
1444 .. _sqlite3-cursor-objects:
1445
1446 Cursor objects
1447 ^^^^^^^^^^^^^^
1448
1449 A ``Cursor`` object represents a `database cursor`_
1450 which is used to execute SQL statements,
1451 and manage the context of a fetch operation.
1452 Cursors are created using :meth:`Connection.cursor`,
1453 or by using any of the :ref:`connection shortcut methods
1454 <sqlite3-connection-shortcuts>`.
1455
1456 Cursor objects are :term:`iterators <iterator>`,
1457 meaning that if you :meth:`~Cursor.execute` a ``SELECT`` query,
1458 you can simply iterate over the cursor to fetch the resulting rows:
1459
1460 .. testsetup:: sqlite3.cursor
1461
1462 import sqlite3
1463 con = sqlite3.connect(":memory:", isolation_level=None)
1464 cur = con.execute("CREATE TABLE data(t)")
1465 cur.execute("INSERT INTO data VALUES(1)")
1466
1467 .. testcode:: sqlite3.cursor
1468
1469 for row in cur.execute("SELECT t FROM data"):
1470 print(row)
1471
1472 .. testoutput:: sqlite3.cursor
1473 :hide:
1474
1475 (1,)
1476
1477 .. _database cursor: https://en.wikipedia.org/wiki/Cursor_(databases)
1478
1479 .. class:: Cursor
1480
1481 A :class:`Cursor` instance has the following attributes and methods.
1482
1483 .. index:: single: ? (question mark); in SQL statements
1484 .. index:: single: : (colon); in SQL statements
1485
1486 .. method:: execute(sql, parameters=(), /)
1487
1488 Execute a single SQL statement,
1489 optionally binding Python values using
1490 :ref:`placeholders <sqlite3-placeholders>`.
1491
1492 :param str sql:
1493 A single SQL statement.
1494
1495 :param parameters:
1496 Python values to bind to placeholders in *sql*.
1497 A :class:`!dict` if named placeholders are used.
1498 A :term:`!sequence` if unnamed placeholders are used.
1499 See :ref:`sqlite3-placeholders`.
1500 :type parameters: :class:`dict` | :term:`sequence`
1501
1502 :raises ProgrammingError:
1503 When *sql* contains more than one SQL statement.
1504 When :ref:`named placeholders <sqlite3-placeholders>` are used
1505 and *parameters* is a sequence instead of a :class:`dict`.
1506
1507 If :attr:`~Connection.autocommit` is
1508 :data:`LEGACY_TRANSACTION_CONTROL`,
1509 :attr:`~Connection.isolation_level` is not ``None``,
1510 *sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
1511 and there is no open transaction,
1512 a transaction is implicitly opened before executing *sql*.
1513
1514 .. versionchanged:: 3.14
1515
1516 :exc:`ProgrammingError` is emitted if
1517 :ref:`named placeholders <sqlite3-placeholders>` are used
1518 and *parameters* is a sequence instead of a :class:`dict`.
1519
1520 Use :meth:`executescript` to execute multiple SQL statements.
1521
1522 .. method:: executemany(sql, parameters, /)
1523
1524 For every item in *parameters*,
1525 repeatedly execute the :ref:`parameterized <sqlite3-placeholders>`
1526 :abbr:`DML (Data Manipulation Language)` SQL statement *sql*.
1527
1528 Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
1529
1530 :param str sql:
1531 A single SQL DML statement.
1532
1533 :param parameters:
1534 An :term:`!iterable` of parameters to bind with
1535 the placeholders in *sql*.
1536 See :ref:`sqlite3-placeholders`.
1537 :type parameters: :term:`iterable`
1538
1539 :raises ProgrammingError:
1540 When *sql* contains more than one SQL statement
1541 or is not a DML statement,
1542 When :ref:`named placeholders <sqlite3-placeholders>` are used
1543 and the items in *parameters* are sequences instead of :class:`dict`\s.
1544
1545 Example:
1546
1547 .. testcode:: sqlite3.cursor
1548
1549 rows = [
1550 ("row1",),
1551 ("row2",),
1552 ]
1553 # cur is an sqlite3.Cursor object
1554 cur.executemany("INSERT INTO data VALUES(?)", rows)
1555
1556 .. testcleanup:: sqlite3.cursor
1557
1558 con.close()
1559
1560 .. note::
1561
1562 Any resulting rows are discarded,
1563 including DML statements with `RETURNING clauses`_.
1564
1565 .. _RETURNING clauses: https://www.sqlite.org/lang_returning.html
1566
1567 .. versionchanged:: 3.14
1568
1569 :exc:`ProgrammingError` is emitted if
1570 :ref:`named placeholders <sqlite3-placeholders>` are used
1571 and the items in *parameters* are sequences
1572 instead of :class:`dict`\s.
1573
1574 .. method:: executescript(sql_script, /)
1575
1576 Execute the SQL statements in *sql_script*.
1577 If the :attr:`~Connection.autocommit` is
1578 :data:`LEGACY_TRANSACTION_CONTROL`
1579 and there is a pending transaction,
1580 an implicit ``COMMIT`` statement is executed first.
1581 No other implicit transaction control is performed;
1582 any transaction control must be added to *sql_script*.
1583
1584 *sql_script* must be a :class:`string <str>`.
1585
1586 Example:
1587
1588 .. testcode:: sqlite3.cursor
1589
1590 # cur is an sqlite3.Cursor object
1591 cur.executescript("""
1592 BEGIN;
1593 CREATE TABLE person(firstname, lastname, age);
1594 CREATE TABLE book(title, author, published);
1595 CREATE TABLE publisher(name, address);
1596 COMMIT;
1597 """)
1598
1599 .. method:: fetchone()
1600
1601 If :attr:`~Cursor.row_factory` is ``None``,
1602 return the next row query result set as a :class:`tuple`.
1603 Else, pass it to the row factory and return its result.
1604 Return ``None`` if no more data is available.
1605
1606
1607 .. method:: fetchmany(size=cursor.arraysize)
1608
1609 Return the next set of rows of a query result as a :class:`list`.
1610 Return an empty list if no more rows are available.
1611
1612 The number of rows to fetch per call is specified by the *size* parameter.
1613 If *size* is not given, :attr:`arraysize` determines the number of rows
1614 to be fetched.
1615 If fewer than *size* rows are available,
1616 as many rows as are available are returned.
1617
1618 Note there are performance considerations involved with the *size* parameter.
1619 For optimal performance, it is usually best to use the arraysize attribute.
1620 If the *size* parameter is used, then it is best for it to retain the same
1621 value from one :meth:`fetchmany` call to the next.
1622
1623 .. versionchanged:: 3.14.1
1624 Negative *size* values are rejected by raising :exc:`ValueError`.
1625
1626 .. method:: fetchall()
1627
1628 Return all (remaining) rows of a query result as a :class:`list`.
1629 Return an empty list if no rows are available.
1630 Note that the :attr:`arraysize` attribute can affect the performance of
1631 this operation.
1632
1633 .. method:: close()
1634
1635 Close the cursor now (rather than whenever ``__del__`` is called).
1636
1637 The cursor will be unusable from this point forward; a :exc:`ProgrammingError`
1638 exception will be raised if any operation is attempted with the cursor.
1639
1640 .. method:: setinputsizes(sizes, /)
1641
1642 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1643
1644 .. method:: setoutputsize(size, column=None, /)
1645
1646 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1647
1648 .. attribute:: arraysize
1649
1650 Read/write attribute that controls the number of rows returned by :meth:`fetchmany`.
1651 The default value is 1 which means a single row would be fetched per call.
1652
1653 .. versionchanged:: 3.14.1
1654 Negative values are rejected by raising :exc:`ValueError`.
1655
1656 .. attribute:: connection
1657
1658 Read-only attribute that provides the SQLite database :class:`Connection`
1659 belonging to the cursor. A :class:`Cursor` object created by
1660 calling :meth:`con.cursor() <Connection.cursor>` will have a
1661 :attr:`connection` attribute that refers to *con*:
1662
1663 .. doctest::
1664
1665 >>> con = sqlite3.connect(":memory:")
1666 >>> cur = con.cursor()
1667 >>> cur.connection == con
1668 True
1669 >>> con.close()
1670
1671 .. attribute:: description
1672
1673 Read-only attribute that provides the column names of the last query. To
1674 remain compatible with the Python DB API, it returns a 7-tuple for each
1675 column where the last six items of each tuple are ``None``.
1676
1677 It is set for ``SELECT`` statements without any matching rows as well.
1678
1679 .. attribute:: lastrowid
1680
1681 Read-only attribute that provides the row id of the last inserted row. It
1682 is only updated after successful ``INSERT`` or ``REPLACE`` statements
1683 using the :meth:`execute` method. For other statements, after
1684 :meth:`executemany` or :meth:`executescript`, or if the insertion failed,
1685 the value of ``lastrowid`` is left unchanged. The initial value of
1686 ``lastrowid`` is ``None``.
1687
1688 .. note::
1689 Inserts into ``WITHOUT ROWID`` tables are not recorded.
1690
1691 .. versionchanged:: 3.6
1692 Added support for the ``REPLACE`` statement.
1693
1694 .. attribute:: rowcount
1695
1696 Read-only attribute that provides the number of modified rows for
1697 ``INSERT``, ``UPDATE``, ``DELETE``, and ``REPLACE`` statements;
1698 is ``-1`` for other statements,
1699 including :abbr:`CTE (Common Table Expression)` queries.
1700 It is only updated by the :meth:`execute` and :meth:`executemany` methods,
1701 after the statement has run to completion.
1702 This means that any resulting rows must be fetched in order for
1703 :attr:`!rowcount` to be updated.
1704
1705 .. attribute:: row_factory
1706
1707 Control how a row fetched from this :class:`!Cursor` is represented.
1708 If ``None``, a row is represented as a :class:`tuple`.
1709 Can be set to the included :class:`sqlite3.Row`;
1710 or a :term:`callable` that accepts two arguments,
1711 a :class:`Cursor` object and the :class:`!tuple` of row values,
1712 and returns a custom object representing an SQLite row.
1713
1714 Defaults to what :attr:`Connection.row_factory` was set to
1715 when the :class:`!Cursor` was created.
1716 Assigning to this attribute does not affect
1717 :attr:`Connection.row_factory` of the parent connection.
1718
1719 See :ref:`sqlite3-howto-row-factory` for more details.
1720
1721 .. versionchanged:: 3.14.6
1722 Deleting the ``row_factory`` attribute is no longer allowed.
1723
1724
1725 .. The sqlite3.Row example used to be a how-to. It has now been incorporated
1726 into the Row reference. We keep the anchor here in order not to break
1727 existing links.
1728
1729 .. _sqlite3-columns-by-name:
1730 .. _sqlite3-row-objects:
1731
1732 Row objects
1733 ^^^^^^^^^^^
1734
1735 .. class:: Row
1736
1737 A :class:`!Row` instance serves as a highly optimized
1738 :attr:`~Connection.row_factory` for :class:`Connection` objects.
1739 It supports iteration, equality testing, :func:`len`,
1740 and :term:`mapping` access by column name and index.
1741
1742 Two :class:`!Row` objects compare equal
1743 if they have identical column names and values.
1744
1745 See :ref:`sqlite3-howto-row-factory` for more details.
1746
1747 .. method:: keys
1748
1749 Return a :class:`list` of column names as :class:`strings <str>`.
1750 Immediately after a query,
1751 it is the first member of each tuple in :attr:`Cursor.description`.
1752
1753 .. versionchanged:: 3.5
1754 Added support of slicing.
1755
1756
1757 .. _sqlite3-blob-objects:
1758
1759 Blob objects
1760 ^^^^^^^^^^^^
1761
1762 .. class:: Blob
1763
1764 .. versionadded:: 3.11
1765
1766 A :class:`Blob` instance is a :term:`file-like object`
1767 that can read and write data in an SQLite :abbr:`BLOB (Binary Large OBject)`.
1768 Call :func:`len(blob) <len>` to get the size (number of bytes) of the blob.
1769 Use indices and :term:`slices <slice>` for direct access to the blob data.
1770
1771 Use the :class:`Blob` as a :term:`context manager` to ensure that the blob
1772 handle is closed after use.
1773
1774 .. testcode::
1775
1776 con = sqlite3.connect(":memory:")
1777 con.execute("CREATE TABLE test(blob_col blob)")
1778 con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))")
1779
1780 # Write to our blob, using two write operations:
1781 with con.blobopen("test", "blob_col", 1) as blob:
1782 blob.write(b"hello, ")
1783 blob.write(b"world.")
1784 # Modify the first and last bytes of our blob
1785 blob[0] = ord("H")
1786 blob[-1] = ord("!")
1787
1788 # Read the contents of our blob
1789 with con.blobopen("test", "blob_col", 1) as blob:
1790 greeting = blob.read()
1791
1792 print(greeting) # outputs "b'Hello, world!'"
1793 con.close()
1794
1795 .. testoutput::
1796 :hide:
1797
1798 b'Hello, world!'
1799
1800 .. method:: close()
1801
1802 Close the blob.
1803
1804 The blob will be unusable from this point onward. An
1805 :class:`~sqlite3.Error` (or subclass) exception will be raised if any
1806 further operation is attempted with the blob.
1807
1808 .. method:: read(length=-1, /)
1809
1810 Read *length* bytes of data from the blob at the current offset position.
1811 If the end of the blob is reached, the data up to
1812 :abbr:`EOF (End of File)` will be returned. When *length* is not
1813 specified, or is negative, :meth:`~Blob.read` will read until the end of
1814 the blob.
1815
1816 .. method:: write(data, /)
1817
1818 Write *data* to the blob at the current offset. This function cannot
1819 change the blob length. Writing beyond the end of the blob will raise
1820 :exc:`ValueError`.
1821
1822 .. method:: tell()
1823
1824 Return the current access position of the blob.
1825
1826 .. method:: seek(offset, origin=os.SEEK_SET, /)
1827
1828 Set the current access position of the blob to *offset*. The *origin*
1829 argument defaults to :const:`os.SEEK_SET` (absolute blob positioning).
1830 Other values for *origin* are :const:`os.SEEK_CUR` (seek relative to the
1831 current position) and :const:`os.SEEK_END` (seek relative to the blob’s
1832 end).
1833
1834
1835 PrepareProtocol objects
1836 ^^^^^^^^^^^^^^^^^^^^^^^
1837
1838 .. class:: PrepareProtocol
1839
1840 The PrepareProtocol type's single purpose is to act as a :pep:`246` style
1841 adaption protocol for objects that can :ref:`adapt themselves
1842 <sqlite3-conform>` to :ref:`native SQLite types <sqlite3-types>`.
1843
1844
1845 .. _sqlite3-exceptions:
1846
1847 Exceptions
1848 ^^^^^^^^^^
1849
1850 The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
1851
1852 .. exception:: Warning
1853
1854 This exception is not currently raised by the :mod:`!sqlite3` module,
1855 but may be raised by applications using :mod:`!sqlite3`,
1856 for example if a user-defined function truncates data while inserting.
1857 ``Warning`` is a subclass of :exc:`Exception`.
1858
1859 .. exception:: Error
1860
1861 The base class of the other exceptions in this module.
1862 Use this to catch all errors with one single :keyword:`except` statement.
1863 ``Error`` is a subclass of :exc:`Exception`.
1864
1865 If the exception originated from within the SQLite library,
1866 the following two attributes are added to the exception:
1867
1868 .. attribute:: sqlite_errorcode
1869
1870 The numeric error code from the
1871 `SQLite API <https://sqlite.org/rescode.html>`_
1872
1873 .. versionadded:: 3.11
1874
1875 .. attribute:: sqlite_errorname
1876
1877 The symbolic name of the numeric error code
1878 from the `SQLite API <https://sqlite.org/rescode.html>`_
1879
1880 .. versionadded:: 3.11
1881
1882 .. exception:: InterfaceError
1883
1884 Exception raised for misuse of the low-level SQLite C API.
1885 In other words, if this exception is raised, it probably indicates a bug in the
1886 :mod:`!sqlite3` module.
1887 ``InterfaceError`` is a subclass of :exc:`Error`.
1888
1889 .. exception:: DatabaseError
1890
1891 Exception raised for errors that are related to the database.
1892 This serves as the base exception for several types of database errors.
1893 It is only raised implicitly through the specialised subclasses.
1894 ``DatabaseError`` is a subclass of :exc:`Error`.
1895
1896 .. exception:: DataError
1897
1898 Exception raised for errors caused by problems with the processed data,
1899 like numeric values out of range, and strings which are too long.
1900 ``DataError`` is a subclass of :exc:`DatabaseError`.
1901
1902 .. exception:: OperationalError
1903
1904 Exception raised for errors that are related to the database's operation,
1905 and not necessarily under the control of the programmer.
1906 For example, the database path is not found,
1907 or a transaction could not be processed.
1908 ``OperationalError`` is a subclass of :exc:`DatabaseError`.
1909
1910 .. exception:: IntegrityError
1911
1912 Exception raised when the relational integrity of the database is affected,
1913 e.g. a foreign key check fails. It is a subclass of :exc:`DatabaseError`.
1914
1915 .. exception:: InternalError
1916
1917 Exception raised when SQLite encounters an internal error.
1918 If this is raised, it may indicate that there is a problem with the runtime
1919 SQLite library.
1920 ``InternalError`` is a subclass of :exc:`DatabaseError`.
1921
1922 .. exception:: ProgrammingError
1923
1924 Exception raised for :mod:`!sqlite3` API programming errors,
1925 for example supplying the wrong number of bindings to a query,
1926 or trying to operate on a closed :class:`Connection`.
1927 ``ProgrammingError`` is a subclass of :exc:`DatabaseError`.
1928
1929 .. exception:: NotSupportedError
1930
1931 Exception raised in case a method or database API is not supported by the
1932 underlying SQLite library. For example, setting *deterministic* to
1933 ``True`` in :meth:`~Connection.create_function`, if the underlying SQLite library
1934 does not support deterministic functions.
1935 ``NotSupportedError`` is a subclass of :exc:`DatabaseError`.
1936
1937
1938 .. _sqlite3-types:
1939
1940 SQLite and Python types
1941 ^^^^^^^^^^^^^^^^^^^^^^^
1942
1943 SQLite natively supports the following types: ``NULL``, ``INTEGER``,
1944 ``REAL``, ``TEXT``, ``BLOB``.
1945
1946 The following Python types can thus be sent to SQLite without any problem:
1947
1948 +-------------------------------+-------------+
1949 | Python type | SQLite type |
1950 +===============================+=============+
1951 | ``None`` | ``NULL`` |
1952 +-------------------------------+-------------+
1953 | :class:`int` | ``INTEGER`` |
1954 +-------------------------------+-------------+
1955 | :class:`float` | ``REAL`` |
1956 +-------------------------------+-------------+
1957 | :class:`str` | ``TEXT`` |
1958 +-------------------------------+-------------+
1959 | :class:`bytes` | ``BLOB`` |
1960 +-------------------------------+-------------+
1961
1962
1963 This is how SQLite types are converted to Python types by default:
1964
1965 +-------------+----------------------------------------------+
1966 | SQLite type | Python type |
1967 +=============+==============================================+
1968 | ``NULL`` | ``None`` |
1969 +-------------+----------------------------------------------+
1970 | ``INTEGER`` | :class:`int` |
1971 +-------------+----------------------------------------------+
1972 | ``REAL`` | :class:`float` |
1973 +-------------+----------------------------------------------+
1974 | ``TEXT`` | depends on :attr:`~Connection.text_factory`, |
1975 | | :class:`str` by default |
1976 +-------------+----------------------------------------------+
1977 | ``BLOB`` | :class:`bytes` |
1978 +-------------+----------------------------------------------+
1979
1980 The type system of the :mod:`!sqlite3` module is extensible in two ways: you can
1981 store additional Python types in an SQLite database via
1982 :ref:`object adapters <sqlite3-adapters>`,
1983 and you can let the :mod:`!sqlite3` module convert SQLite types to
1984 Python types via :ref:`converters <sqlite3-converters>`.
1985
1986
1987 .. _sqlite3-default-converters:
1988
1989 Default adapters and converters (deprecated)
1990 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1991
1992 .. note::
1993
1994 The default adapters and converters are deprecated as of Python 3.12.
1995 Instead, use the :ref:`sqlite3-adapter-converter-recipes`
1996 and tailor them to your needs.
1997
1998 The deprecated default adapters and converters consist of:
1999
2000 * An adapter for :class:`datetime.date` objects to :class:`strings <str>` in
2001 `ISO 8601`_ format.
2002 * An adapter for :class:`datetime.datetime` objects to strings in
2003 ISO 8601 format.
2004 * A converter for :ref:`declared <sqlite3-converters>` "date" types to
2005 :class:`datetime.date` objects.
2006 * A converter for declared "timestamp" types to
2007 :class:`datetime.datetime` objects.
2008 Fractional parts will be truncated to 6 digits (microsecond precision).
2009
2010 .. note::
2011
2012 The default "timestamp" converter ignores UTC offsets in the database and
2013 always returns a naive :class:`datetime.datetime` object. To preserve UTC
2014 offsets in timestamps, either leave converters disabled, or register an
2015 offset-aware converter with :func:`register_converter`.
2016
2017 .. deprecated:: 3.12
2018
2019 .. _ISO 8601: https://en.wikipedia.org/wiki/ISO_8601
2020
2021
2022 .. _sqlite3-cli:
2023
2024 Command-line interface
2025 ^^^^^^^^^^^^^^^^^^^^^^
2026
2027 The :mod:`!sqlite3` module can be invoked as a script,
2028 using the interpreter's :option:`-m` switch,
2029 in order to provide a simple SQLite shell.
2030 The argument signature is as follows::
2031
2032 python -m sqlite3 [-h] [-v] [filename] [sql]
2033
2034 Type ``.quit`` or CTRL-D to exit the shell.
2035
2036 .. program:: python -m sqlite3 [-h] [-v] [filename] [sql]
2037
2038 .. option:: -h, --help
2039
2040 Print CLI help.
2041
2042 .. option:: -v, --version
2043
2044 Print underlying SQLite library version.
2045
2046 .. versionadded:: 3.12
2047
2048
2049 .. _sqlite3-howtos:
2050
2051 How-to guides
2052 -------------
2053
2054 .. _sqlite3-placeholders:
2055
2056 How to use placeholders to bind values in SQL queries
2057 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2058
2059 SQL operations usually need to use values from Python variables. However,
2060 beware of using Python's string operations to assemble queries, as they
2061 are vulnerable to `SQL injection attacks`_. For example, an attacker can simply
2062 close the single quote and inject ``OR TRUE`` to select all rows::
2063
2064 >>> # Never do this -- insecure!
2065 >>> symbol = input()
2066 ' OR TRUE; --
2067 >>> sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
2068 >>> print(sql)
2069 SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
2070 >>> cur.execute(sql)
2071
2072 Instead, use the DB-API's parameter substitution. To insert a variable into a
2073 query string, use a placeholder in the string, and substitute the actual values
2074 into the query by providing them as a :class:`tuple` of values to the second
2075 argument of the cursor's :meth:`~Cursor.execute` method.
2076
2077 An SQL statement may use one of two kinds of placeholders:
2078 question marks (qmark style) or named placeholders (named style).
2079 For the qmark style, *parameters* must be a
2080 :term:`sequence` whose length must match the number of placeholders,
2081 or a :exc:`ProgrammingError` is raised.
2082 For the named style, *parameters* must be
2083 an instance of a :class:`dict` (or a subclass),
2084 which must contain keys for all named parameters;
2085 any extra items are ignored.
2086 Here's an example of both styles:
2087
2088 .. testcode::
2089
2090 con = sqlite3.connect(":memory:")
2091 cur = con.execute("CREATE TABLE lang(name, first_appeared)")
2092
2093 # This is the named style used with executemany():
2094 data = (
2095 {"name": "C", "year": 1972},
2096 {"name": "Fortran", "year": 1957},
2097 {"name": "Python", "year": 1991},
2098 {"name": "Go", "year": 2009},
2099 )
2100 cur.executemany("INSERT INTO lang VALUES(:name, :year)", data)
2101
2102 # This is the qmark style used in a SELECT query:
2103 params = (1972,)
2104 cur.execute("SELECT * FROM lang WHERE first_appeared = ?", params)
2105 print(cur.fetchall())
2106 con.close()
2107
2108 .. testoutput::
2109 :hide:
2110
2111 [('C', 1972)]
2112
2113 .. note::
2114
2115 :pep:`249` numeric placeholders are *not* supported.
2116 If used, they will be interpreted as named placeholders.
2117
2118
2119 .. _sqlite3-adapters:
2120
2121 How to adapt custom Python types to SQLite values
2122 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2123
2124 SQLite supports only a limited set of data types natively.
2125 To store custom Python types in SQLite databases, *adapt* them to one of the
2126 :ref:`Python types SQLite natively understands <sqlite3-types>`.
2127
2128 There are two ways to adapt Python objects to SQLite types:
2129 letting your object adapt itself, or using an *adapter callable*.
2130 The latter will take precedence above the former.
2131 For a library that exports a custom type,
2132 it may make sense to enable that type to adapt itself.
2133 As an application developer, it may make more sense to take direct control by
2134 registering custom adapter functions.
2135
2136
2137 .. _sqlite3-conform:
2138
2139 How to write adaptable objects
2140 """"""""""""""""""""""""""""""
2141
2142 Suppose we have a :class:`!Point` class that represents a pair of coordinates,
2143 ``x`` and ``y``, in a Cartesian coordinate system.
2144 The coordinate pair will be stored as a text string in the database,
2145 using a semicolon to separate the coordinates.
2146 This can be implemented by adding a ``__conform__(self, protocol)``
2147 method which returns the adapted value.
2148 The object passed to *protocol* will be of type :class:`PrepareProtocol`.
2149
2150 .. testcode::
2151
2152 class Point:
2153 def __init__(self, x, y):
2154 self.x, self.y = x, y
2155
2156 def __conform__(self, protocol):
2157 if protocol is sqlite3.PrepareProtocol:
2158 return f"{self.x};{self.y}"
2159
2160 con = sqlite3.connect(":memory:")
2161 cur = con.cursor()
2162
2163 cur.execute("SELECT ?", (Point(4.0, -3.2),))
2164 print(cur.fetchone()[0])
2165 con.close()
2166
2167 .. testoutput::
2168 :hide:
2169
2170 4.0;-3.2
2171
2172
2173 How to register adapter callables
2174 """""""""""""""""""""""""""""""""
2175
2176 The other possibility is to create a function that converts the Python object
2177 to an SQLite-compatible type.
2178 This function can then be registered using :func:`register_adapter`.
2179
2180 .. testcode::
2181
2182 class Point:
2183 def __init__(self, x, y):
2184 self.x, self.y = x, y
2185
2186 def adapt_point(point):
2187 return f"{point.x};{point.y}"
2188
2189 sqlite3.register_adapter(Point, adapt_point)
2190
2191 con = sqlite3.connect(":memory:")
2192 cur = con.cursor()
2193
2194 cur.execute("SELECT ?", (Point(1.0, 2.5),))
2195 print(cur.fetchone()[0])
2196 con.close()
2197
2198 .. testoutput::
2199 :hide:
2200
2201 1.0;2.5
2202
2203
2204 .. _sqlite3-converters:
2205
2206 How to convert SQLite values to custom Python types
2207 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2208
2209 Writing an adapter lets you convert *from* custom Python types *to* SQLite
2210 values.
2211 To be able to convert *from* SQLite values *to* custom Python types,
2212 we use *converters*.
2213
2214 Let's go back to the :class:`!Point` class. We stored the x and y coordinates
2215 separated via semicolons as strings in SQLite.
2216
2217 First, we'll define a converter function that accepts the string as a parameter
2218 and constructs a :class:`!Point` object from it.
2219
2220 .. note::
2221
2222 Converter functions are **always** passed a :class:`bytes` object,
2223 no matter the underlying SQLite data type.
2224
2225 .. testcode::
2226
2227 def convert_point(s):
2228 x, y = map(float, s.split(b";"))
2229 return Point(x, y)
2230
2231 We now need to tell :mod:`!sqlite3` when it should convert a given SQLite value.
2232 This is done when connecting to a database, using the *detect_types* parameter
2233 of :func:`connect`. There are three options:
2234
2235 * Implicit: set *detect_types* to :const:`PARSE_DECLTYPES`
2236 * Explicit: set *detect_types* to :const:`PARSE_COLNAMES`
2237 * Both: set *detect_types* to
2238 ``sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES``.
2239 Column names take precedence over declared types.
2240
2241 The following example illustrates the implicit and explicit approaches:
2242
2243 .. testcode::
2244
2245 class Point:
2246 def __init__(self, x, y):
2247 self.x, self.y = x, y
2248
2249 def __repr__(self):
2250 return f"Point({self.x}, {self.y})"
2251
2252 def adapt_point(point):
2253 return f"{point.x};{point.y}"
2254
2255 def convert_point(s):
2256 x, y = list(map(float, s.split(b";")))
2257 return Point(x, y)
2258
2259 # Register the adapter and converter
2260 sqlite3.register_adapter(Point, adapt_point)
2261 sqlite3.register_converter("point", convert_point)
2262
2263 # 1) Parse using declared types
2264 p = Point(4.0, -3.2)
2265 con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
2266 cur = con.execute("CREATE TABLE test(p point)")
2267
2268 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2269 cur.execute("SELECT p FROM test")
2270 print("with declared types:", cur.fetchone()[0])
2271 cur.close()
2272 con.close()
2273
2274 # 2) Parse using column names
2275 con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
2276 cur = con.execute("CREATE TABLE test(p)")
2277
2278 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2279 cur.execute('SELECT p AS "p [point]" FROM test')
2280 print("with column names:", cur.fetchone()[0])
2281 cur.close()
2282 con.close()
2283
2284 .. testoutput::
2285 :hide:
2286
2287 with declared types: Point(4.0, -3.2)
2288 with column names: Point(4.0, -3.2)
2289
2290
2291 .. _sqlite3-adapter-converter-recipes:
2292
2293 Adapter and converter recipes
2294 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2295
2296 This section shows recipes for common adapters and converters.
2297
2298 .. testcode::
2299
2300 import datetime as dt
2301 import sqlite3
2302
2303 def adapt_date_iso(val):
2304 """Adapt datetime.date to ISO 8601 date."""
2305 return val.isoformat()
2306
2307 def adapt_datetime_iso(val):
2308 """Adapt datetime.datetime to timezone-naive ISO 8601 date."""
2309 return val.replace(tzinfo=None).isoformat()
2310
2311 def adapt_datetime_epoch(val):
2312 """Adapt datetime.datetime to Unix timestamp."""
2313 return int(val.timestamp())
2314
2315 sqlite3.register_adapter(dt.date, adapt_date_iso)
2316 sqlite3.register_adapter(dt.datetime, adapt_datetime_iso)
2317 sqlite3.register_adapter(dt.datetime, adapt_datetime_epoch)
2318
2319 def convert_date(val):
2320 """Convert ISO 8601 date to datetime.date object."""
2321 return dt.date.fromisoformat(val.decode())
2322
2323 def convert_datetime(val):
2324 """Convert ISO 8601 datetime to datetime.datetime object."""
2325 return dt.datetime.fromisoformat(val.decode())
2326
2327 def convert_timestamp(val):
2328 """Convert Unix epoch timestamp to datetime.datetime object."""
2329 return dt.datetime.fromtimestamp(int(val))
2330
2331 sqlite3.register_converter("date", convert_date)
2332 sqlite3.register_converter("datetime", convert_datetime)
2333 sqlite3.register_converter("timestamp", convert_timestamp)
2334
2335 .. testcode::
2336 :hide:
2337
2338 when = dt.datetime(2019, 5, 18, 15, 17, 8, 123456)
2339
2340 assert adapt_date_iso(when.date()) == "2019-05-18"
2341 assert convert_date(b"2019-05-18") == when.date()
2342
2343 assert adapt_datetime_iso(when) == "2019-05-18T15:17:08.123456"
2344 assert convert_datetime(b"2019-05-18T15:17:08.123456") == when
2345
2346 # Using current time as fromtimestamp() returns local date/time.
2347 # Dropping microseconds as adapt_datetime_epoch truncates fractional second part.
2348 now = dt.datetime.now().replace(microsecond=0)
2349 current_timestamp = int(now.timestamp())
2350
2351 assert adapt_datetime_epoch(now) == current_timestamp
2352 assert convert_timestamp(str(current_timestamp).encode()) == now
2353
2354
2355 .. _sqlite3-connection-shortcuts:
2356
2357 How to use connection shortcut methods
2358 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2359
2360 Using the :meth:`~Connection.execute`,
2361 :meth:`~Connection.executemany`, and :meth:`~Connection.executescript`
2362 methods of the :class:`Connection` class, your code can
2363 be written more concisely because you don't have to create the (often
2364 superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
2365 objects are created implicitly and these shortcut methods return the cursor
2366 objects. This way, you can execute a ``SELECT`` statement and iterate over it
2367 directly using only a single call on the :class:`Connection` object.
2368
2369 .. testcode::
2370
2371 # Create and fill the table.
2372 con = sqlite3.connect(":memory:")
2373 con.execute("CREATE TABLE lang(name, first_appeared)")
2374 data = [
2375 ("C++", 1985),
2376 ("Objective-C", 1984),
2377 ]
2378 con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)", data)
2379
2380 # Print the table contents
2381 for row in con.execute("SELECT name, first_appeared FROM lang"):
2382 print(row)
2383
2384 print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows")
2385
2386 # close() is not a shortcut method and it's not called automatically;
2387 # the connection object should be closed manually
2388 con.close()
2389
2390 .. testoutput::
2391 :hide:
2392
2393 ('C++', 1985)
2394 ('Objective-C', 1984)
2395 I just deleted 2 rows
2396
2397
2398 .. _sqlite3-connection-context-manager:
2399
2400 How to use the connection context manager
2401 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2402
2403 A :class:`Connection` object can be used as a context manager that
2404 automatically commits or rolls back open transactions when leaving the body of
2405 the context manager.
2406 If the body of the :keyword:`with` statement finishes without exceptions,
2407 the transaction is committed.
2408 If this commit fails,
2409 or if the body of the ``with`` statement raises an uncaught exception,
2410 the transaction is rolled back.
2411 If :attr:`~Connection.autocommit` is ``False``,
2412 a new transaction is implicitly opened after committing or rolling back.
2413
2414 If there is no open transaction upon leaving the body of the ``with`` statement,
2415 or if :attr:`~Connection.autocommit` is ``True``,
2416 the context manager does nothing.
2417
2418 .. note::
2419 The context manager neither implicitly opens a new transaction
2420 nor closes the connection. If you need a closing context manager, consider
2421 using :meth:`contextlib.closing`.
2422
2423 .. testcode::
2424
2425 con = sqlite3.connect(":memory:")
2426 con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
2427
2428 # Successful, con.commit() is called automatically afterwards
2429 with con:
2430 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2431
2432 # con.rollback() is called after the with block finishes with an exception,
2433 # the exception is still raised and must be caught
2434 try:
2435 with con:
2436 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2437 except sqlite3.IntegrityError:
2438 print("couldn't add Python twice")
2439
2440 # Connection object used as context manager only commits or rollbacks transactions,
2441 # so the connection object should be closed manually
2442 con.close()
2443
2444 .. testoutput::
2445 :hide:
2446
2447 couldn't add Python twice
2448
2449
2450 .. _sqlite3-uri-tricks:
2451
2452 How to work with SQLite URIs
2453 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2454
2455 Some useful URI tricks include:
2456
2457 * Open a database in read-only mode:
2458
2459 .. doctest::
2460
2461 >>> con = sqlite3.connect("file:tutorial.db?mode=ro", uri=True)
2462 >>> con.execute("CREATE TABLE readonly(data)")
2463 Traceback (most recent call last):
2464 OperationalError: attempt to write a readonly database
2465 >>> con.close()
2466
2467 * Do not implicitly create a new database file if it does not already exist;
2468 will raise :exc:`~sqlite3.OperationalError` if unable to create a new file:
2469
2470 .. doctest::
2471
2472 >>> con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
2473 Traceback (most recent call last):
2474 OperationalError: unable to open database file
2475
2476
2477 * Create a shared named in-memory database:
2478
2479 .. testcode::
2480
2481 db = "file:mem1?mode=memory&cache=shared"
2482 con1 = sqlite3.connect(db, uri=True)
2483 con2 = sqlite3.connect(db, uri=True)
2484 with con1:
2485 con1.execute("CREATE TABLE shared(data)")
2486 con1.execute("INSERT INTO shared VALUES(28)")
2487 res = con2.execute("SELECT data FROM shared")
2488 assert res.fetchone() == (28,)
2489
2490 con1.close()
2491 con2.close()
2492
2493 More information about this feature, including a list of parameters,
2494 can be found in the `SQLite URI documentation`_.
2495
2496 .. _SQLite URI documentation: https://www.sqlite.org/uri.html
2497
2498
2499 .. _sqlite3-howto-row-factory:
2500
2501 How to create and use row factories
2502 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2503
2504 By default, :mod:`!sqlite3` represents each row as a :class:`tuple`.
2505 If a :class:`!tuple` does not suit your needs,
2506 you can use the :class:`sqlite3.Row` class
2507 or a custom :attr:`~Cursor.row_factory`.
2508
2509 While :attr:`!row_factory` exists as an attribute both on the
2510 :class:`Cursor` and the :class:`Connection`,
2511 it is recommended to set :class:`Connection.row_factory`,
2512 so all cursors created from the connection will use the same row factory.
2513
2514 :class:`!Row` provides indexed and case-insensitive named access to columns,
2515 with minimal memory overhead and performance impact over a :class:`!tuple`.
2516 To use :class:`!Row` as a row factory,
2517 assign it to the :attr:`!row_factory` attribute:
2518
2519 .. doctest::
2520
2521 >>> con = sqlite3.connect(":memory:")
2522 >>> con.row_factory = sqlite3.Row
2523
2524 Queries now return :class:`!Row` objects:
2525
2526 .. doctest::
2527
2528 >>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius")
2529 >>> row = res.fetchone()
2530 >>> row.keys()
2531 ['name', 'radius']
2532 >>> row[0] # Access by index.
2533 'Earth'
2534 >>> row["name"] # Access by name.
2535 'Earth'
2536 >>> row["RADIUS"] # Column names are case-insensitive.
2537 6378
2538 >>> con.close()
2539
2540 .. note::
2541
2542 The ``FROM`` clause can be omitted in the ``SELECT`` statement, as in the
2543 above example. In such cases, SQLite returns a single row with columns
2544 defined by expressions, e.g. literals, with the given aliases
2545 ``expr AS alias``.
2546
2547 You can create a custom :attr:`~Cursor.row_factory`
2548 that returns each row as a :class:`dict`, with column names mapped to values:
2549
2550 .. testcode::
2551
2552 def dict_factory(cursor, row):
2553 fields = [column[0] for column in cursor.description]
2554 return {key: value for key, value in zip(fields, row)}
2555
2556 Using it, queries now return a :class:`!dict` instead of a :class:`!tuple`:
2557
2558 .. doctest::
2559
2560 >>> con = sqlite3.connect(":memory:")
2561 >>> con.row_factory = dict_factory
2562 >>> for row in con.execute("SELECT 1 AS a, 2 AS b"):
2563 ... print(row)
2564 {'a': 1, 'b': 2}
2565 >>> con.close()
2566
2567 The following row factory returns a :term:`named tuple`:
2568
2569 .. testcode::
2570
2571 from collections import namedtuple
2572
2573 def namedtuple_factory(cursor, row):
2574 fields = [column[0] for column in cursor.description]
2575 cls = namedtuple("Row", fields)
2576 return cls._make(row)
2577
2578 :func:`!namedtuple_factory` can be used as follows:
2579
2580 .. doctest::
2581
2582 >>> con = sqlite3.connect(":memory:")
2583 >>> con.row_factory = namedtuple_factory
2584 >>> cur = con.execute("SELECT 1 AS a, 2 AS b")
2585 >>> row = cur.fetchone()
2586 >>> row
2587 Row(a=1, b=2)
2588 >>> row[0] # Indexed access.
2589 1
2590 >>> row.b # Attribute access.
2591 2
2592 >>> con.close()
2593
2594 With some adjustments, the above recipe can be adapted to use a
2595 :class:`~dataclasses.dataclass`, or any other custom class,
2596 instead of a :class:`~collections.namedtuple`.
2597
2598
2599 .. _sqlite3-howto-encoding:
2600
2601 How to handle non-UTF-8 text encodings
2602 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2603
2604 By default, :mod:`!sqlite3` uses :class:`str` to adapt SQLite values
2605 with the ``TEXT`` data type.
2606 This works well for UTF-8 encoded text, but it might fail for other encodings
2607 and invalid UTF-8.
2608 You can use a custom :attr:`~Connection.text_factory` to handle such cases.
2609
2610 Because of SQLite's `flexible typing`_, it is not uncommon to encounter table
2611 columns with the ``TEXT`` data type containing non-UTF-8 encodings,
2612 or even arbitrary data.
2613 To demonstrate, let's assume we have a database with ISO-8859-2 (Latin-2)
2614 encoded text, for example a table of Czech-English dictionary entries.
2615 Assuming we now have a :class:`Connection` instance :py:data:`!con`
2616 connected to this database,
2617 we can decode the Latin-2 encoded text using this :attr:`~Connection.text_factory`:
2618
2619 .. testcode::
2620
2621 con.text_factory = lambda data: str(data, encoding="latin2")
2622
2623 For invalid UTF-8 or arbitrary data in stored in ``TEXT`` table columns,
2624 you can use the following technique, borrowed from the :ref:`unicode-howto`:
2625
2626 .. testcode::
2627
2628 con.text_factory = lambda data: str(data, errors="surrogateescape")
2629
2630 .. note::
2631
2632 The :mod:`!sqlite3` module API does not support strings
2633 containing surrogates.
2634
2635 .. seealso::
2636
2637 :ref:`unicode-howto`
2638
2639
2640 .. _sqlite3-explanation:
2641
2642 Explanation
2643 -----------
2644
2645 .. _sqlite3-transaction-control:
2646 .. _sqlite3-controlling-transactions:
2647
2648 Transaction control
2649 ^^^^^^^^^^^^^^^^^^^
2650
2651 :mod:`!sqlite3` offers multiple methods of controlling whether,
2652 when and how database transactions are opened and closed.
2653 :ref:`sqlite3-transaction-control-autocommit` is recommended,
2654 while :ref:`sqlite3-transaction-control-isolation-level`
2655 retains the pre-Python 3.12 behaviour.
2656
2657 .. _sqlite3-transaction-control-autocommit:
2658
2659 Transaction control via the ``autocommit`` attribute
2660 """"""""""""""""""""""""""""""""""""""""""""""""""""
2661
2662 The recommended way of controlling transaction behaviour is through
2663 the :attr:`Connection.autocommit` attribute,
2664 which should preferably be set using the *autocommit* parameter
2665 of :func:`connect`.
2666
2667 It is suggested to set *autocommit* to ``False``,
2668 which implies :pep:`249`-compliant transaction control.
2669 This means:
2670
2671 * :mod:`!sqlite3` ensures that a transaction is always open,
2672 so :func:`connect`, :meth:`Connection.commit`, and :meth:`Connection.rollback`
2673 will implicitly open a new transaction
2674 (immediately after closing the pending one, for the latter two).
2675 :mod:`!sqlite3` uses ``BEGIN DEFERRED`` statements when opening transactions.
2676 * Transactions should be committed explicitly using :meth:`!commit`.
2677 * Transactions should be rolled back explicitly using :meth:`!rollback`.
2678 * An implicit rollback is performed if the database is
2679 :meth:`~Connection.close`-ed with pending changes.
2680
2681 Set *autocommit* to ``True`` to enable SQLite's `autocommit mode`_.
2682 In this mode, :meth:`Connection.commit` and :meth:`Connection.rollback`
2683 have no effect.
2684 Note that SQLite's autocommit mode is distinct from
2685 the :pep:`249`-compliant :attr:`Connection.autocommit` attribute;
2686 use :attr:`Connection.in_transaction` to query
2687 the low-level SQLite autocommit mode.
2688
2689 Set *autocommit* to :data:`LEGACY_TRANSACTION_CONTROL`
2690 to leave transaction control behaviour to the
2691 :attr:`Connection.isolation_level` attribute.
2692 See :ref:`sqlite3-transaction-control-isolation-level` for more information.
2693
2694
2695 .. _sqlite3-transaction-control-isolation-level:
2696
2697 Transaction control via the ``isolation_level`` attribute
2698 """""""""""""""""""""""""""""""""""""""""""""""""""""""""
2699
2700 .. note::
2701
2702 The recommended way of controlling transactions is via the
2703 :attr:`~Connection.autocommit` attribute.
2704 See :ref:`sqlite3-transaction-control-autocommit`.
2705
2706 If :attr:`Connection.autocommit` is set to
2707 :data:`LEGACY_TRANSACTION_CONTROL` (the default),
2708 transaction behaviour is controlled using
2709 the :attr:`Connection.isolation_level` attribute.
2710 Otherwise, :attr:`!isolation_level` has no effect.
2711
2712 If the connection attribute :attr:`~Connection.isolation_level`
2713 is not ``None``,
2714 new transactions are implicitly opened before
2715 :meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes
2716 ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statements;
2717 for other statements, no implicit transaction handling is performed.
2718 Use the :meth:`~Connection.commit` and :meth:`~Connection.rollback` methods
2719 to respectively commit and roll back pending transactions.
2720 You can choose the underlying `SQLite transaction behaviour`_ —
2721 that is, whether and what type of ``BEGIN`` statements :mod:`!sqlite3`
2722 implicitly executes –
2723 via the :attr:`~Connection.isolation_level` attribute.
2724
2725 If :attr:`~Connection.isolation_level` is set to ``None``,
2726 no transactions are implicitly opened at all.
2727 This leaves the underlying SQLite library in `autocommit mode`_,
2728 but also allows the user to perform their own transaction handling
2729 using explicit SQL statements.
2730 The underlying SQLite library autocommit mode can be queried using the
2731 :attr:`~Connection.in_transaction` attribute.
2732
2733 The :meth:`~Cursor.executescript` method implicitly commits
2734 any pending transaction before execution of the given SQL script,
2735 regardless of the value of :attr:`~Connection.isolation_level`.
2736
2737 .. versionchanged:: 3.6
2738 :mod:`!sqlite3` used to implicitly commit an open transaction before DDL
2739 statements. This is no longer the case.
2740
2741 .. versionchanged:: 3.12
2742 The recommended way of controlling transactions is now via the
2743 :attr:`~Connection.autocommit` attribute.
2744
2745 .. _autocommit mode:
2746 https://www.sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions
2747
2748 .. _SQLite transaction behaviour:
2749 https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions
2750
2751 .. testcleanup::
2752
2753 import os
2754 os.remove("backup.db")
2755 os.remove("dump.sql")
2756 os.remove("example.db")
2757 os.remove("tutorial.db")