This tutorial picks up from where theCall 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 theconnect() function:

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 stored procedure and optional input values to theexecute() method of thecursor object. For example:

cur.execute("CALL sp_name(%s,%s);", (val1, val2))

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

cur.execute("CALL sp_name);")

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

conn.commit();

Finally, call theclose() method of thecursor andconnection objects to close the connection to the PostgreSQL database server.

cur.close()conn.close()

If you use context managers, you don’t need to explicitly call theclose() 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 thesuppliers database on the local PostgreSQL server:

psql-U postgres-d suppliers

Third, create a new stored procedure calledadd_new_part():

CREATE OR REPLACE PROCEDURE add_new_part(new_part_name varchar,new_vendor_name varchar)AS $$DECLAREv_part_id INT;v_vendor_id INT;BEGIN-- insert into the parts tableINSERT INTO parts(part_name)VALUES(new_part_name)RETURNING part_id INTO v_part_id;-- insert a new vendorINSERT INTO vendors(vendor_name)VALUES(new_vendor_name)RETURNING vendor_id INTO v_vendor_id;-- insert into vendor_partsINSERT INTO vendor_parts(part_id, vendor_id)VALUEs(v_part_id,v_vendor_id);END;$$LANGUAGE PLPGSQL;

2) Create the call_stored_procedure.py module

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

Second, define the followingadd_part() function that calls theadd_new_part() stored procedure from thesuppliers database:

import psycopg2from config import load_configdef add_part(part_name, vendor_name):    """ Add a new part """    #read database configuration    params= load_config()    try:        #connect to the PostgreSQLdatabase        with psycopg2.connect(**params)as conn:            with conn.cursor()as cur:                #call a storedprocedure                cur.execute('CALL add_new_part(%s,%s)', (part_name, vendor_name))            #commit thetransaction            conn.commit()    except (Exception, psycopg2.DatabaseError)as error:        print(error)if __name__== '__main__':    add_part('OLED','LG')

3) Execute the Python module

Execute the following command to run thecall_stored_procedure.py module:

python call_stored_procedure.py

4) Verify the result

Execute the following statement to retrieve data from theparts,vendors, andvendor_parts tables to verify the result:

SELECT * FROM parts;SELECT * FROM vendors;SELECT * FROM vendor_parts;

Download the project source code

Summary

• Use theexecute() method of acursor object to execute a stored procedure call.
• Use theCALL sp_name(arguments) syntax to construct a stored procedure call.

Last updated onJanuary 29, 2024

On this page

• Steps for calling a PostgreSQL stored procedure in Python
• Calling a stored procedure example
• Summary