44.6. Доступ к базе данных#

44.6. Доступ к базе данных
44.6. Доступ к базе данных
Назад НаверхГлава 44. PL/Python — Язык программирования Python для процедурного программированияНачало Далее

44.6. Доступ к базе данных

44.6.1. Функции доступа к базе данных
44.6.2. Перехват ошибок

Модуль языка PL/Python автоматически импортирует модуль Python с именем plpy. Функции и константы в этом модуле доступны вам в коде Python как plpy.foo.

44.6.1. Функции доступа к базе данных

Модуль plpy предоставляет несколько функций для выполнения команд базы данных:

plpy.execute(query [, limit])

Вызов функции plpy.execute с запросом и необязательным аргументом ограничения количества строк приводит к выполнению этого запроса и возвращению результата в виде объекта результата.

Если указано limit и оно больше нуля, то plpy.execute извлекает максимум limit строк, как если бы в запросе было указано предложение LIMIT. Пропуск 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()

Вернуть список имен столбцов, список идентификаторов типов столбцов и список типовых модификаторов типов для столбцов соответственно.

Эти методы вызывают исключение при вызове на объекте результата команды, которая не произвела набор результатов, например, UPDATE без RETURNING или DROP TABLE. Но можно использовать эти методы на наборе результатов, содержащем ноль строк.

__str__()

Сохраняется стандартный метод __str__, определенный таким образом, что, например, можно отладить результаты выполнения запроса, используя plpy.debug(rv).

Объект результата может быть изменен.

Обратите внимание, что вызов plpy.execute приведет к чтению всего набора результатов в память. Используйте эту функцию только тогда, когда вы уверены, что набор результатов будет относительно небольшим. Если вы не хотите рисковать избыточным использованием памяти при получении больших результатов, используйте plpy.cursor вместо plpy.execute.

plpy.prepare(query [, argtypes])
plpy.execute(plan [, arguments [, limit]])

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, как описано в Раздел 44.2.

Когда вы подготавливаете план с использованием модуля PL/Python, он автоматически сохраняется. Прочитайте документацию по SPI (Глава 45) для описания того, что это означает. Чтобы эффективно использовать это при вызове функций, необходимо использовать один из постоянных словарей хранения SD или GD (см. Раздел 44.3). Например: 

CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
    if "plan" in SD:
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT 1")
        SD["plan"] = plan
    # rest of function
$$ LANGUAGE plpython3u;

plpy.cursor(query)
plpy.cursor(plan [, arguments])

Функция 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 plpython3u;

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 plpython3u;

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]))  # or: = list(plan.cursor([2]))

return len(rows)
$$ LANGUAGE plpython3u;

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

Подсказка

Не путайте объекты, созданные с помощью plpy.cursor, с курсорами DB-API, определенными в спецификации Python Database API. Они не имеют ничего общего, кроме названия.

44.6.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 plpython3u;

Фактический класс возникающего исключения соответствует конкретному условию, вызвавшему ошибку. См. Таблица A.1 для списка возможных условий. Модуль plpy.spiexceptions определяет класс исключения для каждого условия Tantor SE, генерируя их имена из имен условия. Например, 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 plpython3u;

Обратите внимание, что поскольку все исключения из модуля plpy.spiexceptions наследуются от SPIError, блок except, обрабатывающий его, будет перехватывать любую ошибку доступа к базе данных.

Как альтернативный способ обработки различных условий ошибок, вы можете перехватить исключение SPIError и определить конкретное условие ошибки внутри блока except, просмотрев атрибут sqlstate объекта исключения. Этот атрибут является строковым значением, содержащим код ошибки “SQLSTATE”. Этот подход обеспечивает примерно ту же функциональность.

Назад Наверх Далее
44.5. Функции триггеров Начало 44.7. Явные подтранзакции