{"id":3861,"library":"adbc-driver-sqlite","title":"ADBC SQLite Driver","description":"adbc-driver-sqlite provides Python bindings for the Arrow Database Connectivity (ADBC) SQLite driver, offering a DBAPI 2.0 / PEP 249-compatible interface. It allows for Arrow-native access to SQLite databases. The current version is 1.11.0, and the project generally follows a bimonthly release cadence for minor versions.","status":"active","version":"1.11.0","language":"en","source_language":"en","source_url":"https://github.com/apache/arrow-adbc","tags":["database","sqlite","adbc","apache arrow","data access"],"install":[{"cmd":"pip install adbc-driver-sqlite","lang":"bash","label":"Basic Installation"},{"cmd":"pip install adbc-driver-sqlite adbc-driver-manager pyarrow","lang":"bash","label":"With DBAPI 2.0 and PyArrow for full features"}],"dependencies":[{"reason":"Required for the Python bindings and DBAPI 2.0 interface.","package":"adbc-driver-manager","optional":false},{"reason":"Optional, but recommended for using the DBAPI 2.0 interface to retrieve Arrow Table objects instead of row-oriented results.","package":"pyarrow","optional":true}],"imports":[{"symbol":"dbapi","correct":"import adbc_driver_sqlite.dbapi"}],"quickstart":{"code":"import adbc_driver_sqlite.dbapi\nimport os\n\ndb_uri = os.environ.get('ADBC_SQLITE_URI', 'file::memory:?cache=shared')\n\nwith adbc_driver_sqlite.dbapi.connect(uri=db_uri) as conn:\n    with conn.cursor() as cur:\n        cur.execute(\"CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)\")\n        cur.execute(\"INSERT INTO users (id, name) VALUES (?, ?)\", (1, 'Alice'))\n        cur.execute(\"INSERT INTO users (id, name) VALUES (?, ?)\", (2, 'Bob'))\n        cur.execute(\"SELECT * FROM users\")\n        result = cur.fetch_arrow_table()\n        print(result)","lang":"python","description":"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."},"warnings":[{"fix":"Use unique URI filenames (e.g., 'file:mydatabase?mode=memory&cache=shared') or manage connection lifecycles carefully for in-memory databases.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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`.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-04-11T00:00:00.000Z","next_check":"2026-07-10T00:00:00.000Z"}