• Menu
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

pynative

A Python Programming blog

Header Right

 
  • Tutorials
  • Python Exercises
  • <>Code Editor
  • Python Tricks
  • Newsletter
  • Tutorials
  • Python Exercises
  • <>Code Editor
  • Python Tricks
  • Newsletter

Execute PostgreSQL Stored Procedure and Function in Python

Filed Under: Python PostgreSQL | November 22, 2019 7 Comments

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.

  • The article is part of a series Python PostgreSQL Tutorial.
  • Also, solve our Python Database Programming Exercise and Python Database programming Quiz to practice and master the Database skills.

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.

python execute PostgreSQL function and stored procedure
python execute PostgreSQL function
and stored procedure

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)

    The connect() method will return a new connection object. Using that connection, we can communicate with the database.

  • Create the Cursor object 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 callproc method.
    cursor.callproc('Function_or_procedure_name',[IN and OUT parameters,])
    

    IN and OUT parameters 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[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

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.

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, I have created a Python Database programming Quiz and Exercise Project.

  • Solve our Python PostgreSQL Database programming Quiz to test your Python database concepts.
  • Solve our Python PostgreSQL Database Exercise project to Practice and master the Python Database operations.

That’s it. Folks Let me know your comments and questions in the section below.

About Vishal

Founder of pynative.com. I am a Python developer and I love to write articles to help developers. Follow me on Twitter | Happy Pythoning!

Python Exercises
Python Tricks

Keep Learning

  • Python Basics (9)
  • Python Generate random data (12)
  • Python MySQL (12)
  • Python PostgreSQL (6)
  • Python SQLite (9)
  • Python Exercises (11)

Join Our Newsletter

I'm determined to improve your Python skills, are You? Subscribe and Get New Python Tutorials, Exercises, Tips and Tricks into your Inbox Every alternate Week.

Primary Sidebar

Python Exercises


Practice Python using our free exercises.
Exercises cover Python Basics to Data analytics and Database.
Show Exercises

Topics


  • Python Basics (9)
  • Python Generate random data (12)
  • Python MySQL (12)
  • Python PostgreSQL (6)
  • Python SQLite (9)
  • Python Exercises (11)

Join Our Newsletter


Subscribe toGet New Python Tutorials, Exercises, Tips and Tricks into your Inbox

Follow Us On

Facebook Twitter

Secondary Sidebar

Python PostgreSQL Tutorial

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

  • Python Tutorials
  • Python Exercises
  • Python Tips and Tricks
  • Python Code Editor

Footer

Follow Pynative

  • Home
  • NewsLetter
  • About Us
  • Facebook
  • Twitter
  • RSS | Sitemap

Python

  • Python Tutorials
  • Python Exercises
  • Online Python Code Editor
  • Python Tricks

Join Our Newsletter

Subscribe and Get New Python Tutorials, Exercises, Tips and Tricks into your Inbox Every alternate Week.

Legal Stuff

  • Privacy Policy
  • Cookie Policy
  • Terms Of Use
  • Contact Us
  • Do Not Sell My Personal Information
DMCA.com Protection Status

Copyright © 2018-2019 · [pynative.com]

We use cookies to ensure that you have the best possible experience on our website.AcceptLearn More