PostgreSQL Python: Call Stored Procedures

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

This tutorial picks up from where the Call PostgreSQL Functions Tutorial left off.

Steps for calling a PostgreSQL stored procedure in Python

To call a PostgreSQL stored procedure in a Python program, you follow these steps:

First, create a new database connection to the PostgreSQL database server by calling the connect() function:

conn = psycopg2.connect(config)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: Python (python)

Then, pass the name of the stored procedure and optional input values to the execute() method of the cursor object. For example:

cur.execute("CALL sp_name(%s, %s);", (val1, val2))Code language: Python (python)

If your stored procedure does not accept any parameters, you can omit the second argument like this:

cur.execute("CALL sp_name);")Code language: Python (python)

After that, call the commit() method to commit the transaction:

conn.commit();Code language: Python (python)

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

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

If you use context managers, you don’t need to explicitly call the close() method of the cursor and connection.

Calling a stored procedure example

Let’s take an example of calling a PostgreSQL stored procedure in Python.

1) Create a new stored procedure

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, connect to the suppliers database on the local PostgreSQL server:

psql -U postgres -d suppliers

Third, create a new stored procedure called add_new_part():

CREATE OR REPLACE PROCEDURE add_new_part(
	new_part_name varchar,
	new_vendor_name varchar
) 
AS $$
DECLARE
	v_part_id INT;
	v_vendor_id INT;
BEGIN
	-- insert into the parts table
	INSERT INTO parts(part_name) 
	VALUES(new_part_name) 
	RETURNING part_id INTO v_part_id;
	
	-- insert a new vendor
	INSERT INTO vendors(vendor_name)
	VALUES(new_vendor_name)
	RETURNING vendor_id INTO v_vendor_id;
	
	-- insert into vendor_parts
	INSERT INTO vendor_parts(part_id, vendor_id)
	VALUEs(v_part_id,v_vendor_id);
	
END;
$$
LANGUAGE PLPGSQL;Code language: SQL (Structured Query Language) (sql)

2) Create the call_stored_procedure.py module

First, create a new module called call_stored_procedure.py file in the project directory.

Second, define the following add_part() function that calls the add_new_part() stored procedure from the suppliers database:

import psycopg2
from config import load_config


def add_part(part_name, vendor_name):
    """ Add a new part """
    # read database configuration
    params = load_config()
    
    try:
        # connect to the PostgreSQL database
        with psycopg2.connect(**params) as conn:
            with conn.cursor() as cur:
                # call a stored procedure
                cur.execute('CALL add_new_part(%s,%s)', (part_name, vendor_name))

            # commit the transaction
            conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)


if __name__ == '__main__':
    add_part('OLED', 'LG')Code language: Python (python)

3) Execute the Python module

Execute the following command to run the call_stored_procedure.py module:

python call_stored_procedure.pyCode language: plaintext (plaintext)

4) Verify the result

Execute the following statement to retrieve data from the parts, vendors, and vendor_parts tables to verify the result:

SELECT * FROM parts;
SELECT * FROM vendors;
SELECT * FROM vendor_parts;Code language: SQL (Structured Query Language) (sql)

Download the project source code

Summary

• Use the execute() method of a cursor object to execute a stored procedure call.
• Use the CALL sp_name(arguments) syntax to construct a stored procedure call.