Choisissez un modèle d’accès et d’analyse aux données avec mssql-python

Le mssql-python pilote propose plusieurs chemins pour lire les données depuis Microsoft SQL. Chaque parcours correspond à des charges de travail différentes. Ce guide vous aide à choisir le bon en fonction de la taille de vos données, de vos besoins d’analyse et de vos exigences de performance.

Décidez par charge de travail

Utilisez ce tableau pour trouver votre point de départ :

Méthodes de récupération du curseur

Utilisez des méthodes de curseur standard lorsque vous avez besoin d’un accès orienté ligne sans dépendances supplémentaires. Cette méthode est le bon choix pour le code applicatif qui traite une ligne à la fois, renvoie des réponses API ou alimente la logique applicative.

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

Utilisation fetchmany() pour le traitement par lots économe en mémoire de grands ensembles de résultats. Utilisez fetchval() lorsque vous avez besoin d’une seule valeur, comme un nombre, un maximum ou une vérification d’existence.

Pour la documentation complète de la méthode de récupération, voir Récupérer les données.

Extraction des flèches

Utilisez l’extraction par flèches lorsque vous avez besoin de données en colonnes pour l’analyse, la construction de DataFrames, ou l’exportation vers Parquet. Arrow fournit un transfert de données sans copie depuis le pilote, ce qui évite la surcharge de conversion ligne par ligne qu’implique la création d’un DataFrame à partir de fetchall().

Les tables avec index de colonne sont déjà stockées au format columnaire dans le moteur de base de données, ce qui fait de l’extraction Arrow un choix naturel pour ces charges de travail.

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

Pour de grands ensembles de résultats, utilisez arrow_reader() pour streamer des lots sans tout charger en mémoire :

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

Les tables Arrow servent de point de départ à pandas, Polars et DuckDB. Extraire une fois, puis convertir :

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)

Pour la documentation complète d’Arrow, voir intégration d’Apache Arrow.

pandas

Utilisez pandas lorsque vous avez besoin d’une API DataFrame familière pour le rapport, l’analyse ad hoc ou le nettoyage de données. Pandas fonctionne mieux avec des ensembles de résultats qui tiennent en mémoire (jusqu’à quelques millions de lignes, selon la largeur de la colonne).

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

Pour des ensembles de résultats plus grands, construisez le DataFrame à partir d’Arrow au lieu de fetchall():

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

Pour découvrir les cas d’usage pandas complets, y compris l’ETL, les séries temporelles et la réécriture, voir l’intégration pandas.

Polars avec Arrow

Utilisez les polars lorsque vous avez besoin d’opérations DataFrame plus rapides sur de plus grands ensembles de résultats. Polars utilise Apache Arrow comme format mémoire, donc le transfert depuis cursor.arrow() est en copie zéro. Polars exécute également des opérations sur plusieurs threads, ce qui évite la contention GIL lors des transformations à forte intensité 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)

Pour diffuser de larges ensembles de résultats :

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)

Pour des modèles Polars complets, voir l’intégration de Polars.

DuckDB avec Arrow

Utilisez DuckDB lorsque vous devez exécuter des analyses SQL sur des données extraites, joindre les données serveur avec des fichiers CSV ou Parquet locaux, ou exporter des résultats vers des formats de fichiers. DuckDB fonctionne sur des tables Arrow avec un accès sans copie.

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

Rejoindre les données du serveur avec un fichier 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
""")

Exporter au format Parquet :

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

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

Pour les patrons complets de DuckDB, voir intégration DuckDB.

Fonctionnalités de Microsoft SQL qui influencent les décisions de chemin de lecture

Le moteur de base de données dispose de fonctionnalités qui influencent directement quel chemin de lecture fonctionne le mieux. Prenez en compte ces caractéristiques lors du choix de votre approche :

Indexes columnstore

Les tables avec des index columnstore stockent les données dans un format colonnaire. L’extraction par flèches est la transmission naturelle pour ces tables car les données sont déjà en colonnes dans le moteur. Si vos requêtes analytiques scannent des tables larges de millions de lignes, un index de colonstock non clusterisé côté serveur, combiné à une extraction Arrow côté client, offre le meilleur débit de bout en bout.

Vues indexées

Les vues indexées précalculent et stockent les résultats agrégés ou joints sur le serveur. Si votre analyse avec pandas ou Polars recalcule régulièrement la même agrégation, envisagez de créer une vue indexée et de l’interroger à la place. Le serveur maintient automatiquement la vue au fur et à mesure que les données sous-jacentes changent.

Magasin des requêtes (magasin de requêtes)

Magasin des requêtes suit les statistiques d’exécution des requêtes au fil du temps. Utilisez-le pour identifier quelles requêtes sont suffisamment coûteuses pour justifier une extraction Arrow et une analyse locale DataFrame par rapport à une lecture directe du curseur. Si une requête s’exécute en quelques millisecondes, l’extraction via curseur convient. S’il scanne des millions de lignes, l’extraction de flèches et l’analyse locale pourraient réduire la charge du serveur.

Traitement intelligent des requêtes

Les fonctionnalités intelligentes de traitement des requêtes de Microsoft SQL, telles que les jointures adaptatives, le mode batch sur rowstore et le retour d'information de la reconnaissance mémoire, optimisent automatiquement l'exécution des requêtes. Ces fonctionnalités fonctionnent quel que soit le chemin de lecture client choisi, mais elles bénéficient surtout aux grandes requêtes analytiques. Vous n’avez pas besoin d’ajuster les indices ou les plans d’exécution pour la plupart des charges de travail.

Traiter en flux de grands ensembles de résultats

Pour les ensembles de résultats qui ne rentrent pas en mémoire, utilisez des motifs de flux :

Streaming basé sur un curseur avec 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 à flèches vers 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()

Antipatrons à éviter

Contenu connexe

• Récupérer des données
• Intégration Apache Arrow
• intégration de pandas
• Intégration polaire
• Intégration DuckDB
• Réglage des performances
• Chargement des données et schémas de déplacement