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». Конечный результат при таком подходе примерно тот же.