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