1
:mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
2
============================================================
5
:synopsis: A DB-API 2.0 implementation using SQLite 3.x.
6
.. sectionauthor:: Gerhard Häring <gh@ghaering.de>
9
SQLite is a C library that provides a lightweight disk-based database that
10
doesn't require a separate server process and allows accessing the database
11
using a nonstandard variant of the SQL query language. Some applications can use
12
SQLite for internal data storage. It's also possible to prototype an
13
application using SQLite and then port the code to a larger database such as
16
pysqlite was written by Gerhard Häring and provides a SQL interface compliant
17
with the DB-API 2.0 specification described by :pep:`249`.
19
To use the module, you must first create a :class:`Connection` object that
20
represents the database. Here the data will be stored in the
21
:file:`/tmp/example` file::
23
conn = sqlite3.connect('/tmp/example')
25
You can also supply the special name ``:memory:`` to create a database in RAM.
27
Once you have a :class:`Connection`, you can create a :class:`Cursor` object
28
and call its :meth:`~Cursor.execute` method to perform SQL commands::
33
c.execute('''create table stocks
34
(date text, trans text, symbol text,
35
qty real, price real)''')
37
# Insert a row of data
38
c.execute("""insert into stocks
39
values ('2006-01-05','BUY','RHAT',100,35.14)""")
41
# Save (commit) the changes
44
# We can also close the cursor if we are done with it
47
Usually your SQL operations will need to use values from Python variables. You
48
shouldn't assemble your query using Python's string operations because doing so
49
is insecure; it makes your program vulnerable to an SQL injection attack.
51
Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder
52
wherever you want to use a value, and then provide a tuple of values as the
53
second argument to the cursor's :meth:`~Cursor.execute` method. (Other database modules
54
may use a different placeholder, such as ``%s`` or ``:1``.) For example::
56
# Never do this -- insecure!
58
c.execute("... where symbol = '%s'" % symbol)
62
c.execute('select * from stocks where symbol=?', t)
65
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
66
('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
67
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
69
c.execute('insert into stocks values (?,?,?,?,?)', t)
71
To retrieve data after executing a SELECT statement, you can either treat the
72
cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
73
retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the
76
This example uses the iterator form::
79
>>> c.execute('select * from stocks order by price')
83
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
84
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
85
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
86
(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
92
http://www.pysqlite.org
93
The pysqlite web page.
96
The SQLite web page; the documentation describes the syntax and the available
97
data types for the supported SQL dialect.
99
:pep:`249` - Database API Specification 2.0
100
PEP written by Marc-André Lemburg.
103
.. _sqlite3-module-contents:
105
Module functions and constants
106
------------------------------
109
.. data:: PARSE_DECLTYPES
111
This constant is meant to be used with the *detect_types* parameter of the
112
:func:`connect` function.
114
Setting it makes the :mod:`sqlite3` module parse the declared type for each
115
column it returns. It will parse out the first word of the declared type,
116
i. e. for "integer primary key", it will parse out "integer", or for
117
"number(10)" it will parse out "number". Then for that column, it will look
118
into the converters dictionary and use the converter function registered for
122
.. data:: PARSE_COLNAMES
124
This constant is meant to be used with the *detect_types* parameter of the
125
:func:`connect` function.
127
Setting this makes the SQLite interface parse the column name for each column it
128
returns. It will look for a string formed [mytype] in there, and then decide
129
that 'mytype' is the type of the column. It will try to find an entry of
130
'mytype' in the converters dictionary and then use the converter function found
131
there to return the value. The column name found in :attr:`Cursor.description`
132
is only the first word of the column name, i. e. if you use something like
133
``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the
134
first blank for the column name: the column name would simply be "x".
137
.. function:: connect(database[, timeout, isolation_level, detect_types, factory])
139
Opens a connection to the SQLite database file *database*. You can use
140
``":memory:"`` to open a database connection to a database that resides in RAM
143
When a database is accessed by multiple connections, and one of the processes
144
modifies the database, the SQLite database is locked until that transaction is
145
committed. The *timeout* parameter specifies how long the connection should wait
146
for the lock to go away until raising an exception. The default for the timeout
147
parameter is 5.0 (five seconds).
149
For the *isolation_level* parameter, please see the
150
:attr:`Connection.isolation_level` property of :class:`Connection` objects.
152
SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If
153
you want to use other types you must add support for them yourself. The
154
*detect_types* parameter and the using custom **converters** registered with the
155
module-level :func:`register_converter` function allow you to easily do that.
157
*detect_types* defaults to 0 (i. e. off, no type detection), you can set it to
158
any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
161
By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the
162
connect call. You can, however, subclass the :class:`Connection` class and make
163
:func:`connect` use your class instead by providing your class for the *factory*
166
Consult the section :ref:`sqlite3-types` of this manual for details.
168
The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing
169
overhead. If you want to explicitly set the number of statements that are cached
170
for the connection, you can set the *cached_statements* parameter. The currently
171
implemented default is to cache 100 statements.
174
.. function:: register_converter(typename, callable)
176
Registers a callable to convert a bytestring from the database into a custom
177
Python type. The callable will be invoked for all database values that are of
178
the type *typename*. Confer the parameter *detect_types* of the :func:`connect`
179
function for how the type detection works. Note that the case of *typename* and
180
the name of the type in your query must match!
183
.. function:: register_adapter(type, callable)
185
Registers a callable to convert the custom Python type *type* into one of
186
SQLite's supported types. The callable *callable* accepts as single parameter
187
the Python value, and must return a value of the following types: int,
188
float, str, bytes (UTF-8 encoded) or buffer.
191
.. function:: complete_statement(sql)
193
Returns :const:`True` if the string *sql* contains one or more complete SQL
194
statements terminated by semicolons. It does not verify that the SQL is
195
syntactically correct, only that there are no unclosed string literals and the
196
statement is terminated by a semicolon.
198
This can be used to build a shell for SQLite, as in the following example:
201
.. literalinclude:: ../includes/sqlite3/complete_statement.py
204
.. function:: enable_callback_tracebacks(flag)
206
By default you will not get any tracebacks in user-defined functions,
207
aggregates, converters, authorizer callbacks etc. If you want to debug them, you
208
can call this function with *flag* as True. Afterwards, you will get tracebacks
209
from callbacks on ``sys.stderr``. Use :const:`False` to disable the feature
213
.. _sqlite3-connection-objects:
218
.. class:: Connection
220
A SQLite database connection has the following attributes and methods:
222
.. attribute:: Connection.isolation_level
224
Get or set the current isolation level. :const:`None` for autocommit mode or
225
one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
226
:ref:`sqlite3-controlling-transactions` for a more detailed explanation.
229
.. method:: Connection.cursor([cursorClass])
231
The cursor method accepts a single optional parameter *cursorClass*. If
232
supplied, this must be a custom cursor class that extends
233
:class:`sqlite3.Cursor`.
236
.. method:: Connection.commit()
238
This method commits the current transaction. If you don't call this method,
239
anything you did since the last call to ``commit()`` is not visible from from
240
other database connections. If you wonder why you don't see the data you've
241
written to the database, please check you didn't forget to call this method.
243
.. method:: Connection.rollback()
245
This method rolls back any changes to the database since the last call to
248
.. method:: Connection.close()
250
This closes the database connection. Note that this does not automatically
251
call :meth:`commit`. If you just close your database connection without
252
calling :meth:`commit` first, your changes will be lost!
254
.. method:: Connection.execute(sql, [parameters])
256
This is a nonstandard shortcut that creates an intermediate cursor object by
257
calling the cursor method, then calls the cursor's :meth:`execute` method with
258
the parameters given.
261
.. method:: Connection.executemany(sql, [parameters])
263
This is a nonstandard shortcut that creates an intermediate cursor object by
264
calling the cursor method, then calls the cursor's :meth:`executemany` method
265
with the parameters given.
268
.. method:: Connection.executescript(sql_script)
270
This is a nonstandard shortcut that creates an intermediate cursor object by
271
calling the cursor method, then calls the cursor's :meth:`executescript` method
272
with the parameters given.
275
.. method:: Connection.create_function(name, num_params, func)
277
Creates a user-defined function that you can later use from within SQL
278
statements under the function name *name*. *num_params* is the number of
279
parameters the function accepts, and *func* is a Python callable that is called
282
The function can return any of the types supported by SQLite: bytes, str, int,
283
float, buffer and None.
287
.. literalinclude:: ../includes/sqlite3/md5func.py
290
.. method:: Connection.create_aggregate(name, num_params, aggregate_class)
292
Creates a user-defined aggregate function.
294
The aggregate class must implement a ``step`` method, which accepts the number
295
of parameters *num_params*, and a ``finalize`` method which will return the
296
final result of the aggregate.
298
The ``finalize`` method can return any of the types supported by SQLite:
299
bytes, str, int, float, buffer and None.
303
.. literalinclude:: ../includes/sqlite3/mysumaggr.py
306
.. method:: Connection.create_collation(name, callable)
308
Creates a collation with the specified *name* and *callable*. The callable will
309
be passed two string arguments. It should return -1 if the first is ordered
310
lower than the second, 0 if they are ordered equal and 1 if the first is ordered
311
higher than the second. Note that this controls sorting (ORDER BY in SQL) so
312
your comparisons don't affect other SQL operations.
314
Note that the callable will get its parameters as Python bytestrings, which will
315
normally be encoded in UTF-8.
317
The following example shows a custom collation that sorts "the wrong way":
319
.. literalinclude:: ../includes/sqlite3/collation_reverse.py
321
To remove a collation, call ``create_collation`` with None as callable::
323
con.create_collation("reverse", None)
326
.. method:: Connection.interrupt()
328
You can call this method from a different thread to abort any queries that might
329
be executing on the connection. The query will then abort and the caller will
333
.. method:: Connection.set_authorizer(authorizer_callback)
335
This routine registers a callback. The callback is invoked for each attempt to
336
access a column of a table in the database. The callback should return
337
:const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL
338
statement should be aborted with an error and :const:`SQLITE_IGNORE` if the
339
column should be treated as a NULL value. These constants are available in the
340
:mod:`sqlite3` module.
342
The first argument to the callback signifies what kind of operation is to be
343
authorized. The second and third argument will be arguments or :const:`None`
344
depending on the first argument. The 4th argument is the name of the database
345
("main", "temp", etc.) if applicable. The 5th argument is the name of the
346
inner-most trigger or view that is responsible for the access attempt or
347
:const:`None` if this access attempt is directly from input SQL code.
349
Please consult the SQLite documentation about the possible values for the first
350
argument and the meaning of the second and third argument depending on the first
351
one. All necessary constants are available in the :mod:`sqlite3` module.
354
.. method:: Connection.set_progress_handler(handler, n)
356
This routine registers a callback. The callback is invoked for every *n*
357
instructions of the SQLite virtual machine. This is useful if you want to
358
get called from SQLite during long-running operations, for example to update
361
If you want to clear any previously installed progress handler, call the
362
method with :const:`None` for *handler*.
365
.. attribute:: Connection.row_factory
367
You can change this attribute to a callable that accepts the cursor and the
368
original row as a tuple and will return the real result row. This way, you can
369
implement more advanced ways of returning results, such as returning an object
370
that can also access columns by name.
374
.. literalinclude:: ../includes/sqlite3/row_factory.py
376
If returning a tuple doesn't suffice and you want name-based access to
377
columns, you should consider setting :attr:`row_factory` to the
378
highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both
379
index-based and case-insensitive name-based access to columns with almost no
380
memory overhead. It will probably be better than your own custom
381
dictionary-based approach or even a db_row based solution.
383
.. XXX what's a db_row-based solution?
386
.. attribute:: Connection.text_factory
388
Using this attribute you can control what objects are returned for the ``TEXT``
389
data type. By default, this attribute is set to :class:`str` and the
390
:mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to
391
return bytestrings instead, you can set it to :class:`bytes`.
393
For efficiency reasons, there's also a way to return :class:`str` objects
394
only for non-ASCII data, and :class:`bytes` otherwise. To activate it, set
395
this attribute to :const:`sqlite3.OptimizedUnicode`.
397
You can also set it to any other callable that accepts a single bytestring
398
parameter and returns the resulting object.
400
See the following example code for illustration:
402
.. literalinclude:: ../includes/sqlite3/text_factory.py
405
.. attribute:: Connection.total_changes
407
Returns the total number of database rows that have been modified, inserted, or
408
deleted since the database connection was opened.
411
.. attribute:: Connection.iterdump
413
Returns an iterator to dump the database in an SQL text format. Useful when
414
saving an in-memory database for later restoration. This function provides
415
the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3`
420
# Convert file existing_db.db to SQL dump file dump.sql
423
con = sqlite3.connect('existing_db.db')
424
with open('dump.sql', 'w') as f:
425
for line in con.iterdump():
426
f.write('%s\n' % line)
429
.. _sqlite3-cursor-objects:
436
A SQLite database cursor has the following attributes and methods:
438
.. method:: Cursor.execute(sql, [parameters])
440
Executes an SQL statement. The SQL statement may be parametrized (i. e.
441
placeholders instead of SQL literals). The :mod:`sqlite3` module supports two
442
kinds of placeholders: question marks (qmark style) and named placeholders
445
This example shows how to use parameters with qmark style:
447
.. literalinclude:: ../includes/sqlite3/execute_1.py
449
This example shows how to use the named style:
451
.. literalinclude:: ../includes/sqlite3/execute_2.py
453
:meth:`execute` will only execute a single SQL statement. If you try to execute
454
more than one statement with it, it will raise a Warning. Use
455
:meth:`executescript` if you want to execute multiple SQL statements with one
459
.. method:: Cursor.executemany(sql, seq_of_parameters)
461
Executes an SQL command against all parameter sequences or mappings found in
462
the sequence *sql*. The :mod:`sqlite3` module also allows using an
463
:term:`iterator` yielding parameters instead of a sequence.
465
.. literalinclude:: ../includes/sqlite3/executemany_1.py
467
Here's a shorter example using a :term:`generator`:
469
.. literalinclude:: ../includes/sqlite3/executemany_2.py
472
.. method:: Cursor.executescript(sql_script)
474
This is a nonstandard convenience method for executing multiple SQL statements
475
at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
478
*sql_script* can be an instance of :class:`str` or :class:`bytes`.
482
.. literalinclude:: ../includes/sqlite3/executescript.py
485
.. method:: Cursor.fetchone()
487
Fetches the next row of a query result set, returning a single sequence,
488
or :const:`None` when no more data is available.
491
.. method:: Cursor.fetchmany([size=cursor.arraysize])
493
Fetches the next set of rows of a query result, returning a list. An empty
494
list is returned when no more rows are available.
496
The number of rows to fetch per call is specified by the *size* parameter.
497
If it is not given, the cursor's arraysize determines the number of rows
498
to be fetched. The method should try to fetch as many rows as indicated by
499
the size parameter. If this is not possible due to the specified number of
500
rows not being available, fewer rows may be returned.
502
Note there are performance considerations involved with the *size* parameter.
503
For optimal performance, it is usually best to use the arraysize attribute.
504
If the *size* parameter is used, then it is best for it to retain the same
505
value from one :meth:`fetchmany` call to the next.
507
.. method:: Cursor.fetchall()
509
Fetches all (remaining) rows of a query result, returning a list. Note that
510
the cursor's arraysize attribute can affect the performance of this operation.
511
An empty list is returned when no rows are available.
514
.. attribute:: Cursor.rowcount
516
Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this
517
attribute, the database engine's own support for the determination of "rows
518
affected"/"rows selected" is quirky.
520
For ``DELETE`` statements, SQLite reports :attr:`rowcount` as 0 if you make a
521
``DELETE FROM table`` without any condition.
523
For :meth:`executemany` statements, the number of modifications are summed up
524
into :attr:`rowcount`.
526
As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in
527
case no ``executeXX()`` has been performed on the cursor or the rowcount of the
528
last operation is not determinable by the interface".
530
This includes ``SELECT`` statements because we cannot determine the number of
531
rows a query produced until all rows were fetched.
533
.. attribute:: Cursor.lastrowid
535
This read-only attribute provides the rowid of the last modified row. It is
536
only set if you issued a ``INSERT`` statement using the :meth:`execute`
537
method. For operations other than ``INSERT`` or when :meth:`executemany` is
538
called, :attr:`lastrowid` is set to :const:`None`.
540
.. attribute:: Cursor.description
542
This read-only attribute provides the column names of the last query. To
543
remain compatible with the Python DB API, it returns a 7-tuple for each
544
column where the last six items of each tuple are :const:`None`.
546
It is set for ``SELECT`` statements without any matching rows as well.
548
.. _sqlite3-row-objects:
555
A :class:`Row` instance serves as a highly optimized
556
:attr:`~Connection.row_factory` for :class:`Connection` objects.
557
It tries to mimic a tuple in most of its features.
559
It supports mapping access by column name and index, iteration,
560
representation, equality testing and :func:`len`.
562
If two :class:`Row` objects have exactly the same columns and their
563
members are equal, they compare equal.
567
This method returns a tuple of column names. Immediately after a query,
568
it is the first member of each tuple in :attr:`Cursor.description`.
570
Let's assume we initialize a table as in the example given above::
572
conn = sqlite3.connect(":memory:")
574
c.execute('''create table stocks
575
(date text, trans text, symbol text,
576
qty real, price real)''')
577
c.execute("""insert into stocks
578
values ('2006-01-05','BUY','RHAT',100,35.14)""")
582
Now we plug :class:`Row` in::
584
>>> conn.row_factory = sqlite3.Row
585
>>> c = conn.cursor()
586
>>> c.execute('select * from stocks')
587
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
592
(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001)
598
['date', 'trans', 'symbol', 'qty', 'price']
601
>>> for member in r: print member
612
SQLite and Python types
613
-----------------------
619
SQLite natively supports the following types: ``NULL``, ``INTEGER``,
620
``REAL``, ``TEXT``, ``BLOB``.
622
The following Python types can thus be sent to SQLite without any problem:
624
+-------------------------------+-------------+
625
| Python type | SQLite type |
626
+===============================+=============+
627
| :const:`None` | ``NULL`` |
628
+-------------------------------+-------------+
629
| :class:`int` | ``INTEGER`` |
630
+-------------------------------+-------------+
631
| :class:`float` | ``REAL`` |
632
+-------------------------------+-------------+
633
| :class:`bytes` (UTF8-encoded) | ``TEXT`` |
634
+-------------------------------+-------------+
635
| :class:`str` | ``TEXT`` |
636
+-------------------------------+-------------+
637
| :class:`buffer` | ``BLOB`` |
638
+-------------------------------+-------------+
641
This is how SQLite types are converted to Python types by default:
643
+-------------+---------------------------------------------+
644
| SQLite type | Python type |
645
+=============+=============================================+
646
| ``NULL`` | :const:`None` |
647
+-------------+---------------------------------------------+
648
| ``INTEGER`` | :class`int` |
649
+-------------+---------------------------------------------+
650
| ``REAL`` | :class:`float` |
651
+-------------+---------------------------------------------+
652
| ``TEXT`` | depends on text_factory, str by default |
653
+-------------+---------------------------------------------+
654
| ``BLOB`` | buffer |
655
+-------------+---------------------------------------------+
657
The type system of the :mod:`sqlite3` module is extensible in two ways: you can
658
store additional Python types in a SQLite database via object adaptation, and
659
you can let the :mod:`sqlite3` module convert SQLite types to different Python
660
types via converters.
663
Using adapters to store additional Python types in SQLite databases
664
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
666
As described before, SQLite supports only a limited set of types natively. To
667
use other Python types with SQLite, you must **adapt** them to one of the
668
sqlite3 module's supported types for SQLite: one of NoneType, int, float,
671
The :mod:`sqlite3` module uses Python object adaptation, as described in
672
:pep:`246` for this. The protocol to use is :class:`PrepareProtocol`.
674
There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
675
type to one of the supported ones.
678
Letting your object adapt itself
679
""""""""""""""""""""""""""""""""
681
This is a good approach if you write the class yourself. Let's suppose you have
685
def __init__(self, x, y):
686
self.x, self.y = x, y
688
Now you want to store the point in a single SQLite column. First you'll have to
689
choose one of the supported types first to be used for representing the point.
690
Let's just use str and separate the coordinates using a semicolon. Then you need
691
to give your class a method ``__conform__(self, protocol)`` which must return
692
the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
694
.. literalinclude:: ../includes/sqlite3/adapter_point_1.py
697
Registering an adapter callable
698
"""""""""""""""""""""""""""""""
700
The other possibility is to create a function that converts the type to the
701
string representation and register the function with :meth:`register_adapter`.
703
.. literalinclude:: ../includes/sqlite3/adapter_point_2.py
705
The :mod:`sqlite3` module has two default adapters for Python's built-in
706
:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose
707
we want to store :class:`datetime.datetime` objects not in ISO representation,
708
but as a Unix timestamp.
710
.. literalinclude:: ../includes/sqlite3/adapter_datetime.py
713
Converting SQLite values to custom Python types
714
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
716
Writing an adapter lets you send custom Python types to SQLite. But to make it
717
really useful we need to make the Python to SQLite to Python roundtrip work.
721
Let's go back to the :class:`Point` class. We stored the x and y coordinates
722
separated via semicolons as strings in SQLite.
724
First, we'll define a converter function that accepts the string as a parameter
725
and constructs a :class:`Point` object from it.
729
Converter functions **always** get called with a string, no matter under which
730
data type you sent the value to SQLite.
734
def convert_point(s):
735
x, y = map(float, s.split(";"))
738
Now you need to make the :mod:`sqlite3` module know that what you select from
739
the database is actually a point. There are two ways of doing this:
741
* Implicitly via the declared type
743
* Explicitly via the column name
745
Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
746
for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
748
The following example illustrates both approaches.
750
.. literalinclude:: ../includes/sqlite3/converter_point.py
753
Default adapters and converters
754
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
756
There are default adapters for the date and datetime types in the datetime
757
module. They will be sent as ISO dates/ISO timestamps to SQLite.
759
The default converters are registered under the name "date" for
760
:class:`datetime.date` and under the name "timestamp" for
761
:class:`datetime.datetime`.
763
This way, you can use date/timestamps from Python without any additional
764
fiddling in most cases. The format of the adapters is also compatible with the
765
experimental SQLite date/time functions.
767
The following example demonstrates this.
769
.. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py
772
.. _sqlite3-controlling-transactions:
774
Controlling Transactions
775
------------------------
777
By default, the :mod:`sqlite3` module opens transactions implicitly before a
778
Data Modification Language (DML) statement (i.e.
779
``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions
780
implicitly before a non-DML, non-query statement (i. e.
781
anything other than ``SELECT`` or the aforementioned).
783
So if you are within a transaction and issue a command like ``CREATE TABLE
784
...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly
785
before executing that command. There are two reasons for doing that. The first
786
is that some of these commands don't work within transactions. The other reason
787
is that pysqlite needs to keep track of the transaction state (if a transaction
790
You can control which kind of ``BEGIN`` statements pysqlite implicitly executes
791
(or none at all) via the *isolation_level* parameter to the :func:`connect`
792
call, or via the :attr:`isolation_level` property of connections.
794
If you want **autocommit mode**, then set :attr:`isolation_level` to None.
796
Otherwise leave it at its default, which will result in a plain "BEGIN"
797
statement, or set it to one of SQLite's supported isolation levels: "DEFERRED",
798
"IMMEDIATE" or "EXCLUSIVE".
802
Using pysqlite efficiently
803
--------------------------
806
Using shortcut methods
807
^^^^^^^^^^^^^^^^^^^^^^
809
Using the nonstandard :meth:`execute`, :meth:`executemany` and
810
:meth:`executescript` methods of the :class:`Connection` object, your code can
811
be written more concisely because you don't have to create the (often
812
superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
813
objects are created implicitly and these shortcut methods return the cursor
814
objects. This way, you can execute a ``SELECT`` statement and iterate over it
815
directly using only a single call on the :class:`Connection` object.
817
.. literalinclude:: ../includes/sqlite3/shortcut_methods.py
820
Accessing columns by name instead of by index
821
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
823
One useful feature of the :mod:`sqlite3` module is the builtin
824
:class:`sqlite3.Row` class designed to be used as a row factory.
826
Rows wrapped with this class can be accessed both by index (like tuples) and
827
case-insensitively by name:
829
.. literalinclude:: ../includes/sqlite3/rowclass.py
832
Using the connection as a context manager
833
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
835
Connection objects can be used as context managers
836
that automatically commit or rollback transactions. In the event of an
837
exception, the transaction is rolled back; otherwise, the transaction is
840
.. literalinclude:: ../includes/sqlite3/ctx_manager.py
added
removed
Doc/library/sqlite3.rst
