PostgreSQL и PL/pythonu
После общения с реализацией питона под Postgres я понял, что чего-то ей не хватает… А именно, неудобно писать запросы с сохранением плана выполнения. Пришлось дописать 
Сначала дам общее введение для полноты изложения. Если кто знаком, могут пролистать вниз до большого куска питоновского кода 
Речь в этом посте пойдет про удобоваримый способ выполнять sql запросы из хранимой процедуры на питоне, если кто забыл
Итак, общее:
PostgreSQL – наиболее функциональная система управления объектно-реляционными базами данных с открытым исходным кодом. Она поддерживает хранимые процедуры на ряде языков: встроенном – PL/pgSQL, и с подключаемыми внешними интерпретаторами – PL/Perl, PL/PythonU и другие. Perl на мой взгляд хорош для небольших скриптов администрирования системы. Для хранимых процедур он не очень подходит. Его сложно поддерживать. Python, хоть и относительно молодой язык, для хранимых процедур подходит куда больше. Синтаксис питона намного читабельнее и интерпретатор не склонен выписывать “фортели” в стиле перла, когда программа вдруг перестает работать так, как должна. Рассмотрим возможности, предоставляемы PostgreSQL по написанию хранимых процедур на питоне.
Чтобы создать функцию на питоне, надо использовать стандартную инструкцию CREATE FUNCTION. Рассмотрим пример функции поиска максимум двух чисел:
CREATE OR REPLACE FUNCTION my_max(p_number1 integer, p_number2 integer) RETURNS integer AS $$ if p_number1 > p_number2: return p_number1 else return p_number2 $$ LANGUAGE 'plpythonu'
Префикс “p_” я использую для имен параметров функции. Делаю это из соображений стилевой совместимости с кодом на PL/pgSQL. Там при подстановке параметров в запрос приоритет у локальных переменных, и если возникает коллизия имен локальной переменной и столбца в таблице, то вместо имени столбца в итоговом запросе окажется значение переменной. По этим же соображениям в коде на PL/pgSQL я даю всем локальным переменным префикс “v_”, но к питону это совершенно не относится. В нем нет автоматической привязки переменных к запросам, а вопрос совместимости не встает, так как “внешнему миру” виден только заголовок функции, а не код.
Так же поддержка питона включает в себя три словаря словаря:
- GD – глобальный словарь, его содержимое не меняется на протяжении жизни базы
- SD – словарь сессии, его содержимое актуально только в пределах одной сессии
- TD – словарь, содержащий информацию для триггеров
Про словатрь TD стоит сказать отдельно. Он содержит в себе следующие ключи:
- event – событие, ‘INSERT’, ‘UPDATE’ или ‘DELETE’
- when – время запуска триггера, ‘BEFORE’ или ‘AFTER’
- old – старая запись
- new – новая запись
Там содержится еще ряд ключей. О них можно почитать в документации к PostgreSQL.
Для взаимодействия с базой данных предоставляется модуль plpy который автоматически импортируется. Он включает ряд функций:
- debug(msg)
- log(msg)
- info(msg)
- notice(msg)
- warning(msg)
- error(msg)
- fatal(msg)
- prepare(query, types)
- execute(plan, variables, max_rows)
Функция prepare получает на вход строку запроса. Места, куда следует подставлять переменные обозначаются как $1,$2,….,$n. types является список из n строк, определяющих типы аргументов. Возвращает эта функция заготовленный план выполнения.
Функция execute получает на вход либо строку запроса и более ничего, либо план, список переменных, которые надо подставить, и максимальное количество строк, которое надо получить. Возвращает она объект запроса, имитирующий список, содержащий результат выполнения. Каждая строка имитирует словарь, где ключи – имена столбцов. Если rec – п=объект, который вернула execute, to rec[5]["column3"] есть столбец “column3″ 5 строки результата выполнения запроса. Так же объект-результат содержит метод nrows, позволяющий получить количество строк в результате.
Чтобы функция при повторном вызове использовала уже заготовленный план надо записать его в SD. Типовой пример выполнения запроса:
CREATE OR REPLACE FUNCTION saved_plan() RETURNS void AS $$ plan = None if SD.has_key("plan"): plan = SD["plan"] else: plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ]) SD["plan"] = plan #some code here... rv = plpy.execute(plan, [ "Вася" ], 5) for i in range(0,rv.nrows()): #operate on rv[i]... # rest of function $$ LANGUAGE 'plpythonu'
Нагромождение со словарем придется писать на каждый запрос. А избыток скобок при более сложных аргументах(если там обращение к элементу другой записи, например) запроса напоминает чем-то лисп.
Собственно подходим к тому, что же стало причиной написания этого поста
Предлагается написать питоновский класс, который все это безобразие инкапсулирует. В виду того, что plpy и SD не глобальные символы, совсем изящно отделаться не получится. Кстати, забыл сказать что есть возможность импортировать любой питоновский модуль, будь то re или еще какой. Собственно решение заключается в следующем: надо написать модуль pgsql.py и поместить его в каталог, в котором питон хранит ищет модули. Я не мудрствуя лукаво определил этот файл в /usr/lib/python2.5. Что же надо написать в этом модуле? А следующее:
Большой кусок питоновского кода:
#!/usr/bin/env python """PostgreSQL convenience module.""" class QueryPlanner(object): plpy = None SD = None def __init__(self,plpy,SD): QueryPlanner.plpy = plpy QueryPlanner.SD = SD def prepare(query, types): plan = None plan_key = "query_plan_" + query if QueryPlanner.SD.has_key(plan_key): plan = QueryPlanner.SD[plan_key] else: plan = QueryPlanner.plpy.prepare(query,types) QueryPlanner.SD[plan_key] = plan return plan def execute(plan, values): return QueryPlanner.plpy.execute(plan, values) prepare = staticmethod(prepare) execute = staticmethod(execute) class Query(object): def __init__(self,query): self.query = query self.plan = None self.values = [] self.types = [] def bind(self, value, type): self.values.append(value) self.types.append(type) def execute(self): if self.plan == None: self.plan = QueryPlanner.prepare(self.query,self.types) return QueryPlanner.execute(self.plan,self.values) def clear(self): self.types = [] self.values = []
Получаем два очень удобных класса, которые всю грязную работу сделают за нас. Вот как будет выглядеть пример выше с их использованием:
CREATE OR REPLACE FUNCTION saved_plan() RETURNS void AS $$ from pgsql import QueryPlanner,Query QueryPlanner(plpy, SD) q = Query("SELECT last_name FROM my_users WHERE first_name = $1") #some code here... q.bind("Вася", text) q.execute() for i in range(0,rv.nrows()): #operate on rv[i]... # rest of function $$ LANGUAGE 'plpythonu'
С виду кажется что строк столько же. Когда количество запросов в функции вырастет хотя бы до 5 вы сможете оценить эффективность этих классов
Методику написания своих модулей можно расширить не только на упрощение жизни при написании запросов, но и для вынесения “за скобки” общей логики всего проекта. Вместо того, чтобы громоздить ненужные функции в базе и тратить ресурсы на лишние переключения контекста их можно писать в родном окружении. Это проще и быстрее.
Функции на питоне резонно писать либо когда требуется функциональность, которая средствами PL/pgSQL не реализуема, либо реализация слишком трудоемкая. Так же стоит оценивать потерю производительности на переключение контекста.
Вот собственно все что я сегодня хотел вам сказать Этот пост не претендует на полноту описания PL/PythonU. Читайте документацию
Комментариев пока нет.
Ваш отзыв
Вы должны войти, чтобы оставлять комментарии.
Управление
WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.