Introducing Apache Arrow Assist in mssql-python

What Is Apache Arrow?

The important thing perception behind Apache Arrow is zero-copy language interoperability. Arrow defines a steady shared-memory structure – the Arrow C Information Interface, a cross-language ABI – that any language can produce or devour by exchanging a pointer, with no serialization, no copies, and no re-parsing. A C++ database driver and a Python DataFrame library can work on the very same reminiscence with out both one figuring out concerning the different.

Constructed on high of that, Arrow makes use of a columnar in-memory format: as a substitute of representing a desk as a listing of rows, every row a group of Python objects, Arrow shops all values for a column contiguously in a typed buffer. Nulls are tracked in a compact bitmap somewhat than per-cell None objects.

For a database driver, this implies your complete fetch loop can run in C++ and write values straight into Arrow buffers – no Python object creation per row, no garbage-collector stress. The DataFrame library receives a pointer to that reminiscence and might start working on it instantly. Crucially, subsequent operations – filters, joins, aggregations – additionally work in-place on those self same buffers. A Polars pipeline studying from mssql-python by no means must materialize intermediate Python objects at any stage, making Arrow the best basis for high-throughput knowledge processing pipelines.

For customers of mssql-python, this interprets into 4 concrete advantages:

• Velocity: The columnar fetch path avoids Python object creation per row, which ought to make fetching noticeably sooner for a lot of SQL Server sorts – particularly temporal sorts like DATETIME and DATETIMEOFFSET, the place Python-side per-value conversions are eradicated totally.
• Decrease reminiscence utilization: A column of 1 million integers is a single contiguous C array, not 1,000,000 particular person Python objects.
• Seamless interoperability: Polars, Pandas (by way of ArrowDtype), DuckDB, Hugging Face datasets, and lots of different libraries all communicate Arrow natively. Zero-copy hand-off between mssql-python and people instruments.
• Purely additive: Your present fetchone, fetchmany, and fetchall code is totally unaffected. You choose in solely the place you want it.

The Arrow Fetch APIs

Three APIs have been added to the Cursor object.

1. cursor.arrow_batch(batch_size=8192) → pyarrow.RecordBatch

Fetches the following batch of as much as batch_size rows as an Arrow RecordBatch and advances the cursor. RecordBatches are the constructing block for extra high-level Arrow knowledge sorts like tables and the batch reader interface.

import mssql_python

conn   = mssql_python.join(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT * FROM SalesData")

partial_data = cursor.arrow_batch(batch_size=50000)
course of(partial_data)   # pyarrow.RecordBatch

2. cursor.arrow(batch_size=8192) → pyarrow.Desk

Eagerly fetches your complete consequence set right into a single Arrow Desk. That is the best path and works properly for analytics queries the place the consequence matches comfortably in reminiscence. Nonetheless, as a result of it materialises the total consequence set directly, it will possibly trigger excessive peak RAM utilization or out-of-memory errors on very massive or unbounded queries. For big exports or ETL workloads, desire cursor.arrow_reader() (streaming, fetches lazily) or cursor.arrow_batch() (fetch one batch at a time). In each circumstances, batch_size is a tuning knob: bigger batches enhance throughput however improve peak reminiscence; smaller batches scale back reminiscence at the price of barely extra per-batch overhead.

cursor.execute("SELECT customer_id, order_date, quantity FROM Orders")
desk = cursor.arrow()

# Zero-copy conversion to Polars
import polars as pl
df = pl.DataFrame(desk)

# Or to Pandas with Arrow-backed dtypes
import pandas as pd
df = desk.to_pandas(types_mapper=pd.ArrowDtype)

3. cursor.arrow_reader(batch_size=8192) → pyarrow.RecordBatchReader

Returns a lazy RecordBatchReader. Batches are fetched solely when the reader is iterated, enabling streaming over very massive consequence units. RecordBatchReader can also be accepted straight by DuckDB, Lance, and different Arrow-native libraries.

cursor.execute("SELECT * FROM LargeEventLog")
reader = cursor.arrow_reader(batch_size=100000)

for batch in reader:
    sink.write(batch)

Testing

We validated the Arrow fetch path in opposition to the usual Python row fetch path throughout a variety of SQL Server sorts — numeric, temporal, string, and UUID – for each single-column and vast (20-column) tables. The total take a look at script and outcomes can be found within the Assets part; we encourage you to run them by yourself {hardware} to see the distinction on your workload.

In our testing, the Arrow path was persistently sooner for many SQL Server sorts. Temporal sorts confirmed the biggest good points: sorts like DATETIME and DATETIMEOFFSET profit considerably as a result of the Arrow path handles timezone normalization and worth encoding totally in C++, eliminating per-value Python-side conversions. DATETIMEOFFSET specifically confirmed among the most pronounced speedups we noticed.

JSON Serialization Bonus

The Arrow path may also profit API workloads that serialize outcomes to JSON. As a substitute of fetchall() + json.dumps(), fetch by way of cursor.arrow(), wrap in a Polars DataFrame, and name df.write_json() – your complete pipeline bypasses Python objects and might be noticeably sooner, particularly for sorts like DATETIMEOFFSET.

pip set up mssql-python pyarrow

pip set up pyarrow-stubs

import mssql_python
import polars as pl

conn   = mssql_python.join(conn_str)
cursor = conn.cursor()

cursor.execute("SELECT * FROM dbo.LargeSalesTable")
df = pl.DataFrame(cursor.arrow())

print(df.describe())