Roep stored procedures aan met mssql-python

De mssql-python-driver implementeert de callproc() methode uit de DB-API 2.0-specificatie niet. Het aanroepen van callproc() veroorzaakt NotSupportedError. Gebruik in plaats daarvan de ODBC-escape-sequentie {CALL ...} met standaard query-uitvoeringsmethoden.

Basis uitvoering van opgeslagen procedures

Zonder parameters

Voer een opgeslagen procedure uit door gebruik te maken van de {CALL} escape-sequentie. Dit voorbeeld roept de sp_databases systeemstored procedure aan, die geen parameters neemt en één rij per database teruggeeft:

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)

Met invoerparameters

Geef parameters door met behulp van positionele of benoemde tijdelijke aanduidingen:

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

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

Uitvoerparameters

Verklaar en haal outputparameters op

SQL Server stored procedures kunnen waarden teruggeven via outputparameters. Gebruik Transact-SQL (T-SQL) variabelen om uitvoerwaarden vast te leggen en haal ze vervolgens op met een SELECT instructie:

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

Meervoudige uitvoerparameters

Vang meerdere uitvoerwaarden vast door aparte variabelen te declareren. Hetzelfde patroon werkt met elke opgeslagen procedure die parameters heeft OUTPUT :

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

Retourwaarden

Retourwaarde van de opgeslagen procedure vastleggen

Voer een opgeslagen procedure uit die resultaten teruggeeft:

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

Retourwaarde met uitvoerparameters

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

Resultatensets

Eén resultatenset

Voer een opgeslagen procedure uit die één enkele resultaatset teruggeeft:

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

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

Meerdere resultaatsets

Voor elk resultaat waar je naartoe gaat, nextset() ververst rowcount. Een data-wijzigingsresultaat rapporteert zijn eigen aantal getroffen rijen en heeft description is None; roep geen fetch-methoden aan voor dat resultaat. Een resultaat dat rij produceert begint met rowcount == -1 vóór het ophalen. SET NOCOUNT ON onderdrukt resultaten van beïnvloede rijen. Voor een batch die deze resultaattypen combineert, zie Volg elk resultaat in een batch.

Sommige opgeslagen procedures retourneren meerdere resultatensets. Gebruik nextset() dit om ertussen te navigeren:

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

Controleer of er meer resultaatsets zijn

Itereer door alle resultaatsets die door een opgeslagen procedure worden teruggegeven met behulp van 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

Transacties met opgeslagen procedures

Expliciete transactiecontrole

Plaats meerdere aanroepen van opgeslagen procedures in een transactie om atomiciteit te garanderen:

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

Laat stored procedure transactie beheren

Als de opgeslagen procedure zijn eigen transacties afhandelt:

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

Foutafhandeling

Fouten in opgeslagen procedures vangen

Uitzonderingen afhandelen die worden gegenereerd door opgeslagen procedures of Transact-SQL-instructies:

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

Leg PRINT-statements en informatieve berichten vast

SQL Server-instructies PRINT en RAISERROR met een ernstniveau lager dan 11 worden na het uitvoeren vastgelegd in cursor.messages. Elke inzending is een (message_type, message_text) tuple. Wanneer een PRINT vóór een resultaatset wordt uitgevoerd, vormt het een eigen resultaatset zonder rijen, dus lees dan eerst cursor.messages en roep vervolgens nextset() aan om de rijen te benaderen:

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

Wanneer een opgeslagen procedure PRINT berichten verzendt over meerdere resultaatsets, lees dan cursor.messages na execute() en opnieuw na elke nextset()-aanroep, zodat berichten uit elke resultaatset worden opgevangen:

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

Voor de volledige cursor.messages API, zie Cursorbeheer.

Beste praktijken

Gebruik benoemde parameters

Benoemde parameters zijn duidelijker en behouden de onafhankelijkheid van de orde:

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

Verwerk nullbare uitvoerparameters

Controleer op NULL-waarden bij het ophalen van uitvoerparameters uit opgeslagen procedures:

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

Gebruik SET NOCOUNT ON in opgeslagen procedures

Voor betere prestaties en een nettere verwerking van resultaten, zorg ervoor dat uw opgeslagen procedures het volgende omvatten:

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

Voorbeeld: Volledige workflow

Hier is een praktisch voorbeeld dat een opgeslagen procedure aanroept, uitvoerwaarden ophaalt en fouten behandelt:

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)

Genereer sleutels door gebruik te maken van OUTPUT INSERTED

Om een identiteitswaarde op te halen uit een INSERT (met of zonder opgeslagen procedure), gebruik OUTPUT INSERTED in plaats van SCOPE_IDENTITY(). Deze benadering geeft de waarde terug in dezelfde resultaatset:

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

Dit patroon werkt voor elke tabel met een identiteitskolom en vereist geen opgeslagen procedure.

Niet-ondersteunde functies

callproc()

De mssql-python chauffeur verhoogt NotSupportedError als je belt cursor.callproc(). Gebruik cursor.execute("{CALL ...}") of cursor.execute("EXECUTE ...") in plaats daarvan, zoals in dit artikel wordt getoond.

Parameters met tabelwaarde (TVP's)

De mssql-python driver ondersteunt geen tabelwaardige parameters. Voor de volledige feature-ondersteuningsmatrix, zie Support lifecycle. Als je een set rijen moet doorgeven aan een opgeslagen procedure, gebruik dan deze alternatieven:

  • Plaats het eerst in een tijdelijke tabel en laat de opgeslagen procedure daar vervolgens uit lezen.
  • Gebruik bulkcopy() om data in een stagingtabel te laden.
  • Geef een JSON-string door en parseer deze met OPENJSON binnen de procedure.

Verwante onderwerpen

  • Laatst bijgewerkt om