06_call_stored_procedure.py

#!/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()
            
            
if __name__ == '__main__':
    get_parts(1)

    
    
    
"""
Calling a stored procedure example

The following get_parts_by_vendors() stored procedure returns a list of parts provided by a specified vendor.


copy and paste this in pgsql command line to generate the function then you can run python ocde to call it!!!   :-) 

----------------------	
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:



"""