Scegli un modello di accesso ai dati e analisi con mssql-python

Il mssql-python driver fornisce più percorsi per la lettura dei dati da Microsoft SQL. Ogni percorso si adatta a carichi di lavoro diversi. Questa guida ti aiuta a scegliere quella giusta in base alle dimensioni dei dati, alle esigenze di analisi e alle prestazioni di qualità.

Decidi in base al carico di lavoro

Usa questa tabella per trovare il punto di partenza:

Metodi di recupero del cursore

Usa metodi standard di cursore quando hai bisogno di accesso orientato alle righe senza dipendenze aggiuntive. Questo metodo è la scelta giusta per il codice applicativo che elabora una riga alla volta, restituisce risposte API o alimenta la logica applicativa.

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

Utilizzo fetchmany() per l'elaborazione batch efficiente in memoria di grandi set di risultati. Usa fetchval() quando hai bisogno di un singolo valore come un conteggio, un massimo o una verifica di esistenza.

Per la documentazione completa del metodo di recupero, vedi Recupero dati.

Estrazione della freccia

Usa l'estrazione con frecce quando hai bisogno di dati columnari per analisi, costruzione DataFrame o esportazione su Parquet. Arrow fornisce trasferimento dati a zero copia dal driver, evitando così il sovraccarico di conversione riga per riga dovuto alla costruzione di un DataFrame da fetchall().

Le tabelle con indici di colonnastore sono già memorizzate in formato columnar nel motore di database, rendendo l'estrazione tramite freccia una soluzione naturale per quei carichi di lavoro.

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

Per grandi set di risultati, usa arrow_reader() per streammare batch senza caricare tutto in 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")

Le tabelle Arrow sono il punto di partenza per pandas, Polars e DuckDB. Estrai una volta, poi converti:

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)

Per la documentazione completa di Arrow, vedi integrazione con Apache Arrow.

pandas

Usa panda quando hai bisogno di un'API DataFrame familiare per reporting, analisi ad hoc o pulizia dei dati. Pandas funziona meglio con set di risultati che si adattano alla memoria (fino a qualche milione di righe, a seconda della larghezza della colonna).

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

Per set di risultati più grandi, costruisci il DataFrame da Arrow invece di fetchall():

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

Per una panoramica completa dei pattern di pandas, inclusi ETL, serie temporali e write-back, consulta integrazione pandas.

Polari con Freccia

Usa i Polar quando hai bisogno di operazioni DataFrame più veloci su set di risultati più grandi. Polars utilizza Apache Arrow come formato di memoria, quindi il trasferimento da cursor.arrow() è zero-copy. Polars esegue anche operazioni su più thread, evitando così la contesa GIL nelle trasformazioni con pesante 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)

Per lo streaming di grandi set di risultati:

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)

Per i modelli Polars completi, consulta integrazione Polars.

DuckDB con Arrow

Usa DuckDB quando devi eseguire analisi SQL sui dati estratti, unire i dati server con file CSV o Parquet locali, o esportare i risultati in formati file. DuckDB funziona su tabelle Arrow con accesso senza copia.

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

Unisci i dati del server a un file locale:

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

Esportazione in parquet:

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

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

Per i pattern completi di DuckDB, vedi integrazione con DuckDB.

Funzionalità Microsoft SQL che influenzano le decisioni sul percorso di lettura

Il motore di database ha funzionalità che influenzano direttamente quale percorso di lettura funziona meglio. Considera queste caratteristiche quando scegli il tuo approccio:

Indici columnstore

Le tabelle con indici di colonna conservano i dati in formato columnar. L'estrazione in Arrow è il naturale passaggio per queste tabelle, perché i dati sono già in formato colonnare nel motore. Se le tue query analitiche scansionano tabelle con molte colonne e milioni di righe, un indice columnstore non clusterizzato sul lato server, combinato con l'estrazione tramite Arrow sul lato client, garantisce il miglior throughput end-to-end.

Viste indicizzate

Le viste indicizzate precalcolano e memorizzano i risultati aggregati o uniti sul server. Se la tua analisi con pandas o Polars calcola ripetutamente la stessa aggregazione, valuta di creare una vista indicizzata ed eseguire query su tale vista. Il server mantiene automaticamente la visuale man mano che i dati sottostanti cambiano.

Archivio query

Query Store monitora le statistiche di esecuzione delle query nel tempo. Usalo per identificare quali query sono abbastanza costose da giustificare un'estrazione Arrow e un'analisi locale DataFrame rispetto a una lettura diretta con cursore. Se una query viene eseguita in millisecondi, il recupero tramite cursore va bene. Se scansiona milioni di righe, l'estrazione e l'analisi locale tramite Arrow potrebbero ridurre il carico del server.

Elaborazione di query intelligenti

Le funzionalità intelligenti di elaborazione delle query di Microsoft SQL, come gli adaptive joins, la modalità batch su rowstore e il feedback di concessione di memoria, ottimizzano automaticamente l'esecuzione delle query. Queste funzionalità funzionano indipendentemente dal percorso di lettura del cliente scelto, ma sono le principali utili per le query analitiche di grandi dimensioni. Non è necessario ottimizzare gli indizi o i piani di esecuzione per la maggior parte dei carichi di lavoro.

Trasmettere in streaming set di risultati di grandi dimensioni

Per i set di risultati che non entrano in memoria, usa i pattern di streaming:

Streaming basato su cursori 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

Streaming basato su frecce su 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-pattern da evitare

Contenuti correlati

• Recupero dati
• Integrazione con Apache Arrow
• Integrazione di Pandas
• Integrazione con Polars
• Integrazione con DuckDB
• Ottimizzazione delle prestazioni
• Caricamento dei dati e schemi di movimento