Выполнение хранимых процедур и функций 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 <= max_price;
END;
$$ LANGUAGE plpgsql;

Теперь посмотрим, как это выполнить.

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 <= max_price;
END;
$$ LANGUAGE plpgsql;
"""
create_func(postgresql_func)

Этот код добавит функцию в базу данных.

Детали вызова функции и хранимой процедуры 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({price}); ".format(price=1000))