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

Onjuiste aantallen rijen in batches met meerdere resultsets

Als rowcount na nextset() het aantal van de eerste instructie opnieuw weergeeft, upgrade dan het stuurprogramma. Elke succesvolle nextset() update werkt het aantal voor het nieuwe resultaat bij. Een resultaat dat rijen oplevert start op -1 vóór het ophalen. Controleer cursor.description vóór het ophalen, omdat data-modificatieresultaten tellingen bevatten in plaats van rijen.

Laat SET NOCOUNT weg wanneer je resultaten van beïnvloede rijen nodig hebt. Voor een volledig voorbeeld, zie Elk resultaat in een batch bijhouden.

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

De constructor van Timestamp vereist een microseconde-argument

Als Timestamp(year, month, day, hour, minute, second)microsecond genereert voor een ontbrekend TypeError-argument, upgrade dan mssql-python. De huidige driver accepteert de zes-arguments DB-API constructor en zet microsecond standaard op nul. Voor meer informatie, zie DB-API tijdstempelconstructor.

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

Bulkkopiëren mislukt na een upgrade van een pakket

Als reguliere queries werken maar bulkcopy() of bulkcopy_arrow() er niet in slagen de native bulk copy-module te importeren of te laden, upgrade dan de driver met afhankelijkheden:

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

De driver vereist een overeenkomende mssql-python-rs runtime. Een gedeeltelijke upgrade of installatie met --no-deps kan incompatibele pakketten geïnstalleerd laten. Als je een private package index gebruikt, spiegel dan beide companion packages en behoud de door de driver gedeclareerde vastgezette afhankelijkheidsversies. Voor de huidige vastgezette versies, zie Vereisten voor afhankelijkheden.

Niet-ondersteunde bestemmingsnamen

bulkcopy() en bulkcopy_arrow() accepteren maximaal drie identificatieonderdelen. Een vierdelige naam zoals server.database.schema.table veroorzaakt RuntimeError tijdens het opzoeken van metagegevens voordat er rijen worden verzonden. Maak direct verbinding met de bestemmingsserver en gebruik een tabelnaam van één deel, twee of drie delen. Voor haakjesontsnapping en fouten door ongeldige namen, zie namen van doeltabellen.

Unicode of fracties van seconden verloren tijdens bulkkopiëren

Upgrade de driver als bulkkopie UTF-8-tekst beschadigt of fracties van seconden afsnijdt bij het laden van compatibele datetime2-waarden . De huidige bulk-copy runtime verwerkt UTF-8 tekst en behoudt de datetime2 precisie beschikbaar in de bron- en bestemmingstypen.

Gebruik nvarchar of een UTF-8-collatie op varchar voor Unicode-tekst. Python-waarden datetime en de Arrow-ophaalmethoden van de driver zijn beperkt tot microseconden. Voor waarden in stappen van 100 nanoseconden die al in een Arrow-nanoseconde-tijdstempelkolom staan, laad je in datetime2(7) zonder eerst naar Python datetime te converteren. Voor meer informatie, zie Unicode en temporele precisie en Arrow temporele precisie.

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

  • Laatst bijgewerkt om