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:

Prerequisites
Steps to call PostgreSQL Function and stored procedure from Python
- Example to execute PostgreSQL Function and stored procedure
Next Steps:

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;Code language: Python (python)

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 new psycopg2.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 and OUT 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 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.
Close the cursor object and database connection object
use cursor.clsoe() and connection.clsoe() method to close the PostgreSQL connections after your work completes.

python execute PostgreSQL function and stored procedure — Python execute PostgreSQL function and stored procedure

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")
Code language: Python (python)

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, ))Code language: Python (python)

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.

Comments

mohamed Asif says

April 13, 2022 at 12:01 pm

Hi, I tried to connect Apache airflow with python but not able to connect , is there any solution for that?

vedha says

July 19, 2021 at 3:27 pm

Hi Vishal ,

I have a stored procedure, which takes input as a table structure. How can I execute it.

jk says

February 1, 2020 at 8:32 am

Where is the table definitions?

Pranay says

September 13, 2019 at 5:21 pm

What is prod_movement in the following statement :

(SELECT empId FROM prod_movement where prod_movement.appId = appId)

I tried to understand what should be placed while I’m executing ,failed.
I ended up trying Database name , table name, column names.
Nothing works.
I got this error : Error relation “demo” does not exist
Please explain …

- Vishal says
  
  September 13, 2019 at 6:45 pm
  
  Hi Pranay, prod_movement is a table present in my database. If you want to execute your function from Python please use your PostgreSQL function and pass the IN and OUT parameters if any.
  
  - Pranay says
    
    September 15, 2019 at 4:38 pm
    
    If prod_movement is a table you’re checking id ,then what is employee.
    Is it another table ?
    Please explain …
    
    - Pranay says
      
      September 15, 2019 at 5:09 pm
      
      Executed Function Using Python.
      Understood your query with the explanation given.
      Thanks
      @Vishal
      
      - Vishal says
        
        September 15, 2019 at 11:14 pm
        
        You are welcome.
Michael Goldshteyn says

January 3, 2019 at 2:23 am

What about named stored procedure arguments instead of sequential?

- Vishal says
  
  January 3, 2019 at 7:13 am
  Hi, Michael is the order of stored procedure input parameter always changes. in this, you can try this
```
query = """\EXEC procedureName @param1=?, @param2=?"""
tuple = ('value1','value2')
cursor.execute(query , tuple)
```
  You can also refer this StackOverflow question https://stackoverflow.com/questions/30964608/python-pyodbc-call-stored-procedure-with-parameter-name
  
  Hope it helps!
  - madhu sudhanan says
    
    September 9, 2021 at 3:35 pm
    
    Hi Vishal can i get some support to call a postgresql function from python

Execute PostgreSQL Stored Procedure and Function in Python

Table of contents

Prerequisites

Steps to call PostgreSQL Function and stored procedure from Python

Example to execute PostgreSQL Function and stored procedure

Next Steps:

About Vishal

Related Tutorial Topics:

All Coding Exercises:

Python Exercises and Quizzes

About PYnative

Follow Us

Explore Python

Coding Exercises

Legal Stuff

Table of contents

Prerequisites

Steps to call PostgreSQL Function and stored procedure from Python

Example to execute PostgreSQL Function and stored procedure

Next Steps:

About Vishal

Related Tutorial Topics:

All Coding Exercises:

Python Exercises and Quizzes

Comments

Leave a Reply Cancel reply

About PYnative

Follow Us

Explore Python

Coding Exercises

Legal Stuff