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 7.. sectionauthor:: Gerhard Häring <gh@ghaering.de> 8 9**Source code:** :source:`Lib/sqlite3/` 10 11-------------- 12 13SQLite is a C library that provides a lightweight disk-based database that 14doesn't require a separate server process and allows accessing the database 15using a nonstandard variant of the SQL query language. Some applications can use 16SQLite for internal data storage. It's also possible to prototype an 17application using SQLite and then port the code to a larger database such as 18PostgreSQL or Oracle. 19 20The sqlite3 module was written by Gerhard Häring. It provides a SQL interface 21compliant with the DB-API 2.0 specification described by :pep:`249`. 22 23To use the module, you must first create a :class:`Connection` object that 24represents the database. Here the data will be stored in the 25:file:`example.db` file:: 26 27 import sqlite3 28 con = sqlite3.connect('example.db') 29 30You can also supply the special name ``:memory:`` to create a database in RAM. 31 32Once you have a :class:`Connection`, you can create a :class:`Cursor` object 33and call its :meth:`~Cursor.execute` method to perform SQL commands:: 34 35 cur = con.cursor() 36 37 # Create table 38 cur.execute('''CREATE TABLE stocks 39 (date text, trans text, symbol text, qty real, price real)''') 40 41 # Insert a row of data 42 cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)") 43 44 # Save (commit) the changes 45 con.commit() 46 47 # We can also close the connection if we are done with it. 48 # Just be sure any changes have been committed or they will be lost. 49 con.close() 50 51The data you've saved is persistent and is available in subsequent sessions:: 52 53 import sqlite3 54 con = sqlite3.connect('example.db') 55 cur = con.cursor() 56 57Usually your SQL operations will need to use values from Python variables. You 58shouldn't assemble your query using Python's string operations because doing so 59is insecure; it makes your program vulnerable to an SQL injection attack 60(see https://xkcd.com/327/ for humorous example of what can go wrong). 61 62Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder 63wherever you want to use a value, and then provide a tuple of values as the 64second argument to the cursor's :meth:`~Cursor.execute` method. (Other database 65modules may use a different placeholder, such as ``%s`` or ``:1``.) For 66example:: 67 68 # Never do this -- insecure! 69 symbol = 'RHAT' 70 cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol) 71 72 # Do this instead 73 t = ('RHAT',) 74 cur.execute('SELECT * FROM stocks WHERE symbol=?', t) 75 print(cur.fetchone()) 76 77 # Larger example that inserts many records at a time 78 purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), 79 ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00), 80 ('2006-04-06', 'SELL', 'IBM', 500, 53.00), 81 ] 82 cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases) 83 84To retrieve data after executing a SELECT statement, you can either treat the 85cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to 86retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the 87matching rows. 88 89This example uses the iterator form:: 90 91 >>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'): 92 print(row) 93 94 ('2006-01-05', 'BUY', 'RHAT', 100, 35.14) 95 ('2006-03-28', 'BUY', 'IBM', 1000, 45.0) 96 ('2006-04-06', 'SELL', 'IBM', 500, 53.0) 97 ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0) 98 99 100.. seealso:: 101 102 https://www.sqlite.org 103 The SQLite web page; the documentation describes the syntax and the 104 available data types for the supported SQL dialect. 105 106 https://www.w3schools.com/sql/ 107 Tutorial, reference and examples for learning SQL syntax. 108 109 :pep:`249` - Database API Specification 2.0 110 PEP written by Marc-André Lemburg. 111 112 113.. _sqlite3-module-contents: 114 115Module functions and constants 116------------------------------ 117 118 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 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 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. 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 163 there to return the value. The column name found in :attr:`Cursor.description` 164 does not include the type, i. e. if you use something like 165 ``'as "Expiration date [datetime]"'`` in your SQL, then we will parse out 166 everything until the first ``'['`` for the column name and strip 167 the preceeding space: the column name would simply be "Expiration date". 168 169 170.. function:: connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri]) 171 172 Opens a connection to the SQLite database file *database*. By default returns a 173 :class:`Connection` object, unless a custom *factory* is given. 174 175 *database* is a :term:`path-like object` giving the pathname (absolute or 176 relative to the current working directory) of the database file to be opened. 177 You can use ``":memory:"`` to open a database connection to a database that 178 resides in RAM instead of on disk. 179 180 When a database is accessed by multiple connections, and one of the processes 181 modifies the database, the SQLite database is locked until that transaction is 182 committed. The *timeout* parameter specifies how long the connection should wait 183 for the lock to go away until raising an exception. The default for the timeout 184 parameter is 5.0 (five seconds). 185 186 For the *isolation_level* parameter, please see the 187 :attr:`~Connection.isolation_level` property of :class:`Connection` objects. 188 189 SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If 190 you want to use other types you must add support for them yourself. The 191 *detect_types* parameter and the using custom **converters** registered with the 192 module-level :func:`register_converter` function allow you to easily do that. 193 194 *detect_types* defaults to 0 (i. e. off, no type detection), you can set it to 195 any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn 196 type detection on. Due to SQLite behaviour, types can't be detected for generated 197 fields (for example ``max(data)``), even when *detect_types* parameter is set. In 198 such case, the returned type is :class:`str`. 199 200 By default, *check_same_thread* is :const:`True` and only the creating thread may 201 use the connection. If set :const:`False`, the returned connection may be shared 202 across multiple threads. When using multiple threads with the same connection 203 writing operations should be serialized by the user to avoid data corruption. 204 205 By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the 206 connect call. You can, however, subclass the :class:`Connection` class and make 207 :func:`connect` use your class instead by providing your class for the *factory* 208 parameter. 209 210 Consult the section :ref:`sqlite3-types` of this manual for details. 211 212 The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing 213 overhead. If you want to explicitly set the number of statements that are cached 214 for the connection, you can set the *cached_statements* parameter. The currently 215 implemented default is to cache 100 statements. 216 217 If *uri* is true, *database* is interpreted as a URI. This allows you 218 to specify options. For example, to open a database in read-only mode 219 you can use:: 220 221 db = sqlite3.connect('file:path/to/database?mode=ro', uri=True) 222 223 More information about this feature, including a list of recognized options, can 224 be found in the `SQLite URI documentation <https://www.sqlite.org/uri.html>`_. 225 226 .. audit-event:: sqlite3.connect database sqlite3.connect 227 228 .. versionchanged:: 3.4 229 Added the *uri* parameter. 230 231 .. versionchanged:: 3.7 232 *database* can now also be a :term:`path-like object`, not only a string. 233 234 235.. function:: register_converter(typename, callable) 236 237 Registers a callable to convert a bytestring from the database into a custom 238 Python type. The callable will be invoked for all database values that are of 239 the type *typename*. Confer the parameter *detect_types* of the :func:`connect` 240 function for how the type detection works. Note that *typename* and the name of 241 the type in your query are matched in case-insensitive manner. 242 243 244.. function:: register_adapter(type, callable) 245 246 Registers a callable to convert the custom Python type *type* into one of 247 SQLite's supported types. The callable *callable* accepts as single parameter 248 the Python value, and must return a value of the following types: int, 249 float, str or bytes. 250 251 252.. function:: complete_statement(sql) 253 254 Returns :const:`True` if the string *sql* contains one or more complete SQL 255 statements terminated by semicolons. It does not verify that the SQL is 256 syntactically correct, only that there are no unclosed string literals and the 257 statement is terminated by a semicolon. 258 259 This can be used to build a shell for SQLite, as in the following example: 260 261 262 .. literalinclude:: ../includes/sqlite3/complete_statement.py 263 264 265.. function:: enable_callback_tracebacks(flag) 266 267 By default you will not get any tracebacks in user-defined functions, 268 aggregates, converters, authorizer callbacks etc. If you want to debug them, 269 you can call this function with *flag* set to ``True``. Afterwards, you will 270 get tracebacks from callbacks on ``sys.stderr``. Use :const:`False` to 271 disable the feature again. 272 273 274.. _sqlite3-connection-objects: 275 276Connection Objects 277------------------ 278 279.. class:: Connection 280 281 A SQLite database connection has the following attributes and methods: 282 283 .. attribute:: isolation_level 284 285 Get or set the current default isolation level. :const:`None` for autocommit mode or 286 one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section 287 :ref:`sqlite3-controlling-transactions` for a more detailed explanation. 288 289 .. attribute:: in_transaction 290 291 :const:`True` if a transaction is active (there are uncommitted changes), 292 :const:`False` otherwise. Read-only attribute. 293 294 .. versionadded:: 3.2 295 296 .. method:: cursor(factory=Cursor) 297 298 The cursor method accepts a single optional parameter *factory*. If 299 supplied, this must be a callable returning an instance of :class:`Cursor` 300 or its subclasses. 301 302 .. method:: commit() 303 304 This method commits the current transaction. If you don't call this method, 305 anything you did since the last call to ``commit()`` is not visible from 306 other database connections. If you wonder why you don't see the data you've 307 written to the database, please check you didn't forget to call this method. 308 309 .. method:: rollback() 310 311 This method rolls back any changes to the database since the last call to 312 :meth:`commit`. 313 314 .. method:: close() 315 316 This closes the database connection. Note that this does not automatically 317 call :meth:`commit`. If you just close your database connection without 318 calling :meth:`commit` first, your changes will be lost! 319 320 .. method:: execute(sql[, parameters]) 321 322 This is a nonstandard shortcut that creates a cursor object by calling 323 the :meth:`~Connection.cursor` method, calls the cursor's 324 :meth:`~Cursor.execute` method with the *parameters* given, and returns 325 the cursor. 326 327 .. method:: executemany(sql[, parameters]) 328 329 This is a nonstandard shortcut that creates a cursor object by 330 calling the :meth:`~Connection.cursor` method, calls the cursor's 331 :meth:`~Cursor.executemany` method with the *parameters* given, and 332 returns the cursor. 333 334 .. method:: executescript(sql_script) 335 336 This is a nonstandard shortcut that creates a cursor object by 337 calling the :meth:`~Connection.cursor` method, calls the cursor's 338 :meth:`~Cursor.executescript` method with the given *sql_script*, and 339 returns the cursor. 340 341 .. method:: create_function(name, num_params, func, *, deterministic=False) 342 343 Creates a user-defined function that you can later use from within SQL 344 statements under the function name *name*. *num_params* is the number of 345 parameters the function accepts (if *num_params* is -1, the function may 346 take any number of arguments), and *func* is a Python callable that is 347 called as the SQL function. If *deterministic* is true, the created function 348 is marked as `deterministic <https://sqlite.org/deterministic.html>`_, which 349 allows SQLite to perform additional optimizations. This flag is supported by 350 SQLite 3.8.3 or higher, :exc:`NotSupportedError` will be raised if used 351 with older versions. 352 353 The function can return any of the types supported by SQLite: bytes, str, int, 354 float and ``None``. 355 356 .. versionchanged:: 3.8 357 The *deterministic* parameter was added. 358 359 Example: 360 361 .. literalinclude:: ../includes/sqlite3/md5func.py 362 363 364 .. method:: create_aggregate(name, num_params, aggregate_class) 365 366 Creates a user-defined aggregate function. 367 368 The aggregate class must implement a ``step`` method, which accepts the number 369 of parameters *num_params* (if *num_params* is -1, the function may take 370 any number of arguments), and a ``finalize`` method which will return the 371 final result of the aggregate. 372 373 The ``finalize`` method can return any of the types supported by SQLite: 374 bytes, str, int, float and ``None``. 375 376 Example: 377 378 .. literalinclude:: ../includes/sqlite3/mysumaggr.py 379 380 381 .. method:: create_collation(name, callable) 382 383 Creates a collation with the specified *name* and *callable*. The callable will 384 be passed two string arguments. It should return -1 if the first is ordered 385 lower than the second, 0 if they are ordered equal and 1 if the first is ordered 386 higher than the second. Note that this controls sorting (ORDER BY in SQL) so 387 your comparisons don't affect other SQL operations. 388 389 Note that the callable will get its parameters as Python bytestrings, which will 390 normally be encoded in UTF-8. 391 392 The following example shows a custom collation that sorts "the wrong way": 393 394 .. literalinclude:: ../includes/sqlite3/collation_reverse.py 395 396 To remove a collation, call ``create_collation`` with ``None`` as callable:: 397 398 con.create_collation("reverse", None) 399 400 401 .. method:: interrupt() 402 403 You can call this method from a different thread to abort any queries that might 404 be executing on the connection. The query will then abort and the caller will 405 get an exception. 406 407 408 .. method:: set_authorizer(authorizer_callback) 409 410 This routine registers a callback. The callback is invoked for each attempt to 411 access a column of a table in the database. The callback should return 412 :const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL 413 statement should be aborted with an error and :const:`SQLITE_IGNORE` if the 414 column should be treated as a NULL value. These constants are available in the 415 :mod:`sqlite3` module. 416 417 The first argument to the callback signifies what kind of operation is to be 418 authorized. The second and third argument will be arguments or :const:`None` 419 depending on the first argument. The 4th argument is the name of the database 420 ("main", "temp", etc.) if applicable. The 5th argument is the name of the 421 inner-most trigger or view that is responsible for the access attempt or 422 :const:`None` if this access attempt is directly from input SQL code. 423 424 Please consult the SQLite documentation about the possible values for the first 425 argument and the meaning of the second and third argument depending on the first 426 one. All necessary constants are available in the :mod:`sqlite3` module. 427 428 429 .. method:: set_progress_handler(handler, n) 430 431 This routine registers a callback. The callback is invoked for every *n* 432 instructions of the SQLite virtual machine. This is useful if you want to 433 get called from SQLite during long-running operations, for example to update 434 a GUI. 435 436 If you want to clear any previously installed progress handler, call the 437 method with :const:`None` for *handler*. 438 439 Returning a non-zero value from the handler function will terminate the 440 currently executing query and cause it to raise an :exc:`OperationalError` 441 exception. 442 443 444 .. method:: set_trace_callback(trace_callback) 445 446 Registers *trace_callback* to be called for each SQL statement that is 447 actually executed by the SQLite backend. 448 449 The only argument passed to the callback is the statement (as string) that 450 is being executed. The return value of the callback is ignored. Note that 451 the backend does not only run statements passed to the :meth:`Cursor.execute` 452 methods. Other sources include the transaction management of the Python 453 module and the execution of triggers defined in the current database. 454 455 Passing :const:`None` as *trace_callback* will disable the trace callback. 456 457 .. versionadded:: 3.3 458 459 460 .. method:: enable_load_extension(enabled) 461 462 This routine allows/disallows the SQLite engine to load SQLite extensions 463 from shared libraries. SQLite extensions can define new functions, 464 aggregates or whole new virtual table implementations. One well-known 465 extension is the fulltext-search extension distributed with SQLite. 466 467 Loadable extensions are disabled by default. See [#f1]_. 468 469 .. versionadded:: 3.2 470 471 .. literalinclude:: ../includes/sqlite3/load_extension.py 472 473 .. method:: load_extension(path) 474 475 This routine loads a SQLite extension from a shared library. You have to 476 enable extension loading with :meth:`enable_load_extension` before you can 477 use this routine. 478 479 Loadable extensions are disabled by default. See [#f1]_. 480 481 .. versionadded:: 3.2 482 483 .. attribute:: row_factory 484 485 You can change this attribute to a callable that accepts the cursor and the 486 original row as a tuple and will return the real result row. This way, you can 487 implement more advanced ways of returning results, such as returning an object 488 that can also access columns by name. 489 490 Example: 491 492 .. literalinclude:: ../includes/sqlite3/row_factory.py 493 494 If returning a tuple doesn't suffice and you want name-based access to 495 columns, you should consider setting :attr:`row_factory` to the 496 highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both 497 index-based and case-insensitive name-based access to columns with almost no 498 memory overhead. It will probably be better than your own custom 499 dictionary-based approach or even a db_row based solution. 500 501 .. XXX what's a db_row-based solution? 502 503 504 .. attribute:: text_factory 505 506 Using this attribute you can control what objects are returned for the ``TEXT`` 507 data type. By default, this attribute is set to :class:`str` and the 508 :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to 509 return bytestrings instead, you can set it to :class:`bytes`. 510 511 You can also set it to any other callable that accepts a single bytestring 512 parameter and returns the resulting object. 513 514 See the following example code for illustration: 515 516 .. literalinclude:: ../includes/sqlite3/text_factory.py 517 518 519 .. attribute:: total_changes 520 521 Returns the total number of database rows that have been modified, inserted, or 522 deleted since the database connection was opened. 523 524 525 .. method:: iterdump 526 527 Returns an iterator to dump the database in an SQL text format. Useful when 528 saving an in-memory database for later restoration. This function provides 529 the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3` 530 shell. 531 532 Example:: 533 534 # Convert file existing_db.db to SQL dump file dump.sql 535 import sqlite3 536 537 con = sqlite3.connect('existing_db.db') 538 with open('dump.sql', 'w') as f: 539 for line in con.iterdump(): 540 f.write('%s\n' % line) 541 con.close() 542 543 544 .. method:: backup(target, *, pages=-1, progress=None, name="main", sleep=0.250) 545 546 This method makes a backup of a SQLite database even while it's being accessed 547 by other clients, or concurrently by the same connection. The copy will be 548 written into the mandatory argument *target*, that must be another 549 :class:`Connection` instance. 550 551 By default, or when *pages* is either ``0`` or a negative integer, the entire 552 database is copied in a single step; otherwise the method performs a loop 553 copying up to *pages* pages at a time. 554 555 If *progress* is specified, it must either be ``None`` or a callable object that 556 will be executed at each iteration with three integer arguments, respectively 557 the *status* of the last iteration, the *remaining* number of pages still to be 558 copied and the *total* number of pages. 559 560 The *name* argument specifies the database name that will be copied: it must be 561 a string containing either ``"main"``, the default, to indicate the main 562 database, ``"temp"`` to indicate the temporary database or the name specified 563 after the ``AS`` keyword in an ``ATTACH DATABASE`` statement for an attached 564 database. 565 566 The *sleep* argument specifies the number of seconds to sleep by between 567 successive attempts to backup remaining pages, can be specified either as an 568 integer or a floating point value. 569 570 Example 1, copy an existing database into another:: 571 572 import sqlite3 573 574 def progress(status, remaining, total): 575 print(f'Copied {total-remaining} of {total} pages...') 576 577 con = sqlite3.connect('existing_db.db') 578 bck = sqlite3.connect('backup.db') 579 with bck: 580 con.backup(bck, pages=1, progress=progress) 581 bck.close() 582 con.close() 583 584 Example 2, copy an existing database into a transient copy:: 585 586 import sqlite3 587 588 source = sqlite3.connect('existing_db.db') 589 dest = sqlite3.connect(':memory:') 590 source.backup(dest) 591 592 Availability: SQLite 3.6.11 or higher 593 594 .. versionadded:: 3.7 595 596 597.. _sqlite3-cursor-objects: 598 599Cursor Objects 600-------------- 601 602.. class:: Cursor 603 604 A :class:`Cursor` instance has the following attributes and methods. 605 606 .. index:: single: ? (question mark); in SQL statements 607 .. index:: single: : (colon); in SQL statements 608 609 .. method:: execute(sql[, parameters]) 610 611 Executes an SQL statement. The SQL statement may be parameterized (i. e. 612 placeholders instead of SQL literals). The :mod:`sqlite3` module supports two 613 kinds of placeholders: question marks (qmark style) and named placeholders 614 (named style). 615 616 Here's an example of both styles: 617 618 .. literalinclude:: ../includes/sqlite3/execute_1.py 619 620 :meth:`execute` will only execute a single SQL statement. If you try to execute 621 more than one statement with it, it will raise a :exc:`.Warning`. Use 622 :meth:`executescript` if you want to execute multiple SQL statements with one 623 call. 624 625 626 .. method:: executemany(sql, seq_of_parameters) 627 628 Executes an SQL command against all parameter sequences or mappings found in 629 the sequence *seq_of_parameters*. The :mod:`sqlite3` module also allows 630 using an :term:`iterator` yielding parameters instead of a sequence. 631 632 .. literalinclude:: ../includes/sqlite3/executemany_1.py 633 634 Here's a shorter example using a :term:`generator`: 635 636 .. literalinclude:: ../includes/sqlite3/executemany_2.py 637 638 639 .. method:: executescript(sql_script) 640 641 This is a nonstandard convenience method for executing multiple SQL statements 642 at once. It issues a ``COMMIT`` statement first, then executes the SQL script it 643 gets as a parameter. 644 645 *sql_script* can be an instance of :class:`str`. 646 647 Example: 648 649 .. literalinclude:: ../includes/sqlite3/executescript.py 650 651 652 .. method:: fetchone() 653 654 Fetches the next row of a query result set, returning a single sequence, 655 or :const:`None` when no more data is available. 656 657 658 .. method:: fetchmany(size=cursor.arraysize) 659 660 Fetches the next set of rows of a query result, returning a list. An empty 661 list is returned when no more rows are available. 662 663 The number of rows to fetch per call is specified by the *size* parameter. 664 If it is not given, the cursor's arraysize determines the number of rows 665 to be fetched. The method should try to fetch as many rows as indicated by 666 the size parameter. If this is not possible due to the specified number of 667 rows not being available, fewer rows may be returned. 668 669 Note there are performance considerations involved with the *size* parameter. 670 For optimal performance, it is usually best to use the arraysize attribute. 671 If the *size* parameter is used, then it is best for it to retain the same 672 value from one :meth:`fetchmany` call to the next. 673 674 .. method:: fetchall() 675 676 Fetches all (remaining) rows of a query result, returning a list. Note that 677 the cursor's arraysize attribute can affect the performance of this operation. 678 An empty list is returned when no rows are available. 679 680 .. method:: close() 681 682 Close the cursor now (rather than whenever ``__del__`` is called). 683 684 The cursor will be unusable from this point forward; a :exc:`ProgrammingError` 685 exception will be raised if any operation is attempted with the cursor. 686 687 .. attribute:: rowcount 688 689 Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this 690 attribute, the database engine's own support for the determination of "rows 691 affected"/"rows selected" is quirky. 692 693 For :meth:`executemany` statements, the number of modifications are summed up 694 into :attr:`rowcount`. 695 696 As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in 697 case no ``executeXX()`` has been performed on the cursor or the rowcount of the 698 last operation is not determinable by the interface". This includes ``SELECT`` 699 statements because we cannot determine the number of rows a query produced 700 until all rows were fetched. 701 702 With SQLite versions before 3.6.5, :attr:`rowcount` is set to 0 if 703 you make a ``DELETE FROM table`` without any condition. 704 705 .. attribute:: lastrowid 706 707 This read-only attribute provides the rowid of the last modified row. It is 708 only set if you issued an ``INSERT`` or a ``REPLACE`` statement using the 709 :meth:`execute` method. For operations other than ``INSERT`` or 710 ``REPLACE`` or when :meth:`executemany` is called, :attr:`lastrowid` is 711 set to :const:`None`. 712 713 If the ``INSERT`` or ``REPLACE`` statement failed to insert the previous 714 successful rowid is returned. 715 716 .. versionchanged:: 3.6 717 Added support for the ``REPLACE`` statement. 718 719 .. attribute:: arraysize 720 721 Read/write attribute that controls the number of rows returned by :meth:`fetchmany`. 722 The default value is 1 which means a single row would be fetched per call. 723 724 .. attribute:: description 725 726 This read-only attribute provides the column names of the last query. To 727 remain compatible with the Python DB API, it returns a 7-tuple for each 728 column where the last six items of each tuple are :const:`None`. 729 730 It is set for ``SELECT`` statements without any matching rows as well. 731 732 .. attribute:: connection 733 734 This read-only attribute provides the SQLite database :class:`Connection` 735 used by the :class:`Cursor` object. A :class:`Cursor` object created by 736 calling :meth:`con.cursor() <Connection.cursor>` will have a 737 :attr:`connection` attribute that refers to *con*:: 738 739 >>> con = sqlite3.connect(":memory:") 740 >>> cur = con.cursor() 741 >>> cur.connection == con 742 True 743 744.. _sqlite3-row-objects: 745 746Row Objects 747----------- 748 749.. class:: Row 750 751 A :class:`Row` instance serves as a highly optimized 752 :attr:`~Connection.row_factory` for :class:`Connection` objects. 753 It tries to mimic a tuple in most of its features. 754 755 It supports mapping access by column name and index, iteration, 756 representation, equality testing and :func:`len`. 757 758 If two :class:`Row` objects have exactly the same columns and their 759 members are equal, they compare equal. 760 761 .. method:: keys 762 763 This method returns a list of column names. Immediately after a query, 764 it is the first member of each tuple in :attr:`Cursor.description`. 765 766 .. versionchanged:: 3.5 767 Added support of slicing. 768 769Let's assume we initialize a table as in the example given above:: 770 771 con = sqlite3.connect(":memory:") 772 cur = con.cursor() 773 cur.execute('''create table stocks 774 (date text, trans text, symbol text, 775 qty real, price real)''') 776 cur.execute("""insert into stocks 777 values ('2006-01-05','BUY','RHAT',100,35.14)""") 778 con.commit() 779 cur.close() 780 781Now we plug :class:`Row` in:: 782 783 >>> con.row_factory = sqlite3.Row 784 >>> cur = con.cursor() 785 >>> cur.execute('select * from stocks') 786 <sqlite3.Cursor object at 0x7f4e7dd8fa80> 787 >>> r = cur.fetchone() 788 >>> type(r) 789 <class 'sqlite3.Row'> 790 >>> tuple(r) 791 ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14) 792 >>> len(r) 793 5 794 >>> r[2] 795 'RHAT' 796 >>> r.keys() 797 ['date', 'trans', 'symbol', 'qty', 'price'] 798 >>> r['qty'] 799 100.0 800 >>> for member in r: 801 ... print(member) 802 ... 803 2006-01-05 804 BUY 805 RHAT 806 100.0 807 35.14 808 809 810.. _sqlite3-exceptions: 811 812Exceptions 813---------- 814 815.. exception:: Warning 816 817 A subclass of :exc:`Exception`. 818 819.. exception:: Error 820 821 The base class of the other exceptions in this module. It is a subclass 822 of :exc:`Exception`. 823 824.. exception:: DatabaseError 825 826 Exception raised for errors that are related to the database. 827 828.. exception:: IntegrityError 829 830 Exception raised when the relational integrity of the database is affected, 831 e.g. a foreign key check fails. It is a subclass of :exc:`DatabaseError`. 832 833.. exception:: ProgrammingError 834 835 Exception raised for programming errors, e.g. table not found or already 836 exists, syntax error in the SQL statement, wrong number of parameters 837 specified, etc. It is a subclass of :exc:`DatabaseError`. 838 839.. exception:: OperationalError 840 841 Exception raised for errors that are related to the database's operation 842 and not necessarily under the control of the programmer, e.g. an unexpected 843 disconnect occurs, the data source name is not found, a transaction could 844 not be processed, etc. It is a subclass of :exc:`DatabaseError`. 845 846.. exception:: NotSupportedError 847 848 Exception raised in case a method or database API was used which is not 849 supported by the database, e.g. calling the :meth:`~Connection.rollback` 850 method on a connection that does not support transaction or has 851 transactions turned off. It is a subclass of :exc:`DatabaseError`. 852 853 854.. _sqlite3-types: 855 856SQLite and Python types 857----------------------- 858 859 860Introduction 861^^^^^^^^^^^^ 862 863SQLite natively supports the following types: ``NULL``, ``INTEGER``, 864``REAL``, ``TEXT``, ``BLOB``. 865 866The following Python types can thus be sent to SQLite without any problem: 867 868+-------------------------------+-------------+ 869| Python type | SQLite type | 870+===============================+=============+ 871| :const:`None` | ``NULL`` | 872+-------------------------------+-------------+ 873| :class:`int` | ``INTEGER`` | 874+-------------------------------+-------------+ 875| :class:`float` | ``REAL`` | 876+-------------------------------+-------------+ 877| :class:`str` | ``TEXT`` | 878+-------------------------------+-------------+ 879| :class:`bytes` | ``BLOB`` | 880+-------------------------------+-------------+ 881 882 883This is how SQLite types are converted to Python types by default: 884 885+-------------+----------------------------------------------+ 886| SQLite type | Python type | 887+=============+==============================================+ 888| ``NULL`` | :const:`None` | 889+-------------+----------------------------------------------+ 890| ``INTEGER`` | :class:`int` | 891+-------------+----------------------------------------------+ 892| ``REAL`` | :class:`float` | 893+-------------+----------------------------------------------+ 894| ``TEXT`` | depends on :attr:`~Connection.text_factory`, | 895| | :class:`str` by default | 896+-------------+----------------------------------------------+ 897| ``BLOB`` | :class:`bytes` | 898+-------------+----------------------------------------------+ 899 900The type system of the :mod:`sqlite3` module is extensible in two ways: you can 901store additional Python types in a SQLite database via object adaptation, and 902you can let the :mod:`sqlite3` module convert SQLite types to different Python 903types via converters. 904 905 906Using adapters to store additional Python types in SQLite databases 907^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 908 909As described before, SQLite supports only a limited set of types natively. To 910use other Python types with SQLite, you must **adapt** them to one of the 911sqlite3 module's supported types for SQLite: one of NoneType, int, float, 912str, bytes. 913 914There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python 915type to one of the supported ones. 916 917 918Letting your object adapt itself 919"""""""""""""""""""""""""""""""" 920 921This is a good approach if you write the class yourself. Let's suppose you have 922a class like this:: 923 924 class Point: 925 def __init__(self, x, y): 926 self.x, self.y = x, y 927 928Now you want to store the point in a single SQLite column. First you'll have to 929choose one of the supported types first to be used for representing the point. 930Let's just use str and separate the coordinates using a semicolon. Then you need 931to give your class a method ``__conform__(self, protocol)`` which must return 932the converted value. The parameter *protocol* will be :class:`PrepareProtocol`. 933 934.. literalinclude:: ../includes/sqlite3/adapter_point_1.py 935 936 937Registering an adapter callable 938""""""""""""""""""""""""""""""" 939 940The other possibility is to create a function that converts the type to the 941string representation and register the function with :meth:`register_adapter`. 942 943.. literalinclude:: ../includes/sqlite3/adapter_point_2.py 944 945The :mod:`sqlite3` module has two default adapters for Python's built-in 946:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose 947we want to store :class:`datetime.datetime` objects not in ISO representation, 948but as a Unix timestamp. 949 950.. literalinclude:: ../includes/sqlite3/adapter_datetime.py 951 952 953Converting SQLite values to custom Python types 954^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 955 956Writing an adapter lets you send custom Python types to SQLite. But to make it 957really useful we need to make the Python to SQLite to Python roundtrip work. 958 959Enter converters. 960 961Let's go back to the :class:`Point` class. We stored the x and y coordinates 962separated via semicolons as strings in SQLite. 963 964First, we'll define a converter function that accepts the string as a parameter 965and constructs a :class:`Point` object from it. 966 967.. note:: 968 969 Converter functions **always** get called with a :class:`bytes` object, no 970 matter under which data type you sent the value to SQLite. 971 972:: 973 974 def convert_point(s): 975 x, y = map(float, s.split(b";")) 976 return Point(x, y) 977 978Now you need to make the :mod:`sqlite3` module know that what you select from 979the database is actually a point. There are two ways of doing this: 980 981* Implicitly via the declared type 982 983* Explicitly via the column name 984 985Both ways are described in section :ref:`sqlite3-module-contents`, in the entries 986for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`. 987 988The following example illustrates both approaches. 989 990.. literalinclude:: ../includes/sqlite3/converter_point.py 991 992 993Default adapters and converters 994^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 995 996There are default adapters for the date and datetime types in the datetime 997module. They will be sent as ISO dates/ISO timestamps to SQLite. 998 999The default converters are registered under the name "date" for 1000:class:`datetime.date` and under the name "timestamp" for 1001:class:`datetime.datetime`. 1002 1003This way, you can use date/timestamps from Python without any additional 1004fiddling in most cases. The format of the adapters is also compatible with the 1005experimental SQLite date/time functions. 1006 1007The following example demonstrates this. 1008 1009.. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py 1010 1011If a timestamp stored in SQLite has a fractional part longer than 6 1012numbers, its value will be truncated to microsecond precision by the 1013timestamp converter. 1014 1015 1016.. _sqlite3-controlling-transactions: 1017 1018Controlling Transactions 1019------------------------ 1020 1021The underlying ``sqlite3`` library operates in ``autocommit`` mode by default, 1022but the Python :mod:`sqlite3` module by default does not. 1023 1024``autocommit`` mode means that statements that modify the database take effect 1025immediately. A ``BEGIN`` or ``SAVEPOINT`` statement disables ``autocommit`` 1026mode, and a ``COMMIT``, a ``ROLLBACK``, or a ``RELEASE`` that ends the 1027outermost transaction, turns ``autocommit`` mode back on. 1028 1029The Python :mod:`sqlite3` module by default issues a ``BEGIN`` statement 1030implicitly before a Data Modification Language (DML) statement (i.e. 1031``INSERT``/``UPDATE``/``DELETE``/``REPLACE``). 1032 1033You can control which kind of ``BEGIN`` statements :mod:`sqlite3` implicitly 1034executes via the *isolation_level* parameter to the :func:`connect` 1035call, or via the :attr:`isolation_level` property of connections. 1036If you specify no *isolation_level*, a plain ``BEGIN`` is used, which is 1037equivalent to specifying ``DEFERRED``. Other possible values are ``IMMEDIATE`` 1038and ``EXCLUSIVE``. 1039 1040You can disable the :mod:`sqlite3` module's implicit transaction management by 1041setting :attr:`isolation_level` to ``None``. This will leave the underlying 1042``sqlite3`` library operating in ``autocommit`` mode. You can then completely 1043control the transaction state by explicitly issuing ``BEGIN``, ``ROLLBACK``, 1044``SAVEPOINT``, and ``RELEASE`` statements in your code. 1045 1046.. versionchanged:: 3.6 1047 :mod:`sqlite3` used to implicitly commit an open transaction before DDL 1048 statements. This is no longer the case. 1049 1050 1051Using :mod:`sqlite3` efficiently 1052-------------------------------- 1053 1054 1055Using shortcut methods 1056^^^^^^^^^^^^^^^^^^^^^^ 1057 1058Using the nonstandard :meth:`execute`, :meth:`executemany` and 1059:meth:`executescript` methods of the :class:`Connection` object, your code can 1060be written more concisely because you don't have to create the (often 1061superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor` 1062objects are created implicitly and these shortcut methods return the cursor 1063objects. This way, you can execute a ``SELECT`` statement and iterate over it 1064directly using only a single call on the :class:`Connection` object. 1065 1066.. literalinclude:: ../includes/sqlite3/shortcut_methods.py 1067 1068 1069Accessing columns by name instead of by index 1070^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1071 1072One useful feature of the :mod:`sqlite3` module is the built-in 1073:class:`sqlite3.Row` class designed to be used as a row factory. 1074 1075Rows wrapped with this class can be accessed both by index (like tuples) and 1076case-insensitively by name: 1077 1078.. literalinclude:: ../includes/sqlite3/rowclass.py 1079 1080 1081Using the connection as a context manager 1082^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1083 1084Connection objects can be used as context managers 1085that automatically commit or rollback transactions. In the event of an 1086exception, the transaction is rolled back; otherwise, the transaction is 1087committed: 1088 1089.. literalinclude:: ../includes/sqlite3/ctx_manager.py 1090 1091 1092.. rubric:: Footnotes 1093 1094.. [#f1] The sqlite3 module is not built with loadable extension support by 1095 default, because some platforms (notably Mac OS X) have SQLite 1096 libraries which are compiled without this feature. To get loadable 1097 extension support, you must pass ``--enable-loadable-sqlite-extensions`` to 1098 configure. 1099