codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 The PL/Python language module automatically imports a Python module4 called <code class="literal">plpy</code>. The functions and constants in5 this module are available to you in the Python code as6 <code class="literal">plpy.<em class="replaceable"><code>foo</code></em></code>.7 </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>8 The <code class="literal">plpy</code> module provides several functions to execute9 database commands:10 </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>11 Calling <code class="function">plpy.execute</code> with a query string and an12 optional row limit argument causes that query to be run and the result to13 be returned in a result object.14 </p><p>15 If <em class="replaceable"><code>limit</code></em> is specified and is greater than16 zero, then <code class="function">plpy.execute</code> retrieves at17 most <em class="replaceable"><code>limit</code></em> rows, much as if the query18 included a <code class="literal">LIMIT</code>19 clause. Omitting <em class="replaceable"><code>limit</code></em> or specifying it as20 zero results in no row limit.21 </p><p>22 The result object emulates a list or dictionary object. The result23 object can be accessed by row number and column name. For example:24</p><pre class="programlisting">25rv = plpy.execute("SELECT * FROM my_table", 5)26</pre><p>27 returns up to 5 rows from <code class="literal">my_table</code>. If28 <code class="literal">my_table</code> has a column29 <code class="literal">my_column</code>, it would be accessed as:30</p><pre class="programlisting">31foo = rv[i]["my_column"]32</pre><p>33 The number of rows returned can be obtained using the built-in34 <code class="function">len</code> function.35 </p><p>36 The result object has these additional methods:37 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal"><code class="function">nrows</code>()</code></span></dt><dd><p>38 Returns the number of rows processed by the command. Note that this39 is not necessarily the same as the number of rows returned. For40 example, an <code class="command">UPDATE</code> command will set this value but41 won't return any rows (unless <code class="literal">RETURNING</code> is used).42 </p></dd><dt><span class="term"><code class="literal"><code class="function">status</code>()</code></span></dt><dd><p>43 The <code class="function">SPI_execute()</code> return value.44 </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>45 Return a list of column names, list of column type OIDs, and list of46 type-specific type modifiers for the columns, respectively.47 </p><p>48 These methods raise an exception when called on a result object from49 a command that did not produce a result set, e.g.,50 <code class="command">UPDATE</code> without <code class="literal">RETURNING</code>, or51 <code class="command">DROP TABLE</code>. But it is OK to use these methods on52 a result set containing zero rows.53 </p></dd><dt><span class="term"><code class="literal"><code class="function">__str__</code>()</code></span></dt><dd><p>54 The standard <code class="literal">__str__</code> method is defined so that it55 is possible for example to debug query execution results56 using <code class="literal">plpy.debug(rv)</code>.57 </p></dd></dl></div><p>58 </p><p>59 The result object can be modified.60 </p><p>61 Note that calling <code class="literal">plpy.execute</code> will cause the entire62 result set to be read into memory. Only use that function when you are63 sure that the result set will be relatively small. If you don't want to64 risk excessive memory usage when fetching large results,65 use <code class="literal">plpy.cursor</code> rather66 than <code class="literal">plpy.execute</code>.67 </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>68 <a id="id-1.8.11.14.3.3.2.3.1.1" class="indexterm"></a>69 <code class="function">plpy.prepare</code> prepares the execution plan for a70 query. It is called with a query string and a list of parameter types,71 if you have parameter references in the query. For example:72</p><pre class="programlisting">73plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])74</pre><p>75 <code class="literal">text</code> is the type of the variable you will be passing76 for <code class="literal">$1</code>. The second argument is optional if you don't77 want to pass any parameters to the query.78 </p><p>79 After preparing a statement, you use a variant of the80 function <code class="function">plpy.execute</code> to run it:81</p><pre class="programlisting">82rv = plpy.execute(plan, ["name"], 5)83</pre><p>84 Pass the plan as the first argument (instead of the query string), and a85 list of values to substitute into the query as the second argument. The86 second argument is optional if the query does not expect any parameters.87 The third argument is the optional row limit as before.88 </p><p>89 Alternatively, you can call the <code class="function">execute</code> method on90 the plan object:91</p><pre class="programlisting">92rv = plan.execute(["name"], 5)93</pre><p>94 </p><p>95 Query parameters and result row fields are converted between PostgreSQL96 and Python data types as described in <a class="xref" href="plpython-data.html" title="46.2. Data Values">Section 46.2</a>.97 </p><p>98 When you prepare a plan using the PL/Python module it is automatically99 saved. Read the SPI documentation (<a class="xref" href="spi.html" title="Chapter 47. Server Programming Interface">Chapter 47</a>) for a100 description of what this means. In order to make effective use of this101 across function calls one needs to use one of the persistent storage102 dictionaries <code class="literal">SD</code> or <code class="literal">GD</code> (see103 <a class="xref" href="plpython-sharing.html" title="46.3. Sharing Data">Section 46.3</a>). For example:104</p><pre class="programlisting">105CREATE FUNCTION usesavedplan() RETURNS trigger AS $$106 if "plan" in SD:107 plan = SD["plan"]108 else:109 plan = plpy.prepare("SELECT 1")110 SD["plan"] = plan111 # rest of function112$$ LANGUAGE plpython3u;113</pre><p>114 </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>115 The <code class="literal">plpy.cursor</code> function accepts the same arguments116 as <code class="literal">plpy.execute</code> (except for the row limit) and returns117 a cursor object, which allows you to process large result sets in smaller118 chunks. As with <code class="literal">plpy.execute</code>, either a query string119 or a plan object along with a list of arguments can be used, or120 the <code class="function">cursor</code> function can be called as a method of121 the plan object.122 </p><p>123 The cursor object provides a <code class="literal">fetch</code> method that accepts124 an integer parameter and returns a result object. Each time you125 call <code class="literal">fetch</code>, the returned object will contain the next126 batch of rows, never larger than the parameter value. Once all rows are127 exhausted, <code class="literal">fetch</code> starts returning an empty result128 object. Cursor objects also provide an129 <a class="ulink" href="https://docs.python.org/library/stdtypes.html#iterator-types" target="_top">iterator130 interface</a>, yielding one row at a time until all rows are131 exhausted. Data fetched that way is not returned as result objects, but132 rather as dictionaries, each dictionary corresponding to a single result133 row.134 </p><p>135 An example of two ways of processing data from a large table is:136</p><pre class="programlisting">137CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$138odd = 0139for row in plpy.cursor("select num from largetable"):140 if row['num'] % 2:141 odd += 1142return odd143$$ LANGUAGE plpython3u;144 145CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$146odd = 0147cursor = plpy.cursor("select num from largetable")148while True:149 rows = cursor.fetch(batch_size)150 if not rows:151 break152 for row in rows:153 if row['num'] % 2:154 odd += 1155return odd156$$ LANGUAGE plpython3u;157 158CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$159odd = 0160plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])161rows = list(plpy.cursor(plan, [2])) # or: = list(plan.cursor([2]))162 163return len(rows)164$$ LANGUAGE plpython3u;165</pre><p>166 </p><p>167 Cursors are automatically disposed of. But if you want to explicitly168 release all resources held by a cursor, use the <code class="literal">close</code>169 method. Once closed, a cursor cannot be fetched from anymore.170 </p><div class="tip"><h3 class="title">Tip</h3><p>171 Do not confuse objects created by <code class="literal">plpy.cursor</code> with172 DB-API cursors as defined by173 the <a class="ulink" href="https://www.python.org/dev/peps/pep-0249/" target="_top">Python174 Database API specification</a>. They don't have anything in common175 except for the name.176 </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>177 Functions accessing the database might encounter errors, which178 will cause them to abort and raise an exception. Both179 <code class="function">plpy.execute</code> and180 <code class="function">plpy.prepare</code> can raise an instance of a subclass of181 <code class="literal">plpy.SPIError</code>, which by default will terminate182 the function. This error can be handled just like any other183 Python exception, by using the <code class="literal">try/except</code>184 construct. For example:185</p><pre class="programlisting">186CREATE FUNCTION try_adding_joe() RETURNS text AS $$187 try:188 plpy.execute("INSERT INTO users(username) VALUES ('joe')")189 except plpy.SPIError:190 return "something went wrong"191 else:192 return "Joe added"193$$ LANGUAGE plpython3u;194</pre><p>195 </p><p>196 The actual class of the exception being raised corresponds to the197 specific condition that caused the error. Refer198 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 possible199 conditions. The module200 <code class="literal">plpy.spiexceptions</code> defines an exception class201 for each <span class="productname">PostgreSQL</span> condition, deriving202 their names from the condition name. For203 instance, <code class="literal">division_by_zero</code>204 becomes <code class="literal">DivisionByZero</code>, <code class="literal">unique_violation</code>205 becomes <code class="literal">UniqueViolation</code>, <code class="literal">fdw_error</code>206 becomes <code class="literal">FdwError</code>, and so on. Each of these207 exception classes inherits from <code class="literal">SPIError</code>. This208 separation makes it easier to handle specific errors, for209 instance:210</p><pre class="programlisting">211CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$212from plpy import spiexceptions213try:214 plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])215 plpy.execute(plan, [numerator, denominator])216except spiexceptions.DivisionByZero:217 return "denominator cannot equal zero"218except spiexceptions.UniqueViolation:219 return "already have that fraction"220except plpy.SPIError as e:221 return "other error, SQLSTATE %s" % e.sqlstate222else:223 return "fraction inserted"224$$ LANGUAGE plpython3u;225</pre><p>226 Note that because all exceptions from227 the <code class="literal">plpy.spiexceptions</code> module inherit228 from <code class="literal">SPIError</code>, an <code class="literal">except</code>229 clause handling it will catch any database access error.230 </p><p>231 As an alternative way of handling different error conditions, you232 can catch the <code class="literal">SPIError</code> exception and determine233 the specific error condition inside the <code class="literal">except</code>234 block by looking at the <code class="literal">sqlstate</code> attribute of235 the exception object. This attribute is a string value containing236 the <span class="quote">“<span class="quote">SQLSTATE</span>”</span> error code. This approach provides237 approximately the same functionality238 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.7. Explicit Subtransactions</td></tr></table></div></body></html>