adbc-driver-spanner

adbc-driver-spanner

A Python ADBC driver for Google Cloud Spanner.

Query Spanner through a standard DBAPI 2.0 connection and get results back as Apache Arrow — ready to hand straight to pandas, polars, DuckDB, or PyArrow with no per-row Python conversion.

Install

pip install adbc-driver-spanner

# For the DataFrame / Arrow helpers (fetch_df, fetch_arrow_table, adbc_ingest, …):
pip install "adbc-driver-spanner[dbapi]" pandas

The wheels ship a prebuilt native library, so there is nothing to compile. Prebuilt wheels are published for Linux (x86-64 glibc + aarch64 glibc, plus x86-64 and aarch64 musl for Alpine), macOS (arm64, x86-64), and Windows (x86-64, arm64) — see Supported platforms for the minimum OS / libc each one requires.

Quickstart

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT SingerId, FirstName FROM Singers")
        df = cur.fetch_df()          # -> pandas.DataFrame

connect() returns an ordinary DBAPI connection: use cur.execute(...) with ?/@name parameters, cur.fetchone() / cur.fetchall(), conn.commit(), and so on. The fetch_* helpers below add zero-copy Arrow output on top.

Authentication

By default the driver uses Application Default Credentials (ADC) — the same credentials gcloud auth application-default login and Google Cloud runtimes provide. To use a service-account key instead, pass its path or its JSON as a raw option in db_kwargs:

# docs-test: skip
import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

spanner.connect(db_kwargs={
    DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d",
    DatabaseOptions.KEYFILE.value: "/path/to/service-account.json",
})

To impersonate another service account on top of your base credentials, set DatabaseOptions.IMPERSONATE_TARGET_PRINCIPAL:

# docs-test: skip
spanner.connect(db_kwargs={
    DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d",
    DatabaseOptions.IMPERSONATE_TARGET_PRINCIPAL.value: "target@p.iam.gserviceaccount.com",
    DatabaseOptions.IMPERSONATE_SCOPES.value: "https://www.googleapis.com/auth/cloud-platform",
})

Set DatabaseOptions.ACCESS_TOKEN to authenticate with an OAuth 2.0 bearer token you already hold (for example from gcloud auth print-access-token). It is sent verbatim with no refresh, and is mutually exclusive with DatabaseOptions.KEYFILE / DatabaseOptions.KEYFILE_JSON / DatabaseOptions.IMPERSONATE_TARGET_PRINCIPAL:

# docs-test: skip
spanner.connect(db_kwargs={
    DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d",
    DatabaseOptions.ACCESS_TOKEN.value: "ya29.a0Af...",
})

To talk to the Spanner emulator, point at its endpoint and set DatabaseOptions.EMULATOR to "true" (which connects with anonymous credentials):

# docs-test: skip
spanner.connect(db_kwargs={
    DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d",
    DatabaseOptions.ENDPOINT.value: "localhost:9010",
    DatabaseOptions.EMULATOR.value: "true",
})

Connection options

connect() takes just three keyword arguments — every driver setting travels as an option key, best spelled with the DatabaseOptions / ConnectionOptions / StatementOptions constants (see Typed option keys):

A database URI is required; everything else is optional. The database-level credential and endpoint options are:

Every other setting is passed the same way — via db_kwargs= (database-level), conn_kwargs= (connection-level), or per cursor with conn.cursor(adbc_stmt_kwargs={...}). The complete, authoritative list — every option with its type, default, and behaviour — is in docs/options.md. A few that are handy from Python:

Typed option keys

The DatabaseOptions, ConnectionOptions, and StatementOptions enums (each member's .value is the raw key) are the recommended way to name options — for typo-safety and discoverability, the same style as the BigQuery ADBC driver's DatabaseOptions:

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import ConnectionOptions, DatabaseOptions, StatementOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d"},
    conn_kwargs={ConnectionOptions.READ_STALENESS.value: "max:10s"},
    autocommit=True,  # one-shot reads: bounded staleness lets Spanner pick the freshest replica
) as conn:
    cur = conn.cursor(
        adbc_stmt_kwargs={StatementOptions.ROWS_PER_BATCH.value: "1024"}
    )
    cur.execute("SELECT * FROM Singers")

(In the default manual-transaction mode, queries share one multi-use read-only transaction — see Transactions — and Spanner accepts the bounded-staleness kinds only on single-use reads, so a max:<d>/min:<t> bound is pinned there to its most-stale legal equivalent: exact staleness <d> / read timestamp <t>.)

The enums cover the full option surface for the db_kwargs= / conn_kwargs= / adbc_stmt_kwargs= escape hatches. Every key is documented in docs/options.md.

Read-only connections

Pass conn_kwargs={ConnectionOptions.READONLY.value: "true"} to guarantee a connection can only read — any INSERT/UPDATE/DELETE, DDL, or bulk ingest raises, while queries still run:

# docs-test: skip
import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import ConnectionOptions, DatabaseOptions

with spanner.connect(db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/p/instances/i/databases/d"},
                     conn_kwargs={ConnectionOptions.READONLY.value: "true"}) as conn:
    conn.cursor().execute("SELECT 1")   # ok
    # any INSERT/UPDATE/DELETE, DDL or adbc_ingest raises

The guarantee also covers conn.commit(): in the default manual-transaction mode DML buffers until commit (see Transactions), so a connection turned read-only after some DML was buffered raises ProgrammingError on conn.commit() — and on switching the connection to autocommit, which commits pending work — instead of writing. The transaction stays open and replayable: clear the flag and commit again to apply it, or conn.rollback() (never gated — discarding buffered work writes nothing) to discard it. Committing a query transaction is likewise always allowed.

Smaller result batches

Results stream back as Arrow record batches. Lower spanner.rows_per_batch on the cursor to cap peak memory on a wide or large result:

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions, StatementOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        cur.adbc_statement.set_options(**{StatementOptions.ROWS_PER_BATCH.value: "1024"})
        cur.execute("SELECT SingerId, FirstName FROM Singers")
        reader = cur.fetch_record_batch()    # batches of <= 1024 rows
        table = reader.read_all()

Transactions

A DBAPI connection is autocommit-off by default, so statements run in manual transactions ended by conn.commit() (or discarded by conn.rollback()). A manual transaction is exactly one kind of work — queries or DML — fixed by its first statement; a statement of the other kind raises adbc_driver_manager.ProgrammingError (ADBC InvalidState) until you commit or roll back:

• Queries share one snapshot. The first query opens a Spanner multi-use read-only transaction, and every query until commit()/rollback() reads from that same consistent snapshot — rows committed by others in the meantime stay invisible. Ending a query transaction is free (Spanner read-only transactions need no commit RPC), so commit or roll back as soon as you no longer need the snapshot.
• DML is buffered — no read-your-writes. INSERT/UPDATE/DELETE (and bulk ingest) buffer and apply atomically on conn.commit(). A query inside a DML transaction could not see the buffered writes, so it is rejected rather than silently returning a stale (pre-insert) result.
• DDL is not transaction-aware. CREATE/ALTER/DROP always apply immediately (Spanner DDL runs through the admin API and is never transactional — the same no-special-handling approach as the ADBC BigQuery driver), regardless of the transaction: commit() is not needed and rollback() cannot undo them, and DDL issued after buffered DML executes before it. A ;-separated DDL batch still applies as one UpdateDatabaseDdl call.

Connect with autocommit=True if you want every statement to apply immediately.

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_manager import ProgrammingError
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:  # DBAPI default: autocommit off => manual transactions
    with conn.cursor() as cur:
        # DDL applies immediately — no commit needed, and rollback cannot undo it.
        cur.execute("DROP TABLE IF EXISTS Albums")
        cur.execute("CREATE TABLE Albums (Id INT64 NOT NULL) PRIMARY KEY (Id)")

        cur.execute("INSERT INTO Albums (Id) VALUES (1)")  # a DML transaction: buffered
        # Querying while the INSERT is buffered is rejected (no read-your-writes) instead of
        # silently returning a stale count.
        try:
            cur.execute("SELECT COUNT(*) FROM Albums")
            raise AssertionError("expected the guarded query to raise")
        except ProgrammingError:
            pass  # commit (or roll back) first to see the write
    conn.commit()  # the buffered INSERT is applied here, atomically

    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM Albums")  # a query transaction: pins a snapshot
        assert cur.fetchone()[0] == 1  # visible only after the DML commit
    conn.rollback()  # ends the query transaction (its snapshot) without a round-trip

Working with DataFrames

Results come back as Apache Arrow, so they flow into the popular DataFrame libraries without a per-row conversion. The DataFrame / Arrow paths need the [dbapi] extra (which pulls in PyArrow). Remember that writes need conn.commit() unless you connect with autocommit=True.

All examples assume a Singers(SingerId INT64, FirstName STRING) table.

pandas:

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT SingerId, FirstName FROM Singers ORDER BY SingerId")
        df = cur.fetch_df()                  # -> pandas.DataFrame

pyarrow — results as a native Arrow table:

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT SingerId, FirstName FROM Singers ORDER BY SingerId")
        table = cur.fetch_arrow_table()      # -> pyarrow.Table

polars — read straight from the connection:

import polars as pl
import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    df = pl.read_database(
        "SELECT SingerId, FirstName FROM Singers ORDER BY SingerId",
        connection=conn,                     # an ADBC connection, not a URI
    )

DuckDB — query the fetched Arrow table in-process:

import duckdb
import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT SingerId, FirstName FROM Singers")
        singers = cur.fetch_arrow_table()

# `singers` is a pyarrow.Table; DuckDB queries it by variable name, no copy.
top = duckdb.sql("SELECT COUNT(*) AS n, MIN(FirstName) AS first FROM singers").fetchone()

Bulk insert a DataFrame

cur.adbc_ingest(table, data, mode=...) inserts an Arrow table (or anything Arrow-convertible, like a pandas DataFrame) in bulk, without writing SQL:

import pandas as pd
import pyarrow as pa
import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions

frame = pd.DataFrame({"SingerId": [10, 11], "FirstName": ["Carol", "Dave"]})

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
    autocommit=True,                         # apply immediately; returns the row count
) as conn:
    with conn.cursor() as cur:
        # `append` inserts into an existing table (the default mode is `create`).
        rows = cur.adbc_ingest("Singers", pa.Table.from_pandas(frame), mode="append")

The mode selects how the target table is handled:

• create — create the table from the data's Arrow schema first, erroring if it already exists (the default).
• append — insert into an existing table.
• create_append — create the table only if it is absent, then insert.
• replace — drop any existing table, recreate it from the schema, then insert.

Spanner requires a primary key on every table, but an ingested Arrow batch has none, so the three create modes add a synthetic adbc_ingest_key column (a UUID string) as the primary key. It is not part of your data, but it is a real column and will show up in SELECT *.

Partitioned reads and Data Boost

A large scan can be split into independent partitions and read in parallel — optionally on Spanner's serverless Data Boost compute, so the work is isolated from your provisioned instance. This uses the ADBC partitioned-execution extension (adbc_execute_partitions / adbc_read_partition):

import adbc_driver_spanner.dbapi as spanner
from adbc_driver_spanner import DatabaseOptions, StatementOptions

with spanner.connect(
    db_kwargs={DatabaseOptions.URI.value: "spanner:///projects/my-project/instances/my-instance/databases/my-db"},
) as conn:
    with conn.cursor() as cur:
        # Optional statement options, set on the underlying ADBC statement:
        cur.adbc_statement.set_options(**{
            StatementOptions.DATA_BOOST.value: "true",  # run on Data Boost
            StatementOptions.MAX_PARTITIONS.value: "8",     # cap the partition count
        })
        partitions, schema = cur.adbc_execute_partitions("SELECT SingerId FROM Singers")

    # Each descriptor is opaque bytes; it can be shipped to another worker,
    # process, or connection and read independently.
    for token in partitions:
        with conn.cursor() as cur:
            cur.adbc_read_partition(token)
            table = cur.fetch_arrow_table()
            ...

Only single-table scans are partitionable — queries with an ORDER BY or aggregation are not.

A descriptor is opaque but executable: it carries the SQL text plus the session and transaction identity, so adbc_read_partition runs whatever it contains with the connection's credentials, and it is not authenticated. Ship descriptors only over trusted channels, and never read one from an untrusted source.

Supported platforms

Each wheel bundles a native library and carries a platform tag with a minimum-OS floor. pip picks the matching wheel automatically:

Any Python 3 works — the wheels are ABI-agnostic. On an older glibc or macOS than the floor above, pip finds no matching wheel; build the native driver from source instead.