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

Conteggi di righe errati in lotti multi-risultato

Se rowcount ripete il conteggio della prima istruzione dopo nextset(), aggiorna il driver. Ogni aggiornamento riuscito nextset() aggiorna il conteggio per il nuovo risultato. Un risultato che produce una riga inizia -1 prima del recupero. Verifica cursor.description prima di recuperare perché i risultati delle modifiche dei dati contengono conteggi invece di righe.

Disattiva SET NOCOUNT quando hai bisogno dei risultati delle righe interessate. Per un esempio completo, vedi Monitora ogni risultato in un batch.

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

Il costruttore Timestamp richiede un argomento in microsecondi

Se Timestamp(year, month, day, hour, minute, second) genera TypeError per un argomento microsecond mancante, aggiorna mssql-python. Il driver corrente accetta il costruttore DB-API a sei argomenti e imposta microsecond a zero per impostazione predefinita. Per maggiori informazioni, vedi DB-API costruttore di timestamp.

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

La copia in blocco fallisce dopo un aggiornamento del pacchetto

Se le query normali funzionano ma bulkcopy() o bulkcopy_arrow() non riescono a importare o caricare il modulo nativo di copia bulk, aggiorna il driver con dipendenze:

python -m pip install --upgrade mssql-python
python -m pip check

Il driver richiede un runtime corrispondente mssql-python-rs. Un aggiornamento parziale o un'installazione con --no-deps possono lasciare pacchetti incompatibili installati. Se usi un indice privato dei pacchetti, replica entrambi i pacchetti companion e mantieni i pin di dipendenza dichiarati dal driver. Per le versioni bloccate correnti, vedi Requisiti di dipendenza.

Nomi di destinazione non supportati

bulkcopy() e bulkcopy_arrow() accettano al massimo tre parti identificative. Un nome in quattro parti come server.database.schema.table genera RuntimeError durante la ricerca dei metadati prima che vengano inviate le righe. Connettiti direttamente al server di destinazione e usa un nome di tabella in una, due o tre parti. Per errori di escape delle parentesi e nomi malformati, vedi Nomi delle tabelle target.

Unicode o secondi frazionari persi durante la copia in massa

Aggiorna il driver se la copia in massa corrompe il testo UTF-8 o tronca frazioni di secondo quando carica valori datetime2 compatibili. L'attuale runtime di copia in massa gestisce il testo UTF-8 e mantiene la precisione datetime2 disponibile nei tipi di sorgente e destinazione.

Usa nvarchar o una collazione UTF-8 su varchar per il testo Unicode. I valori Python datetime e i metodi Arrow fetch del driver sono limitati ai microsecondi. Per valori di 100 nanosecondi già presenti in una colonna di timestamp di nanosecondi Arrow, caricare in datetime2(7) senza prima convertire in Pythondatetime. Per maggiori informazioni, vedi Unicode e precisione temporale e Arrow temporal precision.

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

  • Ultimo aggiornamento: