Connection

class rapsqlite.Connection(path, *, pragmas=None, init_hook=None, timeout=5.0, iter_chunk_size=64, loop_param=None)

Bases: object

Async SQLite connection.

async __aenter__() → Any[source]

__aexit__(exc_type, _exc_val, _exc_tb): Async context manager exit. Commit on clean exit, rollback on exception (aiosqlite/sqlite3-style).

async backup(target: Any, *, pages: int = 0, progress: Any = None, name: str = 'main', sleep: float = 0.25) → None

async begin() → None

changes(): Get the number of rows affected by the last statement.

async close() → None[source]

async commit() → None

connection_timeout

create_aggregate(name, num_params, aggregate_class): Create or remove a custom SQL aggregate function. The aggregate class must implement step(self, *args) and finalize(self). If aggregate_class is None, the aggregate is removed.

create_collation(name, callable): Create or remove a custom collation. The callable receives (s1: str, s2: str) and returns -1, 0, or 1. If callable is None, the collation is removed.

create_cursor_with_query(query, parameters): Create a cursor with a pre-initialized query and parameters. This is used by execute() to return a cursor that can be used as an async context manager.

create_function(name, nargs, func, deterministic=False): Create or remove a user-defined SQL function. If func is None, the function is removed. deterministic: if true, mark function as deterministic (SQLite 3.8.3+); enables optimizations.

cursor(): Create a cursor for this connection.

enable_load_extension(enabled): Enable or disable loading SQLite extensions.

execute(query, parameters=None, cursor=None)

Execute a SQL query (does not return results).

Executes a SQL statement such as CREATE, INSERT, UPDATE, DELETE, etc. For SELECT queries, use fetch_all(), fetch_one(), or fetch_optional() instead. This method supports parameterized queries with both named and positional parameters.

# Arguments

query - SQL query string to execute. Can contain parameter placeholders: - Named parameters: :name, @name, $name - Positional parameters: ?, ?1, ?2
parameters - Optional parameters for the query. Can be: - A dictionary for named parameters: {“name”: “value”, …} - A list/tuple for positional parameters: [value1, value2, …] - A single value (treated as single positional parameter) - None (no parameters)

# Returns

Returns an ExecuteContextManager that can be used as: - await conn.execute(…) - Execute and return None - async with conn.execute(…) as cursor: - Execute and get cursor

# Errors

Raises OperationalError if the query execution fails (e.g., database locked, disk full). Raises ProgrammingError for SQL syntax errors. Raises IntegrityError for constraint violations.

# Example

# Simple query
await conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")

# With positional parameters
await conn.execute("INSERT INTO users (name) VALUES (?)", ["Alice"])

# With named parameters
await conn.execute(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    {"name": "Bob", "email": "bob@example.com"}
)

# Using as context manager (returns cursor)
async with conn.execute("SELECT * FROM users") as cursor:
    rows = await cursor.fetchall()

async execute_fetchall(sql: str, parameters: Any | None = None) → list[list[Any]]

execute_insert(query, parameters=None)

Execute an INSERT/UPDATE/DELETE and return the last insert row ID (aiosqlite-compatible helper).

Runs the statement, updates last_rowid/changes, and returns last_insert_rowid(). Use for INSERTs; for UPDATE/DELETE the returned rowid is typically 0.

execute_iter(sql: str, parameters: Any | None = None, chunk_size: int | None = None) → _StreamChunksIterator: Return an async iterator that yields rows in chunks (streaming / memory-efficient).

execute_many(query, parameters): Execute a query multiple times with different parameters.

executemany(query, parameters): Execute a query multiple times with different parameters.

async explain_query_plan(sql: str, parameters: Any | None = None) → list[list[Any]]

async fetch_all(query: str, parameters: Any = None) → list[list[Any]]

fetch_one(query, parameters=None)

Fetch a single row from a SELECT query.

Executes a SELECT query and returns exactly one row. Raises an error if no rows or more than one row is returned.

# Arguments

query - SELECT query string. Should return exactly one row.
parameters - Optional parameters (same format as execute()).

# Returns

Returns an awaitable that resolves to a single row (format depends on row_factory, same as fetch_all()).

# Errors

Raises ProgrammingError if no rows are found or if more than one row is returned. Raises OperationalError for database errors.

# Example

# Fetch user by ID (expects exactly one)
user = await conn.fetch_one("SELECT * FROM users WHERE id = ?", [1])
# user = [1, "Alice"]  # or dict/Row depending on row_factory

# This will raise if user doesn't exist
try:
    user = await conn.fetch_one("SELECT * FROM users WHERE id = ?", [999])
except ProgrammingError:
    print("User not found")

fetch_optional(query, parameters=None)

Fetch a single row from a SELECT query, returning None if no rows.

Executes a SELECT query and returns one row or None. Raises an error if more than one row is returned.

# Arguments

query - SELECT query string. Should return zero or one row.
parameters - Optional parameters (same format as execute()).

# Returns

Returns an awaitable that resolves to: - A single row (format depends on row_factory) if one row is found - None if no rows are found

# Errors

Raises ProgrammingError if more than one row is returned. Raises OperationalError for database errors.

# Example

# Fetch user by ID (may not exist)
user = await conn.fetch_optional("SELECT * FROM users WHERE id = ?", [1])
if user:
    print(f"Found: {user}")
else:
    print("User not found")

# Safe for optional lookups
user = await conn.fetch_optional(
    "SELECT * FROM users WHERE email = ?",
    ["alice@example.com"]
)

get_foreign_keys(table_name): Get foreign key constraints for a specific table.

get_index_info(index_name): Get information about columns in an index using PRAGMA index_info.

get_index_list(table_name): Get list of indexes for a specific table using PRAGMA index_list.

get_indexes(table_name=None): Get list of indexes in the database.

get_schema(table_name=None): Get comprehensive schema information for a table or all tables.

get_table_info(table_name): Get table information (columns) for a specific table.

get_table_xinfo(table_name): Get extended table information using PRAGMA table_xinfo (SQLite 3.26.0+). Returns additional information beyond table_info, including hidden columns.

get_tables(name=None): Get list of table names in the database.

get_views(name=None): Get list of views in the database.

idle_timeout: Idle connection timeout in seconds. When set, connections idle in the pool longer than this are closed. None (default) means no idle timeout.

property in_transaction: bool: Check if connection is in a transaction (sync property for aiosqlite compat).

in_transaction_async()

Check if connection is currently in a transaction.

Returns True if a transaction has been started with begin() or transaction() context manager and not yet committed or rolled back.

# Returns

Returns an awaitable that resolves to a boolean indicating whether the connection is currently in a transaction.

# Note

In aiosqlite, this is a property. In rapsqlite, it’s an async method due to internal implementation, but functionally equivalent. You must await the result: in_tx = await conn.in_transaction()

# Example

in_tx = await conn.in_transaction()  # False
await conn.begin()
in_tx = await conn.in_transaction()  # True
await conn.commit()
in_tx = await conn.in_transaction()  # False

include_query_in_errors

Get whether query strings are included in error messages.

When True (default), error messages include sanitized query strings for debugging. When False, query strings are excluded entirely for enhanced security.

Queries are always sanitized to remove sensitive patterns (passwords, tokens, etc.) even when included. For maximum security with highly sensitive data, set this to False.

interrupt(): Interrupt a long-running query (Phase 3.9, aiosqlite-compatible). Interrupts the callback connection when present (UDFs, trace, authorizer, etc.); no-op when no callbacks are configured.

isolation_level: Get the transaction isolation level (Phase 3.9). None | “DEFERRED” | “IMMEDIATE” | “EXCLUSIVE”.

iter_chunk_size: Get iter_chunk_size (Phase 3.10). Used for chunked iteration; aiosqlite-compatible.

iterdump() → _IterdumpWrapper: Return a dual-mode iterdump wrapper.

last_insert_rowid(): Get the last insert row ID.

load_extension(name): Load a SQLite extension from the specified file. Extension loading must be enabled first using enable_load_extension(true).

path

async pool_health() → bool

pool_metrics(): Return pool metrics (size, num_idle, in_use). Pool must exist (use connection first).

pool_size

register_adapter(type_, adapter): Register an adapter for a Python type. When binding parameters, if the value’s type matches, adapter(value) is called and the result is used. Pass adapter=None to remove adapters for that type.

register_converter(typename, converter): Register a converter for a declared column type. When reading rows, if the column’s declared type matches typename, converter(bytes) is called and the result is used. Pass converter=None to remove the converter for that type.

async rollback() → None

row_factory

savepoint(name=None): Return an async context manager for a savepoint. Requires an active transaction. On __aenter__ runs SAVEPOINT <name>; on __aexit__ runs RELEASE SAVEPOINT (success) or ROLLBACK TO SAVEPOINT (exception).

set_authorizer(callback): Set or clear the authorizer callback. The callback receives (action, arg1, arg2, arg3, arg4) and returns an int (SQLITE_OK, SQLITE_DENY, etc.).

set_pragma(name, value): Set a PRAGMA value on the database connection.

async set_progress_handler(a: Any, b: Any = None) → Any

set_slow_query_threshold(threshold_secs: float, callback: Callable[[float, str], None] | None = None) → None: Set threshold and optional callback for slow query detection.

set_trace_callback(callback): Set or clear the trace callback. The callback receives SQL strings as they are executed.

stop(): No-op for aiosqlite API compatibility. Does nothing; use close() to close the connection.

text_factory

timeout

Get the SQLite busy_timeout value (in seconds).

This controls how long SQLite will wait when the database is locked by another process/thread before raising an error. Default: 5.0 seconds (matches sqlite3/aiosqlite).

This is an aiosqlite-compatible feature that sets SQLite’s busy_timeout PRAGMA.

property total_changes: int: Get total database changes since connection was opened (sync property for aiosqlite compat).

total_changes_async()

Get the total number of database changes since connection was opened.

This is a cumulative count of all INSERT, UPDATE, and DELETE operations performed on this connection. The count includes changes from all transactions and is reset when the connection is closed.

# Returns

Returns an awaitable that resolves to an integer (u64) representing the total number of changes.

# Note

In aiosqlite, this is a property. In rapsqlite, it’s an async method due to internal implementation, but functionally equivalent. You must await the result: changes = await conn.total_changes()

# Example

await conn.execute("INSERT INTO users (name) VALUES (?)", ["Alice"])
await conn.execute("INSERT INTO users (name) VALUES (?)", ["Bob"])
changes = await conn.total_changes()  # Returns 2

transaction() → _TransactionContextManagerWithState: Return a transaction context manager that updates in_transaction state.

The Connection class represents an async SQLite database connection.

Example

from rapsqlite import Connection

async with Connection("example.db") as conn:
    await conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
    rows = await conn.fetch_all("SELECT * FROM test")

Callback Exception Handling

When using callback methods, exceptions in your Python callbacks are handled as follows:

User-defined functions (create_function): Exceptions are converted to SQLite errors, causing the query to fail with an OperationalError
User-defined aggregates (create_aggregate): Exceptions in step/finalize are converted to SQLite errors, causing the query to fail with an OperationalError
Custom collations (create_collation): Exceptions in the callable may cause comparison/ORDER BY to fail
Trace callbacks (set_trace_callback): Exceptions are silently ignored to prevent affecting database operations
Authorizer callbacks (set_authorizer): Exceptions default to DENY (fail-secure) - operations are denied if callback raises
Progress handlers (set_progress_handler): Exceptions default to continue - operation continues even if callback raises

Best practice: Always handle exceptions within your callback functions to avoid unexpected behavior.

Type adapters and converters

Type adapters and converters (register_adapter, register_converter) are supported per-connection (sqlite3-style). See Type Conversion Strategy for details and examples.

Pool metrics and health

When using a connection (which uses an internal pool), you can observe pool state and run health checks:

``pool_metrics()`` (async): Returns a dict with size (total connections in pool), num_idle (idle connections), and in_use (connections currently in use). Use this to monitor pool usage in production (e.g. log periodically or expose via a metrics endpoint). For Prometheus-style gauges, use the helper rapsqlite.pool_metrics_gauges() (see Advanced Usage Guide Monitoring / Metrics export).
``pool_health()`` (async): Runs a minimal health check (SELECT 1) and returns True on success; raises on failure. Use for liveness/readiness probes.
``idle_timeout`` (property): When set (seconds), connections idle in the pool longer than this are closed. None (default) means no idle timeout. Set before first use (e.g. connect(..., idle_timeout=60) or conn.idle_timeout = 60).
``interrupt()`` (async): Interrupts the callback connection when present (e.g. during a long-running user-defined function or progress handler); no-op when no callback connection is in use. Use from another task to cancel a long-running operation. Limitations: Only affects operations on the dedicated callback connection; pool operations (normal execute/fetch) are not interrupted. For aiosqlite compatibility; full interrupt semantics may be extended in a future release.