Elige un patrón de acceso a datos y análisis con mssql-python

El mssql-python controlador proporciona múltiples rutas para leer datos de Microsoft SQL. Cada opción se adapta a distintas cargas de trabajo. Esta guía te ayuda a elegir la adecuada en función del tamaño de tus datos, necesidades de análisis y requisitos de rendimiento.

Decide por carga de trabajo

Utiliza esta tabla para encontrar tu punto de partida:

Métodos de recuperación de cursores

Utiliza métodos estándar de cursor cuando necesites acceso orientado a filas sin dependencias adicionales. Este método es la opción adecuada para el código de aplicación que procesa una fila cada vez, devuelve respuestas de la API o sirve de entrada para la lógica de la aplicación.

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() para procesar por lotes grandes conjuntos de resultados de forma eficiente en cuanto al uso de memoria. Usa fetchval() cuando necesites un solo valor, como un recuento, un máximo o una comprobación de existencia.

Para la documentación completa del método de obtención, consulte Recuperar datos.

Extracción de flecha

Usa la extracción de Arrow cuando necesites datos en formato columnar para análisis, crear un DataFrame o exportar a Parquet. Arrow proporciona transferencia de datos sin copias desde el driver, lo que evita la sobrecarga de conversión fila por fila que supone crear un DataFrame a partir de fetchall().

Las tablas con índices de almacén por columnas ya se almacenan en formato de columnas en el motor de base de datos, lo que convierte la extracción de Arrow en una opción natural para estas cargas de trabajo.

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")

Para conjuntos de resultados grandes, usa arrow_reader() para transmitir lotes sin cargarlo todo en la memoria:

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")

Las tablas de Arrow son el punto de partida para pandas, Polars y DuckDB. Extrae una vez y luego convierte:

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)

Para la documentación completa de Arrow, véase integración con Apache Arrow.

pandas

Utiliza pandas cuando necesites una API DataFrame familiar para informes, análisis ad hoc o limpieza de datos. Pandas funciona mejor con conjuntos de resultados que caben en memoria (hasta unos pocos millones de filas, dependiendo del ancho de columna).

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"]))

Para conjuntos de resultados más grandes, construye el DataFrame a partir de Arrow en lugar de fetchall():

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

Para patrones completos de pandas incluyendo ETL, series temporales y write-back, véase integración de pandas.

Polares con flecha

Usa Polars cuando necesites operaciones de DataFrame más rápidas en conjuntos de resultados más grandes. Polars utiliza Apache Arrow como formato de memoria, por lo que la transferencia desde cursor.arrow() se realiza sin copia. Polars también ejecuta operaciones en múltiples hilos, lo que evita la contención del GIL en transformaciones intensivas en CPU.

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)

Para streaming de grandes conjuntos de resultados:

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)

Para patrones completos de Polars, véase integración de Polars.

DuckDB con Arrow

Usa DuckDB cuando necesites ejecutar análisis SQL sobre datos extraídos, unir datos de servidores con archivos CSV o Parquet locales, o exportar resultados a formatos de archivo. DuckDB opera sobre tablas Arrow con acceso sin copias.

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())

Unir los datos del servidor con un archivo local:

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
""")

Exportar a Parquet:

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

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

Para patrones completos de DuckDB, véase integración con DuckDB.

Funciones de Microsoft SQL que afectan a las decisiones de ruta de lectura

El motor de base de datos tiene funciones que afectan directamente qué ruta de lectura funciona mejor. Ten en cuenta estas características al elegir tu enfoque:

Índices de columnas almacenadas

Las tablas con índices de almacén de columnas almacenan datos en formato columnar. La exportación mediante Arrow es la vía natural de salida para estas tablas porque los datos ya están en formato columnar en el motor. Si tus consultas analíticas escanean tablas anchas con millones de filas, un índice columnstore no agrupado del lado del servidor, combinado con la extracción mediante Arrow del lado del cliente, ofrece el mejor rendimiento de extremo a extremo.

Vistas indexadas

Las vistas indexadas precomputan y almacenan resultados agregados o unidos en el servidor. Si tu análisis de pandas o Polars calcula repetidamente la misma agregación, considera crear una vista indexada y consultarla en su lugar. El servidor mantiene automáticamente la vista a medida que cambian los datos subyacentes.

Almacén de consultas (Almacén de Consultas)

Almacén de consultas rastrea las estadísticas de ejecución de consultas a lo largo del tiempo. Úsalo para identificar qué consultas son lo bastante costosas como para justificar una extracción con Arrow y un análisis local en un DataFrame, en lugar de una lectura directa del cursor. Si una consulta se ejecuta en milisegundos, la recuperación mediante cursor es adecuada. Si escanea millones de filas, la extracción de flechas y el análisis local podrían reducir la carga del servidor.

Procesamiento inteligente de consultas

Las funciones inteligentes de procesamiento de consultas de Microsoft SQL, como las uniones adaptativas, el modo por lotes en rowstore y la retroalimentación de concesión de memoria, optimizan automáticamente la ejecución de consultas. Estas funciones funcionan independientemente del camino de lectura del cliente que elijas, pero son las que más benefician a las consultas analíticas grandes. No necesitas ajustar pistas ni planes de ejecución para la mayoría de las cargas de trabajo.

Transmitir grandes conjuntos de resultados

Para conjuntos de resultados que no caben en memoria, usa patrones de streaming:

Transmisión basada en cursores con 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

Transmisión por flechas a 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()

Antipatrones a evitar

Contenido relacionado

• Recuperar datos
• Integración con Apache Arrow
• Integración de Pandas
• Integración con Polars
• Integración con DuckDB
• Ajuste del rendimiento
• Carga de datos y patrones de movimiento