Skip to main content

Read the data of an ODBC data source as sequence of Apache Arrow record batches.

Project description

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 read_arrow_batches_from_odbc

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

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

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())
    insert_into_table(
        connection_string=connection_string,
        user="SA",
        password="My@Test@Password",
        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.

  • To build from source you need to install the Rust toolchain. Installation instruction can be found here: https://www.rust-lang.org/tools/install

  • Install ODBC driver manager. See above.

  • Build wheel

    python -m pip install build
    python -m build
    

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 = 0) VarChar(p + 1)
Decimal128(p, s != 0) VarChar(p + 2)
Decimal128(p, s < 0) VarChar(p - s + 1)
Decimal256(p, s = 0) VarChar(p + 1)
Decimal256(p, s != 0) VarChar(p + 2)
Decimal256(p, s < 0) VarChar(p - s + 1)
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.

Project details


Release history Release notifications | RSS feed

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

arrow_odbc-9.1.0.tar.gz (86.8 kB view details)

Uploaded Source

Built Distributions

arrow_odbc-9.1.0-py3-none-win_amd64.whl (636.5 kB view details)

Uploaded Python 3Windows x86-64

arrow_odbc-9.1.0-py3-none-manylinux2014_x86_64.manylinux_2_17_x86_64.whl (897.6 kB view details)

Uploaded Python 3manylinux: glibc 2.17+ x86-64

arrow_odbc-9.1.0-py3-none-manylinux2014_aarch64.manylinux_2_17_aarch64.whl (868.2 kB view details)

Uploaded Python 3manylinux: glibc 2.17+ ARM64

arrow_odbc-9.1.0-py3-none-macosx_11_0_arm64.whl (735.5 kB view details)

Uploaded Python 3macOS 11.0+ ARM64

arrow_odbc-9.1.0-py3-none-macosx_10_12_x86_64.whl (778.5 kB view details)

Uploaded Python 3macOS 10.12+ x86-64

File details

Details for the file arrow_odbc-9.1.0.tar.gz.

File metadata

  • Download URL: arrow_odbc-9.1.0.tar.gz
  • Upload date:
  • Size: 86.8 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? Yes
  • Uploaded via: uv/0.7.13

File hashes

Hashes for arrow_odbc-9.1.0.tar.gz
Algorithm Hash digest
SHA256 330ee1767aff497f9f31f47cf81da9a618df4a9a56540a85f8a82618fc9de967
MD5 8bde119c5fb8dc1ea4ead97c11925b06
BLAKE2b-256 87de7509c78e2155197e6bdcfe5c1dd0c20f956f6b5e14ad046e73904308ce0f

See more details on using hashes here.

File details

Details for the file arrow_odbc-9.1.0-py3-none-win_amd64.whl.

File metadata

File hashes

Hashes for arrow_odbc-9.1.0-py3-none-win_amd64.whl
Algorithm Hash digest
SHA256 e2e815b29903fcdbfea95457e15ace0fa0055833a28ffac438af2d247dbbdc0b
MD5 dcd8c02fc4983cdea2effbe0c77b2975
BLAKE2b-256 5176d6cbf167e0244cf34fdf21167748952cfcee3b1012245fc8a53bc12ab3d3

See more details on using hashes here.

File details

Details for the file arrow_odbc-9.1.0-py3-none-manylinux2014_x86_64.manylinux_2_17_x86_64.whl.

File metadata

File hashes

Hashes for arrow_odbc-9.1.0-py3-none-manylinux2014_x86_64.manylinux_2_17_x86_64.whl
Algorithm Hash digest
SHA256 ad93697793af19b3359a7238b6da00ccf3d6a9c60e0ab8313769aa6552ea7874
MD5 ab0294f843081e56a090391b81e0c2b0
BLAKE2b-256 0dfee91cddaf4c6d2345b3e706d131158332c2dfdbb6b4aea678ff900fc8143e

See more details on using hashes here.

File details

Details for the file arrow_odbc-9.1.0-py3-none-manylinux2014_aarch64.manylinux_2_17_aarch64.whl.

File metadata

File hashes

Hashes for arrow_odbc-9.1.0-py3-none-manylinux2014_aarch64.manylinux_2_17_aarch64.whl
Algorithm Hash digest
SHA256 4fe923f85a8c4d63d342a76aee7b920f3c3ab8de0fbb58ed90b42a9859e00f1e
MD5 73583082b2ac094705e46a31c8e63248
BLAKE2b-256 81b4bae24233c2fb08468b5a540b3dcbcc736f430d252c9fec30fde0d425a4a2

See more details on using hashes here.

File details

Details for the file arrow_odbc-9.1.0-py3-none-macosx_11_0_arm64.whl.

File metadata

File hashes

Hashes for arrow_odbc-9.1.0-py3-none-macosx_11_0_arm64.whl
Algorithm Hash digest
SHA256 fcff2f0d3c437dff2ca5b429d010ae967c0e931045d060d0d53004630101bcd8
MD5 040d431cc674f96e68e9cd0a2bd51c2c
BLAKE2b-256 7a6a9db68cc2099dddfe970da1c2da75368d507e80d614da685452d4a15418a8

See more details on using hashes here.

File details

Details for the file arrow_odbc-9.1.0-py3-none-macosx_10_12_x86_64.whl.

File metadata

File hashes

Hashes for arrow_odbc-9.1.0-py3-none-macosx_10_12_x86_64.whl
Algorithm Hash digest
SHA256 78a3857b0439c26a503aaf678b3f1bf6ffb405e86f51d8509c3000b8c020d834
MD5 514993d3b4c9b8f69af54e90af3e9da1
BLAKE2b-256 1139dc3f0cff2daa8d979f0fe43ac05244af6a7e7152a6634290d7afb2e2e684

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page