{"id":4157,"date":"2024-07-24T13:07:24","date_gmt":"2024-07-24T06:07:24","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4157"},"modified":"2024-07-24T19:50:52","modified_gmt":"2024-07-24T12:50:52","slug":"python-sql-server-call-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-call-stored-procedures\/","title":{"rendered":"Python SQL Server: Call Stored Procedures"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a SQL server’s stored procedure from a Python program.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0performing database transactions in Python program tutorial<\/a> left off.<\/p>\n\n\n\n

Creating a stored procedure #<\/a><\/h2>\n\n\n\n

Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.<\/p>\n\n\n\n

Step 2. Create a new stored procedure<\/a> by executing the following statement:<\/p>\n\n\n

CREATE<\/span> PROCEDURE<\/span> GetBooksByPublishedDate\n    @StartDate DATE<\/span>,\n    @EndDate DATE<\/span>\nAS<\/span>\nBEGIN<\/span>\n    -- Check for valid date range<\/span>\n    IF<\/span> @StartDate > @EndDate\n    BEGIN<\/span>\n        PRINT 'Error: Start date must be less than or equal to end date.'<\/span>\n        RETURN<\/span>\n    END<\/span>\n\n    -- Retrieve books within the date range<\/span>\n    SELECT<\/span> BookID, Title, Publisher, ISBN, PublishedDate\n    FROM<\/span> Books\n    WHERE<\/span> PublishedDate BETWEEN<\/span> @StartDate AND<\/span> @EndDate\n    ORDER<\/span> BY<\/span> PublishedDate;\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The GetBooksByPublishedDate<\/code> stored procedure returns a list of books between start and end dates.<\/p>\n\n\n\n
Calling the stored procedures #<\/a><\/h2>\n\n\n\n
Step 1. Create a new module procedure.py<\/code> with the following code:<\/p>\n\n\n
from<\/span> connect import<\/span> create_connection\n\ndef<\/span> get_books_by_published_date<\/span>(start_date: str, end_date: str)<\/span> -> list[dict] | None<\/span>:<\/span>\n    # Connect to the SQL Server<\/span>\n    conn = create_connection()\n    if<\/span> conn is<\/span> None<\/span>:\n        return<\/span> None<\/span>\n    \n    # Call the stored procedure<\/span>\n    with<\/span> (conn, conn.cursor(as_dict=True<\/span>) as<\/span> cursor):\n        cursor.callproc('GetBooksByPublishedDate'<\/span>, (start_date, end_date))\n        return<\/span> cursor.fetchall()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
from<\/span> connect import<\/span> create_connection<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define get_books_by_published_date()<\/code> function that returns a list of books with the published date within start_date<\/code> and end_date<\/code>:<\/p>\n\n\n
def<\/span> get_books_by_published_date<\/span>(start_date: str, end_date: str)<\/span> -> list[dict] | None<\/span>:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to SQL Server by calling the create_connection()<\/code> function and return None<\/code> if the connection fails:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> None<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, manage the Connection<\/code> and Cursor<\/code> objects using the with<\/code> statement:<\/p>\n\n\n
with<\/span> (conn, conn.cursor() as<\/span> cursor):<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, call the stored procedure by calling the callproc()<\/code> method of the Cursor<\/code> object:<\/p>\n\n\n
cursor.callproc('GetBooksByPublishedDate'<\/span>, (start_date, end_date))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that you need to pass a tuple that includes the stored procedure’s parameters to the second argument of the callproc()<\/code> method.<\/p>\n\n\n\n
Finally, return all the rows from the query result:<\/p>\n\n\n
return<\/span> cursor.fetchall()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.py<\/code> module to use the get_books_by_published_date()<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> procedure import<\/span> get_books_by_published_date\n\n\n# config logging to console<\/span>\nlogging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)\n\n\nbooks = get_books_by_published_date('2022-01-01'<\/span>, '2023-12-31'<\/span>)\nfor<\/span> book in<\/span> books:\n    print(f'{book[\"Title\"<\/span>]}<\/span> - Published on {book[\"PublishedDate\"<\/span>]}<\/span>'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the get_books_by_published_date<\/code> function from the procedure:<\/p>\n\n\n
from<\/span> procedure import<\/span> get_books_by_published_date<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the get_books_by_published_date<\/code> function to get the books published between Jan 01, 2022<\/code> and Dec 31, 2023<\/code>:<\/p>\n\n\n
books = get_books_by_published_date('2022-01-01'<\/span>, '2023-12-31'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display the book title and published date of returning books:<\/p>\n\n\n
for<\/span> book in<\/span> books:\n    print(f'{book[\"Title\"<\/span>]}<\/span> - Published on {book[\"PublishedDate\"<\/span>]}<\/span>'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the main.py<\/code> module:<\/p>\n\n\n
python main.py<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Mastering SQL: A Comprehensive Guide - Published on 2022<\/span>-01<\/span>-15<\/span>\nDatabase Systems: Theory and<\/span> Practice - Published on 2022<\/span>-11<\/span>-05<\/span>\nSQL Queries for<\/span> Mere Mortals - Published on 2023<\/span>-03<\/span>-21<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Call the execute()<\/code> method of a Cursor<\/code> object to call a SQL Server’s stored procedure.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
First, import the create_connection<\/code> function from the connect<\/a> module:<\/p>\n\n\n