DBAPI/Driver Manager Recipes

These recipes show general functionality of the ADBC Python libraries that isn’t necessarily specific to any one driver.

Direct use of the low-level bindings

Recipe source: driver_manager_lowlevel.py

While the DB-API bindings are recommended for general use, the low-level bindings are also available. These mostly mirror the ADBC C API directly. They can be useful to opt out of some behaviors of the DB-API wrapper.

import pyarrow

import adbc_driver_manager
import adbc_driver_sqlite

The driver packages do still have conveniences to create the root AdbcDatabase object.

db: adbc_driver_manager.AdbcDatabase = adbc_driver_sqlite.connect()

The database must then be wrapped in a AdbcConnection. This is similar in scope to the DB-API Connection class.

conn = adbc_driver_manager.AdbcConnection(db)

Finally, we can wrap the connection in a AdbcStatement, which corresponds roughly to the DB-API Cursor class.

stmt = adbc_driver_manager.AdbcStatement(conn)

Now we can directly set the query. Unlike the regular DB-API bindings, this will not prepare the statement. (Depending on the driver, this may or may not make a difference, especially if executing the same query multiple times.)

stmt.set_sql_query("SELECT 1 AS THEANSWER")

When we execute the query, we get an Arrow C Stream Interface handle (wrapped as a PyCapsule) that we need to import using a library like PyArrow.

handle, rowcount = stmt.execute_query()

The SQLite driver does not know the row count of the result set up front (other drivers, like the PostgreSQL driver, may know).

assert rowcount == -1

We can use the PyArrow APIs to read the result.

reader = pyarrow.RecordBatchReader.from_stream(handle)
assert reader.schema == pyarrow.schema([("THEANSWER", "int64")])

Finally, we have to clean up all the objects. (They also support the context manager protocol.)

stmt.close()
conn.close()
db.close()

Manually preparing a statement

Recipe source: driver_manager_prepare.py

The DBAPI bindings prepare all statements before execution, because of this part of the DB-API specification:

A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor can optimize its behavior.

However, you may want to prepare the statement yourself, mostly because this will give you the schema of the parameters (if supported by the server). This can be done with Cursor.adbc_prepare.

We’ll demo this with the SQLite driver, though other drivers also support this.

import pyarrow

import adbc_driver_sqlite.dbapi

conn = adbc_driver_sqlite.dbapi.connect()

with conn.cursor() as cur:
    param_schema = cur.adbc_prepare("SELECT ? + 1")
    assert param_schema == pyarrow.schema([("0", "null")])

Note that the type of the parameter here is NULL, because the driver does not know the exact type.

If we now execute the same query with the parameter, the statement will not be prepared a second time.

    cur.execute("SELECT ? + 1", parameters=(1,))
    assert cur.fetchone() == (2,)

    cur.execute("SELECT ? + 1", parameters=(41,))
    assert cur.fetchone() == (42,)

conn.close()