DB-API (Python)

DB-API (Python) · JDBC · ODBC

potluckdb.dbapi implements PEP 249 inside the Python package. It provides connect, connections, cursors, commit and rollback. Its settings are apilevel 2.0, threadsafety 1 and the qmark parameter style.

Programs written for DB-API drivers run against it. To use the SDK’s operations, call the connection’s sync and resolve_conflict, which return SDK values. The Python installation page applies. Nothing else is installed.

Requirements

Python 3.14 or newer on macOS, Linux or Windows: the requirements of the Python package.

What ships

The Python column of the compatibility matrix, reached through DB-API (Python). Each release runs the program on the platforms in this table. Other platforms have no verification for this interface.

Platform	Targets	Package
macOS	macos-arm64, macos-x86_64	potluckdb
Linux (glibc)	linux-x86_64-gnu, linux-arm64-gnu	potluckdb
Linux (musl)	linux-x86_64-musl, linux-arm64-musl	potluckdb
Windows	windows-x86_64, windows-arm64	potluckdb

Install

pip install potluckdb

The module is part of the package. No separate driver is needed.

Connect

One connection string serves DB-API, JDBC and ODBC. potluckdb:<path> names the local database. Parameters after ? name the remote and options. Each parameter appears at most once, and no other parameters exist.

The string never contains secrets. Storage credentials come from the provider’s standard chain, as the installation pages describe. The encryption key comes from a file named in the string. Strings outside the grammar fail before anything opens.

connect accepts a connection string. Alternatively, programs that already construct SDK objects can pass path, remote and options keyword arguments. It does not accept both forms together.

from potluckdb import dbapi

connection = dbapi.connect("potluckdb:app.potluckdb?remote=s3://my-bucket/notes&region=eu-west-1")

Parameter	Value	Sets
remote	dir:<path>, s3://<bucket>/<namespace>, gs://<bucket>/<namespace> or az://<account>/<container>/<namespace>	The remote. Without it, the database is local only.
region	a region name	The region of an s3:// remote. A string that sets it without an s3:// remote is refused.
endpoint	a URL	The endpoint of an S3-compatible storage service. A string that sets it without an s3:// remote is refused.
sync	automatic or manual	The synchronization policy, with the factory’s defaults.
prefetch	automatic or disabled	The prefetch policy, with the factory’s defaults.
checkpoint	automatic or disabled	The checkpoint policy, with the factory’s defaults.
key_file	a path	A file holding the encryption key as hexadecimal text. The encryption key never appears in the string.
license_key	a license key	The license_key open option. If absent, the environment and configuration directory apply.
analytical_engine	a path	The analytical_engine open option.
client_id	an identifier	The client_id open option.
local_budget_bytes	an integer	The open option of that name.
minimum_local_budget_bytes	an integer	The open option of that name.
local_compaction_bytes	an integer	The open option of that name.
timeout_ms	an integer	The connection option of that name.
maximum_rows	an integer	The connection option of that name.

The options themselves are described on the options page.

Behavior

Cursor.execute distinguishes reads from writes before execution, as the SDK’s query and execute do. A write runs inside the connection’s transaction. That transaction starts with the first write after connect, a successful commit or a successful rollback. It ends with a successful commit or rollback. Reads outside a transaction see the current state.

A statement failure can make SQLite roll back the whole transaction, such as a storage error or INSERT OR ROLLBACK. The error reports this. Every later statement and commit() then fails with invalid_state until you call rollback().

1. Call rollback() to acknowledge the rollback.
2. Run the whole transaction again.

A failed first write needs no acknowledgement because no earlier statements were lost.

Attribute	Value
description	Column names
rowcount	Rows affected by a write
lastrowid	Row identifier from a direct insert

SQLite constraint violations raise IntegrityError. Other errors select a DB-API class by category:

Category	Class
Caller	ProgrammingError
Temporary or permanent	OperationalError
Internal	InternalError

Each error has code, category and action attributes.

executemany runs rows one at a time inside the connection’s transaction, as sqlite3 does. If a row fails, that row ran nothing, but the rows before it ran. This is also true when the row fails with the temporary database_busy while a synchronization holds the database. The error carries a note with the number of earlier rows. Roll back before retrying the whole batch. Alternatively, while the transaction remains open, retry from the failing row.

Quickstart

The program opens a database with a directory remote and a manual synchronization policy. It writes and commits, reads the row, synchronizes once and demonstrates a refused statement. See Concepts for the remote model.

Run it with python quickstart.py. It prints quickstart: ok.

quickstart.py

# One database, one remote, through PEP 249. The connection string names
# the local database, the remote and the sync policy; nothing else is configured.

import potluckdb
from potluckdb import dbapi

connection = dbapi.connect("potluckdb:app.potluckdb?remote=dir:shared-remote&sync=manual")
cursor = connection.cursor()

# Writes run inside the connection's transaction until commit().
cursor.execute("CREATE TABLE IF NOT EXISTS note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
cursor.execute("INSERT INTO note(body) VALUES(?)", ("buy milk",))
assert cursor.rowcount == 1 and cursor.lastrowid is not None
connection.commit()

# Reads return rows; description carries the column names.
cursor.execute("SELECT id, body FROM note ORDER BY id")
assert [column[0] for column in cursor.description] == ["id", "body"]
rows = cursor.fetchall()
assert rows[-1][1] == "buy milk"

# The SDK's own operations keep their names: one synchronization, its outcome as a value.
outcome = connection.sync()
assert isinstance(outcome, (potluckdb.Pushed, potluckdb.UpToDate)), outcome

# The wrong intent is refused before execution, as a DB-API error with the code attached.
try:
    cursor.execute("BEGIN")
except dbapi.ProgrammingError as error:
    assert error.code == "transaction_control_sql"

cursor.close()
connection.close()
print("quickstart: ok")

Verified by the release

Each release verifies this program through installed Python wheels on macOS, Linux (glibc), Linux (musl) and Windows. It checks statements, transactions, and DB-API behavior.