Imported Upstream version 2.6.3
[joel/debian/python-pysqlite2.git] / doc / sphinx / sqlite3.rst
1 :mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
2 ============================================================
3
4 .. module:: sqlite3
5    :synopsis: A DB-API 2.0 implementation using SQLite 3.x.
6 .. sectionauthor:: Gerhard Häring <gh@ghaering.de>
7
8
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
14 PostgreSQL or Oracle.
15
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`.
18
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::
22
23    conn = sqlite3.connect('/tmp/example')
24
25 You can also supply the special name ``:memory:`` to create a database in RAM.
26
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::
29
30    c = conn.cursor()
31
32    # Create table
33    c.execute('''create table stocks
34    (date text, trans text, symbol text,
35     qty real, price real)''')
36
37    # Insert a row of data
38    c.execute("""insert into stocks
39              values ('2006-01-05','BUY','RHAT',100,35.14)""")
40
41    # Save (commit) the changes
42    conn.commit()
43
44    # We can also close the cursor if we are done with it
45    c.close()
46
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.
50
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
54 modules may use a different placeholder, such as ``%s`` or ``:1``.) For
55 example::
56
57    # Never do this -- insecure!
58    symbol = 'IBM'
59    c.execute("... where symbol = '%s'" % symbol)
60
61    # Do this instead
62    t = (symbol,)
63    c.execute('select * from stocks where symbol=?', t)
64
65    # Larger example
66    for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
67              ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
68              ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
69             ]:
70        c.execute('insert into stocks values (?,?,?,?,?)', t)
71
72 To retrieve data after executing a SELECT statement, you can either treat the
73 cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
74 retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the
75 matching rows.
76
77 This example uses the iterator form::
78
79    >>> c = conn.cursor()
80    >>> c.execute('select * from stocks order by price')
81    >>> for row in c:
82    ...    print row
83    ...
84    (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
85    (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
86    (u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
87    (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
88    >>>
89
90
91 .. seealso::
92
93    http://code.google.com/p/pysqlite/
94       The pysqlite web page -- sqlite3 is developed externally under the name
95       "pysqlite".
96
97    http://www.sqlite.org
98       The SQLite web page; the documentation describes the syntax and the
99       available data types for the supported SQL dialect.
100
101    :pep:`249` - Database API Specification 2.0
102       PEP written by Marc-André Lemburg.
103
104
105 .. _sqlite3-module-contents:
106
107 Module functions and constants
108 ------------------------------
109
110
111 .. data:: PARSE_DECLTYPES
112
113    This constant is meant to be used with the *detect_types* parameter of the
114    :func:`connect` function.
115
116    Setting it makes the :mod:`sqlite3` module parse the declared type for each
117    column it returns.  It will parse out the first word of the declared type,
118    i. e.  for "integer primary key", it will parse out "integer", or for
119    "number(10)" it will parse out "number". Then for that column, it will look
120    into the converters dictionary and use the converter function registered for
121    that type there.
122
123
124 .. data:: PARSE_COLNAMES
125
126    This constant is meant to be used with the *detect_types* parameter of the
127    :func:`connect` function.
128
129    Setting this makes the SQLite interface parse the column name for each column it
130    returns.  It will look for a string formed [mytype] in there, and then decide
131    that 'mytype' is the type of the column. It will try to find an entry of
132    'mytype' in the converters dictionary and then use the converter function found
133    there to return the value. The column name found in :attr:`Cursor.description`
134    is only the first word of the column name, i.  e. if you use something like
135    ``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the
136    first blank for the column name: the column name would simply be "x".
137
138
139 .. function:: connect(database[, timeout, isolation_level, detect_types, factory])
140
141    Opens a connection to the SQLite database file *database*. You can use
142    ``":memory:"`` to open a database connection to a database that resides in RAM
143    instead of on disk.
144
145    When a database is accessed by multiple connections, and one of the processes
146    modifies the database, the SQLite database is locked until that transaction is
147    committed. The *timeout* parameter specifies how long the connection should wait
148    for the lock to go away until raising an exception. The default for the timeout
149    parameter is 5.0 (five seconds).
150
151    For the *isolation_level* parameter, please see the
152    :attr:`Connection.isolation_level` property of :class:`Connection` objects.
153
154    SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If
155    you want to use other types you must add support for them yourself. The
156    *detect_types* parameter and the using custom **converters** registered with the
157    module-level :func:`register_converter` function allow you to easily do that.
158
159    *detect_types* defaults to 0 (i. e. off, no type detection), you can set it to
160    any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
161    type detection on.
162
163    By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the
164    connect call.  You can, however, subclass the :class:`Connection` class and make
165    :func:`connect` use your class instead by providing your class for the *factory*
166    parameter.
167
168    Consult the section :ref:`sqlite3-types` of this manual for details.
169
170    The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing
171    overhead. If you want to explicitly set the number of statements that are cached
172    for the connection, you can set the *cached_statements* parameter. The currently
173    implemented default is to cache 100 statements.
174
175
176 .. function:: register_converter(typename, callable)
177
178    Registers a callable to convert a bytestring from the database into a custom
179    Python type. The callable will be invoked for all database values that are of
180    the type *typename*. Confer the parameter *detect_types* of the :func:`connect`
181    function for how the type detection works. Note that the case of *typename* and
182    the name of the type in your query must match!
183
184
185 .. function:: register_adapter(type, callable)
186
187    Registers a callable to convert the custom Python type *type* into one of
188    SQLite's supported types. The callable *callable* accepts as single parameter
189    the Python value, and must return a value of the following types: int, long,
190    float, str (UTF-8 encoded), unicode or buffer.
191
192
193 .. function:: complete_statement(sql)
194
195    Returns :const:`True` if the string *sql* contains one or more complete SQL
196    statements terminated by semicolons. It does not verify that the SQL is
197    syntactically correct, only that there are no unclosed string literals and the
198    statement is terminated by a semicolon.
199
200    This can be used to build a shell for SQLite, as in the following example:
201
202
203    .. literalinclude:: ../includes/sqlite3/complete_statement.py
204
205
206 .. function:: enable_callback_tracebacks(flag)
207
208    By default you will not get any tracebacks in user-defined functions,
209    aggregates, converters, authorizer callbacks etc. If you want to debug them, you
210    can call this function with *flag* as True. Afterwards, you will get tracebacks
211    from callbacks on ``sys.stderr``. Use :const:`False` to disable the feature
212    again.
213
214
215 .. _sqlite3-connection-objects:
216
217 Connection Objects
218 ------------------
219
220 .. class:: Connection
221
222    A SQLite database connection has the following attributes and methods:
223
224 .. attribute:: Connection.isolation_level
225
226    Get or set the current isolation level. :const:`None` for autocommit mode or
227    one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
228    :ref:`sqlite3-controlling-transactions` for a more detailed explanation.
229
230
231 .. method:: Connection.cursor([cursorClass])
232
233    The cursor method accepts a single optional parameter *cursorClass*. If
234    supplied, this must be a custom cursor class that extends
235    :class:`sqlite3.Cursor`.
236
237
238 .. method:: Connection.commit()
239
240    This method commits the current transaction. If you don't call this method,
241    anything you did since the last call to ``commit()`` is not visible from from
242    other database connections. If you wonder why you don't see the data you've
243    written to the database, please check you didn't forget to call this method.
244
245 .. method:: Connection.rollback()
246
247    This method rolls back any changes to the database since the last call to
248    :meth:`commit`.
249
250 .. method:: Connection.close()
251
252    This closes the database connection. Note that this does not automatically
253    call :meth:`commit`. If you just close your database connection without
254    calling :meth:`commit` first, your changes will be lost!
255
256 .. method:: Connection.execute(sql, [parameters])
257
258    This is a nonstandard shortcut that creates an intermediate cursor object by
259    calling the cursor method, then calls the cursor's
260    :meth:`execute<Cursor.execute>` method with the parameters given.
261
262
263 .. method:: Connection.executemany(sql, [parameters])
264
265    This is a nonstandard shortcut that creates an intermediate cursor object by
266    calling the cursor method, then calls the cursor's
267    :meth:`executemany<Cursor.executemany>` method with the parameters given.
268
269 .. method:: Connection.executescript(sql_script)
270
271    This is a nonstandard shortcut that creates an intermediate cursor object by
272    calling the cursor method, then calls the cursor's
273    :meth:`executescript<Cursor.executescript>` method with the parameters
274    given.
275
276
277 .. method:: Connection.create_function(name, num_params, func)
278
279    Creates a user-defined function that you can later use from within SQL
280    statements under the function name *name*. *num_params* is the number of
281    parameters the function accepts, and *func* is a Python callable that is called
282    as the SQL function.
283
284    The function can return any of the types supported by SQLite: unicode, str, int,
285    long, float, buffer and None.
286
287    Example:
288
289    .. literalinclude:: ../includes/sqlite3/md5func.py
290
291
292 .. method:: Connection.create_aggregate(name, num_params, aggregate_class)
293
294    Creates a user-defined aggregate function.
295
296    The aggregate class must implement a ``step`` method, which accepts the number
297    of parameters *num_params*, and a ``finalize`` method which will return the
298    final result of the aggregate.
299
300    The ``finalize`` method can return any of the types supported by SQLite:
301    unicode, str, int, long, float, buffer and None.
302
303    Example:
304
305    .. literalinclude:: ../includes/sqlite3/mysumaggr.py
306
307
308 .. method:: Connection.create_collation(name, callable)
309
310    Creates a collation with the specified *name* and *callable*. The callable will
311    be passed two string arguments. It should return -1 if the first is ordered
312    lower than the second, 0 if they are ordered equal and 1 if the first is ordered
313    higher than the second.  Note that this controls sorting (ORDER BY in SQL) so
314    your comparisons don't affect other SQL operations.
315
316    Note that the callable will get its parameters as Python bytestrings, which will
317    normally be encoded in UTF-8.
318
319    The following example shows a custom collation that sorts "the wrong way":
320
321    .. literalinclude:: ../includes/sqlite3/collation_reverse.py
322
323    To remove a collation, call ``create_collation`` with None as callable::
324
325       con.create_collation("reverse", None)
326
327
328 .. method:: Connection.interrupt()
329
330    You can call this method from a different thread to abort any queries that might
331    be executing on the connection. The query will then abort and the caller will
332    get an exception.
333
334
335 .. method:: Connection.set_authorizer(authorizer_callback)
336
337    This routine registers a callback. The callback is invoked for each attempt to
338    access a column of a table in the database. The callback should return
339    :const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL
340    statement should be aborted with an error and :const:`SQLITE_IGNORE` if the
341    column should be treated as a NULL value. These constants are available in the
342    :mod:`sqlite3` module.
343
344    The first argument to the callback signifies what kind of operation is to be
345    authorized. The second and third argument will be arguments or :const:`None`
346    depending on the first argument. The 4th argument is the name of the database
347    ("main", "temp", etc.) if applicable. The 5th argument is the name of the
348    inner-most trigger or view that is responsible for the access attempt or
349    :const:`None` if this access attempt is directly from input SQL code.
350
351    Please consult the SQLite documentation about the possible values for the first
352    argument and the meaning of the second and third argument depending on the first
353    one. All necessary constants are available in the :mod:`sqlite3` module.
354
355
356 .. method:: Connection.set_progress_handler(handler, n)
357
358    This routine registers a callback. The callback is invoked for every *n*
359    instructions of the SQLite virtual machine. This is useful if you want to
360    get called from SQLite during long-running operations, for example to update
361    a GUI.
362
363    If you want to clear any previously installed progress handler, call the
364    method with :const:`None` for *handler*.
365
366
367 .. method:: Connection.enable_load_extension(enabled)
368
369    This routine allows/disallows the SQLite engine to load SQLite extensions
370    from shared libraries.  SQLite extensions can define new functions,
371    aggregates or whole new virtual table implementations. One well-known
372    extension is the fulltext-search extension distributed with SQLite.
373
374    .. literalinclude:: ../includes/sqlite3/load_extension.py
375
376 .. method:: Connection.load_extension(path)
377
378    This routine loads a SQLite extension from a shared library. You have to
379    enable extension loading with ``enable_load_extension`` before you can use
380    this routine.
381
382 .. attribute:: Connection.row_factory
383
384    You can change this attribute to a callable that accepts the cursor and the
385    original row as a tuple and will return the real result row.  This way, you can
386    implement more advanced ways of returning results, such  as returning an object
387    that can also access columns by name.
388
389    Example:
390
391    .. literalinclude:: ../includes/sqlite3/row_factory.py
392
393    If returning a tuple doesn't suffice and you want name-based access to
394    columns, you should consider setting :attr:`row_factory` to the
395    highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both
396    index-based and case-insensitive name-based access to columns with almost no
397    memory overhead. It will probably be better than your own custom
398    dictionary-based approach or even a db_row based solution.
399
400    .. XXX what's a db_row-based solution?
401
402
403 .. attribute:: Connection.text_factory
404
405    Using this attribute you can control what objects are returned for the ``TEXT``
406    data type. By default, this attribute is set to :class:`unicode` and the
407    :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to
408    return bytestrings instead, you can set it to :class:`str`.
409
410    For efficiency reasons, there's also a way to return Unicode objects only for
411    non-ASCII data, and bytestrings otherwise. To activate it, set this attribute to
412    :const:`sqlite3.OptimizedUnicode`.
413
414    You can also set it to any other callable that accepts a single bytestring
415    parameter and returns the resulting object.
416
417    See the following example code for illustration:
418
419    .. literalinclude:: ../includes/sqlite3/text_factory.py
420
421
422 .. attribute:: Connection.total_changes
423
424    Returns the total number of database rows that have been modified, inserted, or
425    deleted since the database connection was opened.
426
427
428 .. attribute:: Connection.iterdump
429
430    Returns an iterator to dump the database in an SQL text format.  Useful when
431    saving an in-memory database for later restoration.  This function provides
432    the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3`
433    shell.
434
435    Example::
436
437       # Convert file existing_db.db to SQL dump file dump.sql
438       import sqlite3, os
439
440       con = sqlite3.connect('existing_db.db')
441       full_dump = os.linesep.join([line for line in con.iterdump()])
442       f = open('dump.sql', 'w')
443       f.writelines(full_dump)
444       f.close()
445
446
447 .. _sqlite3-cursor-objects:
448
449 Cursor Objects
450 --------------
451
452 A :class:`Cursor` instance has the following attributes and methods:
453
454    A SQLite database cursor has the following attributes and methods:
455
456 .. method:: Cursor.execute(sql, [parameters])
457
458    Executes an SQL statement. The SQL statement may be parametrized (i. e.
459    placeholders instead of SQL literals). The :mod:`sqlite3` module supports two
460    kinds of placeholders: question marks (qmark style) and named placeholders
461    (named style).
462
463    This example shows how to use parameters with qmark style:
464
465    .. literalinclude:: ../includes/sqlite3/execute_1.py
466
467    This example shows how to use the named style:
468
469    .. literalinclude:: ../includes/sqlite3/execute_2.py
470
471    :meth:`execute` will only execute a single SQL statement. If you try to execute
472    more than one statement with it, it will raise a Warning. Use
473    :meth:`executescript` if you want to execute multiple SQL statements with one
474    call.
475
476
477 .. method:: Cursor.executemany(sql, seq_of_parameters)
478
479    Executes an SQL command against all parameter sequences or mappings found in
480    the sequence *sql*.  The :mod:`sqlite3` module also allows using an
481    :term:`iterator` yielding parameters instead of a sequence.
482
483    .. literalinclude:: ../includes/sqlite3/executemany_1.py
484
485    Here's a shorter example using a :term:`generator`:
486
487    .. literalinclude:: ../includes/sqlite3/executemany_2.py
488
489
490 .. method:: Cursor.executescript(sql_script)
491
492    This is a nonstandard convenience method for executing multiple SQL statements
493    at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
494    gets as a parameter.
495
496    *sql_script* can be a bytestring or a Unicode string.
497
498    Example:
499
500    .. literalinclude:: ../includes/sqlite3/executescript.py
501
502
503 .. method:: Cursor.fetchone()
504
505    Fetches the next row of a query result set, returning a single sequence,
506    or :const:`None` when no more data is available.
507
508
509 .. method:: Cursor.fetchmany([size=cursor.arraysize])
510
511    Fetches the next set of rows of a query result, returning a list.  An empty
512    list is returned when no more rows are available.
513
514    The number of rows to fetch per call is specified by the *size* parameter.
515    If it is not given, the cursor's arraysize determines the number of rows
516    to be fetched. The method should try to fetch as many rows as indicated by
517    the size parameter. If this is not possible due to the specified number of
518    rows not being available, fewer rows may be returned.
519
520    Note there are performance considerations involved with the *size* parameter.
521    For optimal performance, it is usually best to use the arraysize attribute.
522    If the *size* parameter is used, then it is best for it to retain the same
523    value from one :meth:`fetchmany` call to the next.
524
525 .. method:: Cursor.fetchall()
526
527    Fetches all (remaining) rows of a query result, returning a list.  Note that
528    the cursor's arraysize attribute can affect the performance of this operation.
529    An empty list is returned when no rows are available.
530
531
532 .. attribute:: Cursor.rowcount
533
534    Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this
535    attribute, the database engine's own support for the determination of "rows
536    affected"/"rows selected" is quirky.
537
538    For ``DELETE`` statements, SQLite reports :attr:`rowcount` as 0 if you make a
539    ``DELETE FROM table`` without any condition.
540
541    For :meth:`executemany` statements, the number of modifications are summed up
542    into :attr:`rowcount`.
543
544    As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in
545    case no ``executeXX()`` has been performed on the cursor or the rowcount of the
546    last operation is not determinable by the interface".
547
548    This includes ``SELECT`` statements because we cannot determine the number of
549    rows a query produced until all rows were fetched.
550
551 .. attribute:: Cursor.lastrowid
552
553    This read-only attribute provides the rowid of the last modified row. It is
554    only set if you issued a ``INSERT`` statement using the :meth:`execute`
555    method. For operations other than ``INSERT`` or when :meth:`executemany` is
556    called, :attr:`lastrowid` is set to :const:`None`.
557
558 .. attribute:: Cursor.description
559
560    This read-only attribute provides the column names of the last query. To
561    remain compatible with the Python DB API, it returns a 7-tuple for each
562    column where the last six items of each tuple are :const:`None`.
563
564    It is set for ``SELECT`` statements without any matching rows as well.
565
566 .. _sqlite3-row-objects:
567
568 Row Objects
569 -----------
570
571 .. class:: Row
572
573    A :class:`Row` instance serves as a highly optimized
574    :attr:`~Connection.row_factory` for :class:`Connection` objects.
575    It tries to mimic a tuple in most of its features.
576
577    It supports mapping access by column name and index, iteration,
578    representation, equality testing and :func:`len`.
579
580    If two :class:`Row` objects have exactly the same columns and their
581    members are equal, they compare equal.
582
583    .. versionchanged:: 2.6
584       Added iteration and equality (hashability).
585
586    .. method:: keys
587
588       This method returns a tuple of column names. Immediately after a query,
589       it is the first member of each tuple in :attr:`Cursor.description`.
590
591       .. versionadded:: 2.6
592
593 Let's assume we initialize a table as in the example given above::
594
595     conn = sqlite3.connect(":memory:")
596     c = conn.cursor()
597     c.execute('''create table stocks
598     (date text, trans text, symbol text,
599      qty real, price real)''')
600     c.execute("""insert into stocks
601               values ('2006-01-05','BUY','RHAT',100,35.14)""")
602     conn.commit()
603     c.close()
604
605 Now we plug :class:`Row` in::
606
607     >>> conn.row_factory = sqlite3.Row
608     >>> c = conn.cursor()
609     >>> c.execute('select * from stocks')
610     <sqlite3.Cursor object at 0x7f4e7dd8fa80>
611     >>> r = c.fetchone()
612     >>> type(r)
613     <type 'sqlite3.Row'>
614     >>> r
615     (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
616     >>> len(r)
617     5
618     >>> r[2]
619     u'RHAT'
620     >>> r.keys()
621     ['date', 'trans', 'symbol', 'qty', 'price']
622     >>> r['qty']
623     100.0
624     >>> for member in r: print member
625     ...
626     2006-01-05
627     BUY
628     RHAT
629     100.0
630     35.14
631
632
633 .. _sqlite3-types:
634
635 SQLite and Python types
636 -----------------------
637
638
639 Introduction
640 ^^^^^^^^^^^^
641
642 SQLite natively supports the following types: ``NULL``, ``INTEGER``,
643 ``REAL``, ``TEXT``, ``BLOB``.
644
645 The following Python types can thus be sent to SQLite without any problem:
646
647 +-----------------------------+-------------+
648 | Python type                 | SQLite type |
649 +=============================+=============+
650 | :const:`None`               | ``NULL``    |
651 +-----------------------------+-------------+
652 | :class:`int`                | ``INTEGER`` |
653 +-----------------------------+-------------+
654 | :class:`long`               | ``INTEGER`` |
655 +-----------------------------+-------------+
656 | :class:`float`              | ``REAL``    |
657 +-----------------------------+-------------+
658 | :class:`str` (UTF8-encoded) | ``TEXT``    |
659 +-----------------------------+-------------+
660 | :class:`unicode`            | ``TEXT``    |
661 +-----------------------------+-------------+
662 | :class:`buffer`             | ``BLOB``    |
663 +-----------------------------+-------------+
664
665 This is how SQLite types are converted to Python types by default:
666
667 +-------------+----------------------------------------------+
668 | SQLite type | Python type                                  |
669 +=============+==============================================+
670 | ``NULL``    | :const:`None`                                |
671 +-------------+----------------------------------------------+
672 | ``INTEGER`` | :class:`int` or :class:`long`,               |
673 |             | depending on size                            |
674 +-------------+----------------------------------------------+
675 | ``REAL``    | :class:`float`                               |
676 +-------------+----------------------------------------------+
677 | ``TEXT``    | depends on :attr:`~Connection.text_factory`, |
678 |             | :class:`unicode` by default                  |
679 +-------------+----------------------------------------------+
680 | ``BLOB``    | :class:`buffer`                              |
681 +-------------+----------------------------------------------+
682
683 The type system of the :mod:`sqlite3` module is extensible in two ways: you can
684 store additional Python types in a SQLite database via object adaptation, and
685 you can let the :mod:`sqlite3` module convert SQLite types to different Python
686 types via converters.
687
688
689 Using adapters to store additional Python types in SQLite databases
690 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
691
692 As described before, SQLite supports only a limited set of types natively. To
693 use other Python types with SQLite, you must **adapt** them to one of the
694 sqlite3 module's supported types for SQLite: one of NoneType, int, long, float,
695 str, unicode, buffer.
696
697 The :mod:`sqlite3` module uses Python object adaptation, as described in
698 :pep:`246` for this.  The protocol to use is :class:`PrepareProtocol`.
699
700 There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
701 type to one of the supported ones.
702
703
704 Letting your object adapt itself
705 """"""""""""""""""""""""""""""""
706
707 This is a good approach if you write the class yourself. Let's suppose you have
708 a class like this::
709
710    class Point(object):
711        def __init__(self, x, y):
712            self.x, self.y = x, y
713
714 Now you want to store the point in a single SQLite column.  First you'll have to
715 choose one of the supported types first to be used for representing the point.
716 Let's just use str and separate the coordinates using a semicolon. Then you need
717 to give your class a method ``__conform__(self, protocol)`` which must return
718 the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
719
720 .. literalinclude:: ../includes/sqlite3/adapter_point_1.py
721
722
723 Registering an adapter callable
724 """""""""""""""""""""""""""""""
725
726 The other possibility is to create a function that converts the type to the
727 string representation and register the function with :meth:`register_adapter`.
728
729 .. note::
730
731    The type/class to adapt must be a :term:`new-style class`, i. e. it must have
732    :class:`object` as one of its bases.
733
734 .. literalinclude:: ../includes/sqlite3/adapter_point_2.py
735
736 The :mod:`sqlite3` module has two default adapters for Python's built-in
737 :class:`datetime.date` and :class:`datetime.datetime` types.  Now let's suppose
738 we want to store :class:`datetime.datetime` objects not in ISO representation,
739 but as a Unix timestamp.
740
741 .. literalinclude:: ../includes/sqlite3/adapter_datetime.py
742
743
744 Converting SQLite values to custom Python types
745 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
746
747 Writing an adapter lets you send custom Python types to SQLite. But to make it
748 really useful we need to make the Python to SQLite to Python roundtrip work.
749
750 Enter converters.
751
752 Let's go back to the :class:`Point` class. We stored the x and y coordinates
753 separated via semicolons as strings in SQLite.
754
755 First, we'll define a converter function that accepts the string as a parameter
756 and constructs a :class:`Point` object from it.
757
758 .. note::
759
760    Converter functions **always** get called with a string, no matter under which
761    data type you sent the value to SQLite.
762
763 ::
764
765    def convert_point(s):
766        x, y = map(float, s.split(";"))
767        return Point(x, y)
768
769 Now you need to make the :mod:`sqlite3` module know that what you select from
770 the database is actually a point. There are two ways of doing this:
771
772 * Implicitly via the declared type
773
774 * Explicitly via the column name
775
776 Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
777 for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
778
779 The following example illustrates both approaches.
780
781 .. literalinclude:: ../includes/sqlite3/converter_point.py
782
783
784 Default adapters and converters
785 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
786
787 There are default adapters for the date and datetime types in the datetime
788 module. They will be sent as ISO dates/ISO timestamps to SQLite.
789
790 The default converters are registered under the name "date" for
791 :class:`datetime.date` and under the name "timestamp" for
792 :class:`datetime.datetime`.
793
794 This way, you can use date/timestamps from Python without any additional
795 fiddling in most cases. The format of the adapters is also compatible with the
796 experimental SQLite date/time functions.
797
798 The following example demonstrates this.
799
800 .. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py
801
802
803 .. _sqlite3-controlling-transactions:
804
805 Controlling Transactions
806 ------------------------
807
808 By default, the :mod:`sqlite3` module opens transactions implicitly before a
809 Data Modification Language (DML)  statement (i.e.
810 ``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions
811 implicitly before a non-DML, non-query statement (i. e.
812 anything other than ``SELECT`` or the aforementioned).
813
814 So if you are within a transaction and issue a command like ``CREATE TABLE
815 ...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly
816 before executing that command. There are two reasons for doing that. The first
817 is that some of these commands don't work within transactions. The other reason
818 is that pysqlite needs to keep track of the transaction state (if a transaction
819 is active or not).
820
821 You can control which kind of ``BEGIN`` statements sqlite3 implicitly executes
822 (or none at all) via the *isolation_level* parameter to the :func:`connect`
823 call, or via the :attr:`isolation_level` property of connections.
824
825 If you want **autocommit mode**, then set :attr:`isolation_level` to None.
826
827 Otherwise leave it at its default, which will result in a plain "BEGIN"
828 statement, or set it to one of SQLite's supported isolation levels: "DEFERRED",
829 "IMMEDIATE" or "EXCLUSIVE".
830
831
832
833 Using :mod:`sqlite3` efficiently
834 --------------------------------
835
836
837 Using shortcut methods
838 ^^^^^^^^^^^^^^^^^^^^^^
839
840 Using the nonstandard :meth:`execute`, :meth:`executemany` and
841 :meth:`executescript` methods of the :class:`Connection` object, your code can
842 be written more concisely because you don't have to create the (often
843 superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
844 objects are created implicitly and these shortcut methods return the cursor
845 objects. This way, you can execute a ``SELECT`` statement and iterate over it
846 directly using only a single call on the :class:`Connection` object.
847
848 .. literalinclude:: ../includes/sqlite3/shortcut_methods.py
849
850
851 Accessing columns by name instead of by index
852 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
853
854 One useful feature of the :mod:`sqlite3` module is the built-in
855 :class:`sqlite3.Row` class designed to be used as a row factory.
856
857 Rows wrapped with this class can be accessed both by index (like tuples) and
858 case-insensitively by name:
859
860 .. literalinclude:: ../includes/sqlite3/rowclass.py
861
862
863 Using the connection as a context manager
864 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
865
866 With Python 2.5 or higher, connection objects can be used as context managers
867 that automatically commit or rollback transactions.  In the event of an
868 exception, the transaction is rolled back; otherwise, the transaction is
869 committed:
870
871 .. literalinclude:: ../includes/sqlite3/ctx_manager.py
872
873
874 Common issues
875 -------------
876
877 Multithreading
878 ^^^^^^^^^^^^^^
879
880 Older SQLite versions had issues with sharing connections between threads.
881 That's why the Python module disallows sharing connections and cursors between
882 threads. If you still try to do so, you will get an exception at runtime.
883
884 The only exception is calling the :meth:`~Connection.interrupt` method, which
885 only makes sense to call from a different thread.
886