Memecahkan masalah kueri, data, dan operasi dengan mssql-python

Gunakan artikel ini untuk mendiagnosis eksekusi kueri, jenis data, kinerja, transaksi, dan masalah salinan massal dengan mssql-python driver.

Masalah eksekusi kueri

Tabel atau objek tidak ditemukan

Gejala:

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

Kemungkinan penyebab dan solusi:

  • Konteks basis data yang salah

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

  • Skema tidak ditentukan

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

  • Tabel tidak ada

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

Kesalahan sintaks

Gejala:

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

Solusi:

  1. Uji pernyataan SQL di SQL Server Management Studio (SSMS) untuk memverifikasi sintaksnya.

  2. Gunakan query yang diparameterisasi sebagai pengganti interpolasi string:

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

Kesalahan parameter

Gejala:

ProgrammingError: [07001] Wrong number of parameters

Solusi:

  1. Hitung jumlah penanda tempat dan parameter. Jumlahnya harus sama.

  2. Pilih gaya parameter yang benar:

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

Masalah jenis data

Kesalahan konversi tanggal dan waktu

Gejala:

DataError: [22007] Invalid datetime format

Solution:

Gunakan objek Python datetime sebagai pengganti string.

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

Masalah presisi desimal

Gejala:

Angka tampak terpotong atau dibulatkan dengan tidak benar.

Solution:

Gunakan decimal.Decimal untuk nilai numerik yang tepat:

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

Masalah pengkodean Unicode

Gejala:

Karakter khusus muncul kacau atau menyebabkan kesalahan.

Solusi:

  1. Gunakan kolom nvarchar untuk data Unicode di database Anda.

  2. Teruskan string secara langsung. Driver menangani pengkodean:

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

Masalah performa

Eksekusi kueri yang lambat

Kemungkinan penyebab dan solusi:

  • Indeks yang hilang: Periksa rencana eksekusi kueri di SSMS.

  • Set hasil besar: Gunakan fetchmany() sebagai pengganti fetchall():

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

  • Pooling koneksi dinonaktifkan: Aktifkan pooling:

    import mssql_python

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

Masalah memori dengan hasil yang besar

Gejala:

Proses Python kehabisan memori.

Solusi:

  1. Alirkan hasil alih-alih memuat semua baris ke dalam memori.

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

  2. Gunakan paginasi sisi server.

    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

Masalah transaksi

Ruang lingkup tabel temporer dengan autocommit

Tabel temp sesi (#tablename) yang Anda buat di dalam transaksi akan hilang saat transaksi dikembalikan. Perilaku ini biasanya menyebabkan kebingungan saat autocommit dimatikan, yang merupakan default:

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 segera setelah Anda membuat tabel sementara, atau gunakan mode autocommit:

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

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

Pernyataan DDL yang memerlukan mode penerapan otomatis, seperti CREATE DATABASE, gagal di dalam transaksi terbuka. Atur autocommit sebelum Anda menjalankannya:

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

Transaksi tidak dilakukan

Gejala:

Perubahan data tidak akan bertahan setelah Anda menutup koneksi.

Solution:

Dengan autocommit=False, yang merupakan default, panggil commit():

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

Sebagai alternatif, gunakan mode autocommit:

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

Kesalahan deadlock

Gejala:

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

Solution:

Logika Retry menangani kegagalan langsung, tetapi deadlock yang berulang menunjukkan adanya masalah desain. Tangkap grafik deadlock, dan analisis pernyataan serta tipe kunci. Perbaikan umum meliputi perubahan berikut:

  • Mengatur ulang operasi sehingga transaksi yang bersaing memperoleh kunci dalam urutan yang sama.
  • Kurangi ruang lingkup transaksi.
  • Tambahkan indeks yang sesuai untuk mengurangi durasi kunci.

Untuk panduan lengkap analisis kebuntuan, lihat Panduan kebuntuan. Jika Anda menggunakan Azure SQL Database, lihat Analisis dan cegah kebuntuan.

Masalah pemuatan massal

Pelanggaran batasan selama salinan massal

Gejala:

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

Penyebab:

Data dalam batch Anda melanggar batasan tabel seperti kunci primer, unik, cek, atau kendala kunci asing.

Solution:

Validasi data sebelum Anda memuatnya. Untuk dataset besar, muat data ke dalam tabel staging, lalu gabungkan ke target:

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

Untuk pola upsert dengan tabel staging, lihat Pola pemuatan dan pemindahan data.

Kesalahan pemetaan kolom

Gejala:

RuntimeError: Bulk copy failure - column count mismatch

Penyebab:

Jumlah kolom dalam data Anda tidak sesuai dengan jumlah kolom tabel target, atau kolom-kolom tersebut berada dalam urutan yang salah.

Solution:

Pastikan data Anda sesuai dengan skema tabel secara berurutan dan jumlahnya:

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)

Ketidakcocokan tipe data selama bulk copy

Gejala:

Data dimuat, tetapi nilainya terpotong, dibulatkan, atau tidak benar.

Penyebab:

Nilai Python tidak cocok secara langsung dengan tipe kolom target. Contoh umum termasuk float nilai yang dimuat ke kolom desimal , yang dapat kehilangan presisi, dan string berukuran besar yang dimuat ke kolom dengan panjang tetap.

Solution:

Gunakan tipe Python yang sesuai dengan skema Anda:

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)

Kegagalan pengikatan tipe NumPy

Gejala:

Parameter secara diam-diam gagal atau menimbulkan kesalahan tipe data saat Anda menggunakan tipe integer atau float NumPy.

Penyebab:

Tipe NumPy seperti numpy.int64 dan numpy.int32 tidak memenuhi isinstance(x, int) pada NumPy 2.x. Inferensi tipe pada driver tidak dapat mengenalinya, yang menyebabkan perilaku tak terduga.

Solution:

Konversi nilai NumPy ke tipe Python asli sebelum Anda mengikatnya:

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

Untuk dataset yang lebih besar, gunakan jalur integrasi Arrow atau pandas . Jalur-jalur ini menangani konversi tipe secara internal.

Salinan massal dengan tabel sementara

Gejala:

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

Penyebab:

bulkcopy() tidak dapat menyelesaikan tabel temp sesi (#tablename) karena keterbatasan pencarian metadata. Tabel suhu global (##tablename) dan tabel permanen berfungsi.

Solution:

Gunakan tabel temp global atau tabel pementasan reguler:

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

Untuk dataset kecil di mana Anda lebih suka tabel temp sesi, gunakan executemany():

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

Konten terkait

  • Last updated on