Migrating from sqlite3¶
Connections¶
Before:
db = sqlite3.connect('app.db', timeout=2)
After using connect():
db = cysqlite.connect('app.db', timeout=2)
Additional things you can do:
db = cysqlite.connect(
'app.db',
journal_mode='wal',
pragmas={'cache_size': -64000, 'foreign_keys': 1})
assert db.pragma('foreign_keys') == 1
Not supported:
isolation_level/autocommit- cysqlite operates in autocommit mode. Transactions may be opened by issuing aBEGINstatement, using thebegin()method, or preferably using the nestableatomic()context-manager/decorator.detect_types- column data-types are detected for the purposes of calling any user-defined data-type converters.check_same_thread- connections may be shared across threads.
Worth noting:
close()raisesOperationalErrorif a transaction is open, whilesqlite3discards the transaction.force=Truerolls back and closes.URIs are handled transparently by cysqlite, e.g.
file:/path.db?mode=ro.
Context manager¶
Before:
with sqlite3.connect('app.db') as db:
# The first write opens a transaction.
...
# stdlib commits on success or rolls back on failure. The connection
# is left open.
After:
with cysqlite.connect('app.db') as db:
# No transaction is automatically opened.
...
# Connection is closed. It can be reopened via db.connect().
To emulate stdlib’s behavior:
db = cysqlite.connect('app.db')
with db.atomic():
db.execute('insert into users (name) values (?)', ('alice',))
Executing queries¶
execute(), executemany() and
executescript() exist on both the connection and the cursor,
with the same parameter styles:
db.execute('create table users (id integer primary key, name text unique)')
db.executemany('insert into users (name) values (?)',
[('alice',), ('bob',)])
curs = db.cursor()
curs.execute('select id from users where name = ?', ('alice',)).fetchone()
# (1,)
curs.execute('select id from users where name = :name',
{'name': 'bob'}).fetchone()
# (2,)
Unlike stdlib, executescript() does not implicitly commit or
otherwise add its own transaction.
Parameter binding¶
Binding works the same, but cysqlite handles more types out-of-the-box:
datetime,dateandtimevalues bind as ISO-8601 textDecimalandFractionbind a floating-pointUUIDvalues bind as text
db.execute_one('select ?, ?, ?, ?', (
datetime.date(2026, 1, 2),
datetime.time(3, 4, 5),
Decimal('1.5'),
uuid.UUID(int=1)))
# ('2026-01-02', '03:04:05', 1.5, '00000000-0000-0000-0000-000000000001')
Reading results¶
Iteration, fetchone() and fetchall() work the same.
cysqlite also has additional helpers that return the first row or value
directly (execute_one() and execute_scalar()):
db.execute_one('select id, name from users where name = ?', ('bob',))
# (2, 'bob')
db.execute_scalar('select count(*) from users')
# 2
fetchmany() requires a size:
curs = db.execute('select name from users')
curs.fetchmany(10)
# [('alice',), ('bob',)]
Each description entry consists only of the column name, where
sqlite3 pads it to seven items with None. Use columns()
to retrieve the names as a list:
curs = db.execute('select id, name from users')
curs.description
# (('id',), ('name',))
curs.columns()
# ['id', 'name']
Replace sqlite3.Row with Row:
db.row_factory = cysqlite.Row
# Or:
db = cysqlite.connect('app.db', row_factory=cysqlite.Row)
Transactions¶
cysqlite runs in autocommit mode and will not implicitly begin or commit transactions. Statements outside a transaction commit as soon as they run. See Transactions for details.
Before:
# stdlib begins a transaction implicitly.
db.execute('update accounts set balance = balance - 10 where id = 1')
db.execute('update accounts set balance = balance + 10 where id = 2')
db.commit() # Without this, close() discards both updates.
After using atomic():
# cysqlite has explicit transaction boundaries.
with db.atomic():
db.execute('update accounts set balance = balance - 10 where id = 1')
db.execute('update accounts set balance = balance + 10 where id = 2')
# Or:
db.begin()
db.execute('update accounts set balance = balance - 10 where id = 1')
db.execute('update accounts set balance = balance + 10 where id = 2')
db.commit()
Additional things you can do:
with db.atomic(lock='IMMEDIATE'): # Replaces isolation_level.
db.execute('insert into users (name) values (?)', ('carol',))
try:
with db.atomic(): # Nested blocks use savepoints.
db.execute('insert into users (name) values (?)', ('alice',))
except cysqlite.IntegrityError:
pass # Only the inner block is rolled back.
Worth noting:
commit()androllback()raiseOperationalErrorwhen no transaction is open, wheresqlite3does nothing.autocommit()is a method, not the attributesqlite3added in Python 3.12.
Adapters and converters¶
Adapters and converters are registered on the connection, not globally on the module. Converters match the declared column type, as described in Converters.
Before:
sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_converter('json', json.loads)
db = sqlite3.connect('app.db', detect_types=sqlite3.PARSE_DECLTYPES)
After:
db = cysqlite.connect('app.db')
db.register_adapter(dict, json.dumps)
db.register_converter('json', json.loads)
Not supported:
PARSE_COLNAMES- column-alias type hints have no equivalent.__conform__()- register an adapter for the class instead.
Errors¶
DB-API exception hierarchy is preserved. Constraint violations and I/O errors raise a subclass based on the SQLite error code.
Before:
try:
db.execute('insert into users (name) values (?)', ('alice',))
except sqlite3.IntegrityError as exc:
if exc.sqlite_errorname != 'SQLITE_CONSTRAINT_UNIQUE':
raise
After:
try:
db.execute('insert into users (name) values (?)', ('alice',))
except cysqlite.UniqueIntegrityError: # Subclass of IntegrityError.
pass
Not supported:
sqlite_errorname-sqlite_errorcodestores the extended result code, as insqlite3.
User-defined functions¶
The callable comes first, name defaults to its __name__ and nargs
defaults to -1 (any number of arguments). The same goes for aggregates, window
functions and collations.
Before:
db.create_function('title_case', 1, title_case)
db.create_aggregate('product', 1, Product)
db.create_window_function('mysum', 1, MySum)
db.create_collation('reverse', collate_reverse)
After:
db.create_function(title_case)
db.create_aggregate(Product)
db.create_window_function(MySum, 'mysum')
db.create_collation(collate_reverse, 'reverse')
Worth noting:
Functions are registered with
deterministic=Trueby default, wheresqlite3usesFalse. Passdeterministic=Falsefor a function whose result can change between calls with the same arguments.sqlite3.enable_callback_tracebacks()becomes the per-connectionprint_callback_tracebacksattribute.The exception a callback raised is chained as the
__cause__of the error the query raises.
BLOBs¶
Before:
blob = db.blobopen('files', 'data', 1)
After using blob_open():
blob = db.blob_open('files', 'data', 1)
Additional things you can do:
Backup and serialization¶
serialize() and deserialize() work the
same. The backup() progress callback takes different
arguments.
Before:
db.backup(dest, progress=lambda status, remaining, total: ...)
After:
db.backup(dest, progress=lambda remaining, total, done: ...)
Not supported:
The
sleepargument tobackup().iterdump().
Additional things you can do:
db.backup_to_file('copy.db')
Extensions, limits and configuration¶
load_extension() works without calling
enable_load_extension() first. getlimit() and
setlimit() work the same.
Before:
db.setconfig(sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY, True)
db.getconfig(sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY)
After using db_config():
db.db_config(cysqlite.SQLITE_DBCONFIG_ENABLE_FKEY, 1)
db.db_config(cysqlite.SQLITE_DBCONFIG_ENABLE_FKEY)
Additional things you can do:
Pass
extensions=Falsetoconnect()to disable extension loading.Common settings have their own methods, such as
set_foreign_keys_enabled().
Additional features¶
Reconnecting¶
connect() reopens a closed connection. Any functions,
aggregates, collations or table functions registered on it are re-registered
automatically:
db.create_function(title_case)
db.close()
db.connect()
db.execute_scalar("select title_case('hello')")
# 'Hello'
Table-valued functions¶
A generator can be queried as a table. Subclass TableFunction for
writable tables. See User-defined functions and hooks:
@db.table_function(columns=['value'])
def series(start, stop, step=1):
for i in range(start, stop, step):
yield (i,)
db.execute('select value from series(0, 10, 3)').fetchall()
# [(0,), (3,), (6,), (9,)]
Commit, rollback and update hooks¶
commit_hook() and rollback_hook() run at
transaction boundaries. update_hook() runs for each changed
row:
changes = []
def on_update(op, dbname, table, rowid):
changes.append((op, table, rowid))
db.update_hook(on_update)
db.execute('insert into users (name) values (?)', ('carol',))
changes
# [('INSERT', 'users', 3)]
Busy handler¶
set_busy_handler() replaces the busy timeout with a handler
that retries more often, with jitter:
db.set_busy_handler(timeout=10)
Introspection¶
cysqlite provides a number of helpers for introspecting the schema:
db.get_tables()
# ['users']
db.get_primary_keys('users')
# ['id']
Maintenance¶
checkpoint()andset_autocheckpoint()control the WAL.optimize()runsPRAGMA optimize.
db.attach('archive.db', 'archive')
db.get_tables('archive')
# ['users_2020']
db.detach('archive')
db.checkpoint(truncate=True)
# (0, 0)
db.optimize()
Diagnostics¶
status()andConnection.status()report SQLite’s memory and cache counters.compile_option()andvfs_list()describe the SQLite build.
current, highwater = cysqlite.status(cysqlite.SQLITE_STATUS_MEMORY_USED)
current, highwater = db.status(cysqlite.SQLITE_DBSTATUS_CACHE_USED)
cysqlite.compile_option('ENABLE_FTS5')
# 1
'unix' in cysqlite.vfs_list()
# True
Slow query log¶
cysqlite.utils.slow_query_log() logs statements that run longer than a
threshold:
from cysqlite.utils import slow_query_log
slow_query_log(db, threshold_ms=100)
Async¶
cysqlite.aio runs queries on a worker thread for use with asyncio. See
Async I/O for its caveats:
from cysqlite.aio import connect
async def main():
async with connect('app.db') as db:
return await db.execute_scalar('select count(*) from users')
asyncio.run(main())
# 2
Builds¶
cysqlite can bundle its own SQLite, or build against SQLCipher or SQLite3 Multiple Ciphers. See Installation.