Усиление PostgreSQL с помощью PL/Python

Kate

Administrator
Команда форума
Сегодня мы прокачаем PostgreSQL, добавив в него Python. А именно — PL/Python. Это расширение позволяет писать функции на Python прямо внутри базы данных. Лично для меня это как объединение двух лучших миров: любимого PostgreSQL и могучего Python.

PL/Python — это про то, когда стандартного SQL мало. Когда надо сделать что-то действительно интересное: сложные расчеты, массивы данных, или интеграция с аналитикой прямо в базе. А самое крутое — можно тянуть любые Python-библиотеки.

Для начала нужно просто установить расширение в PostgreSQL:

CREATE EXTENSION plpython3u;

Основы создания функций на PL/Python​

PL/Python — это процедурный язык внутри PostgreSQL, который позволяет писать функции на Python. По сути, это как обычная SQL-функция, только вместо SQL-запросов ты пишешь Python-код.

Вот базовая конструкция:

CREATE FUNCTION function_name(argument_list)
RETURNS return_type
AS $$
# Здесь идет Python-код
$$ LANGUAGE plpythonu;
Всё, что между $ и $, — это чистый Python. PostgreSQL автоматически передаёт параметры как глобальные переменные в функцию, что позволяет работать с ними, как с обычными переменными Python.

Создадим функцию, которая возвращает большее из двух чисел:

CREATE FUNCTION pymax(a integer, b integer)
RETURNS integer
AS $$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
Эта функция принимает два целых числа и возвращает большее из них. Как видишь, очень похоже на обычный Python-код, только он работает внутри PostgreSQL.

Если не вернуть значение, PostgreSQL вернёт NULL.

Теперь копнем глубже. В PL/Python аргументы передаются как глобальные переменные, что открывает массу возможностей. Но есть нюанс: если попытаться переназначить аргумент внутри функции, можно получить ошибку.

CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
x = x.strip() # ошибка
return x
$$ LANGUAGE plpythonu;
Ошибка возникает, потому что при присвоении переменной нового значения она становится локальной. В этом случае Python считает, что переменная ещё не инициализирована, и выдаёт ошибку.

Нужно явно указать, что используем глобальные переменные:

CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
global x
x = x.strip() # теперь всё работает
return x
$$ LANGUAGE plpythonu;
Теперь всё работает правильно, и Python больше не ругается.

Это мелочь, но такая ошибка может испортить весь день.

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

Создадим функцию, которая извлекает email пользователя по его ID:

CREATE FUNCTION get_user_email(user_id integer)
RETURNS text
AS $$
query = plpy.execute(f"SELECT email FROM users WHERE id = {user_id}")
if query:
return query[0]['email']
return None
$$ LANGUAGE plpythonu;
Здесь используем встроенную функцию plpy.execute() для выполнения SQL-запроса прямо из Python. Она возвращает результат в виде списка словарей, что удобно для дальнейшей обработки.

Интеграция с внешними библиотеками​

PL/Python поддерживает все библиотеки Python, установленные на сервере, где крутится PostgreSQL.

Пример с Pandas. Допустим, есть данные о продажах, и нужно их быстро проанализировать: сумму, среднее значение и медиану по месяцам.

CREATE FUNCTION analyze_sales()
RETURNS table(month text, total_sales numeric, average_sales numeric, median_sales numeric)
AS $$
import pandas as pd

result = plpy.execute("SELECT month, sales FROM sales_data")
df = pd.DataFrame(result)

df_summary = df.groupby('month')['sales'].agg(['sum', 'mean', 'median']).reset_index()

return df_summary.to_dict(orient='records')
$$ LANGUAGE plpythonu;
Здесь мы:

  1. Получаем данные из таблицы sales_data с помощью plpy.execute().
  2. Преобразуем результат в DataFrame через pandas для анализа.
  3. Группируем данные по месяцам и считаем сумму, среднее и медиану.
  4. Возвращаем результат обратно в базу данных.
Переходим к массивам данных. Если нужно работать с большими массивами или матрицами, numpy приходит на помощь.

Допустим, нужно вычислить среднее значение и стандартное отклонение по массиву данных:

CREATE FUNCTION calculate_statistics(arr double precision[])
RETURNS table(mean double precision, stddev double precision)
AS $$
import numpy as np

np_arr = np.array(arr)

mean = np.mean(np_arr)
stddev = np.std(np_arr)

return [{'mean': mean, 'stddev': stddev}]
$$ LANGUAGE plpythonu;
Пенедаем массив данных в PostgreSQL, преобразуем его в numpy-массив, а затем выполняем нужные расчёты.

Исключения и обработка ошибок​

Обрабатывать исключения в PL/Python так же легко, как в обычном Python. Вот пример функции, которая делит два числа, обрабатывая возможные ошибки:

CREATE FUNCTION safe_divide(a float, b float)
RETURNS float
AS $$
try:
return a / b
except ZeroDivisionError:
plpy.error("Деление на ноль невозможно!")
except Exception as e:
plpy.error(f"Произошла ошибка: {e}")
$$ LANGUAGE plpythonu;
Здесь обрабатываем ZeroDivisionError и любые другие исключения, отправляя ошибки в лог PostgreSQL через plpy.error().

В продакшене лучше всегда предусмотреть обработку всех возможных исключений, чтобы избежать падения системы.

Триггеры и транзакции​

Триггеры в PL/Python работают так же, как и в обычном SQL. Они срабатывают при вставке, обновлении или удалении данных, и могут выполнять определённые действия.

Создадим триггерную функцию, которая проверяет количество товаров в заказе перед вставкой:

CREATE FUNCTION validate_order_quantity()
RETURNS trigger
AS $$
if NEW.quantity <= 0:
raise plpy.Error('Количество товаров должно быть больше нуля!')
return NEW
$$ LANGUAGE plpythonu;
Этот триггер проверяет каждую вставку в таблицу заказов и гарантирует, что количество товаров больше нуля.

Теперь создадим сам триггер:

CREATE TRIGGER check_quantity
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXEUTE FUNCTION validate_order_quantity();

Триггер срабатывает до вставки или обновления и вызывает нашу функцию validate_order_quantity().

Управление транзакциями​

PL/Python можно управлять транзакциями вручную. Пример, когда мы явно управляем транзакцией:

CREATE FUNCTION transaction_test()
RETURNS void
AS $$
try:
plpy.execute("BEGIN;")
plpy.execute("INSERT INTO test_table VALUES (1);")
plpy.execute("INSERT INTO test_table VALUES (2);")
plpy.execute("COMMIT;")
except:
plpy.execute("ROLLBACK;")
raise
$$ LANGUAGE plpythonu;
Здесь начинаем транзакцию с BEGIN, выполняем несколько операций и завершаем её командой COMMIT. Если что-то пойдёт не так, откатываем транзакцию с помощью ROLLBACK.

 
Сверху