Metadata-Version: 2.4
Name: zodbc
Version: 0.9.3
Summary: DBAPI2-like ODBC client with Apache Arrow support
Author: Felix Graßl
License-Expression: MIT
Project-URL: Homepage, https://github.com/ffelixg/zodbc_py
Keywords: odbc,arrow,mssql,sql server
Classifier: Programming Language :: Python
Classifier: Topic :: Database
Classifier: Intended Audience :: Developers
Requires-Python: >=3.12
Description-Content-Type: text/markdown
Provides-Extra: pyarrow
Requires-Dist: pyarrow; extra == "pyarrow"

# 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

```bash
pip install zodbc
```
On Linux you may also need the driver manager `unixodbc` as well as the odbc driver for your database, like [msodbcsql](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server).


# Features


## DB API 2

```py
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.

```py
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.

```py
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
```
