PostgreSQL Python: Call PostgreSQL Stored Procedures

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 thế steps:

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

conn = psycopg2.connect(dsn)
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 the 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 you 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)

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;
Code language: SQL (Structured Query Language) (sql)

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')
Code language: Python (python)

Executing the python file

To execute the python file, you use the following statement:

python stored_proc.py
Code language: CSS (css)

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.