Execute PostgreSQL Stored Procedure and Function in Python

In this article, we will learn how to execute a PostgreSQL function and Stored procedure in Python. PostgreSQL function can perform different operations; it can be data manipulation or data retrieval. Let’s see how to execute such a function from Python.

Prerequisites Before we start

Before executing the following program, please make sure you have the following in place:

• Username and password that you need to connect PostgreSQL.
• PostgreSQL database Stored procedure or function name which you want to execute.

For this article, I have created a function get_production_Deployment in PostgreSQL which returns a list of employee records who deployed code changes in the production environment.

CREATE OR REPLACE FUNCTION get_production_deployment(appId integer)
  RETURNS TABLE(empId INTEGER, empName VARCHAR, designation VARCHAR) AS
$$
BEGIN
 RETURN QUERY
 
 SELECT employee.id, employee.name, employee.designation
 FROM employee where employee.id = 
 (SELECT empId FROM prod_movement where prod_movement.appId = appId)

END; $$
 
LANGUAGE plpgsql;

Now, Let see how to execute it.

Steps to call PostgreSQL Function and stored procedure from Python

We are using a psycopg2 module to execute the PostgreSQL function in Python. Follow these simple steps to call PostgreSQL function and stored procedure from python using psycopg2.

• Install psycopg2 using Pip.
• Import psycopg2 in your Python file and Create a PostgreSQL database connection.

  ps_connection = psycopg2.connect(Connection Arguments)

  The connect() method will return a new connection object. Using that connection, we can communicate with the database.

• Create the Cursor object using the PostgreSQL connection object. Using a cursor object, we can execute database queries.

  cursor = ps_connection.cursor()

• Execute the stored procedure or function using a cursor.callproc() method. (Here you must know the stored procedure name and its IN and OUT parameters).  The syntax of the callproc method.

  cursor.callproc('Function_or_procedure_name',[IN and OUT parameters,])

  IN and OUT parameters must be separated by commas.

• Process The result returned by the callproc(). It may be database rows or just an affected row count. Alternatively, it can be anything as per the implementation of the function.
• Catch any SQL exceptions that may occur during this process.
• Close the cursor object and PostgreSQL database connection.

Python Example to execute PostgreSQL Function and stored procedure

Let see the demo now. We already created the stored procedure “get_production_Deployment” which accepts application id as an IN parameter and returning its employee id, employee name, and designation as the OUT parameters.

import psycopg2
from psycopg2 import Error
try:
   ps_connection = psycopg2.connect(user="postgres",
                                  password="pass@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")
   
   cursor = ps_connection.cursor()
   
   #call stored procedure
   cursor.callproc('get_production_Deployment',[72,])

   print("fechting Employee details who pushed changes to the production from function")
   result = cursor.fetchall()
   for row in result:
       print("Id = ", row[0], )
       print("Name = ", row[1])
       print("Designation  = ", row[2])
     
except (Exception, psycopg2.DatabaseError) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
    if(ps_connection):
        cursor.close()
        ps_connection.close()
        print("PostgreSQL connection is closed")

Output:

fechting Employee details who pushed changes to the production from function
Id =  23
Name =  Scot
Designation =  Application Developer
PostgreSQL connection is closed

As you can see we processed the result returned by a stored procedure (i.e., cursor.callproc() ) using the fetchone(). We can also use Python cursor’s fetchall, fetchmany(), fetchone() methods depending on the return value from a function or a stored procedure.

Also, cursor.callproc() internally uses execute() method of the cursor object to call a stored procedure. So you can directly execute the following query to call stored procedure instead of using cursor.callproc()

cursor.execute("SELECT * FROM get_production_Deployment( %s); ", (appId, ))

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.