ADBC Driver Manager for Python

import adbc_driver_sqlite.dbapi
import pyarrow

# Ensure the SQLite ADBC driver is installed for this example.
# pip install adbc-driver-sqlite pyarrow

try:
    # Connect to a SQLite database using the DB-API 2.0 interface
    # Use a context manager to ensure proper resource cleanup
    with adbc_driver_sqlite.dbapi.connect() as conn:
        with conn.cursor() as cur:
            # Execute a query
            cur.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER, name TEXT)")
            cur.execute("INSERT INTO test (id, name) VALUES (?, ?)", (1, 'Alice'))
            cur.execute("INSERT INTO test (id, name) VALUES (?, ?)", (2, 'Bob'))

            # Fetch results as an Arrow Table (an ADBC-specific extension)
            cur.execute("SELECT id, name FROM test ORDER BY id")
            table = cur.fetch_arrow_table()
            print("Fetched Arrow Table:\n", table)

            # Fetch results via standard DB-API row-oriented interface
            cur.execute("SELECT id, name FROM test ORDER BY id")
            row = cur.fetchone()
            print(f"\nFetched one row: {row}")
            rows = cur.fetchall()
            print(f"Fetched remaining rows: {rows}")

            # Bulk ingest data
            new_data = pyarrow.table([[3, 4], ['Charlie', 'David']], names=['id', 'name'])
            cur.adbc_ingest("test", new_data)
            print(f"\nIngested {new_data.num_rows} rows.")

            cur.execute("SELECT COUNT(*) FROM test")
            count = cur.fetchone()[0]
            print(f"Total rows after ingest: {count}")

except Exception as e:
    print(f"An error occurred: {e}")