Cysqlite

cysqlite

cysqlite provides performant bindings to SQLite. cysqlite aims to be roughly compatible with the behavior of the standard lib sqlite3 module, but is closer in spirit to apsw, just with fewer features.

cysqlite supports standalone builds or dynamic-linking with the system SQLite.

Documentation

Overview

cysqlite is a Cython-based SQLite driver that provides:

• DB-API 2.0 compatible
• Performant query execution
• Transaction management with context-managers and decorators
• User-defined functions, aggregates, window functions, and virtual tables
• BLOB support
• Row objects with dict-like access
• Schema introspection utilities
• Asyncio support

Installing

cysqlite can be installed as a pre-built binary wheel with SQLite embedded into the module:

pip install cysqlite

cysqlite can be installed from a source distribution (sdist) which will link against the system SQLite:

# Link against the system sqlite.
pip install --no-binary :all: cysqlite

If you wish to build cysqlite with encryption support, you can create a self-contained build that embeds SQLCipher. At the time of writing SQLCipher does not provide a source amalgamation, so cysqlite includes a script to build an amalgamation and place the sources into the root of your checkout:

# Obtain checkout of cysqlite.
git clone https://github.com/coleifer/cysqlite

# Automatically download latest source amalgamation.
cd cysqlite/
./scripts/fetch_sqlcipher  # Will add sqlite3.c and sqlite3.h in checkout.

# Build self-contained cysqlite with SQLCipher embedded.
pip install .

Alternately, you can create a self-contained build that embeds SQLite3 Multiple Ciphers:

1. Obtain the latest *amalgamation.zip from the sqlite3mc releases page
2. Extract sqlite3mc_amalgamation.c and sqlite3mc_amalgamation.h into the root of the cysqlite checkout.
3. Run pip install .

Example

Example usage:

from cysqlite import connect

db = connect(':memory:')

db.execute('create table data (k, v)')

with db.atomic():
    db.executemany('insert into data (k, v) values (?, ?)',
                   [(f'k{i:02d}', f'v{i:02d}') for i in range(10)])
    print(db.last_insert_rowid())  # 10.

curs = db.execute('select * from data')
for row in curs:
    print(row)  # e.g., ('k00', 'v00')

# We can use named parameters with a dict as well.
row = db.execute_one('select * from data where k = :key and v = :val',
                     {'key': 'k05', 'val': 'v05'})
print(row)  # ('k05', 'v05')

db.close()