How to execute SQL Server stored procedure from Python?

In an earlier article we have seen how to execute a simple select query from a python program on a SQL Server Database. Now we will see how to execute a Stored Procedure from python. For the most part, executing a stored procedure is similar to a select statement. You just need to add parameters and its values during the execution. Let’s see with an illustration.

Prerequisites

Please follow my earlier article about connecting SQL Server from python for:

1. Tools & technologies used.
2. Installing Microsoft ODBC Driver for SQL Server On macOS.
3. Installing Pyodbc Module.
4. Getting the database connection details.
5. Connecting to SQL Server database.

Create A Stored Procedure

To start with, let us create a simple stored procedure. I am using SQL Server’s sample database WideWorldImporters for this illustration.This stored procedure fetches the list of customers from the Sales.Customers table. There are two parameters. One parameter is for getting the search term to filter the customer names. The other parameter is the number of records to fetch. This stored procedure returns two columns, CustomerID and CustomerName.

CREATE PROCEDURE [Sales].[Mtb_GetCustomers] 
@SearchText nvarchar(100), 
@MaximumRowsToReturn int
AS
BEGIN
    SELECT TOP(@MaximumRowsToReturn) 
           c.CustomerID, 
           c.CustomerName 
    FROM Sales.Customers AS c 
    WHERE c.CustomerName LIKE N'%' + @SearchText + N'%'
    ORDER BY c.CustomerName; 
END; 
GO 

Python Program

Here is the sample python code to execute the stored procedure and fetch a few rows from a table and print the data. In this the variable storedProc has the stored procedure execution script. In the script you have to replace the parameter value with question mark (?). In the params variable holds the parameter values in an array.

import pyodbc as po

# Connection variables
server = 'localhost'
database = 'WideWorldImporters'
username = 'sa'
password = 'MyPassword'

try:
    # Connection string
    cnxn = po.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
            server+';DATABASE='+database+';UID='+username+';PWD=' + password)
    cursor = cnxn.cursor()

    # Prepare the stored procedure execution script and parameter values
    storedProc = "Exec [Sales].[Mtb_GetCustomers] @SearchText = ?, @MaximumRowsToReturn = ?"
    params = ("And", 10)

    # Execute Stored Procedure With Parameters
    cursor.execute( storedProc, params )

    # Iterate the cursor
    row = cursor.fetchone()
    while row:
        # Print the row
        print(str(row[0]) + " : " + str(row[1] or '') )
        row = cursor.fetchone()

    # Close the cursor and delete it
    cursor.close()
    del cursor

    # Close the database connection
    cnxn.close()

except Exception as e:
    print("Error: %s" % e)

Reference

• More about pyodbc cursor at GitHub.