PostgreSQL Python: Call PostgreSQL Functions

Summary: in this tutorial, you will learn how to call PostgreSQL functions from a Python program.

Calling a PostgreSQL function in Python steps

To call a PostgreSQL function from a Python program, you use the following steps:

First, create a new database connection to the PostgreSQL database server by calling the connect() function of the psycopg2 module.

conn = psycopg2.connect(dsn)
Code language: Python (python)

The connect() method returns a new instance of the connection class.

Next, create a new cursor by calling the cursor() method of the connection object.

cur = conn.cursor()
Code language: SQL (Structured Query Language) (sql)

Then, pass the name of the function and the optional input values to the callproc() method of the cursor object.

cur.callproc('function_name', (value1,value2))
Code language: SQL (Structured Query Language) (sql)

Internally, the  callproc() method translates the function call and input values into the following statement:

SELECT * FROM function_name(value1,value2);
Code language: Python (python)

Therefore, you can use the execute() method of the cursor object to call a function as follows:

cur.execute("SELECT * FROM function_name( %s,%s); ",(value1,value2))
Code language: Python (python)

Both statements have the same effect.

After that, process the result set returned by the function using the fetchone(),  fetchall(), or  fetchmany() method.

Finally, call the close() method of the cursor and connection objects to close the communication with the PostgreSQL database server.

cur.close()
conn.close()
Code language: Python (python)

Calling a function example

The following get_parts_by_vendors() function returns a list of parts provided by a specified vendor.

CREATE OR REPLACE FUNCTION get_parts_by_vendor(id integer)
  RETURNS TABLE(part_id INTEGER, part_name VARCHAR) AS
$$
BEGIN
 RETURN QUERY

 SELECT parts.part_id, parts.part_name
 FROM parts
 INNER JOIN vendor_parts on vendor_parts.part_id = parts.part_id
 WHERE vendor_id = id;

END; $$

LANGUAGE plpgsql;
Code language: SQL (Structured Query Language) (sql)

The following get_parts() function calls the get_parts_by_vendors() function:

#!/usr/bin/python
import psycopg2
from config import config


def get_parts(vendor_id):
    """ get parts provided by a vendor specified by the vendor_id """
    conn = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a cursor object for execution
        cur = conn.cursor()
        # another way to call a function
        # cur.execute("SELECT * FROM get_parts_by_vendor( %s); ",(vendor_id,))
        cur.callproc('get_parts_by_vendor', (vendor_id,))
        # process the result set
        row = cur.fetchone()
        while row is not None:
            print(row)
            row = cur.fetchone()
        # close the communication with the PostgreSQL database server
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
Code language: Python (python)

The following calls the get_parts() function to get a list of parts provided by the vendor with id 1:

if __name__ == '__main__':
    get_parts(1)
Code language: Python (python)

In this tutorial, you have learned step by step how to call a PostgreSQL function in Python.