Сегодня мы прокачаем PostgreSQL, добавив в него Python. А именно — PL/Python. Это расширение позволяет писать функции на Python прямо внутри базы данных. Лично для меня это как объединение двух лучших миров: любимого PostgreSQL и могучего Python.
PL/Python — это про то, когда стандартного SQL мало. Когда надо сделать что-то действительно интересное: сложные расчеты, массивы данных, или интеграция с аналитикой прямо в базе. А самое крутое — можно тянуть любые Python-библиотеки.
Для начала нужно просто установить расширение в PostgreSQL:
CREATE EXTENSION plpython3u;
Вот базовая конструкция:
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. Она возвращает результат в виде списка словарей, что удобно для дальнейшей обработки.
Пример с 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;
Здесь мы:
Допустим, нужно вычислить среднее значение и стандартное отклонение по массиву данных:
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-массив, а затем выполняем нужные расчёты.
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().
В продакшене лучше всегда предусмотреть обработку всех возможных исключений, чтобы избежать падения системы.
Создадим триггерную функцию, которая проверяет количество товаров в заказе перед вставкой:
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().
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.
habr.com
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;
Здесь мы:
- Получаем данные из таблицы sales_data с помощью plpy.execute().
- Преобразуем результат в DataFrame через pandas для анализа.
- Группируем данные по месяцам и считаем сумму, среднее и медиану.
- Возвращаем результат обратно в базу данных.
Допустим, нужно вычислить среднее значение и стандартное отклонение по массиву данных:
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.
Усиление PostgreSQL с помощью PL/Python
Привет, Хабр! Сегодня мы прокачаем PostgreSQL, добавив в него Python. А именно — PL/Python. Это расширение позволяет писать функции на Python прямо внутри базы данных. Лично для меня это как...
habr.com