Source code: Lib/sqlite3/ Show
SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle. The This document includes four main sections:
See also https://www.sqlite.orgThe SQLite web page; the documentation describes the syntax and the available data types for the supported SQL dialect. https://www.w3schools.com/sql/Tutorial, reference and examples for learning SQL syntax. PEP 249 - Database API Specification 2.0PEP written by Marc-André Lemburg. Tutorial¶In this tutorial, you will create a database of Monty Python movies using basic First,
we need to create a new database and open a database connection to allow import sqlite3 con = sqlite3.connect("tutorial.db") The returned
In order to execute SQL statements and fetch results from SQL queries, we will need to use a database cursor. Call Now that we’ve got a database connection and a cursor, we can create a database table cur.execute("CREATE TABLE movie(title, year, score)") We can verify that the new table has been created by querying the >>> res = cur.execute("SELECT name FROM sqlite_master") >>> res.fetchone() ('movie',) We can see that the table has been created, as the query returns a
>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'") >>> res.fetchone() is None True Now, add two rows of data supplied as SQL literals by executing an cur.execute(""" INSERT INTO movie VALUES ('Monty Python and the Holy Grail', 1975, 8.2), ('And Now for Something Completely Different', 1971, 7.5) """) The We can verify that the data was inserted correctly by executing a >>> res = cur.execute("SELECT score FROM movie") >>> res.fetchall() [(8.2,), (7.5,)] The result is a Now, insert three more rows by calling data = [ ("Monty Python Live at the Hollywood Bowl", 1982, 7.9), ("Monty Python's The Meaning of Life", 1983, 7.5), ("Monty Python's Life of Brian", 1979, 8.0), ] cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data) con.commit() # Remember to commit the transaction after executing INSERT. Notice that We can
verify that the new rows were inserted by executing a >>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"): ... print(row) (1971, 'And Now for Something Completely Different') (1975, 'Monty Python and the Holy Grail') (1979, "Monty Python's Life of Brian") (1982, 'Monty Python Live at the Hollywood Bowl') (1983, "Monty Python's The Meaning of Life") Each row is a two-item Finally, verify that the database has been written to disk by calling
>>> con.close() >>> new_con = sqlite3.connect("tutorial.db") >>> new_cur = new_con.cursor() >>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC") >>> title, year = res.fetchone() >>> print(f'The highest scoring Monty Python movie is {title!r}, released in {year}') The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975 You’ve now created an SQLite database using the See also
Reference¶Module functions¶sqlite3. connect (database, timeout=5.0, detect_types=0,
isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=100, uri=False)¶
Open a connection to an SQLite database. Parameters
Connection Raises an auditing event Raises an auditing event New in version 3.4: The uri parameter. Changed in version 3.7: database can now also be a path-like object, not only a string. New in version 3.10: The sqlite3. complete_statement (statement)¶Return For example: >>> sqlite3.complete_statement("SELECT foo FROM bar;") True >>> sqlite3.complete_statement("SELECT foo") False This function may be useful during command-line input to determine if the entered text seems to form a complete SQL statement, or if additional input is needed before calling sqlite3. enable_callback_tracebacks (flag, /)¶Enable or disable callback tracebacks. By default you will not get any tracebacks in user-defined functions, aggregates, converters, authorizer callbacks etc. If you want to debug them, you can call
this function with flag set to sqlite3. register_adapter (type, adapter, /)¶Register an adapter callable to adapt the Python type type into an SQLite type. The adapter is called with a Python object of type type as its sole argument, and must return a value of a type that SQLite natively understands. sqlite3. register_converter (typename, converter,
/)¶Register the converter callable to convert SQLite objects of type typename into a Python object of a specific type. The converter is invoked for all SQLite values of type typename; it is passed a
Note: typename and the name of the type in your query are matched case-insensitively. Module constants¶sqlite3. PARSE_COLNAMES ¶Pass this flag value to the
detect_types parameter of SELECT p as "p [point]" FROM test; ! will look up converter "point" This flag may be combined with
sqlite3. PARSE_DECLTYPES ¶Pass this flag value to the detect_types parameter of
CREATE TABLE test( i integer primary key, ! will look up a converter named "integer" p point, ! will look up a converter named "point" n number(10) ! will look up a converter named "number" ) This flag may be combined with
sqlite3. SQLITE_OK ¶
sqlite3. SQLITE_DENY ¶ sqlite3. SQLITE_IGNORE ¶Flags that should be returned by the authorizer_callback callable passed to
sqlite3. apilevel ¶String constant stating the supported DB-API level. Required by the DB-API. Hard-coded to sqlite3. paramstyle ¶String
constant stating the type of parameter marker formatting expected by the Note The sqlite3. sqlite_version ¶Version number of the runtime SQLite library as a sqlite3. sqlite_version_info ¶Version number of the runtime SQLite library as a sqlite3. threadsafety ¶Integer constant required by the DB-API, stating the level of thread safety the import sqlite3 con = sqlite3.connect(":memory:") con.execute(""" select * from pragma_compile_options where compile_options like 'THREADSAFE=%' """).fetchall() Note that the SQLITE_THREADSAFE levels do not match the DB-API 2.0 sqlite3. version ¶Version number of this module as a
sqlite3. version_info ¶Version number of this module as a
Connection objects¶classsqlite3. Connection ¶Each open SQLite database is represented by a An SQLite database connection has the following attributes and methods: cursor (factory=Cursor)¶Create and return a commit ()¶Commit any pending transaction to the database. If there is no open transaction, this method is a no-op. rollback ()¶Roll back to the start of any pending transaction. If there is no open transaction, this method is a no-op. close ()¶Close the database connection. Any pending transaction is not committed implicitly; make sure to execute (sql, parameters=(), /)¶Create a new
executemany (sql,
parameters, /)¶Create a new executescript (sql_script,
/)¶Create a new create_function (name, narg, func, \*,
deterministic=False)¶Create or remove a user-defined SQL function. Parameters
NotSupportedError – If deterministic is used with SQLite versions older than 3.8.3. New in version 3.8: The deterministic parameter. Example: >>> import hashlib >>> def md5sum(t): ... return hashlib.md5(t).hexdigest() >>> con = sqlite3.connect(":memory:") >>> con.create_function("md5", 1, md5sum) >>> for row in con.execute("SELECT md5(?)", (b"foo",)): ... print(row) ('acbd18db4cc2f85cedef654fccc4a4d8',) create_aggregate (name, /, n_arg, aggregate_class)¶Create or remove a user-defined SQL aggregate function. Parameters
Example: class MySum: def __init__(self): self.count = 0 def step(self, value): self.count += value def finalize(self): return self.count con = sqlite3.connect(":memory:") con.create_aggregate("mysum", 1, MySum) cur = con.execute("CREATE TABLE test(i)") cur.execute("INSERT INTO test(i) VALUES(1)") cur.execute("INSERT INTO test(i) VALUES(2)") cur.execute("SELECT mysum(i) FROM test") print(cur.fetchone()[0]) con.close() create_collation (name, callable)¶Create a collation
named name using the collating function callable. callable is passed two
The following example shows a reverse sorting collation: def collate_reverse(string1, string2): if string1 == string2: return 0 elif string1 < string2: return 1 else: return -1 con = sqlite3.connect(":memory:") con.create_collation("reverse", collate_reverse) cur = con.execute("CREATE TABLE test(x)") cur.executemany("INSERT INTO test(x) VALUES(?)", [("a",), ("b",)]) cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse") for row in cur: print(row) con.close() Remove a collation function by setting callable to interrupt ()¶Call this method from a different thread to abort any queries that might be executing on the connection. Aborted queries will raise an exception. Register callable authorizer_callback to be invoked for each attempt to access a column of a table in the database. The callback should return one of The first argument to the callback signifies what kind of operation is to be authorized. The second and third argument will be arguments or Please consult the SQLite documentation about the possible values for the first argument and the meaning of the second and third argument depending on the first one. All necessary constants are available in the set_progress_handler (progress_handler, n)¶Register callable progress_handler to be invoked for every n instructions of the SQLite virtual machine. This is useful if you want to get called from SQLite during long-running operations, for example to update a GUI. If you want to clear any previously installed progress handler, call the method with Returning a non-zero value from the handler function will terminate the currently executing query and cause it to raise an set_trace_callback (trace_callback)¶Register callable trace_callback to be invoked for each SQL statement that is actually executed by the SQLite backend. The only argument passed to the callback is the statement (as
Passing Note Exceptions raised in the trace callback are not propagated. As a development and debugging aid, use
New in version 3.3. enable_load_extension (enabled,
/)¶Enable the SQLite engine to load SQLite extensions from shared libraries if enabled is Note The Raises an
auditing event New in version 3.2. Changed in version 3.10: Added the con.enable_load_extension(True) # Load the fulltext search extension con.execute("select load_extension('./fts3.so')") # alternatively you can load the extension using an API call: # con.load_extension("./fts3.so") # disable extension loading again con.enable_load_extension(False) # example from SQLite wiki con.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)") con.executescript(""" INSERT INTO recipe (name, ingredients) VALUES('broccoli stew', 'broccoli peppers cheese tomatoes'); INSERT INTO recipe (name, ingredients) VALUES('pumpkin stew', 'pumpkin onions garlic celery'); INSERT INTO recipe (name, ingredients) VALUES('broccoli pie', 'broccoli cheese onions flour'); INSERT INTO recipe (name, ingredients) VALUES('pumpkin pie', 'pumpkin sugar flour butter'); """) for row in con.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"): print(row) con.close() load_extension (path,
/)¶Load an SQLite extension from a shared library located at path. Enable extension loading with Raises an auditing event New in version 3.2. Changed in version 3.10: Added the iterdump ()¶Return an iterator to dump the database as SQL source code. Useful when saving an in-memory database for later restoration. Similar to the Example: # Convert file example.db to SQL dump file dump.sql con = sqlite3.connect('example.db') with open('dump.sql', 'w') as f: for line in con.iterdump(): f.write('%s\n' % line) con.close() backup (target, \*, pages=-1, progress=None, name="main", sleep=0.250)¶Create a backup of an SQLite database. Works even if the database is being accessed by other clients or concurrently by the same connection. Parameters
Example 1, copy an existing database into another: def progress(status, remaining, total): print(f'Copied {total-remaining} of {total} pages...') src = sqlite3.connect('example.db') dst = sqlite3.connect('backup.db') with dst: src.backup(dst, pages=1, progress=progress) dst.close() src.close() Example 2, copy an existing database into a transient copy: src = sqlite3.connect('example.db') dst = sqlite3.connect(':memory:') src.backup(dst) New in version 3.7. in_transaction ¶This read-only attribute corresponds to the low-level SQLite autocommit mode.
New in version 3.2. isolation_level ¶This attribute controls the transaction handling performed by If not overridden by the isolation_level parameter of
row_factory ¶A callable that accepts two arguments, a
Example: >>> def dict_factory(cursor, row): ... col_names = [col[0] for col in cursor.description] ... return {key: value for key, value in zip(col_names, row)} >>> con = sqlite3.connect(":memory:") >>> con.row_factory = dict_factory >>> for row in con.execute("SELECT 1 AS a, 2 AS b"): ... print(row) {'a': 1, 'b': 2} If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting
text_factory ¶A callable that accepts a Example: con = sqlite3.connect(":memory:") cur = con.cursor() AUSTRIA = "Österreich" # by default, rows are returned as str cur.execute("SELECT ?", (AUSTRIA,)) row = cur.fetchone() assert row[0] == AUSTRIA # but we can make sqlite3 always return bytestrings ... con.text_factory = bytes cur.execute("SELECT ?", (AUSTRIA,)) row = cur.fetchone() assert type(row[0]) is bytes # the bytestrings will be encoded in UTF-8, unless you stored garbage in the # database ... assert row[0] == AUSTRIA.encode("utf-8") # we can also implement a custom text_factory ... # here we implement one that appends "foo" to all strings con.text_factory = lambda x: x.decode("utf-8") + "foo" cur.execute("SELECT ?", ("bar",)) row = cur.fetchone() assert row[0] == "barfoo" con.close() total_changes ¶Return the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. Cursor objects¶class sqlite3. Cursor ¶A
execute (sql, parameters=(),
/)¶Execute SQL statement sql. Bind values to the statement using placeholders that map to the
sequence or
If
executemany (sql,
parameters, /)¶Execute parameterized SQL statement sql against all parameter sequences or mappings found in the sequence parameters. It is also possible to use
an iterator yielding parameters instead of a sequence. Uses the same implicit transaction handling as Example: rows = [ ("row1",), ("row2",), ] # cur is an sqlite3.Cursor object cur.executemany("INSERT INTO data VALUES(?)", rows) executescript (sql_script,
/)¶Execute the SQL statements in sql_script. If there is a pending transaction, an implicit sql_script must be a
Example: # cur is an sqlite3.Cursor object cur.executescript(""" BEGIN; CREATE TABLE person(firstname, lastname, age); CREATE TABLE book(title, author, published); CREATE TABLE publisher(name, address); COMMIT; """) fetchone ()¶If
fetchmany (size=cursor.arraysize)¶Return the next set of rows of a query result as a The number of
rows to fetch per call is specified by the size parameter. If size is not given, Note there are performance considerations involved with the size parameter. For optimal performance, it
is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one fetchall ()¶Return all (remaining) rows of a query result as a close ()¶Close the cursor now (rather than whenever The cursor will be unusable from this point forward; a setinputsizes (sizes,
/)¶Required by the DB-API. Does nothing in setoutputsize (size, column=None,
/)¶Required by the DB-API. Does nothing in arraysize ¶Read/write attribute that controls the number
of rows returned by connection ¶Read-only attribute that provides the SQLite
database >>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True description ¶Read-only attribute that provides the column names of the last query. To remain
compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are It is set for lastrowid ¶Read-only attribute that provides the row id of the last inserted row. It is only
updated after successful Note Inserts into Changed in version 3.6: Added support for the rowcount ¶Read-only attribute that provides the number of modified rows for Row objects¶classsqlite3. Row ¶A Two row objects compare equal if have equal columns and equal members. keys ()¶Return a
Changed in version 3.5: Added support of slicing. Example: >>> con = sqlite3.connect(":memory:") >>> con.row_factory = sqlite3.Row >>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius") >>> row = res.fetchone() >>> row.keys() ['name', 'radius'] >>> row[0], row["name"] # Access by index and name. ('Earth', 'Earth') >>> row["RADIUS"] # Column names are case-insensitive. 6378 PrepareProtocol objects¶classsqlite3. PrepareProtocol ¶The PrepareProtocol type’s single purpose is to act as a PEP 246 style adaption protocol for objects that can adapt themselves to native SQLite types. Exceptions¶The exception hierarchy is defined by the DB-API 2.0 (PEP 249). exceptionsqlite3. Warning ¶This exception is raised by sqlite3. Error ¶The base class of the other exceptions in this module. Use this to catch all errors with one single
sqlite3. InterfaceError ¶This
exception is raised by sqlite3. DatabaseError ¶Exception
raised for errors that are related to the database. This serves as the base exception for several types of database errors. It is only raised implicitly through the specialised subclasses. sqlite3. DataError ¶Exception raised for errors caused by problems with the processed data, like numeric values out of range, and strings which are too long. sqlite3. OperationalError ¶Exception raised for errors that are related to the database’s operation, and not necessarily under the control of the programmer. For example, the database path is not found, or a transaction could not be processed. sqlite3. IntegrityError ¶Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It is a subclass
of sqlite3. InternalError ¶Exception raised when SQLite encounters an internal error. If this is raised, it may indicate that there is a problem with
the runtime SQLite library. sqlite3. ProgrammingError ¶Exception raised for sqlite3. NotSupportedError ¶Exception raised in case a method or database API is not supported by the underlying SQLite library. For example,
setting deterministic to SQLite and Python types¶SQLite natively supports the following types: The following Python types can thus be sent to SQLite without any problem:
This is how SQLite types are converted to Python types by default:
The type system of the Default adapters and converters¶There are default adapters for the date and datetime types in the datetime module. They will be sent as ISO dates/ISO timestamps to SQLite. The default converters are registered under the name “date” for
This way, you can use date/timestamps from Python without any additional fiddling in most cases. The format of the adapters is also compatible with the experimental SQLite date/time functions. The following example demonstrates this. import sqlite3 import datetime con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) cur = con.cursor() cur.execute("create table test(d date, ts timestamp)") today = datetime.date.today() now = datetime.datetime.now() cur.execute("insert into test(d, ts) values (?, ?)", (today, now)) cur.execute("select d, ts from test") row = cur.fetchone() print(today, "=>", row[0], type(row[0])) print(now, "=>", row[1], type(row[1])) cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"') row = cur.fetchone() print("current_date", row[0], type(row[0])) print("current_timestamp", row[1], type(row[1])) con.close() If a timestamp stored in SQLite has a fractional part longer than 6 numbers, its value will be truncated to microsecond precision by the timestamp converter. Note The default “timestamp” converter ignores UTC offsets in the database and always returns a naive How-to guides¶How to use placeholders to bind values in SQL queries¶SQL operations usually need to use values from Python variables. However, beware of using Python’s string operations to assemble queries, as they are vulnerable to SQL injection attacks (see the xkcd webcomic for a humorous example of what can go wrong): # Never do this -- insecure! symbol = 'RHAT' cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol) Instead, use the DB-API’s parameter substitution. To insert a variable into a query string, use a placeholder in the string, and substitute the actual values into the query by providing them as a
con = sqlite3.connect(":memory:") cur = con.execute("CREATE TABLE lang(name, first_appeared)") # This is the qmark style: cur.execute("INSERT INTO lang VALUES(?, ?)", ("C", 1972)) # The qmark style used with executemany(): lang_list = [ ("Fortran", 1957), ("Python", 1991), ("Go", 2009), ] cur.executemany("INSERT INTO lang VALUES(?, ?)", lang_list) # And this is the named style: cur.execute("SELECT * FROM lang WHERE first_appeared = :year", {"year": 1972}) print(cur.fetchall()) How to adapt custom Python types to SQLite values¶SQLite supports only a limited set of data types natively. To store custom Python types in SQLite databases, adapt them to one of the Python types SQLite natively understands. There are two ways to adapt Python objects to SQLite types: letting your object adapt itself, or using an adapter callable. The latter will take precedence above the former. For a library that exports a custom type, it may make sense to enable that type to adapt itself. As an application developer, it may make more sense to take direct control by registering custom adapter functions. How to write adaptable objects¶Suppose we have a class Point: def __init__(self, x, y): self.x, self.y = x, y def __conform__(self, protocol): if protocol is sqlite3.PrepareProtocol: return f"{self.x};{self.y}" con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("SELECT ?", (Point(4.0, -3.2),)) print(cur.fetchone()[0]) How to register adapter callables¶The other possibility is to create a function that converts the Python object to an SQLite-compatible type. This function can then be registered using class Point: def __init__(self, x, y): self.x, self.y = x, y def adapt_point(point): return f"{point.x};{point.y}" sqlite3.register_adapter(Point, adapt_point) con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("SELECT ?", (Point(1.0, 2.5),)) print(cur.fetchone()[0]) How to convert SQLite values to custom Python types¶Writing an adapter lets you convert from custom Python types to SQLite values. To be able to convert from SQLite values to custom Python types, we use converters. Let’s go back to the First, we’ll define a converter function that accepts the string as a parameter and constructs a Note Converter functions are always passed a def convert_point(s): x, y = map(float, s.split(b";")) return Point(x, y) We now need to tell
The following example illustrates the implicit and explicit approaches: class Point: def __init__(self, x, y): self.x, self.y = x, y def __repr__(self): return f"Point({self.x}, {self.y})" def adapt_point(point): return f"{point.x};{point.y}".encode("utf-8") def convert_point(s): x, y = list(map(float, s.split(b";"))) return Point(x, y) # Register the adapter and converter sqlite3.register_adapter(Point, adapt_point) sqlite3.register_converter("point", convert_point) # 1) Parse using declared types p = Point(4.0, -3.2) con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES) cur = con.execute("CREATE TABLE test(p point)") cur.execute("INSERT INTO test(p) VALUES(?)", (p,)) cur.execute("SELECT p FROM test") print("with declared types:", cur.fetchone()[0]) cur.close() con.close() # 2) Parse using column names con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES) cur = con.execute("CREATE TABLE test(p)") cur.execute("INSERT INTO test(p) VALUES(?)", (p,)) cur.execute('SELECT p AS "p [point]" FROM test') print("with column names:", cur.fetchone()[0]) Adapter and converter recipes¶This section shows recipes for common adapters and converters. import datetime import sqlite3 def adapt_date_iso(val): """Adapt datetime.date to ISO 8601 date.""" return val.isoformat() def adapt_datetime_iso(val): """Adapt datetime.datetime to timezone-naive ISO 8601 date.""" return val.isoformat() def adapt_datetime_epoch(val): """Adapt datetime.datetime to Unix timestamp.""" return int(val.timestamp()) sqlite3.register_adapter(datetime.date, adapt_date_iso) sqlite3.register_adapter(datetime.datetime, adapt_datetime_iso) sqlite3.register_adapter(datetime.datetime, adapt_datetime_epoch) def convert_date(val): """Convert ISO 8601 date to datetime.date object.""" return datetime.date.fromisoformat(val) def convert_datetime(val): """Convert ISO 8601 datetime to datetime.datetime object.""" return datetime.datetime.fromisoformat(val) def convert_timestamp(val): """Convert Unix epoch timestamp to datetime.datetime object.""" return datetime.datetime.fromtimestamp(val) sqlite3.register_converter("date", convert_date) sqlite3.register_converter("datetime", convert_datetime) sqlite3.register_converter("timestamp", convert_timestamp) How to use connection shortcut methods¶Using the # Create and fill the table. con = sqlite3.connect(":memory:") con.execute("CREATE TABLE lang(name, first_appeared)") data = [ ("C++", 1985), ("Objective-C", 1984), ] con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)", data) # Print the table contents for row in con.execute("SELECT name, first_appeared FROM lang"): print(row) print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows") # close() is not a shortcut method and it's not called automatically; # the connection object should be closed manually con.close() How to use the connection context manager¶A If there is no open transaction upon leaving the body of the Note The context manager neither implicitly opens a new transaction nor closes the connection. con = sqlite3.connect(":memory:") con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)") # Successful, con.commit() is called automatically afterwards with con: con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",)) # con.rollback() is called after the with block finishes with an exception, # the exception is still raised and must be caught try: with con: con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",)) except sqlite3.IntegrityError: print("couldn't add Python twice") # Connection object used as context manager only commits or rollbacks transactions, # so the connection object should be closed manually con.close() How to work with SQLite URIs¶Some useful URI tricks include:
>>> con = sqlite3.connect("file:tutorial.db?mode=ro", uri=True) >>> con.execute("CREATE TABLE readonly(data)") Traceback (most recent call last): OperationalError: attempt to write a readonly database
>>> con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True) Traceback (most recent call last): OperationalError: unable to open database file
db = "file:mem1?mode=memory&cache=shared" con1 = sqlite3.connect(db, uri=True) con2 = sqlite3.connect(db, uri=True) with con1: con1.execute("CREATE TABLE shared(data)") con1.execute("INSERT INTO shared VALUES(28)") res = con2.execute("SELECT data FROM shared") assert res.fetchone() == (28,) More information about this feature, including a list of parameters, can be found in the SQLite URI documentation. Explanation¶Transaction control¶The If the connection attribute If
The Changed in version 3.6: |