Categories
28°C, New York
Home Python Creating a Stored Procedure

Creating a Stored Procedure

Executing PostgreSQL Stored Procedure and Function in Python

  • The first step is to write a stored procedure, the syntax is similar to that of the conventional SQL statements.
  • The below code snippet shows a stored procedure that extracts the book names and book id, whose sales has crossed a certain benchmark amount.
  • This stored procedure is named get_book_sales(sale_amount), which takes sale_amount as a parameter where the benchmark sales amount has to be passed.
  • The stored procedure contains a nested query that extracts book names and ids of the books that crossed the benchmark sales amount.
  • This stored procedure can be run as and when required.
  • Save the below snippet in your PostgreSQL database as a stored procedure.

CREATE OR REPLACE FUNCTION get_book_sales(sale_amount integer)

RETURNS TABLE(book_name VARCHAR, book_id INTEGER, store VARCHAR) AS

$

BEGIN

RETURN QUERY

SELECT books.id, books.book_name,

FROM books where books.book_id IN

(SELECT book_id FROM book_sales where book_sales.sales > sale_amount)

END

$

LANGUAGE plpgsql

Execute PostgreSQL Stored Procedure and Function in Python

A stored procedure is a sequence of structured procedural language queries stored in a relational database management system as a data dictionary, which can be shared and reused multiple times.  All CRUD operations, querying operations can be performed by calling the stored procedure. The use of stored procedures reduces the repetition of code that is used time and again. In this article let us discuss how to execute PostgreSQL stored procedure and function in python.

Similar Reads

Creating a Stored Procedure

The first step is to write a stored procedure, the syntax is similar to that of the conventional SQL statements. The below code snippet shows a stored procedure that extracts the book names and book id, whose sales has crossed a certain benchmark amount. This stored procedure is named get_book_sales(sale_amount), which takes sale_amount as a parameter where the benchmark sales amount has to be passed. The stored procedure contains a nested query that extracts book names and ids of the books that crossed the benchmark sales amount. This stored procedure can be run as and when required. Save the below snippet in your PostgreSQL database as a stored procedure....

Executing PostgreSQL Stored Procedure and Function in Python

Once the stored procedure is created, we can define a function, that executes this stored procedure as and when this function is invoked. Let us name this function as a get_sale_benchmark(sale_amount)  that takes sales amount as the benchmark. Establish the connection to the PostgreSQL database from python by passing the connection string to psycopg2.connect() function. Once the connection is set up, instantiate the cursor() object to a variable named engine Then call psycopg2 callproc() function, which takes in two parameters, the name of the stored procedure and the parameters of the stored procedure....

Tags:
#Python PostgreSQL #Python Pyscopg2 #Python #python

Executing PostgreSQL Stored Procedure and Function in Python