Risoluzione di problemi di query, dati e operazioni con mssql-python

Usa questo articolo per diagnosticare problemi di esecuzione delle query, tipo di dato, prestazioni, transazioni e copie di massa con il mssql-python driver.

Problemi di esecuzione delle query

Tabella o oggetto non trovato

Sintomi:

ProgrammingError: [42S02] (208) Invalid object name 'TableName'.

Possibili cause e soluzioni:

  • Contesto errato del database

    cursor.execute("SELECT DB_NAME()")
print(cursor.fetchone()[0])

  • Schema non specificato

    cursor.execute("SELECT * FROM dbo.TableName")

  • Il tavolo non esiste

    cursor.execute("""
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'TableName'
""")

Errore di sintassi

Sintomi:

ProgrammingError: [42000] (102) Incorrect syntax near '...'.

Soluzioni:

  1. Testa la dichiarazione SQL in SQL Server Management Studio (SSMS) per verificare la sintassi.

  2. Usa una query parametrizzata invece dell'interpolazione delle stringhe:

    # Don't use string interpolation for query parameters.
cursor.execute(f"SELECT * FROM Production.Product WHERE Name = '{name}'")

# Use parameters.
cursor.execute(
    "SELECT * FROM Production.Product WHERE Name = %(name)s",
    {"name": name},
)

Errori dei parametri

Sintomi:

ProgrammingError: [07001] Wrong number of parameters

Soluzioni:

  1. Conta i segnaposto e i parametri. I conteggi devono corrispondere.

  2. Scegli lo stile di parametro corretto:

    # Qmark style: positional parameters
cursor.execute(
    "SELECT * FROM Production.Product "
    "WHERE ProductID = ? AND Name LIKE ?",
    (1, "Adjustable%"),
)
print(cursor.fetchone())

# Pyformat style: named parameters
cursor.execute(
    "SELECT * FROM Production.Product "
    "WHERE ProductID = %(id)s AND Name LIKE %(name)s",
    {"id": 1, "name": "Adjustable%"},
)
print(cursor.fetchone())

Problemi con i tipi di dati

Errori di conversione di data e ora

Sintomi:

DataError: [22007] Invalid datetime format

Soluzione:

Usa oggetti Python datetime invece delle stringhe.

from datetime import datetime

cursor.execute("CREATE TABLE #Events (EventDate DATETIME)")

# This value raises an error because the date is invalid.
try:
    cursor.execute(
        "INSERT INTO #Events (EventDate) VALUES (%(event_date)s)",
        {"event_date": "2024-13-45"},
    )
except Exception as e:
    print(f"Expected error: {e}")

# Use a Python datetime object.
cursor.execute(
    "INSERT INTO #Events (EventDate) VALUES (%(event_date)s)",
    {"event_date": datetime(2024, 3, 15)},
)
cursor.execute("SELECT EventDate FROM #Events")
print(cursor.fetchone())

Problemi di precisione decimale

Sintomi:

I numeri appaiono troncati o arrotondati in modo errato.

Soluzione:

Uso decimal.Decimal per valori numerici precisi:

from decimal import Decimal

cursor.execute("CREATE TABLE #PriceDemo (ListPrice DECIMAL(10, 2))")
cursor.execute(
    "INSERT INTO #PriceDemo (ListPrice) VALUES (%(list_price)s)",
    {"list_price": Decimal("19.99")},
)

Problemi di codifica Unicode

Sintomi:

I caratteri speciali appaiono distorti o causano errori.

Soluzioni:

  1. Usa le colonne nvarchar per i dati Unicode nel tuo database.

  2. Passa le corde direttamente. Il driver gestisce la codifica:

    cursor.execute("CREATE TABLE #UnicodeDemo (Name NVARCHAR(50))")
cursor.execute(
    "INSERT INTO #UnicodeDemo (Name) VALUES (%(name)s)",
    {"name": "日本語"},
)
cursor.execute("SELECT Name FROM #UnicodeDemo")
print(cursor.fetchone())

Problemi di prestazioni

Esecuzione lenta delle query

Possibili cause e soluzioni:

  • Indici mancanti: Controlla il piano di esecuzione delle query in SSMS.

  • Grandi set di risultati: Usa fetchmany() invece di fetchall():

    cursor.arraysize = 1000
while True:
    rows = cursor.fetchmany()
    if not rows:
        break
    process_rows(rows)

  • Aggregazione della connessione disabilitata: Abilita il pooling:

    import mssql_python

mssql_python.pooling(max_size=20, idle_timeout=300)

Problemi di memoria con risultati grandi

Sintomi:

Il processo Python esaurisce la memoria.

Soluzioni:

  1. Trasmetti i risultati in streaming anziché caricare tutte le righe in memoria.

    cursor.execute("SELECT * FROM LargeTable")
for row in cursor:
    process_row(row)

  2. Usa la paginazione lato server.

    page_size = 1000
offset = 0

while True:
    cursor.execute(
        "SELECT * FROM LargeTable ORDER BY ID "
        "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY",
        (offset, page_size),
    )
    rows = cursor.fetchall()
    if not rows:
        break
    process_rows(rows)
    offset += page_size

Problemi di transazione

Visibilità delle tabelle temporanee con autocommit

Le tabelle temporanee di sessione (#tablename) che crei all'interno di una transazione scompaiono quando la transazione viene annullata. Questo comportamento causa spesso confusione quando l'autocommit è disattivato, che è il valore predefinito:

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

cursor.execute("CREATE TABLE #TempData (ID INT, Name NVARCHAR(50))")
cursor.execute("INSERT INTO #TempData VALUES (1, 'test')")

# An explicit rollback or an error removes #TempData.
conn.rollback()

# This statement fails with "Invalid object name '#TempData'".
cursor.execute("SELECT * FROM #TempData")

Effettua il commit immediatamente dopo aver creato una tabella temporanea, oppure usa la modalità autocommit:

cursor.execute("CREATE TABLE #TempData (ID INT, Name NVARCHAR(50))")
conn.commit()

cursor.execute("INSERT INTO #TempData VALUES (1, 'test')")
conn.commit()

Le istruzioni DDL che richiedono la modalità autocommitto, come CREATE DATABASE, falliscono all'interno di una transazione aperta. Imposta l'autocommit prima di eseguirli:

conn.autocommit = True
cursor.execute("CREATE DATABASE TestDB")
conn.autocommit = False

Transazione non commessa

Sintomi:

I cambiamenti nei dati non persistono dopo aver chiuso la connessione.

Soluzione:

Con autocommit=False, che è il valore predefinito, chiamiamo commit():

cursor.execute("CREATE TABLE #Products (Name NVARCHAR(100))")
cursor.execute(
    "INSERT INTO #Products (Name) VALUES (%(name)s)",
    {"name": "Widget"},
)
conn.commit()

In alternativa, usa la modalità autocommit:

conn = mssql_python.connect(connection_string, autocommit=True)

Errori di deadlock

Sintomi:

OperationalError: [40001] (1205) Transaction ... was deadlocked on lock resources with another process

Soluzione:

La logica di ritentazione gestisce il guasto immediato, ma blocchi ricorrenti indicano un problema di progettazione. Acquisisci il grafo del deadlock e analizza le istruzioni e i tipi di blocco. Le soluzioni più comuni includono queste modifiche:

  • Riordina le operazioni affinché le transazioni concorrenti acquisiscano i lock nella stessa sequenza.
  • Riduci la portata delle transazioni.
  • Aggiungere indici adeguati per ridurre la durata del blocco.

Per una guida completa dell'analisi degli sblocchi, consulta la guida Deadlocks. Se usi database SQL di Azure, vedi Analizza e preveni blocchi.

Problemi di carico in blocco

Violazioni dei vincoli durante la copia di massa

Sintomi:

RuntimeError: CHECK constraint ... Conflict occurred in database ...
RuntimeError: Cannot insert duplicate key ... violation of PRIMARY KEY constraint

Causa:

I dati nel tuo batch violano vincoli di tabella come la chiave primaria, la chiave unica, la chiave di controllo o la chiave esterna.

Soluzione:

Valida i dati prima di caricarli. Per grandi dataset, carichi i dati in una tabella di staging e poi uniscili al target:

cursor.execute("CREATE TABLE ##Staging (ID INT, Name NVARCHAR(100))")
cursor.bulkcopy("##Staging", rows)

# Check for duplicate rows before the merge.
cursor.execute("""
    SELECT s.ID
    FROM ##Staging AS s
    INNER JOIN dbo.Target AS t
        ON s.ID = t.ID
""")
dupes = cursor.fetchall()
if dupes:
    print(f"Skipping {len(dupes)} duplicate rows")

# Insert rows that don't exist in the target.
cursor.execute("""
    INSERT INTO dbo.Target (ID, Name)
    SELECT s.ID, s.Name
    FROM ##Staging AS s
    WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.Target AS t
        WHERE t.ID = s.ID
    )
""")
conn.commit()

Per i modelli di upsert con tabelle di staging, vedi i modelli di caricamento e spostamento dei dati.

Errori di mappatura delle colonne

Sintomi:

RuntimeError: Bulk copy failure - column count mismatch

Causa:

Il numero di colonne nei tuoi dati non corrisponde al numero di colonne target della tabella, oppure le colonne sono nell'ordine sbagliato.

Soluzione:

Assicurati che i dati corrispondano allo schema della tabella per ordine e numero:

from decimal import Decimal

cursor.execute("""
    SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
    ORDER BY ORDINAL_POSITION
""")
for col in cursor.fetchall():
    print(col)

rows = [
    (1, "Widget", Decimal("19.99")),
    (2, "Gadget", Decimal("29.99")),
]
cursor.bulkcopy("dbo.MyTable", rows)

Discorrispondenze di tipo durante la copia di massa

Sintomi:

Carichi dati, ma i valori sono troncati, arrotondati o erratati.

Causa:

I valori di Python non corrispondono nettamente ai tipi di colonne di destinazione. Esempi comuni includono float valori caricati in colonne decimali , che possono perdere precisione, e stringhe sovradimensionate caricate in colonne di lunghezza fissa.

Soluzione:

Usa tipi Python che corrispondono al tuo schema:

from decimal import Decimal

rows = [
    # Use Decimal for decimal and numeric columns.
    (1, "Widget", Decimal("19.99")),
    # Avoid float values because they can lose precision.
    # (1, "Widget", 19.99),
]
cursor.bulkcopy("dbo.Products", rows)

Errori di binding dei tipi NumPy

Sintomi:

I parametri falliscono silenziosamente o aumentano errori di tipo di dati quando si usano interi o tipi float NumPy.

Causa:

I tipi NumPy come numpy.int64 e numpy.int32 non superano isinstance(x, int) in NumPy 2.x. L'inferenza di tipo del conducente non li riconosce, il che provoca comportamenti inaspettati.

Soluzione:

Converti i valori NumPy in tipi nativi di Python prima di assegnarli:

import numpy as np

cursor.execute(
    "SELECT * FROM Production.Product WHERE ProductID = %(product_id)s",
    {"product_id": int(np.int64(42))},
)

for _, row in df.iterrows():
    cursor.execute(
        "INSERT INTO #Orders (ProductID, Qty) "
        "VALUES (%(product_id)s, %(qty)s)",
        {
            "product_id": int(row["ProductID"]),
            "qty": int(row["Qty"]),
        },
    )

Per dataset più grandi, usa i percorsi di integrazione Arrow o pandas . Questi percorsi gestiscono internamente la conversione dei tipi.

Copia in blocco con tabelle temporanee

Sintomi:

cursor.bulkcopy("#TempTable", data) solleva RuntimeError: Invalid object name '#TempTable'.

Causa:

bulkcopy() Non posso risolvere le tabelle temporaneali della sessione (#tablename) a causa delle limitazioni di ricerca dei metadati. Le tabelle temporanee globali (##tablename) e le tabelle permanenti funzionano.

Soluzione:

Usa una tabella temporanea globale o una tabella di staging normale:

# A global temp table is visible to all sessions and is dropped
# when the last session disconnects.
cursor.execute("CREATE TABLE ##Staging (ID INT, Name NVARCHAR(50))")
cursor.bulkcopy("##Staging", rows)

# Alternatively, use a permanent staging table.
cursor.execute("CREATE TABLE dbo.Staging (ID INT, Name NVARCHAR(50))")
cursor.bulkcopy("dbo.Staging", rows)

Per piccoli dataset dove preferisci una tabella temporanea di sessione, usa executemany():

cursor.execute("CREATE TABLE #Staging (ID INT, Name NVARCHAR(50))")
cursor.executemany(
    "INSERT INTO #Staging (ID, Name) VALUES (?, ?)",
    rows,
)

Contenuti correlati

  • Last updated on