Skip to main content

ArpeMSSQL — Examples & recipes

Copy-paste snippets for the common tasks. All assume the driver is installed and loadable by name (driver="arpemssql") — see Install. 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 (arpemssql) from the installed ADBC manifest, so only the language changes:

import adbc_driver_manager.dbapi as dbapi

with dbapi.connect(
    driver="arpemssql",
    db_kwargs={"uri": "sqlserver://sa:<password>@localhost:1433/"
                      "?database=tpch&encrypt=true&TrustServerCertificate=true"},
) as conn, conn.cursor() as cur:
    cur.execute("SELECT TOP 10 * FROM lineitem")
    table = cur.fetch_arrow_table()     # pyarrow.Table
    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="arpemssql", db_kwargs={
    "adbc.arpemssql.server":            "localhost",
    "adbc.arpemssql.database":          "tpch",
    "adbc.arpemssql.username":          "sa",
    "adbc.arpemssql.password":          "<password>",
    "adbc.arpemssql.encrypt":           "true",
    "adbc.arpemssql.trust_server_cert": "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 = ("sqlserver://sa:<password>@localhost:1433/"
       "?database=tpch&encrypt=true&TrustServerCertificate=true")

with dbapi.connect(driver="arpemssql", db_kwargs={"uri": uri}) as conn:
    # pandas — Arrow-backed dtypes, no Python-object columns
    df = pd.read_sql("SELECT * FROM dbo.Orders", conn, dtype_backend="pyarrow")

    # Polars — reads the same ADBC result directly
    pf = pl.read_database("SELECT * FROM dbo.Orders", conn)

Connect and query from DuckDB​

DuckDB itself can be the ADBC client, via the third-party adbc_scanner community extension — it loads the arpemssql 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': 'arpemssql',
    'uri': 'sqlserver://sa:<password>@localhost:1433/?database=tpch&encrypt=true&TrustServerCertificate=true'
});

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

Prepared statements & parameter binding​

Bind a parameter to a ? 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 = ?", parameters=[42])
    row = cur.fetch_arrow_table()

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 (?, ?)",
        seq_of_parameters=[(1, "x"), (2, "y"), (3, "z")],
    )
    conn.commit()

Bulk ingest an Arrow table​

Native TDS INSERT BULK — pipe an Arrow table straight in. With autocommit=True each ingest is one transaction: it lands whole or not at all. With autocommit=False (the Python DB-API default) the ingest joins your open transaction, so call commit() to keep it:

import pyarrow as pa

conn = dbapi.connect(driver="arpemssql", 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 Statement & ingest options).

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 auto-flushes a batch early if a wide column would cross Arrow's 2 GiB offset limit.

Integrated auth (Windows)​

No username/password — the driver signs in as the logged-in Windows user via SSPI (Kerberos). Available on Windows only:

Python
conn = dbapi.connect(driver="arpemssql", db_kwargs={
    "adbc.arpemssql.server":   "sql.corp.example.com",
    "adbc.arpemssql.database": "mydb",
    "adbc.arpemssql.trusted":  "true",
    # optional, only if the registered SPN differs from MSSQLSvc/<host>:<port>:
    # "adbc.arpemssql.krb5.spn": "MSSQLSvc/sql.corp.example.com:1433",
})

See Authentication for SPN details.

Azure SQL with Entra ID​

Access-token passthrough (see Connection for service-principal / managed-identity / default-chain variants):

Python
import subprocess
token = subprocess.check_output(
    ["az", "account", "get-access-token",
     "--resource", "https://database.windows.net/",
     "--query", "accessToken", "-o", "tsv"]).decode().strip()

conn = dbapi.connect(driver="arpemssql", db_kwargs={
    "adbc.arpemssql.server":       "myserver.database.windows.net",
    "adbc.arpemssql.database":     "mydb",
    "adbc.arpemssql.encrypt":      "true",
    "adbc.arpemssql.access_token": token,
})

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 — e.g. in C# via CAdbcDriverImporter.Load("libarpemssql_adbc_driver-linux-x64.so", "AdbcDriverInit") (arpemssql_adbc_driver-win-x64.dll on Windows), or by setting ADBC_DRIVER_PATH for the name-based loaders (see the install guide). Everything after loading is identical.

See also​