Execute PostgreSQL Stored Procedure and Function in Python

A stored procedure is a sequence of structured procedural language queries stored in a relational database management system as a data dictionary, which can be shared and reused multiple times.  All CRUD operations, querying operations can be performed by calling the stored procedure. The use of stored procedures reduces the repetition of code that is used time and again. In this article let us discuss how to execute PostgreSQL stored procedure and function in python.

Creating a Stored Procedure

• The first step is to write a stored procedure, the syntax is similar to that of the conventional SQL statements.
• The below code snippet shows a stored procedure that extracts the book names and book id, whose sales has crossed a certain benchmark amount.
• This stored procedure is named get_book_sales(sale_amount), which takes sale_amount as a parameter where the benchmark sales amount has to be passed.
• The stored procedure contains a nested query that extracts book names and ids of the books that crossed the benchmark sales amount.
• This stored procedure can be run as and when required.
• Save the below snippet in your PostgreSQL database as a stored procedure.

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.book_id IN

(SELECT book_id FROM book_sales where book_sales.sales > sale_amount)

END

$

LANGUAGE plpgsql

Executing PostgreSQL Stored Procedure and Function in Python

• Once the stored procedure is created, we can define a function, that executes this stored procedure as and when this function is invoked. Let us name this function as a get_sale_benchmark(sale_amount)  that takes sales amount as the benchmark.
• Establish the connection to the PostgreSQL database from python by passing the connection string to psycopg2.connect() function.
• Once the connection is set up, instantiate the cursor() object to a variable named engine
• Then call psycopg2 callproc() function, which takes in two parameters, the name of the stored procedure and the parameters of the stored procedure.

Syntax:

cursor.callproc(procname[, parameters])

• The result of this stored procedure is stored in the engine object. This can be accessed using fetchall() function.
• Later use this fetchall() function to iterate through all the rows returned by the stored procedure as shown below.
• In order to capture any errors while executing the stored procedure against a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.
• Test the get_sales_benchmark() function by calling the function with an appropriate sales_amount.

Explanation:

Fetch all rows of a query result, returning them as a list of tuples. An empty list is returned if there are no records.

Python3

import psycopg2

def get_sale_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 = cursor.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_sale_benchmark(500)

Output: