Call SQL Server Stored Procedure in Python

In this article, I am going to show you how to call stored procedures in SQL Server database in Python application. The following diagram shows the typical packages that can be used:

I will use pymssql module in the following example.

SQL Server environment

I'm using a local SQL Server instance with a database named test. In this database, there is one table named [dbo].[customer] with two attributes id and customer_name. 

The following T-SQL queries the table and filters on id column:

SELECT [id]
      ,[customer_name]
  FROM [test].[dbo].[customer]
where id=101;

Returned results:

A procedure named dbo.getCustomerName is created with the following T-SQL:

-- Create a new stored procedure called 'getCustomerName' in schema 'dbo'
-- Drop the stored procedure if it already exists
IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
    AND SPECIFIC_NAME = N'getCustomerName'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE dbo.getCustomerName
GO

-- Create the stored procedure in the specified schema
CREATE PROCEDURE dbo.getCustomerName
    @customerID /*parameter name*/ int /*datatype_for_param1*/
AS
BEGIN
    -- body of the stored procedure
    SELECT [id]
      ,[customer_name]
    FROM [test].[dbo].[customer]
    where id=@customerID;
END
GO
grant EXEC on dbo.getCustomerName to zeppelin;

Run the following T-SQL to test the procedure:

EXECUTE dbo.getCustomerName 101;

It returns the same result as the previous SELECT statement. We will now use Python to call this procedure. 

The server can be authenticated using the following SQL login:

• username: zeppelin
• password: zeppelin

Install pymssql module

Install the required Python package using the following command:

pip install pymssql

The printed out logs look like the following screenshot:

Use pymssql module

Now let's use this module to connect to SQL Server and call the procedure.

import pymssql
import pandas as pd

# Establish the connection
conn = pymssql.connect(server='.',
                       user='zeppelin', password='zeppelin', database='test')
cursor = conn.cursor(as_dict=True)

# Call procedure
customerId = 101
cursor.callproc('dbo.getCustomerName', (customerId,))

# Convert the result to DataFrame
rows = []
for row in cursor:
    rows.append(row)

df = pd.DataFrame(rows)
print(df)

# Close cursor and connection
cursor.close()
conn.close()

The output:

      id  customer_name

0 101 Raymond

In the above script, Cursor.callproc function is used directly to call the procedure. The first parameter is the procedure name and the second is the parameters (in tuple format).

References

pymssql Documentation