ADBC SQLite Driver

Warnings

• gotcha When connecting to an in-memory SQLite database by omitting the 'uri' parameter or using ':memory:', all connections will share the same database. To create distinct in-memory databases, use a URI like 'file::memory:?cache=shared' with a unique identifier or explicitly close connections.
  Fix: Use unique URI filenames (e.g., 'file:mydatabase?mode=memory&cache=shared') or manage connection lifecycles carefully for in-memory databases.
• gotcha SQLite's dynamic typing can lead to 'Type Promotion' and potential errors during ADBC's Arrow type inference. The driver infers types from the first batch of rows; subsequent batches attempting to convert to incompatible types will raise an error.
  Fix: Ensure consistent data types within columns, especially when ingesting or querying large datasets with varying data. The `adbc.sqlite.query.batch_rows` option can influence how many rows are used for initial type inference.
• gotcha Modifying SQLite PRAGMA settings that affect transaction state (e.g., `PRAGMA journal_mode`) may fail if executed within an implicit transaction. The ADBC Python API implicitly wraps statements in transactions.
  Fix: Execute an explicit `COMMIT;` (or `ROLLBACK;`) before attempting to change such PRAGMA settings. Alternatively, ensure `autocommit=True` is set during connection for specific scenarios.
• gotcha SQLite user-defined functions (UDFs) registered with the underlying `sqlite3` module are not directly accessible through `adbc-driver-sqlite` connections, even if they share the same in-memory database. This is due to how ADBC manages its connection to the SQLite C driver.
  Fix: If UDFs are critical, consider using the native `sqlite3` module directly or explore if ADBC's C++ driver offers mechanisms for UDF registration that can be exposed via Python bindings in future versions.
• gotcha Loading SQLite extensions requires specific connection options (`adbc.sqlite.load_extension.enabled`, `adbc.sqlite.load_extension.path`, `adbc.sqlite.load_extension.entrypoint`) to be set on the connection *after* the connection is initialized, or by using specific methods on `AdbcSqliteConnection`.
  Fix: Consult the ADBC SQLite driver documentation for the correct sequence of setting options or calling `enable_load_extension` and `load_extension` methods to properly load SQLite extensions.
• gotcha The underlying C++ SQLite driver library (`libadbc_driver_sqlite.so` on Linux, `.dylib` on macOS, `.dll` on Windows) must be discoverable by the Python package at runtime. If not installed via system package managers or not in a standard search path, this can cause import or connection errors.
  Fix: Ensure the library is in a system-wide search path (e.g., `/usr/local/lib`) or add its directory to `LD_LIBRARY_PATH` (Linux/macOS) or `PATH` (Windows) environment variables before running Python.

Install

• pip install adbc-driver-sqlite Basic Installation
• pip install adbc-driver-sqlite adbc-driver-manager pyarrow With DBAPI 2.0 and PyArrow for full features

Imports

• dbapi

  import adbc_driver_sqlite.dbapi

Quickstart

Connects to an in-memory SQLite database (or a specified URI), creates a table, inserts data, and fetches results as an Arrow Table using the DBAPI 2.0 interface.

import adbc_driver_sqlite.dbapi
import os

db_uri = os.environ.get('ADBC_SQLITE_URI', 'file::memory:?cache=shared')

with adbc_driver_sqlite.dbapi.connect(uri=db_uri) as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)")
        cur.execute("INSERT INTO users (id, name) VALUES (?, ?)", (1, 'Alice'))
        cur.execute("INSERT INTO users (id, name) VALUES (?, ?)", (2, 'Bob'))
        cur.execute("SELECT * FROM users")
        result = cur.fetch_arrow_table()
        print(result)