46.6. 数据库访问#

46.6.1. 数据库访问函数
46.6.2. 捕获错误

PL/Python 语言模块自动导入一个名为plpy的 Python 模块。此模块中的函数和常量在 Python 代码中以plpy.*foo*的形式提供给您。

46.6.1. 数据库访问函数#

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 函数获取返回的行数。

结果对象有以下附加方法

nrows()

返回命令处理的行数。请注意，这并不一定与返回的行数相同。例如，UPDATE 命令将设置此值，但不会返回任何行（除非使用了 RETURNING）。

status()

SPI_execute() 返回值。

colnames() coltypes() coltypmods()

分别返回列名列表、列类型 OID 列表和列的类型特定类型修饰符列表。

当对未生成结果集的命令的结果对象（例如没有 RETURNING 的 UPDATE 或 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 数据类型之间进行转换，如第 46.2 节中所述。

当您使用 PL/Python 模块准备计划时，它会自动保存。阅读 SPI 文档（第 47 章）以了解这意味着什么。为了在函数调用中有效地使用它，需要使用持久存储字典 SD 或 GD（请参阅第 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 就会开始返回一个空结果对象。游标对象还提供一个迭代器接口，一次生成一行，直到所有行都用尽。通过这种方式获取的数据不会作为结果对象返回，而是作为字典返回，每个字典对应一个结果行。

处理来自大型表的数据的两种方法的示例是

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 创建的对象与 Python 数据库 API 规范中定义的 DB-API 游标混淆。除了名称之外，它们没有任何共同点。

46.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为每个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”错误代码的字符串值。此方法提供了大致相同的功能

46.6. 数据库访问# ​

46.6.1. 数据库访问函数# ​

提示

46.6.2. 捕获错误# ​

46.6. 数据库访问#

46.6.1. 数据库访问函数#

46.6.2. 捕获错误#