Summary: in this tutorial, you will learn how to call PostgreSQL stored procedures in Python using psycopg.
Calling a PostgreSQL stored procedure in Python steps
To call a PostgreSQL stored procedure in a Python program, you use the following steps:
First, create a new database connection to the PostgreSQL database server by calling the connect() function of the psycopg module.
1 | 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.
1 | cur = conn.cursor() |
Then, pass the name of the stored procedure and the optional input values to the callproc() method of the cursor object.
1 | cur.callproc(‘stored_procedure_name’, (value1,value2)) |
Internally, the callproc() method translates the stored procedure call and input values into the following statement:
1 | SELECT * FROM stored_procedure_name(value1,value2); |
Therefore, you can use the execute() method of the cursor object to call a stored procedure as follows:
1 | cur.execute("SELECT * FROM stored_procedure_name( %s,%s); ",(value1,value2)) |
Both statements have the same effect.
After that, process the result set returned by the stored procedure using the fetchone(), fetchall(), or fetchmany() method.
Finally, call the close() method of the cursor and connection objects to close the communication with the PostgreSQL database server.
1 2 | cur.close() conn.close() |
Calling a stored procedure example
The following get_parts_by_vendors() stored procedure returns a list of parts provided by a specified vendor.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION get_parts_by_vendor(id integer) RETURNS TABLE(part_id INTEGER, part_name VARCHAR) AS $$ BEGIN RETURN QUERY SELECT parts.part_id, parts.part_name FROM parts INNER JOIN vendor_parts on vendor_parts.part_id = parts.part_id WHERE vendor_id = id; END; $$ LANGUAGE plpgsql; |
The following get_parts() function calls the get_parts_by_vendors() stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | #!/usr/bin/python import psycopg2 from config import config def get_parts(vendor_id): """ get parts provided by a vendor specified by the vendor_id """ 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() # another way to call a stored procedure # cur.execute("SELECT * FROM get_parts_by_vendor( %s); ",(vendor_id,)) cur.callproc('get_parts_by_vendor', (vendor_id,)) # process the result set row = cur.fetchone() while row is not None: print(row) row = cur.fetchone() # close the communication with the PostgreSQL database server cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() |
The following code snippet calls the get_parts() function to get a list of parts provided by the vendor with id 1:
1 2 | if __name__ == '__main__': get_parts(1) |
In this tutorial, we have shown you the step by step how to call a PostgreSQL stored procedure in Python.