Utils

cysqlite utilities.

cysqlite.utils.slow_query_log(conn, threshold_ms=50, logger=None, level=logging.WARNING, expand_sql=True)
Parameters:

  • conn (Connection) – cysqlite connection to install slow query trace.

  • threshold_ms (int) – threshold for logging slow queries.

  • logger (str) – namespace to log slow queries to, default 'cysqlite.utils'.

  • level (int) – loglevel for slow queries.

  • expand_sql (bool) – expand bound parameters in query.

Register a sqlite3_trace_v2 callback that will log slow queries to the given logger. Overrides previously-registered trace()

class cysqlite.utils.Pool(database, readers=4, writer=True, **connect_kwargs)
Parameters:

  • database (str, pathlib.Path) – database filename.

  • readers (int) – number of read-only connections to create.

  • writer (bool) – create a dedicated writer connection.

  • connect_kwargs – arguments for connect()

Connection pool implementation that provides read-only connections and, optionally, a dedicated writer connection. Ensures that multiple writers are serialized, and that readers cannot lock the database. Requires WAL-mode.

The following default pragmas are applied to all connections opened by the pool:

  • journal_mode = wal

  • cache_size = -64000 (64MiB page cache)

  • mmap_size = 256 * 1024 * 1024 (256MiB)

  • foreign_keys = 1 (enable foreign-key constraint enforcement)

Example:

# Override only the page cache size.
pool = Pool('app.db', pragmas={'cache_size': -128000})

with pool.writer() as conn:
    conn.execute('create table if not exists users ('
                 '"id" integer not null primary key, '
                 '"username" text not null)')

    conn.execute('insert into users (username) values (?)', ('alice',))

with pool.reader() as conn:
    curs = conn.execute('select * from users')

    # Raises OperationalError - connection is read-only.
    conn.execute('insert into users (username) values (?)', ('bob',))
reader()
Returns:

read-only connection from pool.

Return type:

Connection

Raises:

InterfaceError if pool has been closed.

Context-manager which checks out a read-only connection from the pool.

writer()
Returns:

read/write connection from pool.

Return type:

Connection

Raises:

InterfaceError if pool has been closed or if writer connection was disabled.

Context-manager which checks out the writer connection from the pool. At the end of the wrapped block, if a transaction is active and un-committed the transaction is rolled-back.

close()

Close all connections.