arrow-odbc-py
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.
-
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.
-
To setup the python environment, and build the wheel itself
uvis recommened. You can get it from here: https://docs.astral.sh/uv/getting-started/installation/ -
Build wheel
uv build
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 contrastarrow-odbcis specifically concerned with bulk reads and writes to arrow arrays.turbodbc- Complies with the Python Database API Specification 2.0 (PEP 249) whicharrow-odbcdoes not aim to do. Likearrow-odbcbulk read and writes is the strong point ofturbodbc.turbodbchas more system dependencies, which can make it cumbersome to install if not using conda.turbodbcis build against the C++ implementation of Arrow, which implies it is only compatible with matching version ofpyarrow.
1 maintainer
Release files for arrow-odbc 10.4.2
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| arrow_odbc-10.4.2.tar.gz | 106.2 kB | Details |
Built distributions (wheels)
| File | Reset | |||
|---|---|---|---|---|
| arrow_odbc-10.4.2-py3-none-win_amd64.whl | Python 3 | none | Windows x86-64 | Details |
| arrow_odbc-10.4.2-py3-none-manylinux_2_28_x86_64.whl | Python 3 | none | Linux glibc 2.28+ x86-64 | Details |
| arrow_odbc-10.4.2-py3-none-manylinux_2_28_aarch64.whl | Python 3 | none | Linux glibc 2.28+ ARM64 | Details |
| arrow_odbc-10.4.2-py3-none-macosx_11_0_arm64.whl | Python 3 | none | macOS 11.0+ ARM64 | Details |
| arrow_odbc-10.4.2-py3-none-macosx_10_12_x86_64.whl | Python 3 | none | macOS 10.12+ x86-64 | Details |
Total release size: 4.2 MB
Release files / arrow_odbc-10.4.2.tar.gz
| Download URL | arrow_odbc-10.4.2.tar.gz |
|---|---|
| Size | 106.2 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
93c2eee84e5c5a404f0c43c4a5d7b1da4a2606031d0d9b5e7059a62685b82fb8
|
|
BLAKE2b-256 checksum How to use checksums |
722a74b808b77ee5ea16246c5bbc15032acb6f2ebf4019160d0bbf5f04b8a836
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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.4.2-py3-none-win_amd64.whl
| Download URL | arrow_odbc-10.4.2-py3-none-win_amd64.whl |
|---|---|
| Size | 688.3 kB |
| Tags | Python 3 Windows x86-64 |
|
SHA-256 checksum How to use checksums |
4db649fc4430029b8f7a6d1eb5133f26c44f45b63f6b08e3b5b655ee37b65234
|
|
BLAKE2b-256 checksum How to use checksums |
aab7ad4893e30d0adff29a151b476190df84756943a77fb8ac9b15ce78f66f00
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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.4.2-py3-none-manylinux_2_28_x86_64.whl
| Download URL | arrow_odbc-10.4.2-py3-none-manylinux_2_28_x86_64.whl |
|---|---|
| Size | 922.6 kB |
| Tags | Linux glibc 2.28+ x86-64 Python 3 |
|
SHA-256 checksum How to use checksums |
0cc8dbaae69d92011306c084d986c2e497fa23f853e819f126ba7926d9613ad1
|
|
BLAKE2b-256 checksum How to use checksums |
393b0fc53b3b1dfefea2de96c8f103a23487e2eb00061b0fed1ee75a11b89927
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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.4.2-py3-none-manylinux_2_28_aarch64.whl
| Download URL | arrow_odbc-10.4.2-py3-none-manylinux_2_28_aarch64.whl |
|---|---|
| Size | 892.7 kB |
| Tags | Linux glibc 2.28+ ARM64 Python 3 |
|
SHA-256 checksum How to use checksums |
badbba502039436894a2116369fd807158a41fcb6fffcfea02a6fb43ba9bf12a
|
|
BLAKE2b-256 checksum How to use checksums |
32395bb72e5a81ebe00dfd99a951b10760c88a9cdcbe94a4818a13dd55d980d5
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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.4.2-py3-none-macosx_11_0_arm64.whl
| Download URL | arrow_odbc-10.4.2-py3-none-macosx_11_0_arm64.whl |
|---|---|
| Size | 781.3 kB |
| Tags | Python 3 macOS 11.0+ ARM64 |
|
SHA-256 checksum How to use checksums |
d6cba785730bd13a1d2ca56f95d8e306272bc0f17363bb5baf5d4d864fd0194e
|
|
BLAKE2b-256 checksum How to use checksums |
75d45c5bc16094a57047eb977c84c5b937ee300ee4ed6170c55e68313447e294
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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.4.2-py3-none-macosx_10_12_x86_64.whl
| Download URL | arrow_odbc-10.4.2-py3-none-macosx_10_12_x86_64.whl |
|---|---|
| Size | 805.4 kB |
| Tags | Python 3 macOS 10.12+ x86-64 |
|
SHA-256 checksum How to use checksums |
263c56ed9bfb79a9fc147039872e0936c64199fa05962211351fabfb28c3f494
|
|
BLAKE2b-256 checksum How to use checksums |
1b180b5e7c9561444646dd9bfa3b7bb59b36296eded77e68361c68eb34c696da
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.11.22 {"installer":{"name":"uv","version":"0.11.22","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}
|
