Choose a data access and analytics pattern with mssql-python

The mssql-python driver provides multiple paths for reading data from Microsoft SQL. Each path fits different workloads. This guide helps you choose the right one based on your data size, analysis needs, and performance requirements.

Decide by workload

Use this table to find your starting point:

Cursor fetch methods

Use standard cursor methods when you need row-oriented access with no extra dependencies. This method is the right choice for application code that processes one row at a time, returns API responses, or feeds application logic.

import mssql_python

conn = mssql_python.connect(
    server="<server>.database.windows.net",
    database="<database>",
    authentication="ActiveDirectoryDefault",
    encrypt="yes"
)

cursor = conn.cursor()

# fetchone(): Process rows one at a time
cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice > %(threshold)s", {"threshold": 100})
row = cursor.fetchone()
while row:
    print(f"{row.Name}: ${row.ListPrice:.2f}")
    row = cursor.fetchone()

# fetchmany(): Process in batches
cursor.execute("SELECT ProductID, Name FROM Production.Product")
while True:
    batch = cursor.fetchmany(100)
    if not batch:
        break
    for row in batch:
        print(row.Name)

# fetchval(): Get a single scalar value
cursor.execute("SELECT COUNT(*) FROM Production.Product")
count = cursor.fetchval()

Use fetchmany() for memory-efficient batch processing of large result sets. Use fetchval() when you need a single value like a count, max, or exists check.

For complete fetch method documentation, see Retrieve data.

Arrow extraction

Use Arrow extraction when you need columnar data for analytics, DataFrame construction, or export to Parquet. Arrow provides zero-copy data transfer from the driver, which avoids the row-by-row conversion overhead of building a DataFrame from fetchall().

Tables with columnstore indexes are already stored in columnar format in the database engine, making Arrow extraction a natural fit for those workloads.

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

# Get a single Arrow table
arrow_table = cursor.arrow()
print(f"{arrow_table.num_rows} rows, {arrow_table.num_columns} columns")

For large result sets, use arrow_reader() to stream batches without loading everything into memory:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")

# Stream Arrow record batches
reader = cursor.arrow_reader(batch_size=10000)
for batch in reader:
    # Each batch is a pyarrow.RecordBatch
    print(f"Batch: {batch.num_rows} rows")

Arrow tables are the starting point for pandas, Polars, and DuckDB. Extract once, then convert:

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")
arrow_table = cursor.arrow()

# Arrow -> pandas
df = arrow_table.to_pandas()

# Arrow -> Polars (zero-copy)
import polars as pl
df = pl.from_arrow(arrow_table)

For complete Arrow documentation, see Apache Arrow integration.

pandas

Use pandas when you need a familiar DataFrame API for reporting, ad hoc analysis, or data cleanup. pandas works best with result sets that fit in memory (up to a few million rows, depending on column width).

cursor.execute("""
    SELECT p.Name, p.ListPrice, pc.Name AS Category
    FROM Production.Product p
    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
    WHERE p.ListPrice > 0
""")

import pandas as pd
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame.from_records(rows, columns=columns)

# Analyze
print(df.groupby("Category")["ListPrice"].agg(["mean", "count"]))

For larger result sets, build the DataFrame from Arrow instead of fetchall():

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
arrow_table = cursor.arrow()
df = arrow_table.to_pandas()

For complete pandas patterns including ETL, time series, and write-back, see pandas integration.

Polars with Arrow

Use Polars when you need faster DataFrame operations on larger result sets. Polars uses Apache Arrow as its memory format, so the transfer from cursor.arrow() is zero-copy. Polars also runs operations on multiple threads, which avoids GIL contention on CPU-heavy transformations.

import polars as pl

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

arrow_table = cursor.arrow()
df = pl.from_arrow(arrow_table)

# Filter and aggregate
result = (
    df.filter(pl.col("ListPrice") > 100)
    .group_by("Color")
    .agg(pl.col("ListPrice").mean().alias("AvgPrice"))
    .sort("AvgPrice", descending=True)
)
print(result)

For streaming large result sets:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")

reader = cursor.arrow_reader(batch_size=50000)
frames = []
for batch in reader:
    frames.append(pl.from_arrow(batch))

df = pl.concat(frames)

For complete Polars patterns, see Polars integration.

DuckDB with Arrow

Use DuckDB when you need to run SQL analytics on extracted data, join server data with local CSV or Parquet files, or export results to file formats. DuckDB operates on Arrow tables with zero-copy access.

import duckdb

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

products = cursor.arrow()

# Run DuckDB SQL on the Arrow table
result = duckdb.sql("""
    SELECT Color, AVG(ListPrice) AS AvgPrice, COUNT(*) AS Count
    FROM products
    WHERE Color IS NOT NULL
    GROUP BY Color
    ORDER BY AvgPrice DESC
""")
print(result.fetchdf())

Join server data with a local file:

cursor.execute("SELECT CustomerID, TerritoryID FROM Sales.Customer")
customers = cursor.arrow()

# Join with a local CSV file
result = duckdb.sql("""
    SELECT c.CustomerID, c.TerritoryID, l.Region
    FROM customers c
    JOIN read_csv_auto('regions.csv') l ON c.TerritoryID = l.TerritoryID
""")

Export to Parquet:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
orders = cursor.arrow()

duckdb.sql("COPY orders TO 'orders.parquet' (FORMAT PARQUET)")

For complete DuckDB patterns, see DuckDB integration.

Microsoft SQL features that affect read-path decisions

The database engine has features that directly affect which read path works best. Consider these features when choosing your approach:

Columnstore indexes

Tables with columnstore indexes store data in columnar format. Arrow extraction is the natural handoff for these tables because the data is already columnar in the engine. If your analytics queries scan wide tables with millions of rows, a nonclustered columnstore index on the server side combined with Arrow extraction on the client side gives the best end-to-end throughput.

Indexed views

Indexed views precompute and store aggregated or joined results on the server. If your pandas or Polars analysis repeatedly computes the same aggregation, consider creating an indexed view and querying that view instead. The server automatically maintains the view as underlying data changes.

Query Store

Query Store tracks query execution statistics over time. Use it to identify which queries are expensive enough to justify an Arrow extract and local DataFrame analysis versus a direct cursor read. If a query runs in milliseconds, cursor fetch is fine. If it scans millions of rows, Arrow extraction and local analysis might reduce server load.

Intelligent query processing

Microsoft SQL's intelligent query processing features, such as adaptive joins, batch mode on rowstore, and memory grant feedback, automatically optimize query execution. These features work regardless of which client read path you choose, but they benefit large analytical queries the most. You don't need to tune hints or execution plans for most workloads.

Stream large result sets

For result sets that don't fit in memory, use streaming patterns:

Cursor-based streaming with fetchmany():

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
while True:
    batch = cursor.fetchmany(5000)
    if not batch:
        break
    for row in batch:
        print(row[0])  # Process each row

Arrow-based streaming to Parquet:

import pyarrow.parquet as pq

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
reader = cursor.arrow_reader(batch_size=50000)
writer = None

for batch in reader:
    if writer is None:
        writer = pq.ParquetWriter("orders.parquet", batch.schema)
    writer.write_batch(batch)

if writer:
    writer.close()

Anti-patterns to avoid

Related content

• Retrieve data
• Apache Arrow integration
• pandas integration
• Polars integration
• DuckDB integration
• Performance tuning
• Data loading and movement patterns