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