begriffs open source - ai-pg/blob - full-docs/html/plpython-database.html

   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>44.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="44.5. Trigger Functions" /><link rel="next" href="plpython-subtransaction.html" title="44.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">44.6. Database Access</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython-trigger.html" title="44.5. Trigger Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpython.html" title="Chapter 44. PL/Python — Python Procedural Language">Up</a></td><th width="60%" align="center">Chapter 44. PL/Python — Python Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpython-subtransaction.html" title="44.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">44.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">44.6.1. Database Access Functions</a></span></dt><dt><span class="sect2"><a href="plpython-database.html#PLPYTHON-TRAPPING">44.6.2. Trapping Errors</a></span></dt></dl></div><p>
   3    The PL/Python language module automatically imports a Python module
   4    called <code class="literal">plpy</code>.  The functions and constants in
   5    this module are available to you in the Python code as
   6    <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">44.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 execute
   9    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 an
  12       optional row limit argument causes that query to be run and the result to
  13       be returned in a result object.
  14      </p><p>
  15       If <em class="replaceable"><code>limit</code></em> is specified and is greater than
  16       zero, then <code class="function">plpy.execute</code> retrieves at
  17       most <em class="replaceable"><code>limit</code></em> rows, much as if the query
  18       included a <code class="literal">LIMIT</code>
  19       clause.  Omitting <em class="replaceable"><code>limit</code></em> or specifying it as
  20       zero results in no row limit.
  21      </p><p>
  22       The result object emulates a list or dictionary object.  The result
  23       object can be accessed by row number and column name.  For example:
  24 </p><pre class="programlisting">
  25 rv = plpy.execute("SELECT * FROM my_table", 5)
  26 </pre><p>
  27       returns up to 5 rows from <code class="literal">my_table</code>.  If
  28       <code class="literal">my_table</code> has a column
  29       <code class="literal">my_column</code>, it would be accessed as:
  30 </p><pre class="programlisting">
  31 foo = rv[i]["my_column"]
  32 </pre><p>
  33       The number of rows returned can be obtained using the built-in
  34       <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 this
  39           is not necessarily the same as the number of rows returned.  For
  40           example, an <code class="command">UPDATE</code> command will set this value but
  41           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 of
  46           type-specific type modifiers for the columns, respectively.
  47          </p><p>
  48           These methods raise an exception when called on a result object from
  49           a command that did not produce a result set, e.g.,
  50           <code class="command">UPDATE</code> without <code class="literal">RETURNING</code>, or
  51           <code class="command">DROP TABLE</code>.  But it is OK to use these methods on
  52           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 it
  55           is possible for example to debug query execution results
  56           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 entire
  62       result set to be read into memory.  Only use that function when you are
  63       sure that the result set will be relatively small.  If you don't want to
  64       risk excessive memory usage when fetching large results,
  65       use <code class="literal">plpy.cursor</code> rather
  66       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 a
  70       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">
  73 plan = 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 passing
  76       for <code class="literal">$1</code>.  The second argument is optional if you don't
  77       want to pass any parameters to the query.
  78      </p><p>
  79       After preparing a statement, you use a variant of the
  80       function <code class="function">plpy.execute</code> to run it:
  81 </p><pre class="programlisting">
  82 rv = plpy.execute(plan, ["name"], 5)
  83 </pre><p>
  84       Pass the plan as the first argument (instead of the query string), and a
  85       list of values to substitute into the query as the second argument.  The
  86       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 on
  90       the plan object:
  91 </p><pre class="programlisting">
  92 rv = plan.execute(["name"], 5)
  93 </pre><p>
  94      </p><p>
  95       Query parameters and result row fields are converted between PostgreSQL
  96       and Python data types as described in <a class="xref" href="plpython-data.html" title="44.2. Data Values">Section 44.2</a>.
  97      </p><p>
  98       When you prepare a plan using the PL/Python module it is automatically
  99       saved.  Read the SPI documentation (<a class="xref" href="spi.html" title="Chapter 45. Server Programming Interface">Chapter 45</a>) for a
 100       description of what this means.  In order to make effective use of this
 101       across function calls one needs to use one of the persistent storage
 102       dictionaries <code class="literal">SD</code> or <code class="literal">GD</code> (see
 103       <a class="xref" href="plpython-sharing.html" title="44.3. Sharing Data">Section 44.3</a>). For example:
 104 </p><pre class="programlisting">
 105 CREATE 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"] = plan
 111     # rest of function
 112 $$ 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 arguments
 116       as <code class="literal">plpy.execute</code> (except for the row limit) and returns
 117       a cursor object, which allows you to process large result sets in smaller
 118       chunks.  As with <code class="literal">plpy.execute</code>, either a query string
 119       or a plan object along with a list of arguments can be used, or
 120       the <code class="function">cursor</code> function can be called as a method of
 121       the plan object.
 122      </p><p>
 123       The cursor object provides a <code class="literal">fetch</code> method that accepts
 124       an integer parameter and returns a result object.  Each time you
 125       call <code class="literal">fetch</code>, the returned object will contain the next
 126       batch of rows, never larger than the parameter value.  Once all rows are
 127       exhausted, <code class="literal">fetch</code> starts returning an empty result
 128       object.  Cursor objects also provide an
 129       <a class="ulink" href="https://docs.python.org/library/stdtypes.html#iterator-types" target="_top">iterator
 130       interface</a>, yielding one row at a time until all rows are
 131       exhausted.  Data fetched that way is not returned as result objects, but
 132       rather as dictionaries, each dictionary corresponding to a single result
 133       row.
 134      </p><p>
 135       An example of two ways of processing data from a large table is:
 136 </p><pre class="programlisting">
 137 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
 138 odd = 0
 139 for row in plpy.cursor("select num from largetable"):
 140     if row['num'] % 2:
 141          odd += 1
 142 return odd
 143 $$ LANGUAGE plpython3u;
 144
 145 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
 146 odd = 0
 147 cursor = plpy.cursor("select num from largetable")
 148 while True:
 149     rows = cursor.fetch(batch_size)
 150     if not rows:
 151         break
 152     for row in rows:
 153         if row['num'] % 2:
 154             odd += 1
 155 return odd
 156 $$ LANGUAGE plpython3u;
 157
 158 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
 159 odd = 0
 160 plan = plpy.prepare("select num from largetable where num % $1 &lt;&gt; 0", ["integer"])
 161 rows = list(plpy.cursor(plan, [2]))  # or: = list(plan.cursor([2]))
 162
 163 return len(rows)
 164 $$ LANGUAGE plpython3u;
 165 </pre><p>
 166      </p><p>
 167       Cursors are automatically disposed of.  But if you want to explicitly
 168       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> with
 172         DB-API cursors as defined by
 173         the <a class="ulink" href="https://www.python.org/dev/peps/pep-0249/" target="_top">Python
 174         Database API specification</a>.  They don't have anything in common
 175         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">44.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, which
 178     will cause them to abort and raise an exception.  Both
 179     <code class="function">plpy.execute</code> and
 180     <code class="function">plpy.prepare</code> can raise an instance of a subclass of
 181     <code class="literal">plpy.SPIError</code>, which by default will terminate
 182     the function.  This error can be handled just like any other
 183     Python exception, by using the <code class="literal">try/except</code>
 184     construct.  For example:
 185 </p><pre class="programlisting">
 186 CREATE 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 the
 197     specific condition that caused the error.  Refer
 198     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
 199     conditions.  The module
 200     <code class="literal">plpy.spiexceptions</code> defines an exception class
 201     for each <span class="productname">PostgreSQL</span> condition, deriving
 202     their names from the condition name.  For
 203     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 these
 207     exception classes inherits from <code class="literal">SPIError</code>.  This
 208     separation makes it easier to handle specific errors, for
 209     instance:
 210 </p><pre class="programlisting">
 211 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
 212 from plpy import spiexceptions
 213 try:
 214     plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
 215     plpy.execute(plan, [numerator, denominator])
 216 except spiexceptions.DivisionByZero:
 217     return "denominator cannot equal zero"
 218 except spiexceptions.UniqueViolation:
 219     return "already have that fraction"
 220 except plpy.SPIError as e:
 221     return "other error, SQLSTATE %s" % e.sqlstate
 222 else:
 223     return "fraction inserted"
 224 $$ LANGUAGE plpython3u;
 225 </pre><p>
 226     Note that because all exceptions from
 227     the <code class="literal">plpy.spiexceptions</code> module inherit
 228     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, you
 232     can catch the <code class="literal">SPIError</code> exception and determine
 233     the specific error condition inside the <code class="literal">except</code>
 234     block by looking at the <code class="literal">sqlstate</code> attribute of
 235     the exception object.  This attribute is a string value containing
 236     the <span class="quote">“<span class="quote">SQLSTATE</span>”</span> error code.  This approach provides
 237     approximately the same functionality
 238    </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="44.5. Trigger Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpython.html" title="Chapter 44. PL/Python — Python Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpython-subtransaction.html" title="44.7. Explicit Subtransactions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">44.5. Trigger Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 44.7. Explicit Subtransactions</td></tr></table></div></body></html>