Lekérdezési, adat- és működési problémák keresése mssql-python használatával

Használja ezt a cikket a lekérdezés végrehajtása, adattípus, teljesítmény, tranzakció és tömeges másolási problémák diagnosztizálására az mssql-python illesztőprogramban.

Lekérdezés végrehajtási problémái

A tábla vagy objektum nem található

Tünetek:

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

Lehetséges okok és megoldások:

  • Hibás adatbázis-kontextus

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

  • Nem meghatározott séma

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

  • A táblázat nem létezik

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

Szintaxishiba

Tünetek:

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

Megoldások:

  1. Tesztelje az SQL utasítást az SQL Server Management Studio-ban (SSMS) a szintaxisnak ellenőrzéséhez.

  2. Paraméterezett lekérdezést használj string interpoláció helyett:

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

Paraméterhibák

Tünetek:

ProgrammingError: [07001] Wrong number of parameters

Megoldások:

  1. Számold meg a helykitöltőket és paramétereket. A számlálásoknak egyezniük kell.

  2. Válaszd ki a megfelelő paraméterstílust:

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

Adattípus-problémák

Dátumidő-átalakítási hibák

Tünetek:

DataError: [22007] Invalid datetime format

Solution:

Használj Python datetime objektumokat a stringek helyett.

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

Tizedes pontossági problémák

Tünetek:

A számok lerövidítettnek vagy helytelenül kerekítve jelennek meg.

Solution:

Pontos numerikus értékek felhasználása decimal.Decimal :

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 kódolási problémák

Tünetek:

A speciális karakterek zavarosnak tűnnek vagy hibákat okoznak.

Megoldások:

  1. Használj nvarchar oszlopokat Unicode adatokhoz az adatbázisodban.

  2. Adja át közvetlenül a karakterláncokat. A meghajtó kezeli a kódolást:

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

Teljesítménnyel kapcsolatos problémák

Lassú lekérdezés végrehajtása

Lehetséges okok és megoldások:

  • Hiányzó indexek: Ellenőrizd az SSMS-ben a lekérdezés végrehajtási tervet.

  • Nagy eredményhalmazok: Használd fetchmany() helyette fetchall():

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

  • Csatlakozási csoportosítás letiltva: Engedélyezd a poolinget:

    import mssql_python

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

Memóriaproblémák nagy méretű eredmények esetén

Tünetek:

A Python folyamat kifogy a memóriából.

Megoldások:

  1. Az eredményeket folyamatosan dolgozza fel ahelyett, hogy minden sort a memóriába töltene.

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

  2. Használj szerveroldali lapozást.

    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

Tranzakciós problémák

Ideiglenes táblák hatóköre automatikus véglegesítés mellett

A tranzakción belül létrehozott session temp táblák (#tablename) eltűnnek, amikor a tranzakció visszafordul. Ez a viselkedés gyakran zavart okoz, amikor az automatikus commitálás ki van kapcsolva, ami az alapértelmezett:

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

Az ideiglenes tábla létrehozása után azonnal véglegesítsd a tranzakciót, vagy használd az autocommit módot:

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

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

DDL utasítások, amelyek automatikus commit módot igényelnek, például CREATE DATABASE, egy nyílt tranzakcióban meghibásodnak. Állítsd be az automatikus véglegesítést, mielőtt futtatod őket:

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

Nem végrehajtott tranzakció

Tünetek:

Az adatváltozások nem maradnak fenn, miután lezárod a kapcsolatot.

Solution:

A(z) autocommit=False használatával, ami az alapértelmezett, hívja meg a(z) commit() elemet:

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

Alternatívaként használj automatikus commit módot:

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

Holtzárhibák

Tünetek:

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

Solution:

Az újrapróbálkozás logika kezeli az azonnali hibát, de az ismétlődő holtpontok tervezési problémára utalnak. Rögzítsd a holtponti gráfot, és elemezd az utasításokat, valamint a zárolási típusokat. Gyakori javítások közé tartoznak a következők:

  • Rendezze át a műveleteket úgy, hogy az egymással versengő tranzakciók ugyanabban a sorrendben szerezzék meg a zárolásokat.
  • Csökkentsd a tranzakciós hatókört.
  • Megfelelő indexeket adj hozzá a zár időtartamának csökkentéséhez.

A holthelyzet elemzésének teljes végigjátszásáért lásd a Deadlocks útmutatót. Ha Azure SQL Database-et használ, lásd: Holtpontok elemzése és megelőzése.

Tömegterhelési problémák

Korlátok megsértése tömegmásolás során

Tünetek:

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

A probléma oka

A kötegben lévő adatok megsértik a tábla korlátozásait, például az elsődleges kulcsra, az egyediségre, az ellenőrzésre vagy az idegen kulcsra vonatkozó korlátozásokat.

Solution:

Ellenőrizd az adatokat, mielőtt betöltöd. Nagy adathalmazok esetén töltsd be az adatokat egy staging table-be, majd egyesítsd őket a célponttal:

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

Az átmeneti táblákat használó upsert mintákkal kapcsolatban lásd a következőt: Adatbetöltési és -mozgatási minták.

Oszlopleképezési hibák

Tünetek:

RuntimeError: Bulk copy failure - column count mismatch

A probléma oka

Az adatodban lévő oszlopok száma nem egyezik a céltáblák oszlopszámával, vagy az oszlopok rossz sorrendben vannak.

Solution:

Győződj meg róla, hogy adataid sorrendben és számban egyeznek a táblázat sémával:

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)

Típusütközések tömeges másolás során

Tünetek:

Az adat betöltődik, de az értékek lerövidítettek, kerekítettek vagy helytelenek.

A probléma oka

A Python értékek nem egyeznek tisztán a céloszlop típusokkal. Gyakori példák közé float tartoznak a tizedes oszlopokba betöltött értékek, amelyek csökkenthetik a pontosságot, valamint a túlméretezett láncok, amelyek fix hosszúságú oszlopokba töltődnek.

Solution:

Használj olyan Python típusokat, amelyek megfelelnek a sémádnak:

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 típusú kötési hibák

Tünetek:

A paraméterek csendben hibásodnak vagy adattípus-hibákat okoznak, amikor NumPy egész számot vagy lebegő típust használsz.

A probléma oka

A NumPy típusok, mint numpy.int64 és numpy.int32 nem mennek isinstance(x, int) át a NumPy 2.x-ben. A sofőr típuskövetkeztetése nem ismeri fel őket, ami váratlan viselkedést okoz.

Solution:

Konvertáld a NumPy értékeket natív Python típusokra, mielőtt kötöd őket:

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

Nagyobb adathalmazokhoz használd az Arrow vagy Pandas integrációs útvonalakat. Ezek az útvonalak belsőleg kezelik a típuskonverziót.

Tömeges másolat ideiglenes táblázatokkal

Tünetek:

cursor.bulkcopy("#TempTable", data) megemeli a(z) RuntimeError: Invalid object name '#TempTable' értékét.

A probléma oka

bulkcopy() Nem tudom megoldani a session temp táblákat (#tablename) a metaadat-keresés korlátai miatt. A globális hőmérsékleti táblázatok (##tablename) és az állandó táblázatok működnek.

Solution:

Használj globális temp táblát vagy hagyományos staging table-t:

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

Kis adathalmazokhoz, ahol a session temp táblát részesíted előnyben, használd executemany()a következőket:

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

Kapcsolódó tartalom

  • Utolsó frissítés időpontja: