Another Python SQLite Wrapper

Warnings

• breaking SQLite 3.52.0 withdrawal led to the withdrawal of APSW 3.52.0.0. While APSW 3.51.3.0 includes most of its features, it excludes `SQLITE_UTF8_ZT` and `sqlite3_carray_bind_v2`.
  Fix: Avoid using APSW 3.52.0.0. Be aware of the feature exclusions if migrating from a hypothetical 3.52.0.0 to 3.51.3.0, or generally ensure your SQLite features are covered by the current APSW version.
• gotcha APSW `Connection` and `Cursor` objects are not thread-safe for concurrent use. Attempting to use the same `Connection` or `Cursor` object simultaneously in multiple threads will raise a `ThreadingViolationError` or lead to hangs.
  Fix: For concurrent database access, use a separate `apsw.Connection` object for each thread. APSW *does* release the GIL around SQLite calls, allowing Python threads to execute concurrently with different connections.
• gotcha While APSW objects automatically close on garbage collection, it is best practice to explicitly close `Connection` and `Cursor` objects or use context managers (`with apsw.Connection(...)`) to ensure timely resource release and proper transaction handling, especially if user-defined functions or collations introduce circular references preventing automatic garbage collection.
  Fix: Always use `with apsw.Connection(...) as connection:` and manage cursors within that context. Explicitly call `.close()` on objects if context managers are not feasible.
• gotcha The `apsw.bestpractice` module offers functions to enable recommended SQLite settings (e.g., WAL mode, foreign keys, busy timeout, query optimization) which are not default due to SQLite's strong backward compatibility. Not applying these can lead to common mistakes or suboptimal performance.
  Fix: Call `apsw.bestpractice.apply(apsw.bestpractice.recommended)` at the start of your application or when opening a connection to enable these best practices.
• gotcha Starting with APSW 3.50.2.0, the PyPI binary builds began using `cibuildwheel` version 3, which advanced the minimum supported Linux distribution. This might affect deployments to older Linux environments.
  Fix: Ensure your Linux deployment environment meets the minimum glibc version requirements introduced by `cibuildwheel` v3. Check `cibuildwheel` documentation for specifics if encountering compatibility issues.
• deprecated APSW follows Python's end-of-life (EOL) cycle for supported versions. Once a Python release goes EOL, there will be one final APSW release supporting that Python version, after which support is dropped.
  Fix: Keep your Python runtime updated to actively supported versions (currently >=3.10). Consult the APSW changelog for specific Python EOL-related support drops.

Install

• pip install apsw Install latest version

Imports

• Connection

  import apsw
  connection = apsw.Connection('database.db')

  The primary classes like Connection and Cursor are direct attributes of the top-level 'apsw' module, not typically imported directly.
• Cursor

  import apsw
  cursor = connection.cursor()

  Similar to Connection, Cursor objects are created via a Connection instance.

Quickstart

This example demonstrates how to establish a connection, apply best practices, create a table, insert data using positional and named bindings, and retrieve data. It uses a context manager for connection handling, which is the recommended approach.

import apsw
import os

db_file = 'example.db'
if os.path.exists(db_file):
    os.remove(db_file)

# Best practice application for optimal settings and error avoidance
apsw.bestpractice.apply(apsw.bestpractice.recommended)

# Use a context manager to ensure proper closing and transaction management
with apsw.Connection(db_file) as connection:
    cursor = connection.cursor()

    # Create a table
    cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")

    # Insert data
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))

    # Select data
    print("All users:")
    for row in cursor.execute("SELECT id, name, email FROM users"):
        print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

    # Select with named bindings
    print("\nUser with ID 1:")
    for row in cursor.execute("SELECT name FROM users WHERE id = :id", {'id': 1}):
        print(f"Name: {row[0]}")

print(f"Database '{db_file}' operations complete.")