How to Execute Stored Procedure in Python

Stored procedure is a sequence of SQL queries to perform certain tasks, that you can recall as and when you need it. You can use stored procedures to avoid code repetition and easy reference. It supports all CRUD (Create, Read, Update, Delete) queries. In this article, we will learn how to execute stored procedure in Python.

How to Execute Stored Procedure in Python

Here are the steps to execute stored procedure in Python.

1. Create Stored Procedure

Stored procedure has its own syntax, even though it incorporates SQL statements. For our example, we will use a simple stored procedure as shown below. Log into PostgreSQL database and run the following query to create a stored procedure with name get_book_sales in it.

CREATE OR REPLACE FUNCTION get_book_sales(sale_amount integer)
RETURNS TABLE(book_name VARCHAR, book_id INTEGER, store VARCHAR) AS
$
BEGIN
RETURN QUERY
SELECT books.id, books.book_name
FROM books where books.sales > sale_amount
END
$
LANGUAGE plpgsql

2. Create Python Function

Next, we need to create a python function to execute the above stored procedure. We will name it get_benchmark(sales) that takes sales as the benchmark.

First, we will import psycopg module that provides methods and classes to connect to PostgreSQL database and run queries. If you don’t have it installed, you can do so with the following command.

$ pip install psycopg2-binary

Once you have installed it, create a new python file get_benchmark.py.

$ vi get_benchmark.py

Add the following lines to to import psycopg and define the function get_benchmark().

import psycopg2

def get_benchmark(sale_amount):
    connector = None
    try:
        conn_string = "host='host_name' dbname='database_name'\
                        user='user_name' password='your_password'"
        connector = psycopg2.connect(conn_string)
        engine = connector.cursor()
  
        # call stored procedure
        engine.callproc('get_book_sales', [sale_amount, ])
  
        print("fechting Book list that has crosssed sales benchmark")
        result = engine.fetchall()
        for row in result:
            print("Book Id = ", row[0], )
            print("Book Name = ", row[1])
  
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error while connecting to PostgreSQL", error)
  
    finally:
        
        # closing database connection.
        if connector:
            engine.close()
            connector.close()
            print("PostgreSQL connection is closed")
  
  
get_benchmark(500)

In the function definition, we first define our connection string. Replace host_name, user_name, database_name, and your_password in it, with your database connection details. Then we use this connection string in connect() to establish database connection. This returns a connection object. We use cursor() function on this connection object to get a database cursor. You need a database cursor in order to be able to run queries in your database.

Once we have the database cursor, we call callproc() function to call the stored procedure. It takes two input values – name of stored procedure and input parameters to the stored procedure. Here is its syntax.

cursor.callproc(procname[, parameters])

For example, we have called the stored procedure get_book_sales, with the following command. We use the get_benchmark() function’s input parameter sale_amount as input parameter of stored procedure.

engine.callproc('get_book_sales', [sale_amount, ])

The key point to remember is to use the proper stored procedure’s name as first argument, within quotes, and also provide the input parameters, if any, using python variables, or literal values.

The result of this stored procedure is stored in database cursor object. We then call fetchall() function to fetch all records of stored procedure’s result. You can use it as an iterable to iterate through the rows of result using for loop. In each iteration, we display the values of each column in that row. Each row is an array of values, with the first column’s value available as first element of the array, that is, row[0]. Similarly, you can access other columns’ values.

We use try…except block to catch any errors and display appropriate error message. We also close the database connection in finally block, in case of errors.

Finally, we call the python function with input parameter=500. This in turn connects your PostgreSQL database and passes the sale_amount value to stored procedure. It also retrieves the result and displays it for your reference.

In this article, we have learnt how to execute stored procedure in python. Please note, before you run a python script to call stored procedure, you need to have created it by logging into PostgreSQL database, or programmatically via python or some other language.

How to Execute Stored Procedure in Python