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

Nesprávné počty řádků ve vícenásobných dávkách

Pokud rowcount opakuje počet prvního příkazu po nextset(), upgradujte ovladač. Každé úspěšné nextset() obnoví počet pro nový výsledek. Výsledek vracející řádky začíná před načtením -1. Před načítáním zkontrolujte cursor.description, protože výsledky úprav dat obsahují počty místo řádků.

Ponechte SET NOCOUNT vypnuté, když potřebujete výsledky ovlivněných řádků. Úplný příklad najdete v článku Sledování každého výsledku v dávce.

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

Konstruktor časového razítka vyžaduje mikrosekundový argument

Pokud Timestamp(year, month, day, hour, minute, second) vyvolá TypeError kvůli chybějícímu argumentu microsecond, upgradujte mssql-python. Aktuální ovladač přijímá konstruktor DB-API se šesti argumenty a microsecond má výchozí hodnotu nula. Pro více informací viz DB-API time stamp constructor.

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

Hromadné kopírování selže po aktualizaci balíčku

Pokud běžné dotazy fungují, ale bulkcopy() nebo bulkcopy_arrow() nedokáže importovat nebo načíst nativní modul hromadné kopie, upgradujte ovladač o závislosti:

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

Ovladač vyžaduje odpovídající mssql-python-rs běhové prostředí. Částečný upgrade nebo instalace s --no-deps mohou zanechat nekompatibilní balíčky nainstalované. Pokud použijete soukromý index balíčků, zrcadlete oba doprovodné balíčky a zachovejte deklarované závislostní piny ovladače. Aktuální piny najdete v části Požadavky na závislosti.

Nepodporované názvy destinací

bulkcopy() a bulkcopy_arrow() přijímají maximálně tři části identifikátoru. Čtyřdílný název, například server.database.schema.table, vyvolá RuntimeError při vyhledávání metadat před odesláním jakýchkoli řádků. Připojte se přímo k cílovému serveru a použijte jedno-, dvou- nebo třísložkový název tabulky. Informace o escapování závorek a chybách v neplatných názvech najdete v části Názvy cílových tabulek.

Unicode nebo zlomky sekund ztracené při hromadném kopírování

Aktualizujte ovladač, pokud hromadné kopírování poškodí text UTF-8 nebo zkrátí zlomek sekund při načítání kompatibilních hodnot datetime2 . Aktuální hromadné kopírovací runtime zpracovává text UTF-8 a zachovává přesnost datetime2 dostupnou ve zdrojovém a cílovém typu.

Pro Unicode text použijte nvarchar nebo UTF-8 kolaci na varchar. Hodnoty v Python datetime a metody načítání Arrow ovladače jsou omezeny na mikrosekundy. Pro hodnoty 100 nanosekund, které jsou již uloženy ve sloupci nanosekundových časových razítek Arrow, načtěte do datetime2(7) bez předchozího převodu na objekt Pythonudatetime. Pro více informací viz Unicode a časová přesnost a Časová přesnost v Arrow.

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

  • Poslední aktualizace v