Database Reference
In-Depth Information
Running queries in the database
If you have ever accessed a database in Python, you know that most database ad-
apters conform to a somewhat loose standard called
Python Database API Spe-
cification v2.0
or
DBAPI 2
for short.
The first thing you need to know about database access in PL/Python is that in-data-
base queries
do not
follow this API.
Running simple queries
Instead of using the standard API, there are just three functions for doing all data-
base access. There are two variants:
plpy.execute()
for running a query, and
plpy.prepare()
for turning query text into a query plan or a prepared query.
The simplest way to do a query is with:
res = plpy.execute(<query text>, [<row count>])
This takes a textual query and an optional row count, and returns a result object,
which emulates a list of dictionaries, one dictionary per row.
As an example, if you want to access a field
'name'
of the third row of the result,
you use:
res[2]['name']
The index is
2
and not
3
because Python lists are indexed starting from 0, so the first
row is
res[0]
, the second row
res[1]
, and so on.
Using prepared queries
In an ideal world this would be all that is needed, but
plpy.execute(query,
cnt)
has two shortcomings:
• It does not support parameters
