Перейти к основному содержимому

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

примечание

Эта страница переведена при помощи нейросети GigaChat.

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

Функции обращения к базе данных​

Модуль 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() – возвращает список имен столбцов, список идентификаторов типов столбца OID и список модификаторов типа, специфичных для типа, соответственно.

    Эти методы вызывают исключение при вызове объекта результата из команды, которая не произвела набор результатов, например, 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, как описано в разделе «Значения данных».

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

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. У них нет ничего общего, кроме названия.

Захват ошибок​

Функции доступа к базе данных могут столкнуться с ошибками, которые приведут к их прерыванию и возбуждению исключения. Как 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 определяет класс исключений для каждого условия PostgreSQL, производя свои имена от имени условия. Например, 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". Этот подход предоставляет примерно ту же функциональность.