Migrating from sqlite3

A side-by-side reference for switching from the stdlib sqlite3 module to cysqlite. For the design rationale, see SQLite Notes.

Connection

stdlib sqlite3

cysqlite

 
db = sqlite3.connect('app.db')

db = cysqlite.connect('app.db')

db = sqlite3.connect('app.db')
db.execute('PRAGMA journal_mode=WAL')

db = cysqlite.connect(
    'app.db',
    journal_mode='wal')

Transactions

The stdlib opens transactions implicitly by string-matching DML statements. cysqlite operates in pure autocommit and uses an explicit context manager. atomic() uses a real transaction at the outer level and a savepoint when nested — no manual SAVEPOINT needed.

stdlib sqlite3

cysqlite

 
db.execute(
    'INSERT INTO t VALUES (?)', (1,))
db.commit()

with db.atomic():
    db.execute(
        'INSERT INTO t VALUES (?)', (1,))

with db:
    db.execute(...)

with db.atomic():
    db.execute(...)

db.execute('SAVEPOINT sp')
try:
    ...
    db.execute('RELEASE SAVEPOINT sp')
except Exception:
    db.execute('ROLLBACK TO SAVEPOINT sp')
    raise

with db.atomic():       # nests freely
    with db.atomic():
        ...

Row factories

cysqlite’s Row adds attribute access and .as_dict() on top of the stdlib’s index/name/keys interface. Note that name lookup is case-sensitive in cysqlite, case-insensitive in stdlib.

stdlib sqlite3

cysqlite

 
db.row_factory = sqlite3.Row
row = db.execute(...).fetchone()
row[0]; row['name']; row.keys()

db.row_factory = cysqlite.Row
row = db.execute(...).fetchone()
row[0]; row['name']; row.name
row.as_dict()

def dict_factory(cur, row):
    cols = [d[0]
            for d in cur.description]
    return dict(zip(cols, row))
db.row_factory = dict_factory

db.row_factory = cysqlite.dict_factory

Adapters and converters

stdlib registers globally; cysqlite registers per connection. There is no detect_types flag — converters always consult the declared column type.

stdlib sqlite3

cysqlite

 
sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_converter('JSON',
                           json.loads)
db = sqlite3.connect(
    'x.db',
    detect_types=sqlite3.PARSE_DECLTYPES)

db = cysqlite.connect('x.db')
db.register_adapter(dict, json.dumps)
db.register_converter('JSON', json.loads)

executescript

stdlib implicitly commits any open transaction first; cysqlite does not. Wrap explicitly if you want script-as-atomic-unit.

stdlib sqlite3

cysqlite

 
db.executescript(schema_sql)

with db.atomic():
    db.executescript(schema_sql)

Error handling

cysqlite splits IntegrityError and OperationalError along the extended error code, so you can catch the specific constraint kind that fired (e.g. duplicate PRIMARY KEY raises PrimaryKeyIntegrityError, not UniqueIntegrityError).

stdlib sqlite3

cysqlite

 
try:
    db.execute(...)
except sqlite3.IntegrityError as e:
    if e.sqlite_errorname == \
       'SQLITE_CONSTRAINT_UNIQUE':
        ...

from cysqlite import \
    UniqueIntegrityError
try:
    db.execute(...)
except UniqueIntegrityError:
    ...

Available subclasses:

  • IntegrityError: UniqueIntegrityError, NotNullIntegrityError, ForeignKeyIntegrityError, CheckIntegrityError, PrimaryKeyIntegrityError

  • OperationalError: DiskFullError, ReadOnlyError, DatabaseLockedError, AuthorizationError

Exception messages include the failing SQL and both error codes:

UniqueIntegrityError: error executing query:
[INSERT INTO users(email) VALUES (?)]
UNIQUE constraint failed: users.email (code=19, ext=2067)

User-defined functions

cysqlite takes the callable first and the name as an optional keyword argument.

stdlib sqlite3

cysqlite

 
db.create_function(
    'doubler', 1, lambda x: x * 2,
    deterministic=True)

db.create_function(
    lambda x: x * 2,
    name='doubler', nargs=1,
    deterministic=True)

db.create_aggregate('avg2', 1, Avg)

db.create_aggregate(
    Avg, name='avg2', nargs=1)

BLOBs

stdlib sqlite3 (3.11+)

cysqlite

 
with db.blobopen(
        't', 'data', rowid) as blob:
    blob.read(100)

with db.blob_open(
        't', 'data', rowid) as blob:
    blob.read(100)
    blob.readline()       # full RawIOBase
    blob.reopen(other_rowid)

Behaviors that change in your tests

  • commit() and rollback() raise OperationalError when no transaction is open. Wrap writes in with db.atomic(): and you won’t call them directly.

  • executescript runs inside any open transaction rather than implicitly committing. Wrap with atomic() if you want it atomic.

  • cysqlite.Row is case-sensitive on string lookup; stdlib’s is case-insensitive.

  • Anywhere you parsed an IntegrityError message, catch the exact subclass instead.

What you gain