Skip to main content

arrow-odbc-py

Licence PyPI version Documentation Status

Fill Apache Arrow arrays from ODBC data sources. This package is build on top of the pyarrow Python package and arrow-odbc Rust crate and enables you to read the data of an ODBC data source as sequence of Apache Arrow record batches.

  • Fast. Makes efficient use of ODBC bulk reads and writes, to lower IO overhead.
  • Flexible. Query any ODBC data source you have a driver for. MySQL, MS SQL, Excel, ...
  • Portable. Easy to install and update dependencies. No binary dependency to specific implemenations of Python interpreter, Arrow or ODBC driver manager.

About Arrow

Apache Arrow defines a language-independent columnar memory format for flat and hierarchical data, organized for efficient analytic operations on modern hardware like CPUs and GPUs. The Arrow memory format also supports zero-copy reads for lightning-fast data access without serialization overhead.

About ODBC

ODBC (Open DataBase Connectivity) is a standard which enables you to access data from a wide variaty of data sources using SQL.

Usage

Query

from arrow_odbc import connect

connection_string = (
    "Driver={ODBC Driver 18 for SQL Server};Server=localhost;TrustServerCertificate=yes;"
)

connection = connect(
    connection_string=connection_string,
    user="SA",
    password="My@Test@Password",
)
reader = connection.read_arrow_batches(
    query=f"SELECT * FROM MyTable WHERE a=?",
    parameters=["I'm a positional query parameter"],
)

for batch in reader:
    # Process arrow batches
    df = batch.to_pandas()
    # ...

Insert

from arrow_odbc import insert_into_table
import pyarrow as pa
import pandas


def dataframe_to_table(df):
    table = pa.Table.from_pandas(df)
    reader = pa.RecordBatchReader.from_batches(table.schema, table.to_batches())

    connection = connect(
        connection_string=connection_string,
        user="SA",
        password="My@Test@Password",
    )
    connection.insert_into_table(
        chunk_size=1000,
        table="MyTable",
        reader=reader,
    )

Installation

Installing ODBC driver manager

The provided wheels dynamically link against the driver manager, which must be provided by the system.

Windows

Nothing to do. ODBC driver manager is preinstalled.

Ubuntu

sudo apt-get install unixodbc-dev

OS-X

You can use homebrew to install UnixODBC

brew install unixodbc

Installing the wheel

This package has been designed to be easily deployable, so it provides a prebuild many linux wheel which is independent of the specific version of your Python interpreter and the specific Arrow Version you want to use. It will dynamically link against the ODBC driver manager provided by your system.

Wheels have been uploaded to PyPi and can be installed using pip. The wheel (including the manylinux wheel) will link against the your system ODBC driver manager at runtime. If there are no prebuild wheels for your platform, you can build the wheel from source. For this the rust toolchain must be installed.

pip install arrow-odbc

arrow-odbc utilizes cffi and the Arrow C-Interface to glue Rust and Python code together. Therefore the wheel does not need to be build against the precise version either of Python or Arrow.

Installing with conda

conda install -c conda-forge arrow-odbc

Warning: The conan recipie is currently unmaintained. So to install the newest version you need to either install from source or use a wheel deployed via pip.

Building wheel from source

There is no ready made wheel for the platform you want to target? Do not worry, you can probably build it from source.

Encodings for SQL statement text

ODBC applications use either narrow or wide encodings. The narrow encoding is either UTF-8 or an extended ASCII, the wide encoding is always UTF-16. The narrow encoding is supposed to be governed by the system locale. arrow-odbc-py chooses to use the wide encoding on windows platform and the narrow ones on all others (e.g. Linux, Mac). UTF-8 is the default locale on many of these systems, and the wide paths are typically less battletested on Mac or Linux drivers. On the other hand, most Windows platforms do not have yet a UTF-8 local active by default. Over all the guess is, that sticking to UTF-16 on windows and hoping for a UTF-8 local and driver support on other Platform, results in the least problems on average.

Your milage may vary though. Please note that the encoding for the parameters and results of your queries can be controlled at runtime with the payload_text_encoding parameter of Connection.read_arrow_batches.

The encoding used for the statement text itself, e.g. for column names is controlled at compile time though. With the wheels deployed to pypi you will always get the wide encoding on Windows and the narrow encoding on the other platforms. If this does not work for you, you can build the wheel yourself with a different encoding. If you can build the wheel from source as described above, you can also change the compile time features flags.

E.g. to build the wheel with the wide encoding use:

uv run maturin build --features wide

or, to use the narrow encoding for windows:

uv run maturin build --features narrow

Matching of ODBC to Arrow types then querying

ODBC Arrow
Numeric(p <= 38) Decimal128
Decimal(p <= 38, s >= 0) Decimal128
Integer Int32
SmallInt Int16
Real Float32
Float(p <=24) Float32
Double Float64
Float(p > 24) Float64
Date Date32
LongVarbinary Binary
Time(p = 0) Time32Second
Time(p = 1..3) Time32Millisecond
Time(p = 4..6) Time64Microsecond
Time(p = 7..9) Time64Nanosecond
Timestamp(p = 0) TimestampSecond
Timestamp(p: 1..3) TimestampMilliSecond
Timestamp(p: 4..6) TimestampMicroSecond
Timestamp(p >= 7 ) TimestampNanoSecond
BigInt Int64
TinyInt Signed Int8
TinyInt Unsigned UInt8
Bit Boolean
Varbinary Binary
Binary FixedSizedBinary
All others Utf8

Matching of Arrow to ODBC types then inserting

Arrow ODBC
Utf8 VarChar
Decimal128(p,s) Decimal(p,s)
Decimal256(p,s) Decimal(p,s)
Int8 TinyInt
Int16 SmallInt
Int32 Integer
Int64 BigInt
Float16 Real
Float32 Real
Float64 Double
Timestamp s Timestamp(7)
Timestamp ms Timestamp(7)
Timestamp us Timestamp(7)
Timestamp ns Timestamp(7)
Timestamp with Tz s VarChar(25)
Timestamp with Tz ms VarChar(29)
Timestamp with Tz us VarChar(32)
Timestamp with Tz ns VarChar(35)
Date32 Date
Date64 Date
Time32 s Time
Time32 ms VarChar(12)
Time64 us VarChar(15)
Time64 ns VarChar(16)
Binary Varbinary
FixedBinary(l) Varbinary(l)
All others Unsupported

Comparision to other Python ODBC bindings

  • pyodbc - General purpose ODBC python bindings. In contrast arrow-odbc is specifically concerned with bulk reads and writes to arrow arrays.
  • turbodbc - Complies with the Python Database API Specification 2.0 (PEP 249) which arrow-odbc does not aim to do. Like arrow-odbc bulk read and writes is the strong point of turbodbc. turbodbc has more system dependencies, which can make it cumbersome to install if not using conda. turbodbc is build against the C++ implementation of Arrow, which implies it is only compatible with matching version of pyarrow.

Metadata

Release files for arrow-odbc 10.7.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for arrow-odbc 10.7.0
File Size Uploaded Provenance Actions
arrow_odbc-10.7.0.tar.gz 120.5 kB Without provenance Details for arrow_odbc-10.7.0.tar.gz

Built distributions (wheels)

Table of built distributions (wheels) for arrow-odbc 10.7.0
File Actions
arrow_odbc-10.7.0-py3-none-win_amd64.whl Python 3 none Windows x86-64 Details for arrow_odbc-10.7.0-py3-none-win_amd64.whl
arrow_odbc-10.7.0-py3-none-manylinux_2_28_x86_64.whl Python 3 none Linux glibc 2.28+ x86-64 Details for arrow_odbc-10.7.0-py3-none-manylinux_2_28_x86_64.whl
arrow_odbc-10.7.0-py3-none-manylinux_2_28_aarch64.whl Python 3 none Linux glibc 2.28+ ARM64 Details for arrow_odbc-10.7.0-py3-none-manylinux_2_28_aarch64.whl
arrow_odbc-10.7.0-py3-none-macosx_11_0_arm64.whl Python 3 none macOS 11.0+ ARM64 Details for arrow_odbc-10.7.0-py3-none-macosx_11_0_arm64.whl
arrow_odbc-10.7.0-py3-none-macosx_10_12_x86_64.whl Python 3 none macOS 10.12+ x86-64 Details for arrow_odbc-10.7.0-py3-none-macosx_10_12_x86_64.whl

Total release size: 4.0 MB

Release files / arrow_odbc-10.7.0.tar.gz

Download URL arrow_odbc-10.7.0.tar.gz
Size 120.5 kB
Tags Source
SHA-256 checksum
How to use checksums
3f112ba000ecb0337655d9bbb8b5c5f881d95d39c2489c3d93bec6e7c092fc2a
BLAKE2b-256 checksum
How to use checksums
5457c45c3d6c9a27e3e87dbec14fbaf8a749c399e7a09bec3e5a540bdf81f2b6
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / arrow_odbc-10.7.0-py3-none-win_amd64.whl

Download URL arrow_odbc-10.7.0-py3-none-win_amd64.whl
Size 662.6 kB
Tags Python 3 Windows x86-64
SHA-256 checksum
How to use checksums
c1e465c4ce54107eb0747e168876ca07c6a5a0b1651e5474ca33630a6651b26a
BLAKE2b-256 checksum
How to use checksums
22ae77dc3b219860279ab70abed241901e370080b565c7d642a060491524e16c
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":null,"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / arrow_odbc-10.7.0-py3-none-manylinux_2_28_x86_64.whl

Download URL arrow_odbc-10.7.0-py3-none-manylinux_2_28_x86_64.whl
Size 874.8 kB
Tags Linux glibc 2.28+ x86-64 Python 3
SHA-256 checksum
How to use checksums
1d30729c21b81f1ceb67cf7933dc3ece5b11c74d517acce10eb9eac5d3d4f6ce
BLAKE2b-256 checksum
How to use checksums
cfda185f20a8580dac0bbe419f8798bf7bcbc2f6fb1645b1fef6cddefd7c4876
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"26.04","id":"resolute","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / arrow_odbc-10.7.0-py3-none-manylinux_2_28_aarch64.whl

Download URL arrow_odbc-10.7.0-py3-none-manylinux_2_28_aarch64.whl
Size 850.9 kB
Tags Linux glibc 2.28+ ARM64 Python 3
SHA-256 checksum
How to use checksums
f780bacb72c7e9c2d9280c239c9d2b6bf2236e518811f90241d97be7276f37c2
BLAKE2b-256 checksum
How to use checksums
5419c717de5d8222c288a7f8f633f9707a67807ea2e04455559c29bff2641f07
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / arrow_odbc-10.7.0-py3-none-macosx_11_0_arm64.whl

Download URL arrow_odbc-10.7.0-py3-none-macosx_11_0_arm64.whl
Size 745.7 kB
Tags Python 3 macOS 11.0+ ARM64
SHA-256 checksum
How to use checksums
67d6d00d23673e112b83d6acaf5aec658112817c58ba0aecb45a328b64531162
BLAKE2b-256 checksum
How to use checksums
fa9d4fcbf5c786394837e9a8d188d042f10de13fa7ed54cd730712809354ef10
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / arrow_odbc-10.7.0-py3-none-macosx_10_12_x86_64.whl

Download URL arrow_odbc-10.7.0-py3-none-macosx_10_12_x86_64.whl
Size 762.8 kB
Tags Python 3 macOS 10.12+ x86-64
SHA-256 checksum
How to use checksums
161ec2ea426c4639e7b8dd8177c441215ef6392a41ca3826b3b0ad31e50f8069
BLAKE2b-256 checksum
How to use checksums
0c5ff80f9b297fcc9c5875d09368395f55d83cc74f2f602a5f306a6d12683bff
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.13.7 {"installer":{"name":"uv","version":"0.13.7","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Security for arrow-odbc 10.7.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

10.7.0 This release

6 release files No provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page