How To Execute SQL Server Stored Procedure From Python?

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 : https://github.com/mkleehammer/pyodbc/wiki/Cursor

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rakesh Tripathi

Rakesh Tripathi

Consulting Engineer, Software Developer, Infra, Quora