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