Python SQL Server: Call Stored Procedures

Summary: in this tutorial, you will learn how to call a SQL server’s stored procedure from a Python program.

This tutorial begins where the performing database transactions in Python program tutorial left off.

Creating a stored procedure

Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Create a new stored procedure by executing the following statement:

CREATE PROCEDURE GetBooksByPublishedDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    -- Check for valid date range
    IF @StartDate > @EndDate
    BEGIN
        PRINT 'Error: Start date must be less than or equal to end date.'
        RETURN
    END

    -- Retrieve books within the date range
    SELECT BookID, Title, Publisher, ISBN, PublishedDate
    FROM Books
    WHERE PublishedDate BETWEEN @StartDate AND @EndDate
    ORDER BY PublishedDate;
ENDCode language: SQL (Structured Query Language) (sql)

The GetBooksByPublishedDate stored procedure returns a list of books between start and end dates.

Calling the stored procedures

Step 1. Create a new module procedure.py with the following code:

from connect import create_connection

def get_books_by_published_date(start_date: str, end_date: str) -> list[dict] | None:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return None
    
    # Call the stored procedure
    with (conn, conn.cursor(as_dict=True) as cursor):
        cursor.callproc('GetBooksByPublishedDate', (start_date, end_date))
        return cursor.fetchall()Code language: Python (python)

How it works.

First, import the create_connection function from the connect module:

from connect import create_connectionCode language: Python (python)

Second, define get_books_by_published_date() function that returns a list of books with the published date within start_date and end_date:

def get_books_by_published_date(start_date: str, end_date: str) -> list[dict] | None:Code language: Python (python)

Third, connect to SQL Server by calling the create_connection() function and return None if the connection fails:

conn = create_connection()
if conn is None:
    return NoneCode language: Python (python)

Fourth, manage the Connection and Cursor objects using the with statement:

with (conn, conn.cursor() as cursor):Code language: Python (python)

Sixth, call the stored procedure by calling the callproc() method of the Cursor object:

cursor.callproc('GetBooksByPublishedDate', (start_date, end_date))Code language: Python (python)

Notice that you need to pass a tuple that includes the stored procedure’s parameters to the second argument of the callproc() method.

Finally, return all the rows from the query result:

return cursor.fetchall()Code language: Python (python)

Step 2. Modify the main.py module to use the get_books_by_published_date() function:

import logging, sys
from procedure import get_books_by_published_date


# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)


books = get_books_by_published_date('2022-01-01', '2023-12-31')
for book in books:
    print(f'{book["Title"]} - Published on {book["PublishedDate"]}')Code language: Python (python)

How it works.

First, import the get_books_by_published_date function from the procedure:

from procedure import get_books_by_published_dateCode language: Python (python)

Second, call the get_books_by_published_date function to get the books published between Jan 01, 2022 and Dec 31, 2023:

books = get_books_by_published_date('2022-01-01', '2023-12-31')Code language: Python (python)

Third, display the book title and published date of returning books:

for book in books:
    print(f'{book["Title"]} - Published on {book["PublishedDate"]}')Code language: Python (python)

Step 3. Open your terminal and run the main.py module:

python main.pyCode language: Python (python)

Output:

Mastering SQL: A Comprehensive Guide - Published on 2022-01-15
Database Systems: Theory and Practice - Published on 2022-11-05
SQL Queries for Mere Mortals - Published on 2023-03-21Code language: Python (python)

Download the project source code

Summary

Call the execute() method of a Cursor object to call a SQL Server’s stored procedure.

Was this tutorial helpful?