In this lesson, you 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 functions from Python.
Also, See:
Table of contents
Prerequisites
Before executing the following program, please make sure you have the following in place:
- Username and password that you need to connect to PostgreSQL.
- PostgreSQL database Stored procedure or function name which you want to execute.
For this lesson, 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;
Steps to call PostgreSQL Function and stored procedure from Python
We are using a psycopg2 module to execute the PostgreSQL function in Python.
How to execute PostgreSQL functions and stored procedure in Python
- Import psycopg2
Install psycopg2 using
pip install psycopg2
and import it in your file. - Connect to PostgreSQL from Python
Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using PSycopg2.
- Get Cursor Object from Connection
Next, use a
connection.cursor()
method to create a cursor object. This method creates a newpsycopg2.extensions.cursor
object. - Execute the stored procedure or function
Execute the stored procedure using the
cursor.callproc()
. here, you must know the stored procedure name and its IN and OUT parameters. For example,cursor.callproc('Function_name',[IN and OUT parameters,])
IN
andOUT
parameters must be separated by commas. - Fetch results
Once the stored procedure executes successfully, we can extract the result using a fetchall().
Process The result returned by thecallproc()
. It may be database rows or just an affected row count. Alternatively, it can be anything as per the implementation of the function. - Close the cursor object and database connection object
use
cursor.clsoe()
andconnection.clsoe()
method to close the PostgreSQL connections after your work completes.

Example to execute PostgreSQL Function and stored procedure
Let see the demo now. We already created the stored procedure get_production_Deployment
, which accepts the application id as an IN parameter and returning its employee id, employee name, and designation as the OUT parameters.
import psycopg2
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
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.