Rozwiązywanie problemów z zapytaniami, danymi i operacjami za pomocą mssql-python

Użyj tego artykułu, aby diagnozować problemy związane z wykonywaniem zapytań, typami danych, wydajnością, transakcjami oraz kopiowaniem zbiorczym sterownika mssql-python.

Problemy z wykonywaniem zapytań

Tabela lub obiekt nie znaleziony

Objawy:

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

Możliwe przyczyny i rozwiązania:

  • Nieprawidłowy kontekst bazy danych

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

  • Schemat nieokreślony

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

  • Tabela nie istnieje

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

Błąd składniowy

Objawy:

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

Rozwiązania:

  1. Przetestuj zdanie SQL w SQL Server Management Studio (SSMS), aby zweryfikować składnię.

  2. Zamiast interpolacji ciągów znaków używaj zapytania parametrycznego:

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

Błędy parametrów

Objawy:

ProgrammingError: [07001] Wrong number of parameters

Rozwiązania:

  1. Policz symbole zastępcze i parametry. Liczenia muszą się zgadzać.

  2. Wybierz właściwy styl parametrów:

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

Problemy z typem danych

Błędy konwersji daty i godziny

Objawy:

DataError: [22007] Invalid datetime format

Rozwiązanie:

Używaj obiektów Python datetime zamiast ciągów znaków.

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

Problemy z precyzją dziesiętną

Objawy:

Liczby są obcięte lub nieprawidłowo zaokrąglone.

Rozwiązanie:

Zastosowanie decimal.Decimal do precyzyjnych wartości liczbowych:

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

Problemy z kodowaniem Unicode

Objawy:

Znaki specjalne pojawiają się zniekształcone lub powodują błędy.

Rozwiązania:

  1. Używaj kolumn nvarchar do danych Unicode w swojej bazie danych.

  2. Przekazuj ciągi bezpośrednio. Sterownik obsługuje kodowanie:

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

Problemy z wydajnością

Powolne wykonywanie zapytań

Możliwe przyczyny i rozwiązania:

  • Brakujące indeksy: Sprawdź plan wykonywania zapytań w SSMS.

  • Duże zbiory wyników: Użycie fetchmany() zamiast :fetchall()

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

  • Wyłączenie pulowania połączeń: Włączenie pulowania:

    import mssql_python

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

Problemy z pamięcią w przypadku dużych wyników

Objawy:

Procesowi Pythona kończy się pamięć.

Rozwiązania:

  1. Przesyłaj wyniki strumieniowo zamiast ładować wszystkie wiersze do pamięci.

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

  2. Używaj stronowania po stronie serwera.

    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

Problemy transakcyjne

Zakres tabeli tymczasowej z automatycznym zatwierdzeniem

Tabele tymczasowe sesji (#tablename), które tworzysz wewnątrz transakcji, znikają, gdy transakcja zostanie wycofana. To zachowanie często powoduje zamieszanie, gdy autocommit jest wyłączony, co jest domyślne:

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

Zatwierdzaj natychmiast po utworzeniu tabeli tymczasowej lub użyj trybu automatycznego zatwierdzania:

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

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

Instrukcje DDL wymagające trybu autocommit, takie jak CREATE DATABASE, kończą się niepowodzeniem wewnątrz otwartej transakcji. Ustaw autocommit przed ich uruchomieniem:

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

Transakcja nie została dokonana

Objawy:

Zmiany danych nie zachowują się po zamknięciu połączenia.

Rozwiązanie:

Przy autocommit=False, co jest domyślnym, wywołajmy commit():

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

Alternatywnie, użyj trybu automatycznego zatwierdzania:

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

Błędy zakleszczeń

Objawy:

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

Rozwiązanie:

Logika ponownej próby radzi sobie z natychmiastową awarią, ale powtarzające się zakleszczenia wskazują na problem projektowy. Uchwyć graf deadlocka i przeanalizować instrukcje oraz typy blokad. Typowe poprawki obejmują następujące zmiany:

  • Zmień kolejność operacji tak, aby konkurujące transakcje uzyskiwały blokady w tej samej kolejności.
  • Ogranicz zakres transakcji.
  • Dodaj odpowiednie indeksy, aby skrócić czas blokady.

Pełne omówienie analizy zakleszczeń znajduje się w przewodniku Zakleszczenia. Jeśli korzystasz z Azure SQL Database, zobacz Analizowanie i zapobieganie zakleszczeniom.

Problemy z obciążeniem zbiorczym

Naruszenia ograniczeń podczas kopiowania zbiorczego

Objawy:

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

Przyczyna:

Dane w twojej partii naruszają ograniczenia tabelowe, takie jak klucz główny, unikalny, kontrolny lub klucz obcy.

Rozwiązanie:

Zweryfikuj dane przed ich załadowaniem. Dla dużych zbiorów danych, załaduj dane do tabeli przejściowej, a następnie scal je z tabelą docelową:

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

W przypadku wzorców operacji upsert z użyciem tabel przejściowych zobacz artykuł Wzorce ładowania i przemieszczania danych.

Błędy odwzorowania kolumn

Objawy:

RuntimeError: Bulk copy failure - column count mismatch

Przyczyna:

Liczba kolumn w danych nie odpowiada liczbie kolumn w docelowej tabeli lub kolumny są w niewłaściwej kolejności.

Rozwiązanie:

Upewnij się, że Twoje dane odpowiadają schematowi tabeli w kolejności i liczbie:

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)

Niezgodności typów podczas kopiowania masowego

Objawy:

Dane się ładują, ale wartości są obcięte, zaokrąglone lub nieprawidłowe.

Przyczyna:

Wartości w języku Python nie odwzorowują się jednoznacznie na docelowe typy kolumn. Typowe przykłady to float wartości ładowane do kolumn dziesiętnych , które mogą tracić precyzję, oraz powiększone ciągi tekstów ładowane do kolumn o stałej długości.

Rozwiązanie:

Używaj typów w Python, które pasują do twojego schematu:

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)

Awarie wiązania typu NumPy

Objawy:

Parametry po cichu nie działają lub powodują błędy typu danych, gdy używasz typów całkowitych lub zmiennoprzecinkowych NumPy.

Przyczyna:

Typy NumPy, takie jak numpy.int64 i numpy.int32, nie przechodzą isinstance(x, int) w NumPy 2.x. Wnioskowanie typu kierowcy ich nie rozpoznaje, co powoduje nieoczekiwane zachowanie.

Rozwiązanie:

Przekonwertuj wartości NumPy na natywne typy Python przed ich powiązaniem:

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

Dla większych zbiorów danych używaj ścieżek integracji Arrow lub Pandas . Te ścieżki obsługują konwersję typów wewnętrznie.

Kopiowanie zbiorcze z użyciem tabel tymczasowych

Objawy:

cursor.bulkcopy("#TempTable", data) powoduje zgłoszenie RuntimeError: Invalid object name '#TempTable'.

Przyczyna:

bulkcopy() Nie mogę rozwiązać tabel temp sesji (#tablename) z powodu ograniczeń wyszukiwania metadanych. Globalne tabele tymczasowe (##tablename) i tabele stałe działają.

Rozwiązanie:

Użyj globalnej tabeli tymczasowej lub zwykłej tabeli etapowej:

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

Dla małych zbiorów danych, gdzie preferujesz tabelę temp sesji, użyj executemany():

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

Treści powiązane

  • Last updated on