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)
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()
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))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 the
commit()
method to commit the transaction:
conn.commit();
Finally, call the
close()
method of the
cursor
and
connection
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 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;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')3) Execute the Python module
Execute the following command to run the
call_stored_procedure.py
module:
python call_stored_procedure.py4) 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;