47.7. Обращение к базе данных
Исполнитель языка PL/Python автоматически импортирует модуль Python с именем plpy
. Вы в своём коде можете использовать функции и константы, объявленные в этом модуле, обращаясь к ним по именам вида plpy.
.имя
47.7.1. Функции обращения к базе данных
Модуль plpy
содержит различные функции для выполнения команд в базе данных:
plpy.
execute
(запрос
[,предел
])При вызове
plpy.execute
со строкой запроса и необязательным аргументом, ограничивающим число строк, выполняется заданный запрос, а то, что он выдаёт, возвращается в виде объекта результата.Если
предел
задан и больше нуля, тоplpy.execute
получает число строк, не превышающеепредел
, как если бы запрос включал предложениеLIMIT
. Без указанияпредела
или когда он равен нулю ограничение на количество строк снимается.Объект результата имитирует список или словарь. Получить из него данные можно по номеру строки и имени столбца. Например, команда:
rv = plpy.execute("SELECT * FROM my_table", 5)
вернёт не более 5 строк из отношения
my_table
. Если вmy_table
есть столбецmy_column
, к нему можно обратиться так:foo = rv[i]["my_column"]
Число возвращённых в этом объекте строк можно получить, воспользовавшись встроенной функцией
len
.Для объекта результата определены следующие дополнительные методы:
nrows
()Возвращает число строк, обработанных командой. Заметьте, что это число не обязательно будет равно числу возвращённых строк. Например, команда
UPDATE
устанавливает это значение, но не возвращает строк (без указанияRETURNING
).status
()Значение состояния, возвращённое
SPI_execute()
.colnames
()coltypes
()coltypmods
()Возвращают список имён столбцов, список OID типов столбцов и список модификаторов типа этих столбцов, соответственно.
Эти методы вызывают исключение, когда им передаётся объект, полученный от команды, не возвращающей результирующий набор, например,
UPDATE
безRETURNING
, либоDROP TABLE
. Но эти методы вполне можно использовать с результатом, содержащим ноль строк.__str__
()Стандартный метод
__str__
определён так, чтобы можно было, например, вывести отладочное сообщение с результатами запроса, вызвавplpy.debug(rv)
.
Объект результата может быть изменён.
Заметьте, что при вызове
plpy.execute
весь набор результатов будет прочитан в память. Эту функцию следует использовать, только если вы знаете, что набор будет относительно небольшим. Если вы хотите исключить риск переполнения памяти при выборке результатов большого объёма, используйтеplpy.cursor
вместоplpy.execute
.plpy.
prepare
(запрос
[,типы_аргументов
])plpy.
execute
(план
[,аргументы
[,предел
]])Функция
plpy.prepare
подготавливает план выполнения для запроса. Она вызывается со строкой запроса и списком типов параметров (если в запросе есть параметры). Например:plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
Здесь
text
представляет переменную, передаваемую в качестве параметра$1
. Второй аргумент необязателен, если запросу не нужно передавать никакие параметры.Чтобы запустить подготовленный оператор на выполнение, используйте вариацию функции
plpy.execute
:rv = plpy.execute(plan, ["name"], 5)
Передайте план в первом аргументе (вместо строки запроса), а список значений, которые будут подставлены в запрос, — во втором. Второй аргумент можно опустить, если запрос не принимает никакие параметры. Третий аргумент, как и раньше, задаёт необязательное ограничение максимального числа строк.
Вы также можете вызвать метод
execute
объекта плана:rv = plan.execute(["name"], 5)
Параметры запросов и поля строк результата преобразуются между типами данных PostgreSQL и Python как описано в Разделе 47.3.
Когда вы подготавливаете план, используя модуль PL/Python, он сохраняется автоматически. Что это означает, вы можете узнать в документации SPI (Глава 48). Чтобы эффективно использовать это в нескольких вызовах функции, может потребоваться применить словарь постоянного хранения
SD
илиGD
(см. Раздел 47.4). Например:CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ if "plan" in SD: plan = SD["plan"] else: plan = plpy.prepare("SELECT 1") SD["plan"] = plan # остальной код функции $$ LANGUAGE plpythonu;
plpy.
cursor
(запрос
)plpy.
cursor
(план
[,аргументы
])Функция
plpy.cursor
принимает те же аргументы, что иplpy.execute
(кроме ограничения строк) и возвращает объект курсора, который позволяет обрабатывать объёмные наборы результатов небольшими порциями. Как иplpy.execute
, этой функции можно передать строку запроса или объект плана со списком аргументов, а можно вызывать функциюcursor
как метод объекта плана.Объект курсора реализует метод
fetch
, который принимает целочисленный параметр и возвращает объект результата. При каждом следующем вызовеfetch
возвращаемый объект будет содержать следующий набор строк, в количестве, не превышающем значение параметра. Когда строки закончатся,fetch
начнёт возвращать пустой объект результата. Объекты курсора также предоставляют интерфейс итератора, выдающий по строке за один раз, пока не будут выданы все строки. Данные, выбираемые таким образом, возвращаются не как объекты результата, а как словари (одной строке результата соответствует один словарь).Следующий пример демонстрирует обработку содержимого большой таблицы двумя способами:
CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$ odd = 0 for row in plpy.cursor("select num from largetable"): if row['num'] % 2: odd += 1 return odd $$ LANGUAGE plpythonu; CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$ odd = 0 cursor = plpy.cursor("select num from largetable") while True: rows = cursor.fetch(batch_size) if not rows: break for row in rows: if row['num'] % 2: odd += 1 return odd $$ LANGUAGE plpythonu; CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$ odd = 0 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"]) rows = list(plpy.cursor(plan, [2])) # или: = list(plan.cursor([2])) return len(rows) $$ LANGUAGE plpythonu;
Курсоры ликвидируются автоматически. Но если вы хотите явно освободить все ресурсы, занятые курсором, вызовите метод
close
. Продолжать получать данные через курсор, который был закрыт, нельзя.Подсказка
Не путайте объекты, создаваемые функцией
plpy.cursor
, с курсорами DB-API, определёнными в спецификации API для работы с базами данных в Python. Они не имеют ничего общего, кроме имени.
47.7.2. Обработка ошибок
Функции, обращающиеся к базе данных, могут сталкиваться с ошибками, в результате которых они будут прерываться и вызывать исключение. Обе функции plpy.execute
и plpy.prepare
могут вызывать экземпляр подкласса исключения plpy.SPIError
, которое по умолчание прекращает выполнение функции. Эту ошибку можно обработать, как и любое другое исключение в Python, применив конструкцию try/except
. Например:
CREATE FUNCTION try_adding_joe() RETURNS text AS $$ try: plpy.execute("INSERT INTO users(username) VALUES ('joe')") except plpy.SPIError: return "something went wrong" else: return "Joe added" $$ LANGUAGE plpythonu;
Фактический класс вызываемого исключения соответствует определённому условию возникновения ошибки. Список всех возможных условий приведён в Таблице A.1. В модуле plpy.spiexceptions
определяются классы исключений для каждого условия Postgres Pro, с именами, производными от имён условий. Например, имя division_by_zero
становится именем DivisionByZero
, unique_violation
— именем UniqueViolation
, fdw_error
— именем FdwError
и т. д. Все эти классы исключений наследуются от SPIError
. Такое разделение на классы упрощает обработку определённых ошибок, например:
CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$ from plpy import spiexceptions try: plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"]) plpy.execute(plan, [numerator, denominator]) except spiexceptions.DivisionByZero: return "denominator cannot equal zero" except spiexceptions.UniqueViolation: return "already have that fraction" except plpy.SPIError as e: return "other error, SQLSTATE %s" % e.sqlstate else: return "fraction inserted" $$ LANGUAGE plpythonu;
Заметьте, что так как все исключения из модуля plpy.spiexceptions
наследуются от исключения SPIError
, команда except
, обрабатывающая это исключение, будет перехватывать все ошибки при обращении к базе данных.
В качестве другого варианта обработки различных условий ошибок, вы можете перехватывать исключение SPIError
и определять конкретное условие ошибки внутри блока except
по значению атрибута sqlstate
объекта исключения. Этот атрибут содержит строку с кодом ошибки «SQLSTATE». Конечный результат при таком подходе примерно тот же.