Lines Matching +full:- +full:- +full:execute
1 :mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
5 :synopsis: A DB-API 2.0 implementation using SQLite 3.x.
21 .. _sqlite3-intro:
23 SQLite is a C library that provides a lightweight disk-based database that
31 compliant with the DB-API 2.0 specification described by :pep:`249`, and
36 * :ref:`sqlite3-tutorial` teaches how to use the :mod:`!sqlite3` module.
37 * :ref:`sqlite3-reference` describes the classes and functions this module
39 * :ref:`sqlite3-howtos` details how to handle specific tasks.
40 * :ref:`sqlite3-explanation` provides in-depth background on
52 :pep:`249` - Database API Specification 2.0
53 PEP written by Marc-André Lemburg.
57 - UPPERCASE for keywords
58 - snake_case for schema
59 - single quotes for string literals
60 - singular for table names
61 - if needed, use double quotes for table and column names
63 .. _sqlite3-tutorial:
66 --------
85 represents the connection to the on-disk database.
87 In order to execute SQL statements and fetch results from SQL queries,
98 For simplicity, we can just use column names in the table declaration --
101 Execute the ``CREATE TABLE`` statement
102 by calling :meth:`cur.execute(...) <Cursor.execute>`:
106 cur.execute("CREATE TABLE movie(title, year, score)")
112 the ``sqlite_master`` table built-in to SQLite,
115 Execute that query by calling :meth:`cur.execute(...) <Cursor.execute>`,
121 >>> res = cur.execute("SELECT name FROM sqlite_master")
127 If we query ``sqlite_master`` for a non-existent table ``spam``,
132 >>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
138 once again by calling :meth:`cur.execute(...) <Cursor.execute>`:
142 cur.execute("""
150 (see :ref:`sqlite3-controlling-transactions` for details).
160 Use the now-familiar :meth:`cur.execute(...) <Cursor.execute>` to
166 >>> res = cur.execute("SELECT score FROM movie")
187 Always use placeholders instead of :ref:`string formatting <tut-formatting>`
190 (see :ref:`sqlite3-placeholders` for more details).
198 >>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
206 Each row is a two-item :class:`tuple` of ``(year, title)``,
219 >>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
236 * :ref:`sqlite3-howtos` for further reading:
238 * :ref:`sqlite3-placeholders`
239 * :ref:`sqlite3-adapters`
240 * :ref:`sqlite3-converters`
241 * :ref:`sqlite3-connection-context-manager`
242 * :ref:`sqlite3-howto-row-factory`
244 * :ref:`sqlite3-explanation` for in-depth background on transaction control.
246 .. _sqlite3-reference:
249 ---------
251 .. We keep the old sqlite3-module-contents ref to prevent breaking links.
252 .. _sqlite3-module-contents:
254 .. _sqlite3-module-functions:
270 :type database: :term:`path-like object`
281 :ref:`natively supported by SQLite <sqlite3-types>`
298 See :ref:`sqlite3-controlling-transactions` for more.
326 enabling various :ref:`sqlite3-uri-tricks`.
330 .. audit-event:: sqlite3.connect database sqlite3.connect
331 .. audit-event:: sqlite3.connect/handle connection_handle sqlite3.connect
337 *database* can now also be a :term:`path-like object`, not only a string.
359 This function may be useful during command-line input
361 or if additional input is needed before calling :meth:`~Cursor.execute`.
366 By default you will not get any tracebacks in user-defined functions,
391 >>> cur = con.execute("SELECT 1")
401 :ref:`type that SQLite natively understands <sqlite3-types>`.
414 case-insensitively.
417 .. _sqlite3-module-constants:
430 .. code-block:: sql
447 .. code-block:: sql
471 String constant stating the supported DB-API level. Required by the DB-API.
472 Hard-coded to ``"2.0"``.
477 the :mod:`!sqlite3` module. Required by the DB-API. Hard-coded to
482 The ``named`` DB-API parameter style is also supported.
495 Integer constant required by the DB-API 2.0, stating the level of thread
500 1. **Single-thread**: In this mode, all mutexes are disabled and SQLite is
502 2. **Multi-thread**: In this mode, SQLite can be safely used by multiple
508 The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels
511 +------------------+-----------------+----------------------+-------------------------------+
512 | SQLite threading | `threadsafety`_ | `SQLITE_THREADSAFE`_ | DB-API 2.0 meaning |
515 | single-thread | 0 | 0 | Threads may not share the |
517 +------------------+-----------------+----------------------+-------------------------------+
518 | multi-thread | 1 | 2 | Threads may share the module, |
520 +------------------+-----------------+----------------------+-------------------------------+
523 +------------------+-----------------+----------------------+-------------------------------+
525 .. _threadsafety: https://peps.python.org/pep-0249/#threadsafety
529 Set *threadsafety* dynamically instead of hard-coding it to ``1``.
542 .. _sqlite3-connection-objects:
552 and :ref:`sqlite3-controlling-transactions`.
556 * :ref:`sqlite3-connection-shortcuts`
557 * :ref:`sqlite3-connection-context-manager`
606 If there is no open transaction, this method is a no-op.
611 If there is no open transaction, this method is a no-op.
620 .. method:: execute(sql, parameters=(), /)
623 :meth:`~Cursor.execute` on it with the given *sql* and *parameters*.
640 Create or remove a user-defined SQL function.
647 If ``-1``, it may take any number of arguments.
652 <sqlite3-types>`.
676 >>> for row in con.execute("SELECT md5(?)", (b"foo",)):
683 Create or remove a user-defined SQL aggregate function.
690 If ``-1``, it may take any number of arguments.
697 :ref:`a type natively supported by SQLite <sqlite3-types>`.
721 cur = con.execute("CREATE TABLE test(i)")
722 cur.execute("INSERT INTO test(i) VALUES(1)")
723 cur.execute("INSERT INTO test(i) VALUES(2)")
724 cur.execute("SELECT mysum(i) FROM test")
737 Create or remove a user-defined aggregate window function.
744 If ``-1``, it may take any number of arguments.
753 :ref:`a type natively supported by SQLite <sqlite3-types>`.
787 self.count -= value
792 Any clean-up actions should be placed here.
798 cur = con.execute("CREATE TABLE test(x, y)")
808 cur.execute("""
828 * ``-1`` if the first is ordered lower than the second
841 return -1
846 cur = con.execute("CREATE TABLE test(x)")
848 cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse")
885 inner-most trigger or view that is responsible for the access attempt or
902 get called from SQLite during long-running operations, for example to update
908 Returning a non-zero value from the handler function will terminate the
921 :meth:`Cursor.execute` methods. Other sources include the
922 :ref:`transaction management <sqlite3-controlling-transactions>` of the
943 aggregates or whole new virtual table implementations. One well-known
944 extension is the fulltext-search extension distributed with SQLite.
952 you must pass the :option:`--enable-loadable-sqlite-extensions` option
955 ….. audit-event:: sqlite3.enable_load_extension connection,enabled sqlite3.Connection.enable_load_e…
973 con.execute("select load_extension('./fts3.so')")
982 con.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)")
989 … for row in con.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"):
1006 .. audit-event:: sqlite3.load_extension connection,path sqlite3.Connection.load_extension
1016 Useful when saving an in-memory database for later restoration.
1031 .. method:: backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)
1045 Defaults to ``-1``.
1072 print(f'Copied {total-remaining} of {total} pages...')
1163 ordinary on-disk database file, the serialization is just a copy of the
1164 disk file. For an in-memory database or a "temp" database, the
1187 *name*, and reopen *name* as an in-memory database based on the
1205 If :func:`len(data) <len>` is larger than ``2**63 - 1``.
1216 This read-only attribute corresponds to the low-level SQLite
1227 <sqlite3-controlling-transactions>` performed by :mod:`!sqlite3`.
1232 <sqlite3-controlling-transactions>` is performed.
1246 See :ref:`sqlite3-howto-row-factory` for more details.
1266 cur.execute("SELECT ?", (AUSTRIA,))
1272 cur.execute("SELECT ?", (AUSTRIA,))
1275 # the bytestrings will be encoded in UTF-8, unless you stored garbage in the
1277 assert row[0] == AUSTRIA.encode("utf-8")
1281 con.text_factory = lambda x: x.decode("utf-8") + "foo"
1282 cur.execute("SELECT ?", ("bar",))
1294 .. _sqlite3-cursor-objects:
1300 which is used to execute SQL statements,
1304 <sqlite3-connection-shortcuts>`.
1307 meaning that if you :meth:`~Cursor.execute` a ``SELECT`` query,
1314 cur = con.execute("CREATE TABLE data(t)")
1315 cur.execute("INSERT INTO data VALUES(1)")
1319 for row in cur.execute("SELECT t FROM data"):
1336 .. method:: execute(sql, parameters=(), /)
1338 Execute SQL a single SQL statement,
1340 :ref:`placeholders <sqlite3-placeholders>`.
1349 See :ref:`sqlite3-placeholders`.
1360 Use :meth:`executescript` to execute multiple SQL statements.
1365 repeatedly execute the :ref:`parameterized <sqlite3-placeholders>`
1368 Uses the same implicit transaction handling as :meth:`~Cursor.execute`.
1376 See :ref:`sqlite3-placeholders`.
1403 Execute the SQL statements in *sql_script*.
1465 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1469 Required by the DB-API. Does nothing in :mod:`!sqlite3`.
1478 Read-only attribute that provides the SQLite database :class:`Connection`
1492 Read-only attribute that provides the column names of the last query. To
1493 remain compatible with the Python DB API, it returns a 7-tuple for each
1500 Read-only attribute that provides the row id of the last inserted row. It
1502 using the :meth:`execute` method. For other statements, after
1515 Read-only attribute that provides the number of modified rows for
1517 is ``-1`` for other statements,
1519 It is only updated by the :meth:`execute` and :meth:`executemany` methods,
1538 See :ref:`sqlite3-howto-row-factory` for more details.
1541 .. The sqlite3.Row example used to be a how-to. It has now been incorporated
1545 .. _sqlite3-columns-by-name:
1546 .. _sqlite3-row-objects:
1561 See :ref:`sqlite3-howto-row-factory` for more details.
1573 .. _sqlite3-blob-objects:
1582 A :class:`Blob` instance is a :term:`file-like object`
1593 con.execute("CREATE TABLE test(blob_col blob)")
1594 con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))")
1602 blob[-1] = ord("!")
1623 .. method:: read(length=-1, /)
1657 <sqlite3-conform>` to :ref:`native SQLite types <sqlite3-types>`.
1660 .. _sqlite3-exceptions:
1665 The exception hierarchy is defined by the DB-API 2.0 (:pep:`249`).
1671 for example if a user-defined function truncates data while inserting.
1699 Exception raised for misuse of the low-level SQLite C API.
1753 .. _sqlite3-types:
1763 +-------------------------------+-------------+
1767 +-------------------------------+-------------+
1769 +-------------------------------+-------------+
1771 +-------------------------------+-------------+
1773 +-------------------------------+-------------+
1775 +-------------------------------+-------------+
1780 +-------------+----------------------------------------------+
1784 +-------------+----------------------------------------------+
1786 +-------------+----------------------------------------------+
1788 +-------------+----------------------------------------------+
1791 +-------------+----------------------------------------------+
1793 +-------------+----------------------------------------------+
1797 :ref:`object adapters <sqlite3-adapters>`,
1799 Python types via :ref:`converters <sqlite3-converters>`.
1802 .. _sqlite3-default-converters:
1831 offset-aware converter with :func:`register_converter`.
1834 .. _sqlite3-howtos:
1836 How-to guides
1837 -------------
1839 .. _sqlite3-placeholders:
1849 >>> # Never do this -- insecure!
1851 ' OR TRUE; --
1854 SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
1855 >>> cur.execute(sql)
1857 Instead, use the DB-API's parameter substitution. To insert a variable into a
1860 argument of the cursor's :meth:`~Cursor.execute` method.
1876 cur = con.execute("CREATE TABLE lang(name, first_appeared)")
1889 cur.execute("SELECT * FROM lang WHERE first_appeared = ?", params)
1903 .. _sqlite3-adapters:
1910 :ref:`Python types SQLite natively understands <sqlite3-types>`.
1921 .. _sqlite3-conform:
1947 cur.execute("SELECT ?", (Point(4.0, -3.2),))
1953 4.0;-3.2
1960 to an SQLite-compatible type.
1977 cur.execute("SELECT ?", (Point(1.0, 2.5),))
1986 .. _sqlite3-converters:
2046 p = Point(4.0, -3.2)
2048 cur = con.execute("CREATE TABLE test(p point)")
2050 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2051 cur.execute("SELECT p FROM test")
2058 cur = con.execute("CREATE TABLE test(p)")
2060 cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
2061 cur.execute('SELECT p AS "p [point]" FROM test')
2067 with declared types: Point(4.0, -3.2)
2068 with column names: Point(4.0, -3.2)
2071 .. _sqlite3-adapter-converter-recipes:
2088 """Adapt datetime.datetime to timezone-naive ISO 8601 date."""
2120 assert adapt_date_iso(dt.date()) == "2019-05-18"
2121 assert convert_date(b"2019-05-18") == dt.date()
2123 assert adapt_datetime_iso(dt) == "2019-05-18T15:17:08.123456"
2124 assert convert_datetime(b"2019-05-18T15:17:08.123456") == dt
2135 .. _sqlite3-connection-shortcuts:
2140 Using the :meth:`~Connection.execute`,
2146 objects. This way, you can execute a ``SELECT`` statement and iterate over it
2153 con.execute("CREATE TABLE lang(name, first_appeared)")
2156 ("Objective-C", 1984),
2161 for row in con.execute("SELECT name, first_appeared FROM lang"):
2164 print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows")
2174 ('Objective-C', 1984)
2178 .. _sqlite3-connection-context-manager:
2193 the context manager is a no-op.
2203 con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
2207 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2213 con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
2227 .. _sqlite3-uri-tricks:
2234 * Open a database in read-only mode:
2239 >>> con.execute("CREATE TABLE readonly(data)")
2253 * Create a shared named in-memory database:
2261 con1.execute("CREATE TABLE shared(data)")
2262 con1.execute("INSERT INTO shared VALUES(28)")
2263 res = con2.execute("SELECT data FROM shared")
2273 .. _sqlite3-howto-row-factory:
2288 :class:`!Row` provides indexed and case-insensitive named access to columns,
2302 >>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius")
2310 >>> row["RADIUS"] # Column names are case-insensitive.
2328 >>> for row in con.execute("SELECT 1 AS a, 2 AS b"):
2349 >>> cur = con.execute("SELECT 1 AS a, 2 AS b")
2363 .. _sqlite3-explanation:
2366 -----------
2368 .. _sqlite3-controlling-transactions:
2379 :meth:`~Cursor.execute` and :meth:`~Cursor.executemany` executes