July 31, 2023
Summary: in this tutorial, you will learn how to call PostgreSQL stored procedures from a Python program.
Steps for calling a PostgreSQL stored procedure in Python
To call a PostgreSQL stored procedure in a Python program, you follow the steps:
First, create a new database connection to the PostgreSQL database server by calling the connect() function:
conn = psycopg2.connect(dsn)
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 the optional input values to the execute() method of the cursor object. For example:
cur.execute("CALL sp_name(%s, %s);", (val1, val2))
If you 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()
Calling a stored procedure example
First, create the following add_new_part() stored procedure in the suppliers database.
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;
Second, create a new file called stored_proc.py and defined the following add_part() function. The add_part() function calls the add_new_part() stored procedure from the suppliers database:
#!/usr/bin/python
import psycopg2
from config import config
def add_part(part_name, vendor_name):
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()
# call a stored procedure
cur.execute('CALL add_new_part(%s,%s)', (part_name, vendor_name))
# commit the transaction
conn.commit()
# close the cursor
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
add_part('OLED', 'LG')
Executing the python file
To execute the python file, you use the following statement:
python stored_proc.py
To verify the insert, you can query data from the parts, vendors, and vendor_parts tables:
SELECT * FROM parts;
SELECT * FROM vendors;
SELECT * FROM vendor_parts;
In this tutorial, you have learned step by step how to call a PostgreSQL stored procedure in Python.