Connection

Python: also in Node · Go · Rust · Swift · Dart · Java and Kotlin · .NET · C++ · Objective-C

One independent SQLite connection owned by a Database.

Connections have independent transactions, read snapshots, TEMP schemas, busy timeouts and connection-local PRAGMAs. Closing one leaves its database and sibling connections usable.

A pull or resolve_conflict() replaces the underlying SQLite connection. The replacement keeps its configured busy timeout and these settings:

cache_size, case_sensitive_like, cell_size_check, foreign_keys, ignore_check_constraints, query_only, recursive_triggers, temp_store, trusted_schema.

TEMP objects and all other settings are lost. Conflicts replay pending transactions with the settings under which they committed.

Once a connection creates or drops a TEMP object, replay cannot reproduce that connection’s transactions. After a connection creates or drops a TEMP object, or changes another setting that can change a statement’s result, a conflict quarantines that connection’s transactions instead of replaying them. This continues until the connection is next replaced.

Settings that govern only storage, memory and lock waits, such as secure_delete, journal_size_limit and busy_timeout, leave transactions replayable. See production considerations for replay limitations.

At a glance

Read and write

• query(): Run one SQLite statement with read execution intent and return rows.
• execute(): Run exactly one state-changing SQLite statement.
• transaction(): Begin one managed transaction and return its context manager.

Status and maintenance

• status(): Return a typed ConnectionStatus of connection-owned state only.
• checkpoint() Advanced: Run an explicit WAL checkpoint and return (checkpointed, total) frame counts.

Close

• close(): Close only this connection.

Read and write

Connection.query

Run one SQLite statement with read execution intent and return rows.

Connection.query(
    sql: str,
    params: Bindings = (),
    *,
    engine: QueryEngine = 'sqlite',
) -> Rows

Parameters

• sql: One SQLite statement.
• params: Values for the statement’s placeholders.
• engine: The SQLite or DuckDB read engine.

Returns

Rows. The result rows and their column names.

Errors

• invalid_argument: An argument has the wrong type or value.
• write_in_query: The statement writes. Use execute for it.
• result_limit_exceeded: The result has more rows than ConnectionOptions.maximum_rows.
• invalid_state: A managed transaction is active. Use its transaction object.
• connection_closed: The connection is closed.
• analytical_engine_unavailable: The analytics package is not installed and no library is named or found.
• transaction_control_sql: The statement starts with BEGIN, COMMIT, END, RELEASE, ROLLBACK, or SAVEPOINT. Use transaction() instead.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        print(connection.query("SELECT ? + ?", (2, 3))[0][0])

Details

Use execute for writes. Use transaction() for transaction control. SQLite can apply effects during statement preparation, including a PRAGMA or EXPLAIN, even if the call refuses execution.

The connection’s maximum_rows bounds results. The check runs after the statement completes. It limits returned rows rather than execution memory or time. Give a query that could grow without end its own SQL LIMIT.

Connection.execute

Run exactly one state-changing SQLite statement.

Connection.execute(
    sql: str,
    params: Bindings = (),
) -> ExecuteResult

Parameters

• sql: One SQLite statement.
• params: Values for the statement’s placeholders.

Returns

ExecuteResult. The affected row count and last inserted row ID.

Errors

• invalid_argument: An argument has the wrong type or value.
• read_in_execute: The statement only reads. Use query for it.
• invalid_state: A managed transaction is active. Use its transaction object.
• connection_closed: The connection is closed.
• transaction_control_sql: The statement starts with BEGIN, COMMIT, END, RELEASE, ROLLBACK, or SAVEPOINT. Use transaction() instead.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        connection.execute("CREATE TABLE note (id INTEGER PRIMARY KEY)")
        print(connection.execute("INSERT INTO note VALUES (8)").last_insert_id)

Details

Use query for reads. SQLite can apply effects during statement preparation even if the call refuses execution. The returned ExecuteResult contains last_insert_id whenever this direct INSERT or REPLACE inserted a row.

This call never accepts transaction-control SQL. Use transaction() to manage that lifecycle.

Connection.transaction

Begin one managed transaction and return its context manager.

Connection.transaction(
    *,
    mode: TransactionMode = 'immediate',
) -> _Transaction

Parameters

• mode: The deferred, immediate, or exclusive transaction mode.

Returns

_Transaction. The managed transaction context.

Errors

• invalid_argument: An argument has the wrong type or value.
• invalid_state: A transaction is active, or this transaction context was already used. Finish the active transaction and use a new context.
• connection_closed: The connection is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        with connection.transaction(mode="deferred") as transaction:
            print(transaction.query("SELECT 11")[0][0])

Details

The default mode is immediate. The scope commits only on successful completion and always attempts rollback after a failure.

Status and maintenance

Connection.status

Return a typed ConnectionStatus of connection-owned state only.

Connection.status() -> ConnectionStatus

Returns

ConnectionStatus. The current connection-owned state.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        print(connection.status().transaction_active)

Connection.checkpoint

Advanced

Run an explicit WAL checkpoint and return (checkpointed, total) frame counts.

Connection.checkpoint() -> tuple[int, int]

Returns

tuple[int, int]. The checkpointed and total WAL frame counts.

Errors

• invalid_state: A managed transaction is active. Call checkpoint after it ends.
• connection_closed: The connection is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        checkpointed, total = connection.checkpoint()
        print(total >= checkpointed)

Details

Writable databases support this even when CheckpointPolicy.disabled() disables automatic checkpoints.

Close

Connection.close

Close only this connection.

Connection.close() -> None

Errors

• invalid_state: A managed transaction is active on this connection. Close it after the transaction ends.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    connection = db.connect()
    connection.close()
    print(connection.status().closed)

Details

Repeated calls have the same effect.

close() fails with invalid_state while this connection has an active managed transaction.

Which engine answers a read

Use query to choose the engine. Default reads use SQLite. Select DuckDB with engine="duckdb". Only these two engines are supported. An invalid selection fails instead of selecting the default.

The packaged Python SDK supports SQLite and DuckDB.

The native SDK loads DuckDB when you first request an analytical read. OpenOptions.analytical_engine selects the library for one database or snapshot. It takes priority over POTLUCKDB_DUCKDB_LIBRARY, package discovery where available, and the platform’s library search. An explicit selection, including an empty string, never uses a fallback.

See analytics engine installation for your platform. Without an implicit library, the read fails with analytical_engine_unavailable. If an explicit library cannot load, the read reports its loader error.

Install it with pip install potluckdb-analytics, which resolves the wheel built for this platform.

DuckDB rejects the named binding forms :name and @name. Both engines accept $name and positional ? bindings. Booleans bind as INTEGER 0/1 on both. IS therefore asks the same question on either engine, unlike a DuckDB BOOLEAN binding.

Bare table names read the database’s main schema. Qualifiers such as temp. are not seen. For table names ending in .csv, .parquet or .json, use potluckdb_scan('name'). DuckDB otherwise treats those names as files.

NUMERIC and DECIMAL columns read as doubles. A value that a double cannot hold exactly fails the query instead of arriving rounded.

A storage failure during an analytical read reports analytical_engine_error, with the failure named in its message. The same query succeeds when storage answers.

Either engine runs one read per query. The analytical engine reads a snapshot of the same data through its own in-memory database. A statement sent to this engine therefore cannot change the stored data.

An engine can remain for the session’s lifetime. Changing its settings or catalog would change later queries on that session. Only SELECT and EXPLAIN are accepted. CREATE, SET, ATTACH, PREPARE and EXPLAIN ANALYZE are refused. EXPLAIN ANALYZE is refused because it executes the explained statement.

Use execute for writes to the stored data.