1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>46.6. Database Access</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpython-trigger.html" title="46.5. Trigger Functions" /><link rel="next" href="plpython-subtransaction.html" title="46.7. Explicit Subtransactions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">46.6. Database Access</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython-trigger.html" title="46.5. Trigger Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><th width="60%" align="center">Chapter 46. PL/Python — Python Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpython-subtransaction.html" title="46.7. Explicit Subtransactions">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPYTHON-DATABASE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">46.6. Database Access <a href="#PLPYTHON-DATABASE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpython-database.html#PLPYTHON-DATABASE-ACCESS-FUNCS">46.6.1. Database Access Functions</a></span></dt><dt><span class="sect2"><a href="plpython-database.html#PLPYTHON-TRAPPING">46.6.2. Trapping Errors</a></span></dt></dl></div><p>
The PL/Python language module automatically imports a Python module
called <code class="literal">plpy</code>. The functions and constants in
this module are available to you in the Python code as
<code class="literal">plpy.<em class="replaceable"><code>foo</code></em></code>.
</p><div class="sect2" id="PLPYTHON-DATABASE-ACCESS-FUNCS"><div class="titlepage"><div><div><h3 class="title">46.6.1. Database Access Functions <a href="#PLPYTHON-DATABASE-ACCESS-FUNCS" class="id_link">#</a></h3></div></div></div><p>
The <code class="literal">plpy</code> module provides several functions to execute
database commands:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">plpy.<code class="function">execute</code>(<em class="replaceable"><code>query</code></em> [, <em class="replaceable"><code>limit</code></em>])</code></span></dt><dd><p>
Calling <code class="function">plpy.execute</code> with a query string and an
optional row limit argument causes that query to be run and the result to
be returned in a result object.
</p><p>
If <em class="replaceable"><code>limit</code></em> is specified and is greater than
zero, then <code class="function">plpy.execute</code> retrieves at
most <em class="replaceable"><code>limit</code></em> rows, much as if the query
included a <code class="literal">LIMIT</code>
clause. Omitting <em class="replaceable"><code>limit</code></em> or specifying it as
zero results in no row limit.
</p><p>
The result object emulates a list or dictionary object. The result
object can be accessed by row number and column name. For example:
</p><pre class="programlisting">
rv = plpy.execute("SELECT * FROM my_table", 5)
</pre><p>
returns up to 5 rows from <code class="literal">my_table</code>. If
<code class="literal">my_table</code> has a column
<code class="literal">my_column</code>, it would be accessed as:
</p><pre class="programlisting">
foo = rv[i]["my_column"]
</pre><p>
The number of rows returned can be obtained using the built-in
<code class="function">len</code> function.
</p><p>
The result object has these additional methods:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal"><code class="function">nrows</code>()</code></span></dt><dd><p>
Returns the number of rows processed by the command. Note that this
is not necessarily the same as the number of rows returned. For
example, an <code class="command">UPDATE</code> command will set this value but
won't return any rows (unless <code class="literal">RETURNING</code> is used).
</p></dd><dt><span class="term"><code class="literal"><code class="function">status</code>()</code></span></dt><dd><p>
The <code class="function">SPI_execute()</code> return value.
</p></dd><dt><span class="term"><code class="literal"><code class="function">colnames</code>()</code><br /></span><span class="term"><code class="literal"><code class="function">coltypes</code>()</code><br /></span><span class="term"><code class="literal"><code class="function">coltypmods</code>()</code></span></dt><dd><p>
Return a list of column names, list of column type OIDs, and list of
type-specific type modifiers for the columns, respectively.
</p><p>
These methods raise an exception when called on a result object from
a command that did not produce a result set, e.g.,
<code class="command">UPDATE</code> without <code class="literal">RETURNING</code>, or
<code class="command">DROP TABLE</code>. But it is OK to use these methods on
a result set containing zero rows.
</p></dd><dt><span class="term"><code class="literal"><code class="function">__str__</code>()</code></span></dt><dd><p>
The standard <code class="literal">__str__</code> method is defined so that it
is possible for example to debug query execution results
using <code class="literal">plpy.debug(rv)</code>.
</p></dd></dl></div><p>
</p><p>
The result object can be modified.
</p><p>
Note that calling <code class="literal">plpy.execute</code> will cause the entire
result set to be read into memory. Only use that function when you are
sure that the result set will be relatively small. If you don't want to
risk excessive memory usage when fetching large results,
use <code class="literal">plpy.cursor</code> rather
than <code class="literal">plpy.execute</code>.
</p></dd><dt><span class="term"><code class="literal">plpy.<code class="function">prepare</code>(<em class="replaceable"><code>query</code></em> [, <em class="replaceable"><code>argtypes</code></em>])</code><br /></span><span class="term"><code class="literal">plpy.<code class="function">execute</code>(<em class="replaceable"><code>plan</code></em> [, <em class="replaceable"><code>arguments</code></em> [, <em class="replaceable"><code>limit</code></em>]])</code></span></dt><dd><p>
<a id="id-1.8.11.14.3.3.2.3.1.1" class="indexterm"></a>
<code class="function">plpy.prepare</code> prepares the execution plan for a
query. It is called with a query string and a list of parameter types,
if you have parameter references in the query. For example:
</p><pre class="programlisting">
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
</pre><p>
<code class="literal">text</code> is the type of the variable you will be passing
for <code class="literal">$1</code>. The second argument is optional if you don't
want to pass any parameters to the query.
</p><p>
After preparing a statement, you use a variant of the
function <code class="function">plpy.execute</code> to run it:
</p><pre class="programlisting">
rv = plpy.execute(plan, ["name"], 5)
</pre><p>
Pass the plan as the first argument (instead of the query string), and a
list of values to substitute into the query as the second argument. The
second argument is optional if the query does not expect any parameters.
The third argument is the optional row limit as before.
</p><p>
Alternatively, you can call the <code class="function">execute</code> method on
the plan object:
</p><pre class="programlisting">
rv = plan.execute(["name"], 5)
</pre><p>
</p><p>
Query parameters and result row fields are converted between PostgreSQL
and Python data types as described in <a class="xref" href="plpython-data.html" title="46.2. Data Values">Section 46.2</a>.
</p><p>
When you prepare a plan using the PL/Python module it is automatically
saved. Read the SPI documentation (<a class="xref" href="spi.html" title="Chapter 47. Server Programming Interface">Chapter 47</a>) for a
description of what this means. In order to make effective use of this
across function calls one needs to use one of the persistent storage
dictionaries <code class="literal">SD</code> or <code class="literal">GD</code> (see
<a class="xref" href="plpython-sharing.html" title="46.3. Sharing Data">Section 46.3</a>). For example:
</p><pre class="programlisting">
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;
</pre><p>
</p></dd><dt><span class="term"><code class="literal">plpy.<code class="function">cursor</code>(<em class="replaceable"><code>query</code></em>)</code><br /></span><span class="term"><code class="literal">plpy.<code class="function">cursor</code>(<em class="replaceable"><code>plan</code></em> [, <em class="replaceable"><code>arguments</code></em>])</code></span></dt><dd><p>
The <code class="literal">plpy.cursor</code> function accepts the same arguments
as <code class="literal">plpy.execute</code> (except for the row limit) and returns
a cursor object, which allows you to process large result sets in smaller
chunks. As with <code class="literal">plpy.execute</code>, either a query string
or a plan object along with a list of arguments can be used, or
the <code class="function">cursor</code> function can be called as a method of
the plan object.
</p><p>
The cursor object provides a <code class="literal">fetch</code> method that accepts
an integer parameter and returns a result object. Each time you
call <code class="literal">fetch</code>, the returned object will contain the next
batch of rows, never larger than the parameter value. Once all rows are
exhausted, <code class="literal">fetch</code> starts returning an empty result
object. Cursor objects also provide an
<a class="ulink" href="https://docs.python.org/library/stdtypes.html#iterator-types" target="_top">iterator
interface</a>, yielding one row at a time until all rows are
exhausted. Data fetched that way is not returned as result objects, but
rather as dictionaries, each dictionary corresponding to a single result
row.
</p><p>
An example of two ways of processing data from a large table is:
</p><pre class="programlisting">
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;
</pre><p>
</p><p>
Cursors are automatically disposed of. But if you want to explicitly
release all resources held by a cursor, use the <code class="literal">close</code>
method. Once closed, a cursor cannot be fetched from anymore.
</p><div class="tip"><h3 class="title">Tip</h3><p>
Do not confuse objects created by <code class="literal">plpy.cursor</code> with
DB-API cursors as defined by
the <a class="ulink" href="https://www.python.org/dev/peps/pep-0249/" target="_top">Python
Database API specification</a>. They don't have anything in common
except for the name.
</p></div></dd></dl></div></div><div class="sect2" id="PLPYTHON-TRAPPING"><div class="titlepage"><div><div><h3 class="title">46.6.2. Trapping Errors <a href="#PLPYTHON-TRAPPING" class="id_link">#</a></h3></div></div></div><p>
Functions accessing the database might encounter errors, which
will cause them to abort and raise an exception. Both
<code class="function">plpy.execute</code> and
<code class="function">plpy.prepare</code> can raise an instance of a subclass of
<code class="literal">plpy.SPIError</code>, which by default will terminate
the function. This error can be handled just like any other
Python exception, by using the <code class="literal">try/except</code>
construct. For example:
</p><pre class="programlisting">
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;
</pre><p>
</p><p>
The actual class of the exception being raised corresponds to the
specific condition that caused the error. Refer
to <a class="xref" href="errcodes-appendix.html#ERRCODES-TABLE" title="Table A.1. PostgreSQL Error Codes">Table A.1</a> for a list of possible
conditions. The module
<code class="literal">plpy.spiexceptions</code> defines an exception class
for each <span class="productname">PostgreSQL</span> condition, deriving
their names from the condition name. For
instance, <code class="literal">division_by_zero</code>
becomes <code class="literal">DivisionByZero</code>, <code class="literal">unique_violation</code>
becomes <code class="literal">UniqueViolation</code>, <code class="literal">fdw_error</code>
becomes <code class="literal">FdwError</code>, and so on. Each of these
exception classes inherits from <code class="literal">SPIError</code>. This
separation makes it easier to handle specific errors, for
instance:
</p><pre class="programlisting">
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;
</pre><p>
Note that because all exceptions from
the <code class="literal">plpy.spiexceptions</code> module inherit
from <code class="literal">SPIError</code>, an <code class="literal">except</code>
clause handling it will catch any database access error.
</p><p>
As an alternative way of handling different error conditions, you
can catch the <code class="literal">SPIError</code> exception and determine
the specific error condition inside the <code class="literal">except</code>
block by looking at the <code class="literal">sqlstate</code> attribute of
the exception object. This attribute is a string value containing
the <span class="quote">“<span class="quote">SQLSTATE</span>”</span> error code. This approach provides
approximately the same functionality
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpython-trigger.html" title="46.5. Trigger Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpython-subtransaction.html" title="46.7. Explicit Subtransactions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">46.5. Trigger Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.7. Explicit Subtransactions</td></tr></table></div></body></html>
|
