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)
connect()method will return a new connection object. Using that connection, we can communicate with the database.
- Create the
Cursorobject 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
cursor.callproc('Function_or_procedure_name',[IN and OUT parameters,])
OUTparameters 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, ) print("Name = ", row) print("Designation = ", row) 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")
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.
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.execute("SELECT * FROM get_production_Deployment( %s); ", (appId, ))
To practice what you learned in this article, I have created a Python Database programming Quiz and Exercise Project.
That’s it. Folks Let me know your comments and questions in the section below.