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.py
Code 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 acursor
object to execute a stored procedure call. - Use the
CALL sp_name(arguments)
syntax to construct a stored procedure call.