ADBC PostgreSQL Driver

Warnings

• deprecated The `ADBC:postgresql:typname` metadata key, previously attached to schema fields for unknown columns, has been deprecated in favor of the `Opaque` canonical extension type. Users should not rely on this key's continued existence.
  Fix: Migrate to using the `Opaque` extension type metadata for differentiating binary column intent.
• gotcha The PostgreSQL driver uses `COPY` for optimal performance in query execution by default. However, this optimization is not supported for all queries (e.g., `SHOW` queries). Such queries may fail or produce unexpected results.
  Fix: For queries incompatible with `COPY`, disable the optimization by setting the statement option `adbc.postgresql.use_copy` to `False` (or `0`).
• gotcha The PostgreSQL ADBC driver's support for prepared statements with parameters is currently limited to queries that *do not* return result sets (e.g., `INSERT`, `UPDATE`). This is due to the driver's reliance on the `COPY` protocol for performance, which is not compatible with parameterized `SELECT` statements.
  Fix: Avoid using prepared statements with parameters for `SELECT` queries that are expected to return results. Construct queries with string formatting (carefully, to avoid SQL injection) or use alternative fetching mechanisms for such cases.
• gotcha There are known limitations and specific behaviors in type mapping between PostgreSQL and Arrow/Python types. Notably, PostgreSQL `NUMERIC` types are read as their string representation, and time zone information in `timestamp` values is ignored during binding.
  Fix: Be aware of type conversions and handle them explicitly in your application code. For `NUMERIC`, parse the string to the desired numeric type. For timestamps, ensure time zone handling is managed before binding or after fetching, if critical.
• gotcha Failure to explicitly close connections and cursors can lead to resource leaks (e.g., database connections remaining open indefinitely). While the Python driver manager attempts to close unclosed cursors when a connection is closed, explicit management is best practice.
  Fix: Always use `with` statements for `connect()` and `cursor()` calls to ensure proper resource management and automatic closure. For manual connections, ensure `conn.close()` is called in a `finally` block.

Install

• pip install adbc-driver-postgresql PyPI
• mamba install adbc-driver-postgresql Conda-forge
• # Note: This Python package requires an underlying C/C++ ADBC PostgreSQL driver (libadbc_driver_postgresql.so/.dll/.dylib) to be available in your system's library path or specified via ADBC_POSTGRESQL_LIBRARY environment variable. Standard pip/conda installations often handle this for common platforms. export ADBC_POSTGRESQL_LIBRARY=/path/to/libadbc_driver_postgresql.so C Driver Dependency (Advanced)

Imports

• connect

  import adbc_driver_postgresql.dbapi
  conn = adbc_driver_postgresql.dbapi.connect(...)

Quickstart

This quickstart demonstrates how to connect to a PostgreSQL database using `adbc-driver-postgresql.dbapi`, execute DDL and DML statements, and fetch results as both an Apache Arrow Table and a single row. It assumes a PostgreSQL instance is accessible via the provided URI, preferably set via an environment variable for security.

import os
import adbc_driver_postgresql.dbapi

# Configure your PostgreSQL connection URI.
# For example, using a local Dockerized PostgreSQL with default credentials:
# docker run -it --rm -e POSTGRES_PASSWORD=password -e POSTGRES_DB=testdb -p 5432:5432 postgres:latest
# export ADBC_POSTGRESQL_TEST_URI="postgresql://postgres:password@localhost:5432/testdb"
uri = os.environ.get("ADBC_POSTGRESQL_TEST_URI", "postgresql://user:password@localhost:5432/mydb")

try:
    with adbc_driver_postgresql.dbapi.connect(uri) as conn:
        with conn.cursor() as cur:
            # DDL operations
            cur.execute("DROP TABLE IF EXISTS example_adbc;")
            cur.execute("CREATE TABLE example_adbc (id INTEGER, name VARCHAR(50));")

            # DML operations with bind parameters
            cur.executemany("INSERT INTO example_adbc VALUES (?, ?);", [(1, 'Alice'), (2, 'Bob')])
            conn.commit() # Commit changes for DML

            # Query data and fetch as an Arrow Table (requires pyarrow)
            cur.execute("SELECT id, name FROM example_adbc WHERE id > 0;")
            result_table = cur.fetch_arrow_table()
            print("Fetched Arrow Table:")
            print(result_table)

            # Query data and fetch a single row
            cur.execute("SELECT id, name FROM example_adbc WHERE id = 1;")
            first_row = cur.fetchone()
            print(f"\nFetched one row: {first_row}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Ensure your PostgreSQL database is running and ADBC_POSTGRESQL_TEST_URI is set correctly.")