/Doc/library/sqlite3.rst

http://unladen-swallow.googlecode.com/ · ReStructuredText · 862 lines · 579 code · 283 blank · 0 comment · 0 complexity · d21d131e708512c30876f260533ef460 MD5 · raw file

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