Panggil prosedur tersimpan dengan mssql-python

Driver mssql-python tidak mengimplementasikan callproc() metode dari spesifikasi DB-API 2.0. Pemanggilan callproc() memunculkan NotSupportedError. Sebagai gantinya, gunakan urutan escape ODBC {CALL ...} dengan metode eksekusi kueri standar.

Eksekusi prosedur tersimpan dasar

Tanpa parameter

Jalankan prosedur tersimpan menggunakan urutan escape {CALL}. Contoh ini memanggil prosedur tersimpan sp_databases sistem, yang tidak mengambil parameter dan mengembalikan satu baris per database:

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

cursor.execute("{CALL sp_databases}")

for row in cursor:
    print(row.DATABASE_NAME, row.DATABASE_SIZE)

Dengan parameter input

Teruskan parameter menggunakan penanda posisional atau bernama:

# Single parameter
cursor.execute(
    "{CALL dbo.uspGetManagerEmployees(?)}", (16,)
)

for row in cursor:
    print(row.FirstName, row.LastName)

Parameter output

Mendeklarasikan dan mengambil parameter keluaran

Prosedur tersimpan SQL Server dapat mengembalikan nilai melalui parameter output. Gunakan variabel Transact-SQL (T-SQL) untuk menangkap nilai output, lalu ambil nilai tersebut dengan pernyataan SELECT:

cursor.execute("""
    DECLARE @total_out MONEY;
    SELECT @total_out = SUM(TotalDue)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = %(customer_id)s;
    SELECT @total_out AS TotalAmount;
""", {"customer_id": 29825})

row = cursor.fetchone()
total = row.TotalAmount
print(f"Customer total: ${total}")

Beberapa parameter keluaran

Tangkap beberapa nilai output dengan mendeklarasikan variabel terpisah. Pola yang sama berfungsi dengan prosedur tersimpan apa pun yang memiliki OUTPUT parameter:

cursor.execute("""
    DECLARE @total_orders INT, @total_spent MONEY;
    SELECT @total_orders = COUNT(*), @total_spent = SUM(TotalDue)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = %(cust_id)s;
    SELECT @total_orders AS OrderCount, @total_spent AS TotalSpent;
""", {"cust_id": 29825})

stats = cursor.fetchone()
print(f"Orders: {stats.OrderCount}, Total spent: ${stats.TotalSpent}")

Mengembalikan nilai

Menangkap nilai pengembalian prosedur tersimpan

Jalankan prosedur tersimpan yang mengembalikan hasil:

cursor.execute("EXECUTE dbo.uspGetEmployeeManagers @BusinessEntityID = %(emp_id)s", {"emp_id": 5})

rows = cursor.fetchall()
if rows:
    print(f"Found {len(rows)} managers in chain")
    for row in rows:
        print(f"  Manager: {row.FirstName} {row.LastName}")
else:
    print("No managers found")

Nilai pengembalian dengan parameter keluaran

cursor.execute("""
    DECLARE @return_value INT, @message NVARCHAR(500);
    SELECT @return_value = CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END,
           @message = CASE WHEN COUNT(*) > 0 THEN N'Customer found' ELSE N'Customer not found' END
    FROM Sales.Customer WHERE CustomerID = %(cust_id)s;
    SELECT @return_value AS ReturnCode, @message AS Message;
""", {"cust_id": 29825})

result = cursor.fetchone()
print(f"Return code: {result.ReturnCode}, Message: {result.Message}")

Kumpulan hasil

Set hasil tunggal

Jalankan prosedur tersimpan yang mengembalikan satu kumpulan hasil:

cursor.execute(
    "{CALL dbo.uspGetBillOfMaterials(?, ?)}",
    (800, "2026-01-01")
)

customers = cursor.fetchall()
for row in customers:
    print(f"{row.ProductAssemblyID}: {row.ComponentDesc}")

Beberapa kumpulan hasil

Untuk setiap hasil yang Anda beralih ke, nextset() akan memperbarui rowcount. Hasil modifikasi data melaporkan jumlah baris terpengaruh sendiri dan memiliki description is None; jangan panggil fetch methods untuk hasil tersebut. Hasil yang menghasilkan baris dimulai dengan rowcount == -1 sebelum mengambil data. SET NOCOUNT ON menyembunyikan hasil baris yang terpengaruh. Untuk batch yang mencampur tipe hasil ini, lihat Lacak setiap hasil dalam batch.

Beberapa prosedur tersimpan mengembalikan beberapa set hasil. Gunakan nextset() untuk menavigasi di antara keduanya:

cursor.execute("""
    SELECT TOP 1 SalesOrderID, OrderDate, TotalDue
    FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
    SELECT TOP 3 Name, ListPrice
    FROM Production.Product WHERE ListPrice > 0
    ORDER BY ListPrice DESC;
""")

# First result set: order header
order = cursor.fetchone()
print(f"Order: {order.SalesOrderID}, Date: {order.OrderDate}")

cursor.nextset()

# Second result set: products
print("Products:")
for item in cursor:
    print(f"  {item.Name}: ${item.ListPrice}")

Periksa kumpulan hasil lainnya

Telusuri semua kumpulan hasil yang dikembalikan oleh prosedur tersimpan menggunakan nextset():

cursor.execute("SELECT TOP 3 ProductID, Name FROM Production.Product; SELECT TOP 3 FirstName, LastName FROM Person.Person")

result_set_num = 1
while True:
    print(f"--- Result Set {result_set_num} ---")
    for row in cursor:
        print(row)
    
    if not cursor.nextset():
        break
    result_set_num += 1

Transaksi dengan prosedur tersimpan

Kontrol transaksi eksplisit

Bungkus beberapa pemanggilan prosedur tersimpan dalam satu transaksi untuk memastikan atomisitas:

conn.autocommit = False

try:
    cursor.execute("{CALL dbo.DebitAccount(?, ?)}", (1001, 100.00))
    
    cursor.execute("{CALL dbo.CreditAccount(?, ?)}", (1002, 100.00))
    
    conn.commit()
    print("Transfer completed")
except mssql_python.DatabaseError as e:
    conn.rollback()
    print(f"Transfer failed: {e}")

Biarkan prosedur tersimpan mengelola transaksi

Jika prosedur tersimpan menangani transaksinya sendiri:

conn.autocommit = True  # Let SP manage transactions

cursor.execute("""
    DECLARE @result INT;
    EXECUTE @result = dbo.TransferFunds 
        @FromAccount = %(from_acc)s,
        @ToAccount = %(to_acc)s,
        @Amount = %(amount)s;
    SELECT @result AS TransferResult;
""", {"from_acc": 1001, "to_acc": 1002, "amount": 100.00})

result = cursor.fetchone()
if result.TransferResult == 0:
    print("Transfer successful")

Penanganan kesalahan

Menangkap kesalahan prosedur tersimpan

Menangani pengecualian yang diajukan oleh prosedur tersimpan atau pernyataan Transact-SQL:

try:
    cursor.execute("{CALL dbo.DangerousProcedure}")
except mssql_python.ProgrammingError as e:
    # Handle SQL errors raised by RAISERROR or THROW
    print(f"Stored procedure error: {e}")
except mssql_python.DatabaseError as e:
    # Handle other database errors
    print(f"Database error: {e}")

Rekam pernyataan PRINT dan pesan informasional

Pernyataan SQL Server PRINT dan RAISERROR dengan tingkat keparahan di bawah 11 dicatat dalam cursor.messages setelah dieksekusi. Setiap entri merupakan tupel (message_type, message_text). Ketika PRINT dijalankan sebelum set hasil, perintah tersebut akan menempati set hasil tanpa barisnya sendiri, jadi baca cursor.messages terlebih dahulu, lalu panggil nextset() untuk mengakses baris-barisnya:

cursor.execute("PRINT 'Operation complete'; SELECT 1 AS Status")

for msg_type, msg_text in cursor.messages:
    print(f"Server message: {msg_text}")

cursor.nextset()
row = cursor.fetchone()
print(f"Status: {row.Status}")

Saat prosedur tersimpan memancarkan PRINT pesan di beberapa kumpulan hasil, baca cursor.messages sesudah execute() dan lagi setelah setiap nextset() panggilan sehingga pesan dari setiap kumpulan hasil diambil:

cursor.execute("""
    PRINT 'Starting first result set';
    SELECT TOP 3 ProductID, Name FROM Production.Product;
    PRINT 'Starting second result set';
    SELECT TOP 3 FirstName, LastName FROM Person.Person;
""")

all_messages = []
while True:
    all_messages.extend(cursor.messages)
    if cursor.description:
        for row in cursor:
            print(row)
    if not cursor.nextset():
        break

for _, text in all_messages:
    print(f"Server: {text}")

Untuk API lengkap cursor.messages , lihat Manajemen kursor.

Praktik terbaik

Gunakan parameter bernama

Parameter bernama lebih jelas dan tidak bergantung pada urutan:

# Recommended: {CALL} with positional parameters
cursor.execute(
    "{CALL dbo.uspGetBillOfMaterials(?, ?)}",
    (800, "2026-01-01")
)

# Also valid: EXECUTE with named T-SQL parameters
cursor.execute("""
    EXECUTE dbo.uspGetBillOfMaterials
        @StartProductID = ?,
        @CheckDate = ?
""", (800, "2026-01-01"))

Tangani parameter keluaran yang dapat bernilai null

Periksa nilai NULL saat mengambil parameter output dari prosedur tersimpan:

cursor.execute("""
    DECLARE @optional_value NVARCHAR(100);
    SELECT @optional_value = Color FROM Production.Product WHERE ProductID = %(id)s;
    SELECT @optional_value AS OutputValue;
""", {"id": 1})

result = cursor.fetchone()
if result.OutputValue is not None:
    print(f"Value: {result.OutputValue}")
else:
    print("No value returned")

Gunakan SET NOCOUNT ON pada prosedur tersimpan

Untuk kinerja yang lebih baik dan penanganan hasil yang lebih bersih, pastikan prosedur yang Anda simpan meliputi:

CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
    SET NOCOUNT ON;  -- Prevents "n rows affected" messages
    -- procedure logic
END

Contoh: Alur kerja lengkap

Berikut adalah contoh praktis yang memanggil prosedur tersimpan, mengambil nilai output, dan menangani kesalahan:

import mssql_python

def get_employee_report(manager_id: int) -> dict:
    """Look up a manager's employees and compute average vacation hours."""
    conn = mssql_python.connect(connection_string)
    conn.autocommit = False
    cursor = conn.cursor()

    try:
        # Get manager info
        cursor.execute("""
            SELECT BusinessEntityID, JobTitle
            FROM HumanResources.Employee
            WHERE BusinessEntityID = %(mgr)s
        """, {"mgr": manager_id})
        mgr = cursor.fetchone()
        print(f"Manager {mgr.BusinessEntityID}: {mgr.JobTitle}")

        # Get direct reports via stored procedure
        cursor.execute("{CALL dbo.uspGetManagerEmployees(?)}", (manager_id,))
        employees = cursor.fetchall()
        print(f"Found {len(employees)} employee(s)")

        # Compute average vacation hours
        cursor.execute("""
            DECLARE @avg_hours INT;
            SELECT @avg_hours = AVG(VacationHours)
            FROM HumanResources.Employee;
            SELECT @avg_hours AS AvgVacation;
        """)
        avg = cursor.fetchone().AvgVacation
        print(f"Avg vacation hours: {avg}")

        conn.commit()
        return {"manager": mgr.JobTitle, "reports": len(employees), "avg_vacation": avg}

    except mssql_python.DatabaseError as e:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()

# Usage
result = get_employee_report(manager_id=16)

Dapatkan kunci yang dihasilkan dengan menggunakan OUTPUT INSERTED

Untuk mengambil nilai identitas dari ( INSERT dengan atau tanpa prosedur tersimpan), gunakan OUTPUT INSERTED alih-alih SCOPE_IDENTITY(). Pendekatan ini mengembalikan nilai dalam kumpulan hasil yang sama:

cursor.execute("""
    INSERT INTO Production.ProductCategory (Name)
    OUTPUT INSERTED.ProductCategoryID
    VALUES (%(name)s)
""", {"name": "Custom Parts"})

new_id = cursor.fetchval()
print(f"New category ID: {new_id}")

Pola ini berfungsi untuk tabel apa pun dengan kolom identitas dan tidak memerlukan prosedur tersimpan.

Fitur yang tidak didukung

callproc()

Driver mssql-python memunculkan NotSupportedError jika Anda memanggil cursor.callproc(). Gunakan cursor.execute("{CALL ...}") atau cursor.execute("EXECUTE ...") sebagai gantinya, seperti yang ditunjukkan di seluruh artikel ini.

Parameter bertipe tabel (TVP)

Driver mssql-python tidak mendukung parameter bernilai tabel. Untuk matriks dukungan fitur lengkap, lihat Siklus hidup Dukungan. Jika Anda perlu meneruskan sekumpulan baris ke prosedur tersimpan, gunakan alternatif berikut:

  • Masukkan ke dalam tabel sementara terlebih dahulu, lalu minta prosedur yang disimpan dibaca darinya.
  • Gunakan bulkcopy() untuk memuat data ke dalam tabel penahapan.
  • Berikan string JSON dan parse dengan OPENJSON di dalam prosedur.

Konten terkait

  • Terakhir diperbarui pada