45.7. 数据库访问

• 45 .7.1. 数据库访问功能

• 45 .7.2. 陷阱错误

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

45 .7.1. 数据库访问功能

plpy模块提供了几种执行数据库命令的功能：

• plpy.execute(query [, max-rows])

  • 使用查询字符串和可选的行数限制参数调用plpy.execute会导致查询运行并在结果对象中返回结果。

结果对象模拟列表或字典对象。可以通过行号和列名访问结果对象。例如：

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 列表和列的类型特定类型修饰符列表。

当从未生成结果集的命令(例如UPDATE而不使用RETURNING或DROP TABLE)调用结果对象时，这些方法会引发异常。但是可以在包含零行的结果集上使用这些方法。

• __str__()

  • 定义了标准的__str__方法，以便例如可以使用plpy.debug(rv)调试查询执行结果。

可以修改结果对象。

请注意，调用plpy.execute将导致将整个结果集读取到内存中。仅在确定结果集相对较小时才使用该功能。如果您不希望在获取较大结果时冒用过多内存的风险，请使用plpy.cursor而不是plpy.execute。

• plpy.prepare(query [, argtypes])
plpy.execute(plan [, arguments [, max-rows]])

  • 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 45.3中所述，查询参数和结果行字段在 PostgreSQL 和 Python 数据类型之间转换。

使用 PL/Python 模块准备计划时，它会自动保存。阅读 SPI 文档(Chapter 46)，以获取有关此含义的描述。为了有效地跨函数调用使用此功能，需要使用持久性存储词典SD或GD(请参见Section 45.4)之一。例如：

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

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

return len(rows)
$$ LANGUAGE plpythonu;

游标会自动处理。但是，如果要显式释放游标持有的所有资源，请使用close方法。一旦关闭，便无法再获取游标。

45 .7.2. 陷阱错误

访问数据库的函数可能会遇到错误，这将导致它们中止并引发异常。 plpy.execute和plpy.prepare都可以引发plpy.SPIError子类的实例，默认情况下它将终止该函数。通过使用try/except构造，可以像处理任何其他 Python 异常一样处理此错误。例如：

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;

引发的异常的实际类别与导致错误的特定条件相对应。请参阅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, e:
    return "other error, SQLSTATE %s" % e.sqlstate
else:
    return "fraction inserted"
$$ LANGUAGE plpythonu;

请注意，由于plpy.spiexceptions模块的所有异常都继承自SPIError，因此处理它的except子句将捕获任何数据库访问错误。

作为处理不同错误条件的另一种方法，您可以捕获SPIError异常并通过查看异常对象的sqlstate属性来确定except块内的特定错误条件。此属性是包含“ SQLSTATE”错误代码的字符串值。这种方法提供了大致相同的功能