PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Execute PostgreSQL Stored Procedure and Function in Python

Execute PostgreSQL Stored Procedure and Function in Python

Updated on: March 9, 2021 | 11 Comments

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:

  • Solve Python PostgreSQL Exercise
  • Read Python PostgreSQL Tutorial (Complete Guide)

Table of contents

  • 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;

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

  1. Import psycopg2

    Install psycopg2 using pip install psycopg2 and import it in your file.

  2. Connect to PostgreSQL from Python

    Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using PSycopg2.

  3. 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.

  4. 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.

  5. 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.

  6. 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")

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python PostgreSQL

  • Python PostgreSQL Guide
  • Python PostgreSQL Select
  • Python PostgreSQL CRUD
  • Python PostgreSQL call Function
  • Python PostgreSQL Transactions
  • Python PostgreSQL Connection Pool
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com