Escolha um padrão de acesso e análise de dados com mssql-python

O mssql-python driver oferece múltiplos caminhos para leitura de dados do Microsoft SQL. Cada caminho se encaixa em diferentes cargas de trabalho. Este guia ajuda você a escolher o correto com base no tamanho dos seus dados, necessidades de análise e requisitos de desempenho.

Decida com base na carga de trabalho

Use esta tabela para encontrar seu ponto de partida:

Carga de Trabalho Caminho recomendado Por que
Acesso à linha de aplicações (web API, CRUD) Métodos de busca por cursor Baixa sobrecarga, processamento linha a linha, sem dependências extras.
Consultas de relatórios de pequenas a médias dimensões pandas API familiar para filtragem, agrupamento e visualização.
Grandes conjuntos de resultados ou tabelas largas Extração de flechas Transferência colunar sem cópia, sobrecarga mínima de memória.
Análise de alto desempenho Polars with Arrow Execução com múltiplas threads em dados colunares, sem contenção da GIL.
SQL ad hoc sobre dados locais e remotos DuckDB com Flecha Análises SQL em tabelas Arrow, com junção com arquivos CSV/Parquet locais.
Exploração de cadernos pandas ou Polars com Flecha Escolha com base na familiaridade da equipe e no tamanho dos dados.

Métodos de busca por cursor

Use métodos padrão de cursor quando precisar de acesso orientado a linhas, sem dependências extras. Esse método é a escolha certa para código de aplicação que processa uma linha de cada vez, retorna respostas da API ou alimenta a lógica da aplicação.

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 processamento em lote com uso eficiente de memória de grandes conjuntos de resultados. Use fetchval() quando precisar de um único valor, como uma contagem, um máximo ou uma verificação de existência.

Para documentação completa do método fetch, veja Recuperar dados.

Extração de flechas

Use a extração Arrow quando precisar de dados colunares para análise de dados, construção de DataFrames ou exportação para Parquet. O Arrow fornece transferência de dados sem cópia do driver, o que evita a sobrecarga da conversão linha por linha ao criar um DataFrame a partir de fetchall().

Tabelas com índices columnstore já são armazenadas em formato colunar no mecanismo de banco de dados, o que torna a extração com Arrow uma escolha natural para essas cargas de trabalho.

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, use arrow_reader() para transmitir lotes sem carregar tudo na memória:

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

As tabelas Arrow são o ponto de partida para pandas, Polars e DuckDB. Extraia uma vez, depois converta:

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 documentação completa do Arrow, veja integração com o Apache Arrow.

pandas

Use pandas quando precisar de uma API DataFrame familiar para relatórios, análises ad hoc ou limpeza de dados. O Pandas funciona melhor com conjuntos de resultados que cabem na memória (até alguns milhões de linhas, dependendo da largura da coluna).

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 maiores, construa o DataFrame a partir do Arrow em vez de fetchall():

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

Para padrões completos de pandas, incluindo ETL, séries temporais e write-back, veja integração com pandas.

Polares com Flecha

Use Polars quando precisar de operações de DataFrame mais rápidas em conjuntos de resultados maiores. Polars usa o Apache Arrow como formato na memória, então a transferência a partir de cursor.arrow() ocorre sem cópia de dados. O Polars também executa operações em várias threads, o que evita a contenção da GIL em transformações que exigem muito da 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 padrões completos de Polars, veja integração de Polars.

DuckDB com Arrow

Use o DuckDB quando precisar rodar análises SQL em dados extraídos, conectar dados do servidor com arquivos CSV ou Parquet locais, ou exportar resultados para formatos de arquivo. O DuckDB opera em tabelas Arrow com acesso sem cópia.

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

Conecte os dados do servidor com um arquivo 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 para Parquet:

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

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

Para padrões completos do DuckDB, veja integração com o DuckDB.

Recursos do Microsoft SQL que afetam decisões de caminho de leitura

O motor de banco de dados tem recursos que afetam diretamente qual caminho de leitura funciona melhor. Considere estas características ao escolher sua abordagem:

Índices Columnstore

Tabelas com índices de coluna armazenam dados em formato colunar. A extração com Arrow é o encaminhamento natural para essas tabelas, porque os dados já estão em formato colunar no mecanismo. Se suas consultas de analytics escaneiam tabelas amplas com milhões de linhas, um índice de columnstore não clusterizado no lado do servidor, combinado com extração Arrow no lado do cliente, oferece a melhor taxa de transferência de ponta a ponta.

Visões indexadas

Visualizações indexadas pré-computam e armazenam resultados agregados ou conectados no servidor. Se a sua análise com pandas ou Polars calcula repetidamente a mesma agregação, considere criar uma exibição indexada e consultá-la em vez disso. O servidor mantém automaticamente a visualização conforme os dados subjacentes mudam.

Repositório de Consultas

A Repositório de Consultas acompanha estatísticas de execução de consultas ao longo do tempo. Use-o para identificar quais consultas são caras o suficiente para justificar uma extração com Arrow e uma análise local em DataFrame, em vez de uma leitura direta do cursor. Se uma consulta é executada em milissegundos, a recuperação via cursor é adequada. Se ele escanear milhões de linhas, a extração de Arrow e a análise local podem reduzir a carga do servidor.

Processamento de consulta inteligente

Os recursos inteligentes de processamento de consultas do Microsoft SQL, como joins adaptativos, modo batch no rowstore e feedback de concessão de memória, otimizam automaticamente a execução de consultas. Esses recursos funcionam independentemente do caminho de leitura do cliente escolhido, mas são os que mais beneficiam consultas analíticas grandes. Você não precisa ajustar dicas ou planos de execução para a maioria das cargas de trabalho.

Transmitir grandes conjuntos de resultados em fluxo

Para conjuntos de resultados que não cabem na memória, use padrões de streaming:

Streaming baseado em cursor com 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

Streaming com base em Arrow para 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-padrões a evitar

Antipadrão Problema Melhor abordagem
fetchall() depois pd.DataFrame() , para tabelas grandes Carrega todas as linhas na memória duas vezes (uma como tuplas, outra como DataFrame). Use cursor.arrow() depois arrow_table.to_pandas().
Converter o Arrow em pandas apenas para filtrar linhas Desperdiça memória na cópia completa do pandas. Filtre em SQL (cláusula WHERE) ou use Polars/DuckDB diretamente na tabela Arrow.
SELECT * Quando você precisa de três colunas Transfere dados desnecessários do servidor. Liste apenas as colunas que você precisa.
Construindo um DataFrame para calcular COUNT(*) O servidor calcula agregados mais rápido que Python. Use SELECT COUNT(*) e fetchval().
Abrir uma nova conexão por consulta A criação de conexão é cara mesmo com o overhead do pooling. Reutilize conexões dentro de uma unidade lógica de trabalho.
Seta de Encadeamento -> pandas -> Polars Cada conversão copia os dados. Vá diretamente para o seu formato de destino: Arrow -> Polars ou Arrow -> pandas.

Conteúdo relacionado

  • Last updated on