Postgresql 中文操作指南

46.6. Database Access #

PL/Python 语言模块自动导入一个名为 plpy 的 Python 模块。此模块中的函数和常量以 plpy._foo_ 的形式在 Python 代码中可用。

46.6.1. Database Access Functions #

plpy 模块提供了用于执行数据库命令的几个函数：

plpy._execute(query [, limit])_
- 使用查询字符串和可选的行限制参数调用 plpy.execute 将导致查询运行并以结果对象形式返回结果。
- 如果指定了 limit 并且大于零，则 plpy.execute 最多检索 limit 行，就像查询包括 LIMIT 子句一样。省略 limit 或将它指定为零会导致没有行限制。
- 结果对象模拟了一个列表或字典对象。可以通过行号和列名访问结果对象。例如：

rv = plpy.execute("SELECT * FROM my_table", 5)

从 my_table 返回多达 5 行。如果 my_table 有一列 my_column，则可以将它访问为：

foo = rv[i]["my_column"]

可以使用内置 len 函数获取返回的行数。
结果对象具有以下附加方法：
结果对象可以修改。
请注意，调用 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)

查询参数和结果行字段按照 Section 46.2 中的说明在 PostgreSQL 和 Python 数据类型之间转换。
当使用 PL/Python 模块准备计划时，它会自动保存。阅读 SPI 文档（ Chapter 47 ）以了解这意味着什么。为了在函数调用中有效使用此功能，需要使用持久性存储字典 SD 或 GD（请参阅 Section 46.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 就会开始返回一个空结果对象。游标对象还提供了一个 iterator interface，一次生成一行，直到用尽所有行。以这种方式获取的数据不会作为结果对象返回，而是作为字典返回，每个字典对应一行结果。
- 以下是处理大型表格数据中两种方法的示例：

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 方法。关闭后，不能再从游标中获取。
- _nrows()_
返回命令处理的行号。请注意，这并不一定与返回的行号相同。例如， UPDATE 命令将设置此值，但不会返回任何行（除非使用 RETURNING ）。
- _status()_
The SPI_execute() return value.
- colnames()coltypes_()coltypmods()_
分别返回列的名称列表、列类型 OID 列表以及列的类型特定类型修饰符列表。
这些方法在没有生成结果集的命令结果对象上调用时会引发异常，例如，UPDATE 不带 RETURNING 或 DROP TABLE。但在包含 0 行的结果集上使用这些方法是可以的。
- str()
标准 str 方法被定义为这样，即可以使用 plpy.debug(rv) 调试查询执行结果。

Tip

不要将 plpy.cursor 创建的对象与 Python Database API specification 中定义的 DB-API 游标混淆。除了名称之外，它们没有任何共同之处。

46.6.2. Trapping Errors #

访问数据库的函数可能会遇到错误，这将导致它们中止并引发异常。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;

引发的异常的实际类与导致错误的特定条件相对应。请参阅 Table 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 异常，并通过查看异常对象的 sqlstate 属性来确定 except 块内的特定错误条件。此属性是一个字符串值，其中包含“SQLSTATE”错误代码。此方法提供了大约相同的功能