Probleemoplossing voor query-, data- en operationele problemen met mssql-python

Gebruik dit artikel om problemen met query-uitvoering, datatype, prestaties, transacties en bulkkopieën met de mssql-python driver te diagnosticeren.

Problemen met de uitvoering van query's

Tabel of object niet gevonden

Symptomen:

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

Mogelijke oorzaken en oplossingen:

• Onjuiste databasecontext

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

• Schema niet gespecificeerd

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

• Tabel bestaat niet

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

Syntaxisfout

Symptomen:

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

Oplossingen:

1. Test de SQL-instructie in SQL Server Management Studio (SSMS) om de syntaxis te controleren.

2. Gebruik een geparametriseerde query in plaats van stringinterpolatie:

   # 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},
   )
   

Parameterfouten

Symptomen:

ProgrammingError: [07001] Wrong number of parameters

Oplossingen:

1. Tel de tijdelijke en parameters mee. De tellingen moeten overeenkomen.

2. Kies de juiste parameterstijl:

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

Datatypeproblemen

Fouten bij data-tijdconversie

Symptomen:

DataError: [22007] Invalid datetime format

Solution:

Gebruik Python-objecten datetime in plaats van strings.

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

Decimale precisieproblemen

Symptomen:

Getallen lijken afgekapt of onjuist afgerond.

Solution:

Gebruik decimal.Decimal voor precieze numerieke waarden:

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

Unicode-coderingsproblemen

Symptomen:

Speciale tekens lijken onverstaanbaar of veroorzaken fouten.

Oplossingen:

1. Gebruik nvarchar-kolommen voor Unicode-data in je database.

2. Geef de snaren direct door. De driver verzorgt de codering:

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

Prestatieproblemen

Langzame query-uitvoering

Mogelijke oorzaken en oplossingen:

• Ontbrekende indexen: Controleer het query-uitvoeringsplan in SSMS.

• Grote resultaatsets: Gebruik fetchmany() in plaats van fetchall():

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

• Verbindingspooling uitgeschakeld: Pooling inschakelen:

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

Geheugenproblemen met grote resultaten

Symptomen:

Het Python-proces heeft onvoldoende geheugen.

Oplossingen:

1. Stream resultaten in plaats van alle rijen in het geheugen te laden.

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

2. Gebruik server-side paginering.

   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
   

Transactieproblemen

Bereik van tijdelijke tabellen met autocommit

Sessietijdelijke tabellen (#tablename) die je binnen een transactie aanmaakt, verdwijnen wanneer de transactie wordt teruggerold. Dit gedrag veroorzaakt vaak verwarring wanneer autocommit uit staat, wat standaard is:

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

Commit direct nadat je een tijdelijke tabel hebt gemaakt, of gebruik de autocommit-modus:

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

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

DDL-instructies die autocommit vereisen, zoals CREATE DATABASE, falen binnen een open transactie. Stel autocommit in voordat je ze uitvoert:

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

Transactie niet gerealiseerd

Symptomen:

Datawijzigingen blijven niet bestaan nadat je de verbinding hebt gesloten.

Solution:

Met autocommit=False, wat de standaard is, roep commit():

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

Alternatief kun je de autocommit-modus gebruiken:

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

Deadlockfouten

Symptomen:

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

Solution:

Herprobeerlogica zorgt voor het directe falen, maar terugkerende deadlocks wijzen op een ontwerpprobleem. Leg het deadlockdiagram vast en analyseer de instructies en vergrendelingstypen. Veelvoorkomende oplossingen zijn onder andere de volgende wijzigingen:

• Herordeningsoperaties zodat concurrerende transacties vergrendelingen in dezelfde volgorde krijgen.
• Verminder de reikwijdte van transacties.
• Voeg passende indexen toe om de duur van de vergrendeling te verkorten.

Voor een volledige walkthrough van deadlock-analyse, zie de Deadlocks-gids. Als je Azure SQL Database gebruikt, zie Deadlocks analyseren en voorkomen.

Problemen met bulklading

Beperkingsovertredingen tijdens bulkcopy

Symptomen:

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

Oorzaak:

Data in je batch overtreedt tabelbeperkingen zoals primaire sleutel, unieke, controle- of vreemde sleutelbeperkingen.

Solution:

Valideer de data voordat je het laadt. Voor grote gegevenssets laad je de gegevens in een stagingtabel en voeg je deze vervolgens samen in de doeltabel:

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

Voor upsert-patronen met stagingtabellen, zie Patronen voor het laden en verplaatsen van gegevens.

Kolomafbeeldingsfouten

Symptomen:

RuntimeError: Bulk copy failure - column count mismatch

Oorzaak:

Het aantal kolommen in je data komt niet overeen met het aantal kolommen in de doeltabel, of de kolommen staan in de verkeerde volgorde.

Solution:

Zorg ervoor dat je gegevens overeenkomen met het tabelschema in volgorde en aantal:

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)

Typeverschillen tijdens een bulkcopy

Symptomen:

Gegevens worden geladen, maar waarden worden afgekort, afgerond of incorrect.

Oorzaak:

Python-waarden worden niet netjes gekoppeld aan de doelkolomtypen. Veelvoorkomende voorbeelden zijn float waarden die in decimale kolommen worden geladen, die aan precisie kunnen verliezen, en oversized strings die in kolommen van vaste lengte worden geladen.

Solution:

Gebruik Python-types die bij je schema passen:

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)

NumPy-type binding fouten

Symptomen:

Parameters falen stilletjes of veroorzaken datatypefouten wanneer je NumPy-integer- of float-types gebruikt.

Oorzaak:

NumPy-typen zoals numpy.int64 en numpy.int32 slagen niet door isinstance(x, int) in NumPy 2.x. De type-inferentie van de bestuurder herkent ze niet, wat onverwacht gedrag veroorzaakt.

Solution:

Converteer NumPy-waarden naar native Python-types voordat je ze bindt:

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

Voor grotere datasets gebruik je de integratiepaden van Arrow of pandas . Deze paden verzorgen de typeconversie intern.

Bulk-kopiëren met tijdelijke tabellen

Symptomen:

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

Oorzaak:

bulkcopy() Kan sessie-tijdelijke tabellen (#tablename) niet oplossen vanwege beperkingen in metadata-opzoeken. Globale tijdelijke tabellen (##tablename) en permanente tabellen werken.

Solution:

Gebruik een globale tijdelijke tabel of een gewone stagingtabel:

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

Voor kleine datasets waarbij je een sessie-tijdelijke tabel prefereert, gebruik executemany():

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

Verwante onderwerpen

• Voer queries uit met mssql-python
• Toewijzingen van gegevenstypen met mssql-python
• Beheer transacties met mssql-python
• Prestatieoptimalisatie met mssql-python
• Bulkkopiëren met mssql-python
• Foutafhandeling en SQLSTATE-codes voor mssql-python
• Problemen oplossen met mssql-python