Arrow Flight SQL: Python (ADBC)

Bulk-ingests the demo CSV as an Arrow table and reads an aggregate back into pandas and Polars, all columnar.

The only change

# URI
 grpc+tls://flight.internal:32010
 grpc+tls://warehouse.internal:32010
# user / password (or token)
 the server's account
 MachWarehouse account

Run

python3 -m venv .venv && . .venv/bin/activate
pip install -r requirements.txt
FLIGHT_SQL_URI=grpc+tls://warehouse.internal:32010 FLIGHT_SQL_USER=app FLIGHT_SQL_PASSWORD=... python main.py

Libraries: adbc-driver-flightsql 1.13.0, adbc-driver-manager 1.13.0, pyarrow 26.0.0, pandas 3.1.0, polars 2.1.0.

Source

The program as it is kept and tested in the Mach Database Warehouse repository; highlighted lines read the connection setting.

main.py

"""Query over Arrow Flight SQL with ADBC: results land in pandas and Polars
as Arrow, with no row-by-row conversion. Plain ADBC code."""

import os

import adbc_driver_flightsql.dbapi as flightsql
import polars as pl
import pyarrow as pa
import pyarrow.csv as pacsv


def main() -> None:
    conn = flightsql.connect(
        os.environ.get("FLIGHT_SQL_URI", "grpc+tls://localhost:32010"),
        db_kwargs={
            "username": os.environ["FLIGHT_SQL_USER"],
            "password": os.environ["FLIGHT_SQL_PASSWORD"],
        },
    )
    with conn, conn.cursor() as cur:
        # Bulk ingest an Arrow table (ADBC ExecuteIngest / Flight SQL DoPut).
        events: pa.Table = pacsv.read_csv(os.path.join(os.path.dirname(__file__), "../../dataset/events.csv"))
        cur.adbc_ingest("events_flight", events, mode="create_append")
        conn.commit()

        cur.execute(
            "SELECT country, count(*) AS events, sum(revenue) AS revenue "
            "FROM events_flight GROUP BY country ORDER BY revenue DESC"
        )
        table = cur.fetch_arrow_table()
        print(table.to_pandas())
        print(pl.from_arrow(table))


if __name__ == "__main__":
    main()

requirements.txt

adbc-driver-flightsql==1.13.0
adbc-driver-manager==1.13.0
pyarrow==26.0.0
pandas==3.1.0
polars==2.1.0