SQLite Notes

SQLite’s type system is different from other databases. SQLite stores data in five simple types:

Python type	SQLite type
None	NULL
int, bool	INTEGER
float, Decimal	REAL
str	TEXT
bytes / buffers	BLOB

Notably SQLite does not natively support:

• datetime / date - store these as ISO-formatted text or as unix timestamps.

• fixed-precision decimals - no native type. cysqlite stores Decimal as REAL by default, which can lose precision for values that aren’t exactly representable as floats. Register a custom adapter to store as TEXT if exact precision is required.

• boolean - emulated as integer 1 and 0.

• json - SQLite has JSON support, but it is stored as TEXT or BLOB (for JSONB).

SQLite will store any value in a column regardless of its declared type, using type affinity to influence how the data is stored. Strict typing is opt-in with strict tables (requires SQLite 3.37.0+).

For convenience, cysqlite applies the following rules for adapting Python types to match SQLite’s available data-types:

Python type	SQLite type
datetime	TEXT (isoformat with ‘ ‘ delimiter).
date	TEXT (isoformat)
Fraction, Decimal, __float__()	REAL
Anything else	TEXT (coerced to str()) or custom via Connection.register_adapter().

Examples:

values = [
    None,
    1,
    2.3,
    'a text \u2012 string',
    b'\x00\xff\x00\xff',
    bytearray(b'this is a buffer'),
    datetime(2026, 1, 2, 3, 4, 5).astimezone(timezone.utc),
    datetime(2026, 2, 3, 4, 5, 6),
    date(2026, 3, 4),
    uuid.uuid4(),  # str()
]

for value in values:
    row = db.execute_one('select typeof(?), ?', (value, value))
    print(row)

# ('null',    None)
# ('integer', 1)
# ('real',    2.3)
# ('text',    'a text ‒ string')
# ('blob',    b'\x00\xff\x00\xff')
# ('blob',    b'this is a buffer')
# ('text',    '2026-01-02 03:04:05+00:00')
# ('text',    '2026-02-03 04:05:06')
# ('text',    '2026-03-04')
# ('text',    '0c4ca10a-56ab-470a-9357-d28366d97ceb')

Adapters

You can add custom adapters to control exactly how Python types are sent to SQLite using Connection.register_adapter() and the adapter() decorator. For example, it may be desirable to store Decimal values as TEXT in order to avoid float-point precision issues, or to store date as an 8-digit integer:

db.register_adapter(Decimal, str)

@db.adapter(datetime.date)
def adapt_date(value):
    return int(value.strftime('%Y%m%d'))

values = [
    date(2026, 3, 4),
    Decimal('1.3'),
]

for value in values:
    row = db.execute_one('select typeof(?), ?', (value, value))
    print(row)

# ('integer', 20260304)
# ('text',    '1.3')

Converters

By default, no special attempts at type inference are applied to data coming from SQLite. As you can see in the above examples, all our Python values were coerced to reasonable SQLite-friendly representations. But that richness is lost when reading data from SQLite into Python without specific helpers that read each column’s declared type.

To convert data going from SQLite into Python, you will need to register one or more converters using Connection.register_converter() or using the Connection.converter() decorator. Converters rely on the SQLite sqlite3_column_decltype API, which retrieves the declared type of the given column. cysqlite then applies your converter for any type that was registered.

For example, to convert columns declared DATETIME back to Python datetimes:

db = cysqlite.connect(':memory:')

db.register_converter('datetime', datetime.datetime.fromisoformat)

# Or use a decorator:
@db.converter('datetime')
def convert_datetime(value):
    # Converts our ISO-formatted date string into a python datetime.
    return datetime.datetime.fromisoformat(value)

If the value is NULL then the convert function is not applied, so you do not need to test for value is None.

Below are some examples:

db = cysqlite.connect(':memory:')

@db.converter('datetime')
def convert_datetime(value):
    # Converts our ISO-formatted date string into a python datetime.
    return datetime.datetime.fromisoformat(value)

# Automatically parse and load data declared as JSON.
db.register_converter('json', json.loads)

# Handle decimal data.
@db.converter('numeric')
def convert_numeric(value):
    return Decimal(value).quantize(Decimal('1.00'))

db.execute('create table vals (ts datetime, js json, dec numeric(10, 2))')

# Create a TZ-aware datetime and a JSON object.
ts = datetime.datetime(2026, 1, 2, 3, 4, 5).astimezone(datetime.timezone.utc)
js = {'key': {'nested': 'value'}, 'arr': ['i0', 1, 2., None]}
dec = Decimal('1.3')

# When we INSERT the JSON, note that we need to dump it to string.
db.execute('insert into vals (ts, js, dec) values (?, ?, ?)',
           (ts, json.dumps(js), dec))

# When reading back the data, it is converted automatically based on
# the declared column types.
row = db.execute_one('select * from vals')
assert row == (ts, js, dec)

The converter accepts a data_type and uses the following rules for matching a specified data-type to what SQLite tells us:

• Matching is case-insensitive, e.g. JSON or json is fine.

• Split on the first whitespace or "(" character, e.g. if SQLite tells us the data-type is NUMERIC(10, 2), cysqlite will attempt to find a converter named numeric.

Differences from stdlib

The stdlib sqlite3 module uses different conventions for registering adapters and converters:

• Adapters and converters in sqlite3 are registered globally. In cysqlite, they are scoped to the Connection.

• sqlite3 converters — functions that transform values coming from SQLite back into richer Python types — are disabled by default, and are enabled via the detect_types parameter on connect(). Two modes are available: matching against the column’s declared type (PARSE_DECLTYPES) and matching against a type hint embedded in a column alias (PARSE_COLNAMES). cysqlite always matches against declared types, and has no column-alias mechanism.

• sqlite3 has a separate adaptation mechanism that checks for a __conform__() method on the object being bound; the method’s return value is used in place of the original. cysqlite has no equivalent — bind either a supported type directly, or register an adapter.

• sqlite3 historically shipped default adapters for datetime and date, and also handled datetime via two magic type names, but these are deprecated as of Python 3.12.

cysqlite is more permissive about what types it will accept at bind time. Any object with a __float__ method (e.g. Decimal, Fraction) is bound as REAL, and anything else falls back to str(x) rather than raising TypeError. Additionally, cysqlite provides ISO-8601 formatting for datetime and date out of the box with no surprise deprecations. Sqlite’s built-in date functions work well on ISO-8601 format, so this allows date/times stored by cysqlite to be usable with the builtin date functions.

Transactions

cysqlite operates in pure autocommit mode as this is how SQLite itself operates. Statements execute immediately unless you’ve explicitly opened a transaction. The driver never issues implicit transaction control statements on your behalf. To use transactions, open one with atomic(), transaction(), begin(), or by executing BEGIN directly.

db = connect(':memory:')

# Autocommit: each statement is its own transaction.
db.execute('create table t (x)')
db.execute('insert into t values (1)')  # Committed immediately.

# Explicit transaction via context manager, supports nesting.
with db.atomic():
    db.execute('insert into t values (2)')
    db.execute('insert into t values (3)')
# Committed on successful exit, rolled back on exception.

# Or manage it by hand.
db.begin()
try:
    db.execute('insert into t values (4)')
    db.commit()
except:
    db.rollback()
    raise

in_transaction uses sqlite3_get_autocommit() to detect if a transaction is currently active.

Why autocommit

SQLite allows only one writer at a time, and a transaction that has performed any write will hold an exclusive lock until it commits or rolls back. An application that holds transactions open longer than necessary, even by accident, will see OperationalError: database is locked under concurrency. The standard lib sqlite3 makes this class of bug trivially easy to write: you execute an UPDATE, a transaction opens silently, and then your application spends more time issuing SELECT queries or handling other things before you call commit(), which finally releases the lock.

sqlite3 transactions use a string prefix compare to detect if a statement is data-modifying, which triggers the silent BEGIN. Even in Python 3.14, if the statement starts with INSERT, UPDATE, DELETE, or REPLACE, the driver automatically begins a transaction before executing it. This misses things like queries beginning with a comment or queries using CTEs, among other interesting failure modes.

Note

There was a moment in the Python 3.6 days where this was going to be fixed by calling sqlite3_stmt_readonly(), the C API SQLite provides for exactly this question. The change was reverted before release because it broke conn.execute("BEGIN IMMEDIATE") (BEGIN isn’t read-only, so the new logic tried to auto-BEGIN before the user’s explicit BEGIN and errored). Rather than fix that edge case, the maintainers went back to string matching and added a second special case for DDL. That’s the code that’s been in production since 2016. The SQLite forum has some discussion. I also tried to get CPython on-board again in 2019 with this pull request.

Tip

CPython loves treating backwards-compat as sacrosanct when it suits them, but they will aggressively deprecate at other times. Right now they are moving towards changing the way transactions work (naturally making the situation even worse).

Before Python 3.12, sqlite3 used the isolation_level parameter to control driver-managed transactions. By default a transaction auto-begins before any DML statement using the string compare. This transaction is held open until you call commit() or rollback(). The isolation_level parameter controls what flavor of BEGIN gets emitted, but by passing isolation_level=None you can disable driver transaction management and get something reasonable:

# Specifying isolation_level=None tells sqlite3 to leave transaction
# management alone.
>>> db = sqlite3.connect(':memory:', isolation_level=None)

# Wait, what's this? Legacy??
>>> db.autocommit == sqlite3.LEGACY_TRANSACTION_CONTROL
True

# Whatever, at least we are in autocommit mode.
>>> db.in_transaction
False

# Run a transaction.
>>> db.execute('begin')
>>> db.in_transaction
True

# Let's get out of this transaction.
>>> db.commit()
>>> db.in_transaction
False

At time of writing this still works, but based on how CPython moves fast and breaks things, I expect this will stop working in the next year or two. In its place, CPython has given us a new, completely separate alternative:

# Let's do it the way modern Python wants us to.
>>> db = sqlite3.connect(':memory:', autocommit=True)

# Isolation level returns an empty string. Empty. It means whatever you
# want it to mean.
>>> db.isolation_level
''

>>> db.in_transaction
False

# So far so good.
>>> db.execute('begin')
>>> db.in_transaction
True

# Wait, what the fuck?!
>>> db.commit()
>>> db.in_transaction
True

>>> db.execute('commit -- who thought this was a good idea?!')
>>> db.in_transaction
False

If you didn’t catch what happened there, when we set autocommit=True, Python silently turned commit() into a no-op. No deprecation warning, no exception. If you call commit() and then close the connection, or let it be garbage-collected, all your changes are silently lost.

The icing on the cake is that the “legacy” mode we’re supposed to migrate away from handles this just fine:

>>> db = sqlite3.connect(':memory:', isolation_level=None)
>>> db.execute('begin')
>>> db.in_transaction
True
>>> db.commit()
>>> db.in_transaction
False

The legacy method just works, you can call the commit() method or explicitly execute a COMMIT query, and both do the obvious thing. The new-and-improved method is doing all kinds of weird stuff.

The other direction is just as bad. With autocommit=False (which the docs now recommend), a transaction begins immediately on connection, lasts until you commit, and a fresh one begins right after. Every connection is always in a transaction. This is problematic because of the way SQLite upgrades the lock when a write occurs, retaining it until a commit/rollback occurs.

The end result is that, depending on your Python version and which keyword arguments you passed to connect(), you are now in one of three incompatible transaction regimes, and in one of them, commit() silently does nothing. The predictable consequence is that the standard library has collected pull requests like this one.

cysqlite’s answer to all of this:

• begin() begins.

• commit() commits.

• rollback() rolls back.

• atomic() provides a context-manager or decorator that wraps the block in a transaction or savepoint. Calls can be nested.

Recommended patterns

atomic() can wrap a block of code or decorate a function with transaction semantics, and supports nesting. It opens a transaction at the outermost level and a savepoint for nested calls:

with db.atomic():
    db.execute('insert into orders (user_id, total) values (?, ?)',
               (user_id, total))
    for item in line_items:
        db.execute('insert into order_items (...) values (...)', item)
# Commits on clean exit; rolls back if anything raised.

Atomic blocks nest correctly. A failure inside a nested block rolls back the savepoint without affecting the outer transaction:

with db.atomic():
    db.execute('insert into users (name) values (?)', ('alice',))

    try:
        with db.atomic():
            db.execute('insert into users (name) values (?)', ('alice',))
            # IntegrityError — savepoint is rolled back.
    except IntegrityError:
        pass

    # Outer transaction is unaffected. 'alice' is still pending.

# 'alice' is committed.

If you need a specific lock type, pass lock=. Use IMMEDIATE when you know you’re going to write and want to fail fast if another writer holds the lock, rather than discovering it mid-transaction:

with db.atomic(lock='IMMEDIATE'):
    db.execute('update counters set n = n + 1 where id = ?', (id_,))

If you explicitly want a flat transaction without savepoint nesting, use transaction() instead of atomic(). Nested transaction() calls no-op on the inner levels, only the outermost block controls commit/rollback.

For partial rollbacks within a larger transaction, atomic() (or savepoint()) provides savepoints that can be committed or rolled-back within a larger atomic context:

with db.atomic():
    db.execute('insert into log (msg) values (?)', ('step 1',))

    with db.atomic() as sp:
        db.execute('insert into log (msg) values (?)', ('step 2',))
        if something_went_wrong:
            sp.rollback()  # Undo step 2 only.

    db.execute('insert into log (msg) values (?)', ('step 3',))

As a decorator, any of the three helpers wraps the decorated function in a transaction/savepoint:

@db.atomic()
def transfer_funds(from_id, to_id, amount):
    db.execute('update accounts set bal = bal - ? where id = ?',
               (amount, from_id))
    db.execute('update accounts set bal = bal + ? where id = ?',
               (amount, to_id))

For all other cases you can explicitly call begin(), commit() and rollback() (or execute the SQL directly via execute()).