In this article, we will learn how to execute a PostgreSQL function and Stored procedure in Python. PostgreSQL function and the Stored procedure can perform different operations it can be data manipulation or data retrieval. We will see how to execute such functions 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 returns 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 accept 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 fetchone(), fetchall(), or fetchmany() method 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.