Postgresql python stored procedures

Execute PostgreSQL Stored Procedure and Function in Python

In this lesson, you will learn how to execute a PostgreSQL function and Stored procedure in Python. PostgreSQL function can perform different operations; it can be data manipulation or data retrieval. Let’s see how to execute such functions from Python.

Table of contents

Prerequisites

Before executing the following program, please make sure you have the following in place:

• Username and password that you need to connect to PostgreSQL.
• PostgreSQL database Stored procedure or function name which you want to execute.

For this lesson, I have created a function get_production_Deployment in PostgreSQL, which returns a list of employee records who deployed code changes in the production environment.

CREATE OR REPLACE FUNCTION get_production_deployment(appId integer) RETURNS TABLE(empId INTEGER, empName VARCHAR, designation VARCHAR) AS $ BEGIN RETURN QUERY SELECT employee.id, employee.name, employee.designation FROM employee where employee.id = (SELECT empId FROM prod_movement where prod_movement.appId = appId) END; $ LANGUAGE plpgsql;

Steps to call PostgreSQL Function and stored procedure from Python

We are using a psycopg2 module to execute the PostgreSQL function in Python.

How to execute PostgreSQL functions and stored procedure in Python

1. Import psycopg2 Install psycopg2 using pip install psycopg2 and import it in your file.
2. Connect to PostgreSQL from Python Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using PSycopg2.
3. Get Cursor Object from Connection Next, use a connection.cursor() method to create a cursor object. This method creates a new psycopg2.extensions.cursor object.
4. Execute the stored procedure or function Execute the stored procedure using the cursor.callproc() . here, you must know the stored procedure name and its IN and OUT parameters. For example, cursor.callproc(‘Function_name’,[IN and OUT parameters,]) IN and OUT parameters must be separated by commas.
5. Fetch results Once the stored procedure executes successfully, we can extract the result using a fetchall().
   Process The result returned by the callproc() . It may be database rows or just an affected row count. Alternatively, it can be anything as per the implementation of the function.
6. Close the cursor object and database connection object use cursor.clsoe() and connection.clsoe() method to close the PostgreSQL connections after your work completes.

Example to execute PostgreSQL Function and stored procedure

Let see the demo now. We already created the stored procedure get_production_Deployment , which accepts the application id as an IN parameter and returning its employee id, employee name, and designation as the OUT parameters.

import psycopg2 try: ps_connection = psycopg2.connect(user="postgres", password="pass@#29", host="127.0.0.1", port="5432", database="postgres_db") cursor = ps_connection.cursor() # call stored procedure cursor.callproc('get_production_Deployment', [72, ]) print("fechting Employee details who pushed changes to the production from function") result = cursor.fetchall() for row in result: print("Id = ", row[0], ) print("Name = ", row[1]) print("Designation = ", row[2]) except (Exception, psycopg2.DatabaseError) as error: print("Error while connecting to PostgreSQL", error) finally: # closing database connection. if ps_connection: cursor.close() ps_connection.close() print("PostgreSQL connection is closed") 

fechting Employee details who pushed changes to the production from function Id = 23 Name = Scot Designation = Application Developer PostgreSQL connection is closed

We can also use Python cursor’s fetchall(), fetchmany(), fetchone() methods depending on the return value from a function or a stored procedure.

Also, cursor.callproc() internally uses execute() method of the cursor object to call a stored procedure. So you can directly execute the following query to call stored procedure instead of using cursor.callproc()

cursor.execute("SELECT * FROM get_production_Deployment( %s); ", (appId, ))

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

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.pyCode 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.

Источник

Выполнение хранимых процедур и функций PostgreSQL

В этом руководстве рассмотрим, как выполнять хранимые процедуры и функции PostgreSQL из Python. Эти функции могут отвечать как за получение, так и за управление данными.

Подготовка базы данных

Перед выполнением следующей программы убедитесь, что у вас есть следующее:

• Имя пользователя и пароль для подключения к базе данных.
• Название хранимой процедуры или функции, которые нужно выполнить.

Для этого примера была создана функция get_production_deployment , которая возвращает список записей о сотрудниках, участвовавших в написании кода.

CREATE OR REPLACE FUNCTION filter_by_price(max_price integer) RETURNS TABLE(id INT, model TEXT, price REAL) AS $$ BEGIN RETURN QUERY SELECT * FROM mobile where mobile.price 

 
 
import psycopg2 from psycopg2 import Error def create_func(query): try: # Подключиться к существующей базе данных connection = psycopg2.connect(user="postgres", # пароль, который указали при установке PostgreSQL password="1111", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() cursor.execute(query) connection.commit() except (Exception, Error) as error: print("Ошибка при работе с PostgreSQL", error) finally: if connection: cursor.close() connection.close() print("Соединение с PostgreSQL закрыто") postgresql_func = """ CREATE OR REPLACE FUNCTION filter_by_price(max_price integer) RETURNS TABLE(id INT, model TEXT, price REAL) AS $$ BEGIN RETURN QUERY SELECT * FROM mobile where mobile.price 
Этот код добавит функцию в базу данных.
Детали вызова функции и хранимой процедуры PostgreSQL
Используем модуль psycopg2 для вызова функции PostgreSQL в Python. Дальше требуется выполнить следующие шаги:

  
Метод connect() вернет новый объект соединения. С его помощью и можно общаться с базой.
  
Создать объект Cursor с помощью соединения. Именно он позволит выполнять запросы к базе данных.
  
Выполнить функцию или хранимую процедуру с помощью метода cursor.callproc() . На этом этапе нужно знать ее название, а также параметры IN и OUT. Синтаксис метода следующий. 
cursor.callproc('filter_by_price',[IN and OUT parameters,]) 
Параметры следует разделить запятыми.
  
Этот метод возвращает либо строки базы данных, либо количество измененных строк. Все зависит от назначения самой функции.

Пример выполнения функции и хранимой процедуры
Посмотрим на демо. Процедура filter_by_price уже создана. В качестве параметра IN она принимает идентификатор приложения, а возвращает ID, модель и цену телефона.
 
 
import psycopg2 from psycopg2 import Error try: # Подключиться к существующей базе данных connection = psycopg2.connect(user="postgres", # пароль, который указали при установке PostgreSQL password="1111", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() # хранимая процедура cursor.callproc('filter_by_price',[999,]) print("Записи с ценой меньше или равной 999") result = cursor.fetchall() for row in result: print("Id = ", row[0], ) print("Model = ", row[1]) print("Price = ", row[2]) except (Exception, Error) as error: print("Ошибка при работе с PostgreSQL", error) finally: if connection: cursor.close() connection.close() print("Соединение с PostgreSQL закрыто")
 
После предыдущего урока у меня осталось всего 2 записи, вывод вернул одну. Вторая с ценой 1000:
 
Записи с ценой меньше или равной 999 Id = 2 Model = Google Pixel 2 Price = 700.0 Соединение с PostgreSQL закрыто
 
После этого обрабатываем результаты с помощью fetchone . Также можно использовать методы fetchall , fetchmany() в зависимости от того, что должна вернуть функция.
 
Также cursor.callproc() внутри вызывает метод execute() для вызова процедуры. Так что ничего не мешает использовать его явно:
 
cursor.execute("SELECT * FROM filter_by_price(); ".format(price=1000))
 
Источник