Quickstart (Python)

Here we’ll briefly tour basic features of ADBC with the SQLite driver.

Installation

pip install adbc_driver_manager adbc_driver_sqlite pyarrow

DBAPI (PEP 249)-style API

If PyArrow is installed, ADBC provides a high-level API in the style of the DBAPI standard.

Creating a Connection

>>> import adbc_driver_sqlite.dbapi
>>> conn = adbc_driver_sqlite.dbapi.connect()

In application code, the connection must be closed after usage or memory may leak. Connections can be used as context managers to accomplish this.

Creating a Cursor

>>> cursor = conn.cursor()

In application code, the cursor must be closed after usage or memory may leak. Cursors can be used as context managers to accomplish this.

Executing a Query

We can execute a query and get the results via the normal, row-oriented DBAPI interface:

>>> cursor.execute("SELECT 1, 2.0, 'Hello, world!'")
>>> cursor.fetchone()
(1, 2.0, 'Hello, world!')
>>> cursor.fetchone()

We can also get the results as Arrow data via a non-standard method:

>>> cursor.execute("SELECT 1, 2.0, 'Hello, world!'")
>>> cursor.fetch_arrow_table()
pyarrow.Table
1: int64
2.0: double
'Hello, world!': string
----
1: [[1]]
2.0: [[2]]
'Hello, world!': [["Hello, world!"]]

Parameterized Queries

We can bind parameters in our queries:

>>> cursor.execute("SELECT ? + 1 AS the_answer", parameters=(41,))
>>> cursor.fetch_arrow_table()
pyarrow.Table
the_answer: int64
----
the_answer: [[42]]

Ingesting Bulk Data

So far we’ve mostly demonstrated the usual DBAPI interface. The ADBC APIs also offer additional methods. For example, we can insert a table of Arrow data into a new database table:

>>> import pyarrow
>>> table = pyarrow.table([[1, 2], ["a", None]], names=["ints", "strs"])
>>> cursor.adbc_ingest("sample", table)
2
>>> cursor.execute("SELECT COUNT(DISTINCT ints) FROM sample")
>>> cursor.fetchall()
[(2,)]

We can also append to an existing table:

>>> table = pyarrow.table([[2, 3], [None, "c"]], names=["ints", "strs"])
>>> cursor.adbc_ingest("sample", table, mode="append")
2
>>> cursor.execute("SELECT COUNT(DISTINCT ints) FROM sample")
>>> cursor.fetchall()
[(3,)]

Getting Database/Driver Metadata

We can get information about the driver and the database using another extension method, this time on the connection itself:

>>> conn.adbc_get_info()["vendor_name"]
'SQLite'
>>> conn.adbc_get_info()["driver_name"]
'ADBC SQLite Driver'

We can also query for tables and columns in the database. This gives a nested structure describing all the catalogs, schemas, tables, and columns:

>>> info = conn.adbc_get_objects().read_all().to_pylist()
>>> main_catalog = info[0]
>>> schema = main_catalog["catalog_db_schemas"][0]
>>> tables = schema["db_schema_tables"]
>>> tables[0]["table_name"]
'sample'
>>> [column["column_name"] for column in tables[0]["table_columns"]]
['ints', 'strs']

We can get the Arrow schema of a table:

>>> conn.adbc_get_table_schema("sample")
ints: int64
strs: string