Kies een data-access- en analytics-patroon met mssql-python

De mssql-python driver biedt meerdere paden voor het lezen van data uit Microsoft SQL. Elk pad past bij verschillende werklasten. Deze gids helpt je de juiste te kiezen op basis van je datagrootte, analysebehoeften en prestatie-eisen.

Bepaal op basis van werklast

Gebruik deze tabel om je startpunt te vinden:

Werklast Aanbevolen pad Waarom
Applicatie-rijtoegang (web-API, CRUD) Cursor fetch-methoden Weinig overhead, verwerking per rij, geen extra afhankelijkheden.
Kleine tot middelgrote rapportagequeries pandas Vertrouwde API voor filteren, groeperen en visualiseren.
Grote resultaatsets of brede tabellen Extractie van pijlen Zero-copy kolomsgewijze overdracht, minimale geheugenoverhead.
Hoogwaardige analyses Polars met pijl Multithreaded uitvoering op kolomgegevens, zonder GIL-conflicten.
Ad hoc SQL voor lokale en externe gegevens DuckDB met Arrow SQL-analyses op Arrow-tabellen, samenvoegen met lokale CSV/Parquet-bestanden.
Notebook verkennen panda's of Pools met pijl Kies op basis van teamvertrouwdheid en datagrootte.

Cursor fetch-methoden

Gebruik standaard cursormethoden wanneer je rijgerichte toegang nodig hebt zonder extra afhankelijkheden. Deze methode is de juiste keuze voor applicatiecode die één rij tegelijk verwerkt, API-antwoorden teruggeeft of applicatielogica voedt.

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

Gebruik fetchmany() voor geheugenefficiënte batchverwerking van grote resultaatsets. Gebruik fetchval() wanneer je één waarde nodig hebt, zoals een count-, max- of existenscheck.

Voor volledige documentatie van de fetch-methode, zie Data ophalen.

Pijlextractie

Gebruik Arrow-extractie wanneer je kolomdata nodig hebt voor analyses, DataFrame-constructie of exporteren naar Parquet. Arrow biedt nul-kopieoverdracht van data vanuit de driver, wat de overhead van de rij-voor-rij conversie van het bouwen van een DataFrame uit fetchall()voorkomt.

Tabellen met kolomopslagindexen worden al in kolomformaat opgeslagen in de database-engine, waardoor Arrow-extractie een natuurlijke keuze is voor die werklasten.

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

Gebruik arrow_reader() om voor grote resultaatsets batches te streamen zonder alles in het geheugen te laden:

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-tabellen zijn het startpunt voor panda's, Polars en DuckDB. Haal één keer uit, en zet dan om:

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)

Voor volledige Arrow-documentatie, zie Apache Arrow-integratie.

pandas

Gebruik pandas wanneer je een vertrouwde DataFrame API nodig hebt voor rapportage, ad-hoc analyse of data-opschoning. PANDAS werkt het beste met resultaatsets die in het geheugen passen (tot een paar miljoen rijen, afhankelijk van de kolombreedte).

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

Voor grotere resultaatsets bouw je de DataFrame uit Arrow in plaats van fetchall():

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

Voor volledige pandas-patronen, inclusief ETL, tijdreeksen en write-back, zie pandas-integratie.

Polars met pijl

Gebruik Polars wanneer je snellere DataFrame-operaties nodig hebt op grotere resultatensets. Polars gebruikt Apache Arrow als geheugenformaat, dus de overdracht van cursor.arrow() gebeurt zonder kopiëren. Polars voert bewerkingen ook met meerdere threads uit, waardoor strijd om de GIL bij CPU-intensieve transformaties wordt voorkomen.

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)

Voor het streamen van grote resultaatsets:

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)

Voor volledige Polars-patronen, zie Polars-integratie.

DuckDB met Arrow

Gebruik DuckDB wanneer je SQL-analyses moet uitvoeren op geëxtraheerde data, servergegevens moet joinen met lokale CSV- of Parquet-bestanden, of resultaten exporteert naar bestandsformaten. DuckDB werkt op Arrow-tabellen met nul-kopieertoegang.

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

Voeg servergegevens samen met een lokaal bestand:

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 naar Parquet:

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

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

Voor volledige DuckDB-patronen, zie DuckDB-integratie.

Microsoft SQL-functies die beslissingen over leespaden beïnvloeden

De database-engine heeft functies die direct bepalen welk leespad het beste werkt. Houd rekening met deze kenmerken bij het kiezen van uw aanpak:

Columnstore-indexen

Tabellen met kolomopslagindexen slaan data op in kolomformaat. Extractie via Arrow is de logische keuze voor deze tabellen, omdat de gegevens in de engine al kolomgeoriënteerd zijn. Als je analytics-queries brede tabellen scannen met miljoenen rijen, zorgt een niet-geclusterde columnstore-index aan de serverzijde gecombineerd met Arrow-extractie aan de clientzijde voor de beste end-to-end doorvoer.

Geïndexeerde weergaven

Geïndexeerde weergaven worden vooraf berekend en geaggregeerde of samengevoegde resultaten worden op de server opgeslagen. Als je pandas- of Polars-analyse herhaaldelijk dezelfde aggregatie berekent, overweeg dan een geïndexeerde weergave te maken en die weergave in plaats daarvan op te vragen. De server onderhoudt de weergave automatisch naarmate de onderliggende data verandert.

Querywinkel

Query Store volgt de uitvoeringsstatistieken van opdrachten in de tijd. Gebruik het om te identificeren welke queries duur genoeg zijn om een Arrow-extractie en lokale DataFrame-analyse te rechtvaardigen versus een directe cursor read. Als een query binnen milliseconden draait, is cursor fetch prima. Als het miljoenen rijen scant, kunnen Arrow-extractie en lokale analyse de serverbelasting verminderen.

Intelligente queryverwerking

De intelligente queryverwerkingsfuncties van Microsoft SQL, zoals adaptieve joins, batchmodus op rowstore en feedback over geheugentoekenningen, optimaliseren automatisch de uitvoering van querys. Deze functies werken ongeacht welk cliëntleespad je kiest, maar ze zijn het meest gunstig voor grote analytische zoekopdrachten. Je hoeft geen hints of uitvoeringsplannen voor de meeste workloads af te stemmen.

Stroom grote resultaatsets

Voor resultaatsets die niet in het geheugen passen, gebruik streamingpatronen:

Cursor-gebaseerde streaming met 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-gebaseerde streaming naar 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-patronen om te vermijden

Antipatroon Probleem Betere aanpak
fetchall() dan voor pd.DataFrame() grote tabellen Laadt alle rijen twee keer in het geheugen (één keer als tuples, één keer als DataFrame). Gebruik cursor.arrow() dan arrow_table.to_pandas().
Arrow omzetten naar pandas om alleen rijen te filteren Verspilt geheugen aan de volledige pandas-kopie. Filter in SQL (WHERE clausule) of gebruik Polars/DuckDB direct op de Arrow-tabel.
SELECT * Wanneer je drie kolommen nodig hebt Draagt onnodige data over van de server. Vermeld alleen de kolommen die je nodig hebt.
Een DataFrame bouwen om te berekenen COUNT(*) De server berekent aggregaten sneller dan Python. Gebruiken SELECT COUNT(*) en fetchval().
Een nieuwe verbinding per query openen Het opzetten van verbindingen is duur, zelfs ondanks de overhead van pooling. Hergebruik verbindingen binnen een logische eenheid van werk.
Chaining Arrow -> pandas -> Polars Elke conversie kopieert data. Ga rechtstreeks naar je doelformaat: Arrow -> Polars of Arrow -> pandas.

Verwante onderwerpen

  • Last updated on