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 pymssqlmodule 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 idcolumn:
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 existsIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINESWHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'getCustomerName' AND ROUTINE_TYPE = N'PROCEDURE')DROP PROCEDURE dbo.getCustomerNameGO
-- Create the stored procedure in the specified schemaCREATE PROCEDURE dbo.getCustomerName @customerID /*parameter name*/ int /*datatype_for_param1*/ASBEGIN -- body of the stored procedure SELECT [id] ,[customer_name] FROM [test].[dbo].[customer] where id=@customerID;ENDGO
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 pymssqlimport pandas as pd
# Establish the connectionconn = pymssql.connect(server='.', user='zeppelin', password='zeppelin', database='test')cursor = conn.cursor(as_dict=True)
# Call procedurecustomerId = 101cursor.callproc('dbo.getCustomerName', (customerId,))
# Convert the result to DataFramerows = []for row in cursor: rows.append(row)
df = pd.DataFrame(rows)print(df)
# Close cursor and connectioncursor.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).