Skip to main content

ArrowTDS — Examples & recipes

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

Connect and query to Arrow​

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

import adbc_driver_manager.dbapi as dbapi

with dbapi.connect(
    driver="arrowtds",
    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="arrowtds", db_kwargs={
    "adbc.arrowtds.server":            "localhost",
    "adbc.arrowtds.database":          "tpch",
    "adbc.arrowtds.username":          "sa",
    "adbc.arrowtds.password":          "<password>",
    "adbc.arrowtds.encrypt":           "true",
    "adbc.arrowtds.trust_server_cert": "true",
})

Or the DB-API shortcut from the Python package:

Python
import arrowtds_adbc
conn = arrowtds_adbc.connect("localhost", "tpch", "sa", "<password>")

Straight to pandas / Polars / DuckDB​

Because results are already Arrow, there is no row-by-row marshalling. These handoffs are Python-ecosystem specific:

Python
with conn.cursor() as cur:
    cur.execute("SELECT * FROM orders WHERE o_orderdate >= '1996-01-01'")

    df   = cur.fetch_arrow_table().to_pandas()   # pandas
    # or, without materialising twice:
    import polars as pl
    pf   = pl.from_arrow(cur.fetch_arrow_table()) # Polars
import duckdb
tbl = conn.cursor().execute("SELECT * FROM lineitem").fetch_arrow_table()
duckdb.sql("SELECT l_returnflag, count(*) FROM tbl GROUP BY 1").show()

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. Use autocommit=True for the single-connection TRUNCATE+ingest pattern (the default autocommit=False enables IMPLICIT_TRANSACTIONS and can deadlock the bulk path):

import pyarrow as pa

conn = dbapi.connect(driver="arrowtds", 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.md).

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 / Kerberos auth​

No username/password — Windows SSPI or POSIX Kerberos:

Python
conn = dbapi.connect(driver="arrowtds", db_kwargs={
    "adbc.arrowtds.server":   "sql.corp.example.com",
    "adbc.arrowtds.database": "mydb",
    "adbc.arrowtds.trusted":  "true",
    # optional explicit Kerberos credentials on Linux/macOS:
    # "adbc.arrowtds.krb5.ccache": "/tmp/krb5cc_1000",
    # "adbc.arrowtds.krb5.keytab": "/etc/krb5.keytab",
})

See AUTHENTICATION.md for SPN and credential details.

Azure SQL with Entra ID​

Access-token passthrough (see CONNECTION.md 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="arrowtds", db_kwargs={
    "adbc.arrowtds.server":       "myserver.database.windows.net",
    "adbc.arrowtds.database":     "mydb",
    "adbc.arrowtds.encrypt":      "true",
    "adbc.arrowtds.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("libarrowtds_adbc_driver.so", "AdbcDriverInit"), or by setting ARROWTDS/ADBC_DRIVER_PATH for the name-based loaders. Everything after loading is identical.

See also​