PL/Python 语言模块会自动导入一个被称为plpy
的 Python 模块。这个模块中的函数和常量在 Python 代码中可以用plpy.
这样的方式访问。
foo
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 列表以及列的类型相关的类型修饰符列表。
在来自于不产生结果集合的命令的结果对象上调用这些方法会产生异常,例如不带RETURNING
的UPDATE
或者DROP TABLE
。但是在包含的行数为零的结果集合上使用这些方法是 OK 的。
__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)
查询参数以及结果行域会按照第 45.3 节中所述在 PostgreSQL 和 Python 数据类型之间转换。
在使用 PL/Python 模块准备一个计划时,它会被自动保存。其含义可以阅读 SPI 文档(第 46 章)。为了有效在函数调用之间利用这种特性,需要使用一种持久化存储字典SD
或者GD
(见第 45.4 节)。例如:
CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ if "plan" in SD: plan = SD["plan"] else: plan = plpy.prepare("SELECT 1") SD["plan"] = plan # 函数的剩余部分 $$ LANGUAGE plpythonu;
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 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
方法。一旦被关闭,就再也不能从游标中取得数据。
不要把plpy.cursor
创建的游标对象与Python Database API specification定义的 DB-API 游标弄混。除了名字之外,它们之间没有任何共同点。
访问数据库的函数可能会碰到错误,这将导致函数中止并且产生异常。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;
产生的异常的实际类对应于特定的导致该错误的情况。可能的情况列表请参考表 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
异常并且在except
块中通过查看异常对象的sqlstate
属性来判断错误情况。这种属性是包含着“SQLSTATE”错误代码的一个字符串值。这种方法提供了近乎相同的功能