Skip to main content

ArpePGSQL — Examples & recipes

Copy-paste snippets for the common tasks. All assume the driver is installed and loadable by name (driver="arpepgsql") — see the install guide. For the full option reference behind each db_kwargs, see Connection.

Connect and query to Arrow​

The same recipe in every ADBC client — the driver is loaded by name (arpepgsql) from the installed ADBC manifest, so only the language changes:

import adbc_driver_manager.dbapi as dbapi

with dbapi.connect(
    driver="arpepgsql",
    db_kwargs={"uri": "postgresql://alice:<password>@localhost:5432/tpch?sslmode=require"},
    autocommit=True,
) as conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM lineitem LIMIT 10")
    table = cur.fetch_arrow_table()     # pyarrow.Table, COPY-binary fast path
    print(table.schema)

Connect with discrete options​

Discrete options keep secrets out of a single string and override any uri. Set them where the uri went — the rest of each program is identical to the recipe above:

conn = dbapi.connect(driver="arpepgsql", db_kwargs={
    "adbc.arpepgsql.server":   "localhost",
    "adbc.arpepgsql.port":     "5432",
    "adbc.arpepgsql.database": "tpch",
    "adbc.arpepgsql.username": "alice",
    "adbc.arpepgsql.password": "<password>",
    "adbc.arpepgsql.sslmode":  "require",
}, autocommit=True)

Straight to pandas / Polars​

The result is already Arrow, so these handoffs stay zero-copy — no row-by-row marshalling. pandas and Polars read straight from the ADBC connection:

Python
import adbc_driver_manager.dbapi as dbapi
import pandas as pd
import polars as pl

uri = "postgresql://alice:<password>@localhost:5432/tpch?sslmode=require"

with dbapi.connect(driver="arpepgsql", db_kwargs={"uri": uri}) as conn:
    # pandas — Arrow-backed dtypes, no Python-object columns
    df = pd.read_sql(
        "SELECT * FROM orders WHERE o_orderdate >= '1996-01-01'",
        conn, dtype_backend="pyarrow",
    )

    # Polars — reads the same ADBC result directly
    pf = pl.read_database(
        "SELECT * FROM orders WHERE o_orderdate >= '1996-01-01'", conn
    )

Connect and query from DuckDB​

DuckDB itself can be the ADBC client, via the third-party adbc_scanner community extension — it loads the arpepgsql driver and scans the result straight into DuckDB, in pure SQL:

-- DuckDB itself as the ADBC client, via the adbc_scanner community extension:
-- https://query.farm/products/extensions/adbc_scanner/
INSTALL adbc_scanner FROM community;
LOAD adbc_scanner;

-- Load the driver by name from the installed ADBC manifest.
SET VARIABLE conn = adbc_connect({
    'driver': 'arpepgsql',
    'uri': 'postgresql://alice:<password>@localhost:5432/tpch?sslmode=require'
});

SELECT * FROM adbc_scan(getvariable('conn'), 'SELECT * FROM lineitem LIMIT 10');

Prepared statements & parameter binding​

Bind a parameter to a $1 placeholder. In the compiled clients you bind an Arrow batch of parameters to the statement before executing:

with conn.cursor() as cur:
    cur.execute("SELECT * FROM orders WHERE o_orderkey = $1", parameters=[42])
    row = cur.fetch_arrow_table()

PostgreSQL uses $1, $2, … placeholders in the extended-query protocol. Array binding — bind a whole Arrow batch of parameters in one call (Python's executemany):

Python
with conn.cursor() as cur:
    cur.executemany(
        "INSERT INTO t(a, b) VALUES ($1, $2)",
        seq_of_parameters=[(1, "x"), (2, "y"), (3, "z")],
    )
    conn.commit()

Bulk ingest an Arrow table​

Native COPY FROM STDIN (binary) — pipe an Arrow table straight in. Use autocommit=True for the single-connection TRUNCATE+ingest pattern:

import pyarrow as pa

conn = dbapi.connect(driver="arpepgsql", db_kwargs={...}, autocommit=True)

table = pa.table({
    "id":   pa.array([1, 2, 3], pa.int32()),
    "name": pa.array(["a", "b", "c"], pa.string()),
})

with conn.cursor() as cur:
    cur.adbc_ingest("my_table", table, mode="create")   # create | append | replace | create_append
note

Rust & C++ have no one-call adbc_ingest helper: set the standard statement options adbc.ingest.target_table / adbc.ingest.mode, Bind the Arrow batch, then execute_update (Rust) / AdbcStatementExecuteQuery (C++).

Ingest into a specific catalog/schema or a temp table via the statement options adbc.ingest.target_catalog, adbc.ingest.target_db_schema, adbc.ingest.temporary (see Connection → Statement & ingest options). The ingest path introspects the target column OIDs, so an Arrow utf8 column lands correctly in a jsonb / inet / uuid column — see Data types → Write path.

Export a query to Parquet​

Python
import pyarrow.parquet as pq

with conn.cursor() as cur:
    cur.execute("SELECT * FROM lineitem")
    reader = cur.fetch_record_batch()            # streaming RecordBatchReader
    with pq.ParquetWriter("lineitem.parquet", reader.schema) as w:
        for batch in reader:
            w.write_batch(batch)

Streaming keeps memory flat regardless of result size: the driver hands over one batch of buffer_size rows (default 32,000) at a time.

TLS with certificate verification​

Verify the server certificate chain and hostname, and pin a custom root CA:

Python
conn = dbapi.connect(driver="arpepgsql", db_kwargs={
    "adbc.arpepgsql.server":        "db.example.com",
    "adbc.arpepgsql.database":      "mydb",
    "adbc.arpepgsql.username":      "alice",
    "adbc.arpepgsql.password":      "<password>",
    "adbc.arpepgsql.sslmode":       "verify-full",
    "adbc.arpepgsql.ssl_root_cert": "/etc/ssl/certs/pg-root.pem",
    # bind SCRAM to the TLS channel, refusing a PLUS-stripping downgrade:
    "adbc.arpepgsql.channel_binding": "require",
}, autocommit=True)

See Connection → TLS & sslmode for the sslmode matrix.

Integrated authentication (Windows)​

Windows only: log in as the current Windows user through SSPI, with no password. Integrated authentication is not available with the Linux driver.

Python
conn = dbapi.connect(driver="arpepgsql", db_kwargs={
    "adbc.arpepgsql.server":    "db.example.com",   # FQDN the SPN is registered for
    "adbc.arpepgsql.database":  "mydb",
    "adbc.arpepgsql.username":  "alice",            # PostgreSQL role mapped to your Windows login
    "adbc.arpepgsql.auth_type": "integrated",
    # optional, when the SPN is not POSTGRES/db.example.com:
    # "adbc.arpepgsql.krb5.spn": "POSTGRES/pg-cluster.example.com",
})

See Authentication for SPN and role mapping details.

Loading by explicit path​

Load-by-name (above) uses the ADBC manifest the installer registers. When the driver is not installed system-wide, point the driver manager at the shared library instead (libarpepgsql_adbc_driver-linux-x64.so on Linux, arpepgsql_adbc_driver-win-x64.dll on Windows) — e.g. pass the full path as driver= in Python, or in C# use CAdbcDriverImporter.Load("arpepgsql_adbc_driver-win-x64.dll", "AdbcDriverInit"). To keep loading by name with a manifest in a non-standard directory, point the driver manager's ADBC_DRIVER_PATH environment variable at that directory. Everything after loading is identical.

See also​