class sqlite3.Connection
A SQLite database connection has the following attributes and methods:
-
isolation_level
-
Get or set the current isolation level.
None
for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See section Controlling Transactions for a more detailed explanation.
-
in_transaction
-
True
if a transaction is active (there are uncommitted changes),False
otherwise. Read-only attribute.New in version 3.2.
-
cursor([cursorClass])
-
The cursor method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends
sqlite3.Cursor
.
-
commit()
-
This method commits the current transaction. If you don’t call this method, anything you did since the last call to
commit()
is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.
-
rollback()
-
This method rolls back any changes to the database since the last call to
commit()
.
-
close()
-
This closes the database connection. Note that this does not automatically call
commit()
. If you just close your database connection without callingcommit()
first, your changes will be lost!
-
execute(sql[, parameters])
-
This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor’sexecute()
method with the parameters given, and returns the cursor.
-
executemany(sql[, parameters])
-
This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor’sexecutemany()
method with the parameters given, and returns the cursor.
-
executescript(sql_script)
-
This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor’sexecutescript()
method with the given sql_script, and returns the cursor.
-
create_function(name, num_params, func)
-
Creates a user-defined function that you can later use from within SQL statements under the function name name. num_params is the number of parameters the function accepts (if num_params is -1, the function may take any number of arguments), and func is a Python callable that is called as the SQL function.
The function can return any of the types supported by SQLite: bytes, str, int, float and None.
Example:
import sqlite3 import hashlib def md5sum(t): return hashlib.md5(t).hexdigest() con = sqlite3.connect(":memory:") con.create_function("md5", 1, md5sum) cur = con.cursor() cur.execute("select md5(?)", (b"foo",)) print(cur.fetchone()[0])
-
create_aggregate(name, num_params, aggregate_class)
-
Creates a user-defined aggregate function.
The aggregate class must implement a
step
method, which accepts the number of parameters num_params (if num_params is -1, the function may take any number of arguments), and afinalize
method which will return the final result of the aggregate.The
finalize
method can return any of the types supported by SQLite: bytes, str, int, float and None.Example:
import sqlite3 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.cursor() cur.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])
-
create_collation(name, callable)
-
Creates a collation with the specified name and callable. The callable will be passed two string arguments. It should return -1 if the first is ordered lower than the second, 0 if they are ordered equal and 1 if the first is ordered higher than the second. Note that this controls sorting (ORDER BY in SQL) so your comparisons don’t affect other SQL operations.
Note that the callable will get its parameters as Python bytestrings, which will normally be encoded in UTF-8.
The following example shows a custom collation that sorts “the wrong way”:
import sqlite3 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.cursor() cur.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()
To remove a collation, call
create_collation
with None as callable:con.create_collation("reverse", None)
-
interrupt()
-
You can call this method from a different thread to abort any queries that might be executing on the connection. The query will then abort and the caller will get an exception.
-
set_authorizer(authorizer_callback)
-
This routine registers a callback. The callback is invoked for each attempt to access a column of a table in the database. The callback should return
SQLITE_OK
if access is allowed,SQLITE_DENY
if the entire SQL statement should be aborted with an error andSQLITE_IGNORE
if the column should be treated as a NULL value. These constants are available in thesqlite3
module.The first argument to the callback signifies what kind of operation is to be authorized. The second and third argument will be arguments or
None
depending on the first argument. The 4th argument is the name of the database (“main”, “temp”, etc.) if applicable. The 5th argument is the name of the inner-most trigger or view that is responsible for the access attempt orNone
if this access attempt is directly from input SQL code.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
sqlite3
module.
-
set_progress_handler(handler, n)
-
This routine registers a callback. The callback is 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
None
for handler.
-
set_trace_callback(trace_callback)
-
Registers trace_callback to be called for each SQL statement that is actually executed by the SQLite backend.
The only argument passed to the callback is the statement (as string) that is being executed. The return value of the callback is ignored. Note that the backend does not only run statements passed to the
Cursor.execute()
methods. Other sources include the transaction management of the Python module and the execution of triggers defined in the current database.Passing
None
as trace_callback will disable the trace callback.New in version 3.3.
-
enable_load_extension(enabled)
-
This routine allows/disallows the SQLite engine to load SQLite extensions from shared libraries. SQLite extensions can define new functions, aggregates or whole new virtual table implementations. One well-known extension is the fulltext-search extension distributed with SQLite.
Loadable extensions are disabled by default. See [1].
New in version 3.2.
import sqlite3 con = sqlite3.connect(":memory:") # enable extension loading 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 laoding 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)
-
load_extension(path)
-
This routine loads a SQLite extension from a shared library. You have to enable extension loading with
enable_load_extension()
before you can use this routine.Loadable extensions are disabled by default. See [1].
New in version 3.2.
-
row_factory
-
You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.
Example:
import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con = sqlite3.connect(":memory:") con.row_factory = dict_factory cur = con.cursor() cur.execute("select 1 as a") print(cur.fetchone()["a"])
If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting
row_factory
to the highly-optimizedsqlite3.Row
type.Row
provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.
-
text_factory
-
Using this attribute you can control what objects are returned for the
TEXT
data type. By default, this attribute is set tostr
and thesqlite3
module will return Unicode objects forTEXT
. If you want to return bytestrings instead, you can set it tobytes
.You can also set it to any other callable that accepts a single bytestring parameter and returns the resulting object.
See the following example code for illustration:
import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() AUSTRIA = "\xd6sterreich" # by default, rows are returned as Unicode 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"
-
total_changes
-
Returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.
-
iterdump()
-
Returns an iterator to dump the database in an SQL text format. Useful when saving an in-memory database for later restoration. This function provides the same capabilities as the
.dump
command in the sqlite3 shell.Example:
# Convert file existing_db.db to SQL dump file dump.sql import sqlite3 con = sqlite3.connect('existing_db.db') with open('dump.sql', 'w') as f: for line in con.iterdump(): f.write('%s\n' % line)
Please login to continue.