This tutorial picks up from where theTransaction Tutorial left off.

Calling a PostgreSQL function in Python

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

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

conn = psycopg2.connect(config)

Theconnect() method returns a new instance of theconnection class.

Next, create a new cursor by calling thecursor() method of theconnection object.

cur= conn.cursor()

Then, pass the name of the function and the optionally pass values to thecallproc() method of thecursor object:

cur.callproc('function_name', (value1,value2))

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

SELECT * FROM function_name(value1,value2);

Therefore, you can use theexecute() method of thecursor object to call a function as follows:

cur.execute("SELECT * FROM function_name(%s,%s); ",(value1,value2))

Both statements have the same effect.

After that, process the result set returned by the function using thefetchone(),  fetchall(), orfetchmany() method.

Finally, call theclose() method of thecursor andconnection objects to close the communication with the PostgreSQL database server:

cur.close()conn.close()

Calling a PostgreSQL function example

Let’s take an example of calling a PostgreSQL function from Python.

1) Create a new function

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to thesuppliers database:

psql-U postgres-d suppliers

Second, execute the following command to create a new function calledget_parts_by_vendors() that returns a list of parts 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;

Notice that you can use any PostgreSQL client tools to create a function such as pgAdmin.

2) Create call_function.py module

First, create a new module in the project directory calledcall_function.py:

Second, define a new function calledget_parts() that calls theget_parts_by_vendors() function in PostgreSQL:

import psycopg2from config import load_configdef get_parts(vendor_id):    """ Get parts provided by a vendor specified by the vendor_id """    parts = []    # read database configuration    params = load_config()    try:        # connect to the PostgreSQL database        with  psycopg2.connect(**params)as conn:            with conn.cursor()as cur:                # create a cursor object for execution                cur = conn.cursor()                cur.callproc('get_parts_by_vendor', (vendor_id,))                # process the result set                row = cur.fetchone()                while row is not None:                    parts.append(row)                    row = cur.fetchone()    except (Exception,psycopg2.DatabaseError) as error:        print(error)    finally:        return partsif __name__ == '__main__':    parts = get_parts(1)    print(parts)

3) Call the call_function.py module

Run the following command to execute thecall_function.py module:

python call_function.py

Output:

[(1, 'SIM Tray'), (5, 'Home Button'), (6, 'LTE Modem')]

Download the project source code

Summary

• Use thecallproc() method of thecursor object to call a function in PostgreSQL from Python.

Last updated onJanuary 29, 2024

On this page

• Calling a PostgreSQL function in Python
• Calling a PostgreSQL function example
• Summary