Řešení problémů s dotazy, daty a provozem pomocí mssql-python

Použijte tento článek k diagnostice problémů s prováděním dotazů, typem dat, výkonem, transakcemi a hromadným kopírováním ovladače mssql-python .

Problémy s prováděním dotazů

Tabulka nebo objekt nenalezený

Příznaky:

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

Možné příčiny a řešení:

  • Nesprávný databázový kontext

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

  • Schéma nespecifikované

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

  • Tabulka neexistuje

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

Chyba syntaxe

Příznaky:

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

Řešení:

  1. Otestujte SQL příkaz v SQL Server Management Studio (SSMS) pro ověření syntaxe.

  2. Použijte parametrizovaný dotaz místo interpolace řetězců:

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

Chyby parametrů

Příznaky:

ProgrammingError: [07001] Wrong number of parameters

Řešení:

  1. Spočítejte zástupné symboly a parametry. Počty se musí shodovat.

  2. Vyberte správný styl parametrů:

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

Problémy s datovými typy

Chyby při převodu data a času

Příznaky:

DataError: [22007] Invalid datetime format

Solution:

Používejte Python datetime objekty místo řetězců.

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

Problémy s desetinnou přesností

Příznaky:

Čísla se zobrazují oříznutá nebo nesprávně zaokrouhlená.

Solution:

Použití decimal.Decimal pro přesné číselné hodnoty:

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

Problémy s kódováním v Unicode

Příznaky:

Speciální znaky se objevují zkresleně nebo způsobují chyby.

Řešení:

  1. Používejte sloupce nvarchar pro Unicode data ve vaší databázi.

  2. Předávejte řetězce přímo. Ovladač zajišťuje kódování:

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

Problémy s výkonem

Pomalé provádění dotazů

Možné příčiny a řešení:

  • Chybějící indexy: Zkontrolujte plán provádění dotazů v SSMS.

  • Velké množiny výsledků: Použijte fetchmany() místo :fetchall()

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

  • Sdílení spojení zakázáno: Povolit poolování:

    import mssql_python

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

Problémy s pamětí při velkých výsledcích

Příznaky:

Procesu Pythonu došla paměť.

Řešení:

  1. Streamujte výsledky místo načítání všech řádků do paměti.

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

  2. Používejte stránkování na straně serveru.

    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

Problémy s transakcí

Rozsah platnosti dočasné tabulky s autocommit režimem

Tabulky session temp (#tablename), které vytvoříte uvnitř transakce, zmizí, když se transakce vrátí zpět. Toto chování často způsobuje zmatek, když je automatický commit vypnutý, což je výchozí nastavení:

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

Potvrďte transakci ihned po vytvoření dočasné tabulky nebo použijte režim automatického potvrzování:

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

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

DDL příkazy, které vyžadují automatický commit režim, například CREATE DATABASE, selžou uvnitř otevřené transakce. Nastavte automatický commit před jejich spuštěním:

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

Transakce neprovedena

Příznaky:

Změny dat se po uzavření připojení nepřetrvávají.

Solution:

S autocommit=False, což je výchozí, označme commit():

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

Alternativně použijte režim automatického závazku:

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

Chyby zablokování

Příznaky:

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

Solution:

Logika opakování zvládá okamžité selhání, ale opakující se patové situace naznačují problém v návrhu. Zachyťte graf mrtvého bodu a analyzujte příkazy a typy zámků. Běžné opravy zahrnují tyto změny:

  • Přeuspořádejte operace tak, aby souběžné transakce získávaly zámky ve stejném pořadí.
  • Zúžit rozsah transakcí.
  • Přidejte vhodné indexy pro zkrácení doby zámku.

Podrobný návod k analýze deadlocků najdete v článku Průvodce deadlocky. Pokud používáte Azure SQL Database, viz Analýza a prevence uváznutí.

Problémy s hromadným zatížením

Porušení omezení během hromadného kopírování

Příznaky:

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

Příčina:

Data v dávce porušují omezení tabulky, například omezení primárního klíče, jedinečnosti, kontrolní omezení nebo omezení cizího klíče.

Solution:

Ověřte data před načtením. Pro velké datové sady načtěte data do přípravné tabulky a poté je slučte s cílovou tabulkou:

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

Vzory pro operace upsert s přípravnými tabulkami najdete v tématu Vzory načítání a přesunu dat.

Chyby při mapování sloupců

Příznaky:

RuntimeError: Bulk copy failure - column count mismatch

Příčina:

Počet sloupců ve vašich datech neodpovídá počtu sloupců v cílové tabulce, nebo jsou sloupce ve špatném pořadí.

Solution:

Ujistěte se, že vaše data odpovídají tabulkovému schématu v pořadí a počtu:

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)

Typové neshody během hromadné kopie

Příznaky:

Data se načítají, ale hodnoty jsou zkrácené, zaokrouhlené nebo nesprávné.

Příčina:

Hodnoty Pythonu nelze jednoznačně mapovat na typy cílových sloupců. Běžné příklady zahrnují float hodnoty načtené do desetinných sloupců, které mohou ztratit přesnost, a nadměrně velké řetězce načítané do sloupců s pevnou délkou.

Solution:

Používejte Python typy, které odpovídají vašemu schématu:

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)

Selhání vázání typu NumPy

Příznaky:

Parametry při použití celočíselných typů NumPy nebo typů s plovoucí desetinnou čárkou buď tiše selžou, nebo vyvolají chyby datového typu.

Příčina:

Typy NumPy, například numpy.int64 a numpy.int32, v NumPy 2.x neprojdou kontrolou isinstance(x, int). Typová inference řidiče je nerozpozná, což způsobuje neočekávané chování.

Solution:

Převeďte hodnoty NumPy na nativní typy v Python před jejich přiřazením:

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

Pro větší datové sady použijte integrační cesty Arrow nebo Pandas . Tyto cesty zpracovávají převod typů interně.

Hromadné kopírování pomocí dočasných tabulek

Příznaky:

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

Příčina:

bulkcopy() nelze rozpoznat dočasné tabulky relace (#tablename) kvůli omezením při vyhledávání metadat. Globální dočasné tabulky (##tablename) a trvalé tabulky fungují.

Solution:

Použijte globální dočasnou tabulku nebo běžnou pracovní tabulku:

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

Pro malé datové sady, pokud dáváte přednost dočasné tabulce relace, použijte executemany():

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

Související obsah

  • Last updated on