Newer
Older
:mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
============================================================
.. module:: sqlite3
:synopsis: A DB-API 2.0 implementation using SQLite 3.x.
Terry Jan Reedy
committed
.. sectionauthor:: Gerhard Häring <gh@ghaering.de>
Terry Jan Reedy
committed
**Source code:** :source:`Lib/sqlite3/`
--------------
SQLite is a C library that provides a lightweight disk-based database that
doesn't require a separate server process and allows accessing the database
using a nonstandard variant of the SQL query language. Some applications can use
SQLite for internal data storage. It's also possible to prototype an
application using SQLite and then port the code to a larger database such as
PostgreSQL or Oracle.
The sqlite3 module was written by Gerhard Häring. It provides an SQL interface
compliant with the DB-API 2.0 specification described by :pep:`249`, and
requires SQLite 3.7.15 or newer.
To use the module, start by creating a :class:`Connection` object that
represents the database. Here the data will be stored in the
:file:`example.db` file::
con = sqlite3.connect('example.db')
The special path name ``:memory:`` can be provided to create a temporary
database in RAM.
Once a :class:`Connection` has been established, create a :class:`Cursor` object
and call its :meth:`~Cursor.execute` method to perform SQL commands::
cur = con.cursor()
cur.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
con.commit()
# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()
The saved data is persistent: it can be reloaded in a subsequent session even
after restarting the Python interpreter::
con = sqlite3.connect('example.db')
cur = con.cursor()
To retrieve data after executing a SELECT statement, either treat the cursor as
an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list
of the matching rows.
This example uses the iterator form::
>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
.. _sqlite3-placeholders:
SQL operations usually need to use values from Python variables. However,
beware of using Python's string operations to assemble queries, as they
are vulnerable to SQL injection attacks (see the `xkcd webcomic
<https://xkcd.com/327/>`_ for a humorous example of what can go wrong)::
# Never do this -- insecure!
symbol = 'RHAT'
cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
Instead, use the DB-API's parameter substitution. To insert a variable into a
query string, use a placeholder in the string, and substitute the actual values
into the query by providing them as a :class:`tuple` of values to the second
argument of the cursor's :meth:`~Cursor.execute` method. An SQL statement may
use one of two kinds of placeholders: question marks (qmark style) or named
placeholders (named style). For the qmark style, ``parameters`` must be a
:term:`sequence <sequence>`. For the named style, it can be either a
:term:`sequence <sequence>` or :class:`dict` instance. The length of the
:term:`sequence <sequence>` must match the number of placeholders, or a
:exc:`ProgrammingError` is raised. If a :class:`dict` is given, it must contain
keys for all named parameters. Any extra items are ignored. Here's an example of
both styles:
.. literalinclude:: ../includes/sqlite3/execute_1.py
Serhiy Storchaka
committed
https://www.sqlite.org
The SQLite web page; the documentation describes the syntax and the
available data types for the supported SQL dialect.
https://www.w3schools.com/sql/
Tutorial, reference and examples for learning SQL syntax.
:pep:`249` - Database API Specification 2.0
PEP written by Marc-André Lemburg.
.. _sqlite3-module-contents:
Module functions and constants
------------------------------
Erlend Egeberg Aasland
committed
.. data:: apilevel
String constant stating the supported DB-API level. Required by the DB-API.
Hard-coded to ``"2.0"``.
.. data:: paramstyle
String constant stating the type of parameter marker formatting expected by
the :mod:`sqlite3` module. Required by the DB-API. Hard-coded to
``"qmark"``.
.. note::
The :mod:`sqlite3` module supports both ``qmark`` and ``numeric`` DB-API
parameter styles, because that is what the underlying SQLite library
supports. However, the DB-API does not allow multiple values for
the ``paramstyle`` attribute.
.. data:: version
The version number of this module, as a string. This is not the version of
the SQLite library.
.. data:: version_info
The version number of this module, as a tuple of integers. This is not the
version of the SQLite library.
.. data:: sqlite_version
The version number of the run-time SQLite library, as a string.
.. data:: sqlite_version_info
The version number of the run-time SQLite library, as a tuple of integers.
Erlend Egeberg Aasland
committed
.. data:: threadsafety
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
Integer constant required by the DB-API 2.0, stating the level of thread
safety the :mod:`sqlite3` module supports. This attribute is set based on
the default `threading mode <https://sqlite.org/threadsafe.html>`_ the
underlying SQLite library is compiled with. The SQLite threading modes are:
1. **Single-thread**: In this mode, all mutexes are disabled and SQLite is
unsafe to use in more than a single thread at once.
2. **Multi-thread**: In this mode, SQLite can be safely used by multiple
threads provided that no single database connection is used
simultaneously in two or more threads.
3. **Serialized**: In serialized mode, SQLite can be safely used by
multiple threads with no restriction.
The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels
are as follows:
+------------------+-----------------+----------------------+-------------------------------+
| SQLite threading | `threadsafety`_ | `SQLITE_THREADSAFE`_ | DB-API 2.0 meaning |
| mode | | | |
+==================+=================+======================+===============================+
| single-thread | 0 | 0 | Threads may not share the |
| | | | module |
+------------------+-----------------+----------------------+-------------------------------+
| multi-thread | 1 | 2 | Threads may share the module, |
| | | | but not connections |
+------------------+-----------------+----------------------+-------------------------------+
| serialized | 3 | 1 | Threads may share the module, |
| | | | connections and cursors |
+------------------+-----------------+----------------------+-------------------------------+
.. _threadsafety: https://peps.python.org/pep-0249/#threadsafety
.. _SQLITE_THREADSAFE: https://sqlite.org/compile.html#threadsafe
.. versionchanged:: 3.11
Set *threadsafety* dynamically instead of hard-coding it to ``1``.
Erlend Egeberg Aasland
committed
Erlend Egeberg Aasland
committed
Pass this flag value to the *detect_types* parameter of
:func:`connect` to look up a converter function using
the declared types for each column.
The types are declared when the database table is created.
``sqlite3`` will look up a converter function using the first word of the
declared type as the converter dictionary key.
For example:
Erlend Egeberg Aasland
committed
.. code-block:: sql
CREATE TABLE test(
i integer primary key, ! will look up a converter named "integer"
p point, ! will look up a converter named "point"
n number(10) ! will look up a converter named "number"
)
This flag may be combined with :const:`PARSE_COLNAMES` using the ``|``
(bitwise or) operator.
Erlend Egeberg Aasland
committed
Pass this flag value to the *detect_types* parameter of
:func:`connect` to look up a converter function by
using the type name, parsed from the query column name,
as the converter dictionary key.
The type name must be wrapped in square brackets (``[]``).
.. code-block:: sql
Erlend Egeberg Aasland
committed
SELECT p as "p [point]" FROM test; ! will look up converter "point"
This flag may be combined with :const:`PARSE_DECLTYPES` using the ``|``
(bitwise or) operator.
Erlend Egeberg Aasland
committed
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
.. function:: connect(database, timeout=5.0, detect_types=0, isolation_level="DEFERRED", check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)
Open a connection to an SQLite database.
:param database:
The path to the database file to be opened.
Pass ``":memory:"`` to open a connection to a database that is
in RAM instead of on disk.
:type database: :term:`path-like object`
:param timeout:
How many seconds the connection should wait before raising
an exception, if the database is locked by another connection.
If another connection opens a transaction to modify the database,
it will be locked until that transaction is committed.
Default five seconds.
:type timeout: float
:param detect_types:
Control whether and how data types not
:ref:`natively supported by SQLite <sqlite3-types>`
are looked up to be converted to Python types,
using the converters registered with :func:`register_converter`.
Set it to any combination (using ``|``, bitwise or) of
:const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`
to enable this.
Column names takes precedence over declared types if both flags are set.
Types cannot be detected for generated fields (for example ``max(data)``),
even when the *detect_types* parameter is set; :class:`str` will be
returned instead.
By default (``0``), type detection is disabled.
:type detect_types: int
:param isolation_level:
The :attr:`~Connection.isolation_level` of the connection,
controlling whether and how transactions are implicitly opened.
Can be ``"DEFERRED"`` (default), ``"EXCLUSIVE"`` or ``"IMMEDIATE"``;
or :const:`None` to disable opening transactions implicitly.
See :ref:`sqlite3-controlling-transactions` for more.
:type isolation_level: str | None
:param check_same_thread:
If :const:`True` (default), only the creating thread may use the connection.
If :const:`False`, the connection may be shared across multiple threads;
if so, write operations should be serialized by the user to avoid data
corruption.
:type check_same_thread: bool
:param factory:
A custom subclass of :class:`Connection` to create the connection with,
if not the default :class:`Connection` class.
:type factory: :class:`Connection`
:param cached_statements:
The number of statements that ``sqlite3``
should internally cache for this connection, to avoid parsing overhead.
By default, 128 statements.
:type cached_statements: int
:param uri:
If set to :const:`True`, *database* is interpreted as a
:abbr:`URI (Uniform Resource Identifier)` with a file path
and an optional query string.
The scheme part *must* be ``"file:"``,
and the path can be relative or absolute.
The query string allows passing parameters to SQLite,
enabling various :ref:`sqlite3-uri-tricks`.
:type uri: bool
:rtype: sqlite3.Connection
.. audit-event:: sqlite3.connect database sqlite3.connect
Erlend Egeberg Aasland
committed
.. audit-event:: sqlite3.connect/handle connection_handle sqlite3.connect
Erlend Egeberg Aasland
committed
.. versionadded:: 3.4
The *uri* parameter.
Anders Lorentsen
committed
.. versionchanged:: 3.7
*database* can now also be a :term:`path-like object`, not only a string.
Erlend Egeberg Aasland
committed
.. versionadded:: 3.10
The ``sqlite3.connect/handle`` auditing event.
Erlend Egeberg Aasland
committed
Miss Islington (bot)
committed
.. function:: register_converter(typename, converter, /)
Erlend Egeberg Aasland
committed
Register the *converter* callable to convert SQLite objects of type
*typename* into a Python object of a specific type.
The converter is invoked for all SQLite values of type *typename*;
it is passed a :class:`bytes` object and should return an object of the
desired Python type.
Consult the parameter *detect_types* of
:func:`connect` for information regarding how type detection works.
Erlend Egeberg Aasland
committed
Note: *typename* and the name of the type in your query are matched
case-insensitively.
Miss Islington (bot)
committed
.. function:: register_adapter(type, adapter, /)
Erlend Egeberg Aasland
committed
Register an *adapter* callable to adapt the Python type *type* into an
SQLite type.
The adapter is called with a Python object of type *type* as its sole
argument, and must return a value of a
:ref:`type that SQLite natively understands<sqlite3-types>`.
Miss Islington (bot)
committed
.. function:: complete_statement(statement)
Miss Islington (bot)
committed
Returns :const:`True` if the string *statement* contains one or more complete SQL
statements terminated by semicolons. It does not verify that the SQL is
syntactically correct, only that there are no unclosed string literals and the
statement is terminated by a semicolon.
This can be used to build a shell for SQLite, as in the following example:
.. literalinclude:: ../includes/sqlite3/complete_statement.py
Miss Islington (bot)
committed
.. function:: enable_callback_tracebacks(flag, /)
By default you will not get any tracebacks in user-defined functions,
aggregates, converters, authorizer callbacks etc. If you want to debug them,
Erlend Egeberg Aasland
committed
you can call this function with *flag* set to :const:`True`. Afterwards, you
will get tracebacks from callbacks on :data:`sys.stderr`. Use :const:`False`
to disable the feature again.
Register an :func:`unraisable hook handler <sys.unraisablehook>` for an
improved debug experience::
>>> import sqlite3
>>> sqlite3.enable_callback_tracebacks(True)
>>> cx = sqlite3.connect(":memory:")
>>> cx.set_trace_callback(lambda stmt: 5/0)
>>> cx.execute("select 1")
Exception ignored in: <function <lambda> at 0x10b4e3ee0>
Traceback (most recent call last):
File "<stdin>", line 1, in <lambda>
ZeroDivisionError: division by zero
>>> import sys
>>> sys.unraisablehook = lambda unraisable: print(unraisable)
>>> cx.execute("select 1")
UnraisableHookArgs(exc_type=<class 'ZeroDivisionError'>, exc_value=ZeroDivisionError('division by zero'), exc_traceback=<traceback object at 0x10b559900>, err_msg=None, object=<function <lambda> at 0x10b4e3ee0>)
<sqlite3.Cursor object at 0x10b1fe840>
.. _sqlite3-connection-objects:
Connection Objects
------------------
An SQLite database connection has the following attributes and methods:
Miss Islington (bot)
committed
This attribute controls the :ref:`transaction handling
<sqlite3-controlling-transactions>` performed by ``sqlite3``.
If set to :const:`None`, transactions are never implicitly opened.
If set to one of ``"DEFERRED"``, ``"IMMEDIATE"``, or ``"EXCLUSIVE"``,
corresponding to the underlying `SQLite transaction behaviour`_,
implicit :ref:`transaction management
<sqlite3-controlling-transactions>` is performed.
If not overridden by the *isolation_level* parameter of :func:`connect`,
the default is ``""``, which is an alias for ``"DEFERRED"``.
R. David Murray
committed
Miss Islington (bot)
committed
This read-only attribute corresponds to the low-level SQLite
`autocommit mode`_.
:const:`True` if a transaction is active (there are uncommitted changes),
Miss Islington (bot)
committed
:const:`False` otherwise.
R. David Murray
committed
Serhiy Storchaka
committed
.. method:: cursor(factory=Cursor)
Serhiy Storchaka
committed
The cursor method accepts a single optional parameter *factory*. If
supplied, this must be a callable returning an instance of :class:`Cursor`
or its subclasses.
Erlend Egeberg Aasland
committed
.. method:: blobopen(table, column, row, /, *, readonly=False, name="main")
Open a :class:`Blob` handle to the :abbr:`BLOB (Binary Large OBject)`
located in table name *table*, column name *column*, and row index *row*
of database *name*.
Erlend Egeberg Aasland
committed
When *readonly* is :const:`True` the blob is opened without write
permissions.
Trying to open a blob in a ``WITHOUT ROWID`` table will raise
:exc:`OperationalError`.
Erlend Egeberg Aasland
committed
.. note::
The blob size cannot be changed using the :class:`Blob` class.
Use the SQL function ``zeroblob`` to create a blob with a fixed size.
.. versionadded:: 3.11
Miss Islington (bot)
committed
Commit any pending transaction to the database.
If there is no open transaction, this method is a no-op.
Miss Islington (bot)
committed
Roll back to the start of any pending transaction.
If there is no open transaction, this method is a no-op.
Miss Islington (bot)
committed
Close the database connection.
Any pending transaction is not committed implicitly;
make sure to :meth:`commit` before closing
to avoid losing pending changes.
.. method:: execute(sql[, parameters])
Create a new :class:`Cursor` object and call
:meth:`~Cursor.execute` on it with the given *sql* and *parameters*.
Return the new cursor object.
.. method:: executemany(sql[, parameters])
Create a new :class:`Cursor` object and call
:meth:`~Cursor.executemany` on it with the given *sql* and *parameters*.
Return the new cursor object.
Create a new :class:`Cursor` object and call
:meth:`~Cursor.executescript` on it with the given *sql_script*.
Return the new cursor object.
Miss Islington (bot)
committed
.. method:: create_function(name, narg, func, *, deterministic=False)
Creates a user-defined function that you can later use from within SQL
Miss Islington (bot)
committed
statements under the function name *name*. *narg* is the number of
parameters the function accepts (if *narg* is -1, the function may
Berker Peksag
committed
take any number of arguments), and *func* is a Python callable that is
Sergey Fedoseev
committed
called as the SQL function. If *deterministic* is true, the created function
is marked as `deterministic <https://sqlite.org/deterministic.html>`_, which
allows SQLite to perform additional optimizations. This flag is supported by
SQLite 3.8.3 or higher, :exc:`NotSupportedError` will be raised if used
Sergey Fedoseev
committed
with older versions.
Erlend Egeberg Aasland
committed
The function can return any of
:ref:`the types natively supported by SQLite <sqlite3-types>`.
Sergey Fedoseev
committed
.. versionchanged:: 3.8
The *deterministic* parameter was added.
.. literalinclude:: ../includes/sqlite3/md5func.py
Miss Islington (bot)
committed
.. method:: create_aggregate(name, /, n_arg, aggregate_class)
The aggregate class must implement a ``step`` method, which accepts the number
Miss Islington (bot)
committed
of parameters *n_arg* (if *n_arg* is -1, the function may take
Berker Peksag
committed
any number of arguments), and a ``finalize`` method which will return the
Erlend Egeberg Aasland
committed
The ``finalize`` method can return any of
:ref:`the types natively supported by SQLite <sqlite3-types>`.
.. literalinclude:: ../includes/sqlite3/mysumaggr.py
Erlend Egeberg Aasland
committed
.. method:: create_window_function(name, num_params, aggregate_class, /)
Creates user-defined aggregate window function *name*.
*aggregate_class* must implement the following methods:
* ``step``: adds a row to the current window
* ``value``: returns the current value of the aggregate
* ``inverse``: removes a row from the current window
* ``finalize``: returns the final value of the aggregate
``step`` and ``value`` accept *num_params* number of parameters,
unless *num_params* is ``-1``, in which case they may take any number of
Erlend Egeberg Aasland
committed
arguments.
``finalize`` and ``value`` can return any of
:ref:`the types natively supported by SQLite <sqlite3-types>`.
Call :meth:`create_window_function` with
Erlend Egeberg Aasland
committed
*aggregate_class* set to :const:`None` to clear window function *name*.
Aggregate window functions are supported by SQLite 3.25.0 and higher.
:exc:`NotSupportedError` will be raised if used with older versions.
.. versionadded:: 3.11
Example:
.. literalinclude:: ../includes/sqlite3/sumintwindow.py
Miss Islington (bot)
committed
Create a collation named *name* using the collating function *callable*.
*callable* is passed two :class:`string <str>` arguments,
and it should return an :class:`integer <int>`:
Miss Islington (bot)
committed
* ``1`` if the first is ordered higher than the second
* ``-1`` if the first is ordered lower than the second
* ``0`` if they are ordered equal
Miss Islington (bot)
committed
The following example shows a reverse sorting collation:
.. literalinclude:: ../includes/sqlite3/collation_reverse.py
Miss Islington (bot)
committed
Remove a collation function by setting *callable* to :const:`None`.
Erlend Egeberg Aasland
committed
.. versionchanged:: 3.11
The collation name can contain any Unicode character. Earlier, only
ASCII characters were allowed.
You can call this method from a different thread to abort any queries that might
be executing on the connection. The query will then abort and the caller will
get an exception.
.. method:: set_authorizer(authorizer_callback)
This routine registers a callback. The callback is invoked for each attempt to
access a column of a table in the database. The callback should return
:const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL
statement should be aborted with an error and :const:`SQLITE_IGNORE` if the
column should be treated as a NULL value. These constants are available in the
:mod:`sqlite3` module.
The first argument to the callback signifies what kind of operation is to be
authorized. The second and third argument will be arguments or :const:`None`
depending on the first argument. The 4th argument is the name of the database
("main", "temp", etc.) if applicable. The 5th argument is the name of the
inner-most trigger or view that is responsible for the access attempt or
:const:`None` if this access attempt is directly from input SQL code.
Please consult the SQLite documentation about the possible values for the first
argument and the meaning of the second and third argument depending on the first
one. All necessary constants are available in the :mod:`sqlite3` module.
Erlend Egeberg Aasland
committed
Passing :const:`None` as *authorizer_callback* will disable the authorizer.
.. versionchanged:: 3.11
Added support for disabling the authorizer using :const:`None`.
Miss Islington (bot)
committed
.. method:: set_progress_handler(progress_handler, n)
This routine registers a callback. The callback is invoked for every *n*
instructions of the SQLite virtual machine. This is useful if you want to
get called from SQLite during long-running operations, for example to update
a GUI.
If you want to clear any previously installed progress handler, call the
Miss Islington (bot)
committed
method with :const:`None` for *progress_handler*.
Returning a non-zero value from the handler function will terminate the
currently executing query and cause it to raise an :exc:`OperationalError`
exception.
.. method:: set_trace_callback(trace_callback)
Antoine Pitrou
committed
Registers *trace_callback* to be called for each SQL statement that is
actually executed by the SQLite backend.
Antoine Pitrou
committed
The only argument passed to the callback is the statement (as
:class:`str`) that is being executed. The return value of the callback is
ignored. Note that the backend does not only run statements passed to the
:meth:`Cursor.execute` methods. Other sources include the
:ref:`transaction management <sqlite3-controlling-transactions>` of the
sqlite3 module and the execution of triggers defined in the current
database.
Antoine Pitrou
committed
Passing :const:`None` as *trace_callback* will disable the trace callback.
Antoine Pitrou
committed
.. note::
Exceptions raised in the trace callback are not propagated. As a
development and debugging aid, use
:meth:`~sqlite3.enable_callback_tracebacks` to enable printing
tracebacks from exceptions raised in the trace callback.
Antoine Pitrou
committed
Miss Islington (bot)
committed
.. method:: enable_load_extension(enabled, /)
This routine allows/disallows the SQLite engine to load SQLite extensions
from shared libraries. SQLite extensions can define new functions,
aggregates or whole new virtual table implementations. One well-known
extension is the fulltext-search extension distributed with SQLite.
Loadable extensions are disabled by default. See [#f1]_.
.. audit-event:: sqlite3.enable_load_extension connection,enabled sqlite3.Connection.enable_load_extension
Erlend Egeberg Aasland
committed
Erlend Egeberg Aasland
committed
.. versionchanged:: 3.10
Added the ``sqlite3.enable_load_extension`` auditing event.
.. literalinclude:: ../includes/sqlite3/load_extension.py
Miss Islington (bot)
committed
.. method:: load_extension(path, /)
This routine loads an SQLite extension from a shared library. You have to
enable extension loading with :meth:`enable_load_extension` before you can
use this routine.
Loadable extensions are disabled by default. See [#f1]_.
.. audit-event:: sqlite3.load_extension connection,path sqlite3.Connection.load_extension
Erlend Egeberg Aasland
committed
Erlend Egeberg Aasland
committed
.. versionchanged:: 3.10
Added the ``sqlite3.load_extension`` auditing event.
You can change this attribute to a callable that accepts the cursor and the
original row as a tuple and will return the real result row. This way, you can
implement more advanced ways of returning results, such as returning an object
that can also access columns by name.
.. literalinclude:: ../includes/sqlite3/row_factory.py
If returning a tuple doesn't suffice and you want name-based access to
columns, you should consider setting :attr:`row_factory` to the
Miss Islington (bot)
committed
highly optimized :class:`sqlite3.Row` type. :class:`Row` provides both
index-based and case-insensitive name-based access to columns with almost no
memory overhead. It will probably be better than your own custom
dictionary-based approach or even a db_row based solution.
Using this attribute you can control what objects are returned for the ``TEXT``
data type. By default, this attribute is set to :class:`str` and the
:mod:`sqlite3` module will return :class:`str` objects for ``TEXT``.
If you want to return :class:`bytes` instead, you can set it to :class:`bytes`.
You can also set it to any other callable that accepts a single bytestring
parameter and returns the resulting object.
See the following example code for illustration:
.. literalinclude:: ../includes/sqlite3/text_factory.py
Returns the total number of database rows that have been modified, inserted, or
deleted since the database connection was opened.
Christian Heimes
committed
.. method:: iterdump
Christian Heimes
committed
Returns an iterator to dump the database in an SQL text format. Useful when
saving an in-memory database for later restoration. This function provides
the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3`
shell.
Christian Heimes
committed
Christian Heimes
committed
# Convert file existing_db.db to SQL dump file dump.sql
Christian Heimes
committed
con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
for line in con.iterdump():
f.write('%s\n' % line)
con.close()
Christian Heimes
committed
.. method:: backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)
This method makes a backup of an SQLite database even while it's being accessed
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
by other clients, or concurrently by the same connection. The copy will be
written into the mandatory argument *target*, that must be another
:class:`Connection` instance.
By default, or when *pages* is either ``0`` or a negative integer, the entire
database is copied in a single step; otherwise the method performs a loop
copying up to *pages* pages at a time.
If *progress* is specified, it must either be ``None`` or a callable object that
will be executed at each iteration with three integer arguments, respectively
the *status* of the last iteration, the *remaining* number of pages still to be
copied and the *total* number of pages.
The *name* argument specifies the database name that will be copied: it must be
a string containing either ``"main"``, the default, to indicate the main
database, ``"temp"`` to indicate the temporary database or the name specified
after the ``AS`` keyword in an ``ATTACH DATABASE`` statement for an attached
database.
The *sleep* argument specifies the number of seconds to sleep by between
successive attempts to backup remaining pages, can be specified either as an
integer or a floating point value.
Example 1, copy an existing database into another::
import sqlite3
def progress(status, remaining, total):
print(f'Copied {total-remaining} of {total} pages...')
con = sqlite3.connect('existing_db.db')
bck = sqlite3.connect('backup.db')
with bck:
con.backup(bck, pages=1, progress=progress)
bck.close()
con.close()
Example 2, copy an existing database into a transient copy::
import sqlite3
source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)
.. versionadded:: 3.7
Erlend Egeberg Aasland
committed
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
.. method:: getlimit(category, /)
Get a connection run-time limit. *category* is the limit category to be
queried.
Example, query the maximum length of an SQL statement::
import sqlite3
con = sqlite3.connect(":memory:")
lim = con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)
print(f"SQLITE_LIMIT_SQL_LENGTH={lim}")
.. versionadded:: 3.11
.. method:: setlimit(category, limit, /)
Set a connection run-time limit. *category* is the limit category to be
set. *limit* is the new limit. If the new limit is a negative number, the
limit is unchanged.
Attempts to increase a limit above its hard upper bound are silently
truncated to the hard upper bound. Regardless of whether or not the limit
was changed, the prior value of the limit is returned.
Example, limit the number of attached databases to 1::
import sqlite3
con = sqlite3.connect(":memory:")
con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1)
.. versionadded:: 3.11
Erlend Egeberg Aasland
committed
.. method:: serialize(*, name="main")
This method serializes a database into a :class:`bytes` object. For an
ordinary on-disk database file, the serialization is just a copy of the
disk file. For an in-memory database or a "temp" database, the
serialization is the same sequence of bytes which would be written to
disk if that database were backed up to disk.
*name* is the database to be serialized, and defaults to the main
database.
.. note::
This method is only available if the underlying SQLite library has the
serialize API.
.. versionadded:: 3.11
.. method:: deserialize(data, /, *, name="main")
This method causes the database connection to disconnect from database
*name*, and reopen *name* as an in-memory database based on the
serialization contained in *data*. Deserialization will raise
:exc:`OperationalError` if the database connection is currently involved
Erlend Egeberg Aasland
committed
in a read transaction or a backup operation. :exc:`OverflowError` will be
Erlend Egeberg Aasland
committed
raised if ``len(data)`` is larger than ``2**63 - 1``, and
:exc:`DatabaseError` will be raised if *data* does not contain a valid
SQLite database.
.. note::
This method is only available if the underlying SQLite library has the
deserialize API.
.. versionadded:: 3.11
.. _sqlite3-cursor-objects:
Cursor Objects
--------------
A :class:`Cursor` instance has the following attributes and methods.
.. index:: single: ? (question mark); in SQL statements
.. index:: single: : (colon); in SQL statements
Miss Islington (bot)
committed
.. method:: execute(sql, parameters=(), /)
Miss Islington (bot)
committed
Execute an SQL statement. Values may be bound to the statement using
:ref:`placeholders <sqlite3-placeholders>`.
:meth:`execute` will only execute a single SQL statement. If you try to execute
Erlend Egeberg Aasland
committed
more than one statement with it, it will raise a :exc:`ProgrammingError`. Use
:meth:`executescript` if you want to execute multiple SQL statements with one
call.
Miss Islington (bot)
committed
If :attr:`~Connection.isolation_level` is not :const:`None`,
*sql* is an ``INSERT``, ``UPDATE``, ``DELETE``, or ``REPLACE`` statement,
and there is no open transaction,
a transaction is implicitly opened before executing *sql*.
Miss Islington (bot)
committed
.. method:: executemany(sql, seq_of_parameters, /)
Miss Islington (bot)
committed
Execute a :ref:`parameterized <sqlite3-placeholders>` SQL command
against all parameter sequences or mappings found in the sequence
Miss Islington (bot)
committed
*seq_of_parameters*. It is also possible to use an
:term:`iterator` yielding parameters instead of a sequence.
Miss Islington (bot)
committed
Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
.. literalinclude:: ../includes/sqlite3/executemany_1.py
Here's a shorter example using a :term:`generator`:
.. literalinclude:: ../includes/sqlite3/executemany_2.py
Miss Islington (bot)
committed
.. method:: executescript(sql_script, /)
Miss Islington (bot)
committed
Execute multiple SQL statements at once.
If there is a pending transaciton,
an implicit ``COMMIT`` statement is executed first.
No other implicit transaction control is performed;
any transaction control must be added to *sql_script*.
Miss Islington (bot)
committed
*sql_script* must be a :class:`string <str>`.
.. literalinclude:: ../includes/sqlite3/executescript.py
Fetches the next row of a query result set, returning a single sequence,
or :const:`None` when no more data is available.
Fetches the next set of rows of a query result, returning a list. An empty
list is returned when no more rows are available.
The number of rows to fetch per call is specified by the *size* parameter.
If it is not given, the cursor's arraysize determines the number of rows
to be fetched. The method should try to fetch as many rows as indicated by
the size parameter. If this is not possible due to the specified number of
rows not being available, fewer rows may be returned.
Note there are performance considerations involved with the *size* parameter.
For optimal performance, it is usually best to use the arraysize attribute.
If the *size* parameter is used, then it is best for it to retain the same
value from one :meth:`fetchmany` call to the next.
Fetches all (remaining) rows of a query result, returning a list. Note that
the cursor's arraysize attribute can affect the performance of this operation.
An empty list is returned when no rows are available.
.. method:: close()
Close the cursor now (rather than whenever ``__del__`` is called).
The cursor will be unusable from this point forward; a :exc:`ProgrammingError`
exception will be raised if any operation is attempted with the cursor.
Miss Islington (bot)
committed
.. method:: setinputsizes(sizes, /)
Erlend Egeberg Aasland
committed
Required by the DB-API. Does nothing in :mod:`sqlite3`.
Erlend Egeberg Aasland
committed
Miss Islington (bot)
committed
.. method:: setoutputsize(size, column=None, /)
Erlend Egeberg Aasland
committed
Required by the DB-API. Does nothing in :mod:`sqlite3`.
Erlend Egeberg Aasland
committed
Read-only attribute that provides the number of modified rows for
``INSERT``, ``UPDATE``, ``DELETE``, and ``REPLACE`` statements;
is ``-1`` for other statements,
including :abbr:`CTE (Common Table Expression)` queries.
It is only updated by the :meth:`execute` and :meth:`executemany` methods.
This read-only attribute provides the row id of the last inserted row. It
is only updated after successful ``INSERT`` or ``REPLACE`` statements
using the :meth:`execute` method. For other statements, after
:meth:`executemany` or :meth:`executescript`, or if the insertion failed,
the value of ``lastrowid`` is left unchanged. The initial value of
``lastrowid`` is :const:`None`.
.. note::
Inserts into ``WITHOUT ROWID`` tables are not recorded.
.. versionchanged:: 3.6
Added support for the ``REPLACE`` statement.
.. attribute:: arraysize
Read/write attribute that controls the number of rows returned by :meth:`fetchmany`.
The default value is 1 which means a single row would be fetched per call.
This read-only attribute provides the column names of the last query. To
remain compatible with the Python DB API, it returns a 7-tuple for each
column where the last six items of each tuple are :const:`None`.
It is set for ``SELECT`` statements without any matching rows as well.
Ezio Melotti
committed
.. attribute:: connection
This read-only attribute provides the SQLite database :class:`Connection`
used by the :class:`Cursor` object. A :class:`Cursor` object created by
calling :meth:`con.cursor() <Connection.cursor>` will have a
:attr:`connection` attribute that refers to *con*::
>>> con = sqlite3.connect(":memory:")