1
:mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
2
============================================================
5
:synopsis: A DB-API 2.0 implementation using SQLite 3.x.
7
.. sectionauthor:: Gerhard Häring <gh@ghaering.de>
9
**Source code:** :source:`Lib/sqlite3/`
13
SQLite is a C library that provides a lightweight disk-based database that
14
doesn't require a separate server process and allows accessing the database
15
using a nonstandard variant of the SQL query language. Some applications can use
16
SQLite for internal data storage. It's also possible to prototype an
17
application using SQLite and then port the code to a larger database such as
20
The sqlite3 module was written by Gerhard Häring. It provides a SQL interface
21
compliant with the DB-API 2.0 specification described by :pep:`249`.
23
To use the module, you must first create a :class:`Connection` object that
24
represents the database. Here the data will be stored in the
25
:file:`example.db` file::
28
conn = sqlite3.connect('example.db')
30
You can also supply the special name ``:memory:`` to create a database in RAM.
32
Once you have a :class:`Connection`, you can create a :class:`Cursor` object
33
and call its :meth:`~Cursor.execute` method to perform SQL commands::
38
c.execute('''CREATE TABLE stocks
39
(date text, trans text, symbol text, qty real, price real)''')
41
# Insert a row of data
42
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
44
# Save (commit) the changes
47
# We can also close the connection if we are done with it.
48
# Just be sure any changes have been committed or they will be lost.
51
The data you've saved is persistent and is available in subsequent sessions::
54
conn = sqlite3.connect('example.db')
57
Usually your SQL operations will need to use values from Python variables. You
58
shouldn't assemble your query using Python's string operations because doing so
59
is insecure; it makes your program vulnerable to an SQL injection attack
60
(see https://xkcd.com/327/ for humorous example of what can go wrong).
62
Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder
63
wherever you want to use a value, and then provide a tuple of values as the
64
second argument to the cursor's :meth:`~Cursor.execute` method. (Other database
65
modules may use a different placeholder, such as ``%s`` or ``:1``.) For
68
# Never do this -- insecure!
70
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
74
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
77
# Larger example that inserts many records at a time
78
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
79
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
80
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
82
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
84
To retrieve data after executing a SELECT statement, you can either treat the
85
cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
86
retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the
89
This example uses the iterator form::
91
>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
94
('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
95
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
96
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
97
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
102
https://github.com/ghaering/pysqlite
103
The pysqlite web page -- sqlite3 is developed externally under the name
106
https://www.sqlite.org
107
The SQLite web page; the documentation describes the syntax and the
108
available data types for the supported SQL dialect.
110
http://www.w3schools.com/sql/
111
Tutorial, reference and examples for learning SQL syntax.
113
:pep:`249` - Database API Specification 2.0
114
PEP written by Marc-André Lemburg.
117
.. _sqlite3-module-contents:
119
Module functions and constants
120
------------------------------
125
The version number of this module, as a string. This is not the version of
129
.. data:: version_info
131
The version number of this module, as a tuple of integers. This is not the
132
version of the SQLite library.
135
.. data:: sqlite_version
137
The version number of the run-time SQLite library, as a string.
140
.. data:: sqlite_version_info
142
The version number of the run-time SQLite library, as a tuple of integers.
145
.. data:: PARSE_DECLTYPES
147
This constant is meant to be used with the *detect_types* parameter of the
148
:func:`connect` function.
150
Setting it makes the :mod:`sqlite3` module parse the declared type for each
151
column it returns. It will parse out the first word of the declared type,
152
i. e. for "integer primary key", it will parse out "integer", or for
153
"number(10)" it will parse out "number". Then for that column, it will look
154
into the converters dictionary and use the converter function registered for
158
.. data:: PARSE_COLNAMES
160
This constant is meant to be used with the *detect_types* parameter of the
161
:func:`connect` function.
163
Setting this makes the SQLite interface parse the column name for each column it
164
returns. It will look for a string formed [mytype] in there, and then decide
165
that 'mytype' is the type of the column. It will try to find an entry of
166
'mytype' in the converters dictionary and then use the converter function found
167
there to return the value. The column name found in :attr:`Cursor.description`
168
is only the first word of the column name, i. e. if you use something like
169
``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the
170
first blank for the column name: the column name would simply be "x".
173
.. function:: connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
175
Opens a connection to the SQLite database file *database*. You can use
176
``":memory:"`` to open a database connection to a database that resides in RAM
179
When a database is accessed by multiple connections, and one of the processes
180
modifies the database, the SQLite database is locked until that transaction is
181
committed. The *timeout* parameter specifies how long the connection should wait
182
for the lock to go away until raising an exception. The default for the timeout
183
parameter is 5.0 (five seconds).
185
For the *isolation_level* parameter, please see the
186
:attr:`~Connection.isolation_level` property of :class:`Connection` objects.
188
SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If
189
you want to use other types you must add support for them yourself. The
190
*detect_types* parameter and the using custom **converters** registered with the
191
module-level :func:`register_converter` function allow you to easily do that.
193
*detect_types* defaults to 0 (i. e. off, no type detection), you can set it to
194
any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
197
By default, *check_same_thread* is :const:`True` and only the creating thread may
198
use the connection. If set :const:`False`, the returned connection may be shared
199
across multiple threads. When using multiple threads with the same connection
200
writing operations should be serialized by the user to avoid data corruption.
202
By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the
203
connect call. You can, however, subclass the :class:`Connection` class and make
204
:func:`connect` use your class instead by providing your class for the *factory*
207
Consult the section :ref:`sqlite3-types` of this manual for details.
209
The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing
210
overhead. If you want to explicitly set the number of statements that are cached
211
for the connection, you can set the *cached_statements* parameter. The currently
212
implemented default is to cache 100 statements.
214
If *uri* is true, *database* is interpreted as a URI. This allows you
215
to specify options. For example, to open a database in read-only mode
218
db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)
220
More information about this feature, including a list of recognized options, can
221
be found in the `SQLite URI documentation <https://www.sqlite.org/uri.html>`_.
223
.. versionchanged:: 3.4
224
Added the *uri* parameter.
227
.. function:: register_converter(typename, callable)
229
Registers a callable to convert a bytestring from the database into a custom
230
Python type. The callable will be invoked for all database values that are of
231
the type *typename*. Confer the parameter *detect_types* of the :func:`connect`
232
function for how the type detection works. Note that the case of *typename* and
233
the name of the type in your query must match!
236
.. function:: register_adapter(type, callable)
238
Registers a callable to convert the custom Python type *type* into one of
239
SQLite's supported types. The callable *callable* accepts as single parameter
240
the Python value, and must return a value of the following types: int,
244
.. function:: complete_statement(sql)
246
Returns :const:`True` if the string *sql* contains one or more complete SQL
247
statements terminated by semicolons. It does not verify that the SQL is
248
syntactically correct, only that there are no unclosed string literals and the
249
statement is terminated by a semicolon.
251
This can be used to build a shell for SQLite, as in the following example:
254
.. literalinclude:: ../includes/sqlite3/complete_statement.py
257
.. function:: enable_callback_tracebacks(flag)
259
By default you will not get any tracebacks in user-defined functions,
260
aggregates, converters, authorizer callbacks etc. If you want to debug them,
261
you can call this function with *flag* set to ``True``. Afterwards, you will
262
get tracebacks from callbacks on ``sys.stderr``. Use :const:`False` to
263
disable the feature again.
266
.. _sqlite3-connection-objects:
271
.. class:: Connection
273
A SQLite database connection has the following attributes and methods:
275
.. attribute:: isolation_level
277
Get or set the current isolation level. :const:`None` for autocommit mode or
278
one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
279
:ref:`sqlite3-controlling-transactions` for a more detailed explanation.
281
.. attribute:: in_transaction
283
:const:`True` if a transaction is active (there are uncommitted changes),
284
:const:`False` otherwise. Read-only attribute.
286
.. versionadded:: 3.2
288
.. method:: cursor(factory=Cursor)
290
The cursor method accepts a single optional parameter *factory*. If
291
supplied, this must be a callable returning an instance of :class:`Cursor`
296
This method commits the current transaction. If you don't call this method,
297
anything you did since the last call to ``commit()`` is not visible from
298
other database connections. If you wonder why you don't see the data you've
299
written to the database, please check you didn't forget to call this method.
301
.. method:: rollback()
303
This method rolls back any changes to the database since the last call to
308
This closes the database connection. Note that this does not automatically
309
call :meth:`commit`. If you just close your database connection without
310
calling :meth:`commit` first, your changes will be lost!
312
.. method:: execute(sql[, parameters])
314
This is a nonstandard shortcut that creates a cursor object by calling
315
the :meth:`~Connection.cursor` method, calls the cursor's
316
:meth:`~Cursor.execute` method with the *parameters* given, and returns
319
.. method:: executemany(sql[, parameters])
321
This is a nonstandard shortcut that creates a cursor object by
322
calling the :meth:`~Connection.cursor` method, calls the cursor's
323
:meth:`~Cursor.executemany` method with the *parameters* given, and
326
.. method:: executescript(sql_script)
328
This is a nonstandard shortcut that creates a cursor object by
329
calling the :meth:`~Connection.cursor` method, calls the cursor's
330
:meth:`~Cursor.executescript` method with the given *sql_script*, and
333
.. method:: create_function(name, num_params, func)
335
Creates a user-defined function that you can later use from within SQL
336
statements under the function name *name*. *num_params* is the number of
337
parameters the function accepts (if *num_params* is -1, the function may
338
take any number of arguments), and *func* is a Python callable that is
339
called as the SQL function.
341
The function can return any of the types supported by SQLite: bytes, str, int,
346
.. literalinclude:: ../includes/sqlite3/md5func.py
349
.. method:: create_aggregate(name, num_params, aggregate_class)
351
Creates a user-defined aggregate function.
353
The aggregate class must implement a ``step`` method, which accepts the number
354
of parameters *num_params* (if *num_params* is -1, the function may take
355
any number of arguments), and a ``finalize`` method which will return the
356
final result of the aggregate.
358
The ``finalize`` method can return any of the types supported by SQLite:
359
bytes, str, int, float and ``None``.
363
.. literalinclude:: ../includes/sqlite3/mysumaggr.py
366
.. method:: create_collation(name, callable)
368
Creates a collation with the specified *name* and *callable*. The callable will
369
be passed two string arguments. It should return -1 if the first is ordered
370
lower than the second, 0 if they are ordered equal and 1 if the first is ordered
371
higher than the second. Note that this controls sorting (ORDER BY in SQL) so
372
your comparisons don't affect other SQL operations.
374
Note that the callable will get its parameters as Python bytestrings, which will
375
normally be encoded in UTF-8.
377
The following example shows a custom collation that sorts "the wrong way":
379
.. literalinclude:: ../includes/sqlite3/collation_reverse.py
381
To remove a collation, call ``create_collation`` with ``None`` as callable::
383
con.create_collation("reverse", None)
386
.. method:: interrupt()
388
You can call this method from a different thread to abort any queries that might
389
be executing on the connection. The query will then abort and the caller will
393
.. method:: set_authorizer(authorizer_callback)
395
This routine registers a callback. The callback is invoked for each attempt to
396
access a column of a table in the database. The callback should return
397
:const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL
398
statement should be aborted with an error and :const:`SQLITE_IGNORE` if the
399
column should be treated as a NULL value. These constants are available in the
400
:mod:`sqlite3` module.
402
The first argument to the callback signifies what kind of operation is to be
403
authorized. The second and third argument will be arguments or :const:`None`
404
depending on the first argument. The 4th argument is the name of the database
405
("main", "temp", etc.) if applicable. The 5th argument is the name of the
406
inner-most trigger or view that is responsible for the access attempt or
407
:const:`None` if this access attempt is directly from input SQL code.
409
Please consult the SQLite documentation about the possible values for the first
410
argument and the meaning of the second and third argument depending on the first
411
one. All necessary constants are available in the :mod:`sqlite3` module.
414
.. method:: set_progress_handler(handler, n)
416
This routine registers a callback. The callback is invoked for every *n*
417
instructions of the SQLite virtual machine. This is useful if you want to
418
get called from SQLite during long-running operations, for example to update
421
If you want to clear any previously installed progress handler, call the
422
method with :const:`None` for *handler*.
425
.. method:: set_trace_callback(trace_callback)
427
Registers *trace_callback* to be called for each SQL statement that is
428
actually executed by the SQLite backend.
430
The only argument passed to the callback is the statement (as string) that
431
is being executed. The return value of the callback is ignored. Note that
432
the backend does not only run statements passed to the :meth:`Cursor.execute`
433
methods. Other sources include the transaction management of the Python
434
module and the execution of triggers defined in the current database.
436
Passing :const:`None` as *trace_callback* will disable the trace callback.
438
.. versionadded:: 3.3
441
.. method:: enable_load_extension(enabled)
443
This routine allows/disallows the SQLite engine to load SQLite extensions
444
from shared libraries. SQLite extensions can define new functions,
445
aggregates or whole new virtual table implementations. One well-known
446
extension is the fulltext-search extension distributed with SQLite.
448
Loadable extensions are disabled by default. See [#f1]_.
450
.. versionadded:: 3.2
452
.. literalinclude:: ../includes/sqlite3/load_extension.py
454
.. method:: load_extension(path)
456
This routine loads a SQLite extension from a shared library. You have to
457
enable extension loading with :meth:`enable_load_extension` before you can
460
Loadable extensions are disabled by default. See [#f1]_.
462
.. versionadded:: 3.2
464
.. attribute:: row_factory
466
You can change this attribute to a callable that accepts the cursor and the
467
original row as a tuple and will return the real result row. This way, you can
468
implement more advanced ways of returning results, such as returning an object
469
that can also access columns by name.
473
.. literalinclude:: ../includes/sqlite3/row_factory.py
475
If returning a tuple doesn't suffice and you want name-based access to
476
columns, you should consider setting :attr:`row_factory` to the
477
highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both
478
index-based and case-insensitive name-based access to columns with almost no
479
memory overhead. It will probably be better than your own custom
480
dictionary-based approach or even a db_row based solution.
482
.. XXX what's a db_row-based solution?
485
.. attribute:: text_factory
487
Using this attribute you can control what objects are returned for the ``TEXT``
488
data type. By default, this attribute is set to :class:`str` and the
489
:mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to
490
return bytestrings instead, you can set it to :class:`bytes`.
492
You can also set it to any other callable that accepts a single bytestring
493
parameter and returns the resulting object.
495
See the following example code for illustration:
497
.. literalinclude:: ../includes/sqlite3/text_factory.py
500
.. attribute:: total_changes
502
Returns the total number of database rows that have been modified, inserted, or
503
deleted since the database connection was opened.
508
Returns an iterator to dump the database in an SQL text format. Useful when
509
saving an in-memory database for later restoration. This function provides
510
the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3`
515
# Convert file existing_db.db to SQL dump file dump.sql
518
con = sqlite3.connect('existing_db.db')
519
with open('dump.sql', 'w') as f:
520
for line in con.iterdump():
521
f.write('%s\n' % line)
524
.. _sqlite3-cursor-objects:
531
A :class:`Cursor` instance has the following attributes and methods.
533
.. method:: execute(sql[, parameters])
535
Executes an SQL statement. The SQL statement may be parameterized (i. e.
536
placeholders instead of SQL literals). The :mod:`sqlite3` module supports two
537
kinds of placeholders: question marks (qmark style) and named placeholders
540
Here's an example of both styles:
542
.. literalinclude:: ../includes/sqlite3/execute_1.py
544
:meth:`execute` will only execute a single SQL statement. If you try to execute
545
more than one statement with it, it will raise a :exc:`.Warning`. Use
546
:meth:`executescript` if you want to execute multiple SQL statements with one
550
.. method:: executemany(sql, seq_of_parameters)
552
Executes an SQL command against all parameter sequences or mappings found in
553
the sequence *seq_of_parameters*. The :mod:`sqlite3` module also allows
554
using an :term:`iterator` yielding parameters instead of a sequence.
556
.. literalinclude:: ../includes/sqlite3/executemany_1.py
558
Here's a shorter example using a :term:`generator`:
560
.. literalinclude:: ../includes/sqlite3/executemany_2.py
563
.. method:: executescript(sql_script)
565
This is a nonstandard convenience method for executing multiple SQL statements
566
at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
569
*sql_script* can be an instance of :class:`str`.
573
.. literalinclude:: ../includes/sqlite3/executescript.py
576
.. method:: fetchone()
578
Fetches the next row of a query result set, returning a single sequence,
579
or :const:`None` when no more data is available.
582
.. method:: fetchmany(size=cursor.arraysize)
584
Fetches the next set of rows of a query result, returning a list. An empty
585
list is returned when no more rows are available.
587
The number of rows to fetch per call is specified by the *size* parameter.
588
If it is not given, the cursor's arraysize determines the number of rows
589
to be fetched. The method should try to fetch as many rows as indicated by
590
the size parameter. If this is not possible due to the specified number of
591
rows not being available, fewer rows may be returned.
593
Note there are performance considerations involved with the *size* parameter.
594
For optimal performance, it is usually best to use the arraysize attribute.
595
If the *size* parameter is used, then it is best for it to retain the same
596
value from one :meth:`fetchmany` call to the next.
598
.. method:: fetchall()
600
Fetches all (remaining) rows of a query result, returning a list. Note that
601
the cursor's arraysize attribute can affect the performance of this operation.
602
An empty list is returned when no rows are available.
606
Close the cursor now (rather than whenever ``__del__`` is called).
608
The cursor will be unusable from this point forward; a :exc:`ProgrammingError`
609
exception will be raised if any operation is attempted with the cursor.
611
.. attribute:: rowcount
613
Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this
614
attribute, the database engine's own support for the determination of "rows
615
affected"/"rows selected" is quirky.
617
For :meth:`executemany` statements, the number of modifications are summed up
618
into :attr:`rowcount`.
620
As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in
621
case no ``executeXX()`` has been performed on the cursor or the rowcount of the
622
last operation is not determinable by the interface". This includes ``SELECT``
623
statements because we cannot determine the number of rows a query produced
624
until all rows were fetched.
626
With SQLite versions before 3.6.5, :attr:`rowcount` is set to 0 if
627
you make a ``DELETE FROM table`` without any condition.
629
.. attribute:: lastrowid
631
This read-only attribute provides the rowid of the last modified row. It is
632
only set if you issued an ``INSERT`` statement using the :meth:`execute`
633
method. For operations other than ``INSERT`` or when :meth:`executemany` is
634
called, :attr:`lastrowid` is set to :const:`None`.
636
.. attribute:: description
638
This read-only attribute provides the column names of the last query. To
639
remain compatible with the Python DB API, it returns a 7-tuple for each
640
column where the last six items of each tuple are :const:`None`.
642
It is set for ``SELECT`` statements without any matching rows as well.
644
.. attribute:: connection
646
This read-only attribute provides the SQLite database :class:`Connection`
647
used by the :class:`Cursor` object. A :class:`Cursor` object created by
648
calling :meth:`con.cursor() <Connection.cursor>` will have a
649
:attr:`connection` attribute that refers to *con*::
651
>>> con = sqlite3.connect(":memory:")
652
>>> cur = con.cursor()
653
>>> cur.connection == con
656
.. _sqlite3-row-objects:
663
A :class:`Row` instance serves as a highly optimized
664
:attr:`~Connection.row_factory` for :class:`Connection` objects.
665
It tries to mimic a tuple in most of its features.
667
It supports mapping access by column name and index, iteration,
668
representation, equality testing and :func:`len`.
670
If two :class:`Row` objects have exactly the same columns and their
671
members are equal, they compare equal.
675
This method returns a list of column names. Immediately after a query,
676
it is the first member of each tuple in :attr:`Cursor.description`.
678
.. versionchanged:: 3.5
679
Added support of slicing.
681
Let's assume we initialize a table as in the example given above::
683
conn = sqlite3.connect(":memory:")
685
c.execute('''create table stocks
686
(date text, trans text, symbol text,
687
qty real, price real)''')
688
c.execute("""insert into stocks
689
values ('2006-01-05','BUY','RHAT',100,35.14)""")
693
Now we plug :class:`Row` in::
695
>>> conn.row_factory = sqlite3.Row
696
>>> c = conn.cursor()
697
>>> c.execute('select * from stocks')
698
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
701
<class 'sqlite3.Row'>
703
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
709
['date', 'trans', 'symbol', 'qty', 'price']
722
.. _sqlite3-exceptions:
727
.. exception:: Warning
729
A subclass of :exc:`Exception`.
733
The base class of the other exceptions in this module. It is a subclass
736
.. exception:: DatabaseError
738
Exception raised for errors that are related to the database.
740
.. exception:: IntegrityError
742
Exception raised when the relational integrity of the database is affected,
743
e.g. a foreign key check fails. It is a subclass of :exc:`DatabaseError`.
745
.. exception:: ProgrammingError
747
Exception raised for programming errors, e.g. table not found or already
748
exists, syntax error in the SQL statement, wrong number of parameters
749
specified, etc. It is a subclass of :exc:`DatabaseError`.
754
SQLite and Python types
755
-----------------------
761
SQLite natively supports the following types: ``NULL``, ``INTEGER``,
762
``REAL``, ``TEXT``, ``BLOB``.
764
The following Python types can thus be sent to SQLite without any problem:
766
+-------------------------------+-------------+
767
| Python type | SQLite type |
768
+===============================+=============+
769
| :const:`None` | ``NULL`` |
770
+-------------------------------+-------------+
771
| :class:`int` | ``INTEGER`` |
772
+-------------------------------+-------------+
773
| :class:`float` | ``REAL`` |
774
+-------------------------------+-------------+
775
| :class:`str` | ``TEXT`` |
776
+-------------------------------+-------------+
777
| :class:`bytes` | ``BLOB`` |
778
+-------------------------------+-------------+
781
This is how SQLite types are converted to Python types by default:
783
+-------------+----------------------------------------------+
784
| SQLite type | Python type |
785
+=============+==============================================+
786
| ``NULL`` | :const:`None` |
787
+-------------+----------------------------------------------+
788
| ``INTEGER`` | :class:`int` |
789
+-------------+----------------------------------------------+
790
| ``REAL`` | :class:`float` |
791
+-------------+----------------------------------------------+
792
| ``TEXT`` | depends on :attr:`~Connection.text_factory`, |
793
| | :class:`str` by default |
794
+-------------+----------------------------------------------+
795
| ``BLOB`` | :class:`bytes` |
796
+-------------+----------------------------------------------+
798
The type system of the :mod:`sqlite3` module is extensible in two ways: you can
799
store additional Python types in a SQLite database via object adaptation, and
800
you can let the :mod:`sqlite3` module convert SQLite types to different Python
801
types via converters.
804
Using adapters to store additional Python types in SQLite databases
805
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
807
As described before, SQLite supports only a limited set of types natively. To
808
use other Python types with SQLite, you must **adapt** them to one of the
809
sqlite3 module's supported types for SQLite: one of NoneType, int, float,
812
There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
813
type to one of the supported ones.
816
Letting your object adapt itself
817
""""""""""""""""""""""""""""""""
819
This is a good approach if you write the class yourself. Let's suppose you have
823
def __init__(self, x, y):
824
self.x, self.y = x, y
826
Now you want to store the point in a single SQLite column. First you'll have to
827
choose one of the supported types first to be used for representing the point.
828
Let's just use str and separate the coordinates using a semicolon. Then you need
829
to give your class a method ``__conform__(self, protocol)`` which must return
830
the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
832
.. literalinclude:: ../includes/sqlite3/adapter_point_1.py
835
Registering an adapter callable
836
"""""""""""""""""""""""""""""""
838
The other possibility is to create a function that converts the type to the
839
string representation and register the function with :meth:`register_adapter`.
841
.. literalinclude:: ../includes/sqlite3/adapter_point_2.py
843
The :mod:`sqlite3` module has two default adapters for Python's built-in
844
:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose
845
we want to store :class:`datetime.datetime` objects not in ISO representation,
846
but as a Unix timestamp.
848
.. literalinclude:: ../includes/sqlite3/adapter_datetime.py
851
Converting SQLite values to custom Python types
852
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
854
Writing an adapter lets you send custom Python types to SQLite. But to make it
855
really useful we need to make the Python to SQLite to Python roundtrip work.
859
Let's go back to the :class:`Point` class. We stored the x and y coordinates
860
separated via semicolons as strings in SQLite.
862
First, we'll define a converter function that accepts the string as a parameter
863
and constructs a :class:`Point` object from it.
867
Converter functions **always** get called with a :class:`bytes` object, no
868
matter under which data type you sent the value to SQLite.
872
def convert_point(s):
873
x, y = map(float, s.split(b";"))
876
Now you need to make the :mod:`sqlite3` module know that what you select from
877
the database is actually a point. There are two ways of doing this:
879
* Implicitly via the declared type
881
* Explicitly via the column name
883
Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
884
for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
886
The following example illustrates both approaches.
888
.. literalinclude:: ../includes/sqlite3/converter_point.py
891
Default adapters and converters
892
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
894
There are default adapters for the date and datetime types in the datetime
895
module. They will be sent as ISO dates/ISO timestamps to SQLite.
897
The default converters are registered under the name "date" for
898
:class:`datetime.date` and under the name "timestamp" for
899
:class:`datetime.datetime`.
901
This way, you can use date/timestamps from Python without any additional
902
fiddling in most cases. The format of the adapters is also compatible with the
903
experimental SQLite date/time functions.
905
The following example demonstrates this.
907
.. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py
909
If a timestamp stored in SQLite has a fractional part longer than 6
910
numbers, its value will be truncated to microsecond precision by the
914
.. _sqlite3-controlling-transactions:
916
Controlling Transactions
917
------------------------
919
By default, the :mod:`sqlite3` module opens transactions implicitly before a
920
Data Modification Language (DML) statement (i.e.
921
``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions
922
implicitly before a non-DML, non-query statement (i. e.
923
anything other than ``SELECT`` or the aforementioned).
925
So if you are within a transaction and issue a command like ``CREATE TABLE
926
...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly
927
before executing that command. There are two reasons for doing that. The first
928
is that some of these commands don't work within transactions. The other reason
929
is that sqlite3 needs to keep track of the transaction state (if a transaction
930
is active or not). The current transaction state is exposed through the
931
:attr:`Connection.in_transaction` attribute of the connection object.
933
You can control which kind of ``BEGIN`` statements sqlite3 implicitly executes
934
(or none at all) via the *isolation_level* parameter to the :func:`connect`
935
call, or via the :attr:`isolation_level` property of connections.
937
If you want **autocommit mode**, then set :attr:`isolation_level` to ``None``.
939
Otherwise leave it at its default, which will result in a plain "BEGIN"
940
statement, or set it to one of SQLite's supported isolation levels: "DEFERRED",
941
"IMMEDIATE" or "EXCLUSIVE".
945
Using :mod:`sqlite3` efficiently
946
--------------------------------
949
Using shortcut methods
950
^^^^^^^^^^^^^^^^^^^^^^
952
Using the nonstandard :meth:`execute`, :meth:`executemany` and
953
:meth:`executescript` methods of the :class:`Connection` object, your code can
954
be written more concisely because you don't have to create the (often
955
superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
956
objects are created implicitly and these shortcut methods return the cursor
957
objects. This way, you can execute a ``SELECT`` statement and iterate over it
958
directly using only a single call on the :class:`Connection` object.
960
.. literalinclude:: ../includes/sqlite3/shortcut_methods.py
963
Accessing columns by name instead of by index
964
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
966
One useful feature of the :mod:`sqlite3` module is the built-in
967
:class:`sqlite3.Row` class designed to be used as a row factory.
969
Rows wrapped with this class can be accessed both by index (like tuples) and
970
case-insensitively by name:
972
.. literalinclude:: ../includes/sqlite3/rowclass.py
975
Using the connection as a context manager
976
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
978
Connection objects can be used as context managers
979
that automatically commit or rollback transactions. In the event of an
980
exception, the transaction is rolled back; otherwise, the transaction is
983
.. literalinclude:: ../includes/sqlite3/ctx_manager.py
992
Older SQLite versions had issues with sharing connections between threads.
993
That's why the Python module disallows sharing connections and cursors between
994
threads. If you still try to do so, you will get an exception at runtime.
996
The only exception is calling the :meth:`~Connection.interrupt` method, which
997
only makes sense to call from a different thread.
999
.. rubric:: Footnotes
1001
.. [#f1] The sqlite3 module is not built with loadable extension support by
1002
default, because some platforms (notably Mac OS X) have SQLite
1003
libraries which are compiled without this feature. To get loadable
1004
extension support, you must pass --enable-loadable-sqlite-extensions to
added
removed
python-3.5.2-docs-html/_sources/library/sqlite3.txt
