zodbc

This odbc client tries to mirror pyodbc's interface and behavior, while also treating pyarrow compatibility as a first class citizen and providing various performance benefits. At the moment it is only tested with the msodbc driver. Compilation and installation is made easy by ziglang being a simple build dependency. The only system dependencies for compilation should be development headers for unixodbc (if on linux) and python.

Installation

pip install zodbc

On Linux you may also need the driver manager unixodbc as well as the odbc driver for your database, like msodbcsql.

Features

DB API 2

import zodbc
import os

con = zodbc.connect(os.environ["ODBC_CONSTR"])  # Connect with odbc connection string or DSN
cur = con.cursor()  # Cursor, which translates to an odbc statement handle

# python parameters & return values, data types like str, int, bool, UUID, decimal, date, time, datetime
cur.execute("select ? a", [42])
assert cur.fetchone()[0] == 42

# Short hand alternative for getting column names from cur.description
assert [c[0] for c in cur.description] == cur.column_names == ["a"]

con.commit()  # Transactions, autocommit is disabled by default

Fetch methods

• The DB API 2 fetch methods fetchone, fetchmany and fetchall return (lists of) Rows, which are essentially tuples that also allow named access.
• fetchtuples returns a list of tuples. Most performant option for fetching python types
• fetchdicts returns a list of dictionary records. Marginally less performant than tuples, but significantly more performant than fetching tuples and creating dictionarys from them.
• arrow returns the entire result set as an Arrow Table. Best performance, as python type overhead is skipped entirely
• arrow_batch returns a single Arrow RecordBatch of specified size. Building block for arrow.

Apache Arrow

A query can be executed for every row in an Arrow RecordBatch.

import pyarrow as pa

cur.execute("drop table if exists t1")
cur.execute("create table t1(a int)")
cur.executemany_arrow(
    "insert into t1(a) values(?)",
    pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], ["a"])
)
cur.execute("select * from t1").fetchall()  # [(1,), (2,), (3,)]

Arrow RecordBatches can be used as a table valued parameter for MS SQL.

cur.execute("drop type if exists test_tabletype")
cur.execute("create type test_tabletype as table(a int)")
con.commit()

assert cur.execute(
    "select sum(a) from ? where a <= ?",
    [
        zodbc.ArrowTVP(
            zodbc.ArrowTVPType.from_name("test_tabletype"),
            pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], ["a"]),
        ),
        2,
    ]
).fetchone()[0] == 3