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

   1
   2 44.6. Database Access #
   3
   4    44.6.1. Database Access Functions
   5    44.6.2. Trapping Errors
   6
   7    The PL/Python language module automatically imports a Python module
   8    called plpy. The functions and constants in this module are available
   9    to you in the Python code as plpy.foo.
  10
  11 44.6.1. Database Access Functions #
  12
  13    The plpy module provides several functions to execute database
  14    commands:
  15
  16    plpy.execute(query [, limit])
  17           Calling plpy.execute with a query string and an optional row
  18           limit argument causes that query to be run and the result to be
  19           returned in a result object.
  20
  21           If limit is specified and is greater than zero, then
  22           plpy.execute retrieves at most limit rows, much as if the query
  23           included a LIMIT clause. Omitting limit or specifying it as zero
  24           results in no row limit.
  25
  26           The result object emulates a list or dictionary object. The
  27           result object can be accessed by row number and column name. For
  28           example:
  29
  30 rv = plpy.execute("SELECT * FROM my_table", 5)
  31
  32           returns up to 5 rows from my_table. If my_table has a column
  33           my_column, it would be accessed as:
  34
  35 foo = rv[i]["my_column"]
  36
  37           The number of rows returned can be obtained using the built-in
  38           len function.
  39
  40           The result object has these additional methods:
  41
  42         nrows()
  43                 Returns the number of rows processed by the command. Note
  44                 that this is not necessarily the same as the number of
  45                 rows returned. For example, an UPDATE command will set
  46                 this value but won't return any rows (unless RETURNING is
  47                 used).
  48
  49         status()
  50                 The SPI_execute() return value.
  51
  52         colnames()
  53                 coltypes()
  54                 coltypmods()
  55                 Return a list of column names, list of column type OIDs,
  56                 and list of type-specific type modifiers for the columns,
  57                 respectively.
  58
  59                 These methods raise an exception when called on a result
  60                 object from a command that did not produce a result set,
  61                 e.g., UPDATE without RETURNING, or DROP TABLE. But it is
  62                 OK to use these methods on a result set containing zero
  63                 rows.
  64
  65         __str__()
  66                 The standard __str__ method is defined so that it is
  67                 possible for example to debug query execution results
  68                 using plpy.debug(rv).
  69
  70           The result object can be modified.
  71
  72           Note that calling plpy.execute will cause the entire result set
  73           to be read into memory. Only use that function when you are sure
  74           that the result set will be relatively small. If you don't want
  75           to risk excessive memory usage when fetching large results, use
  76           plpy.cursor rather than plpy.execute.
  77
  78    plpy.prepare(query [, argtypes])
  79           plpy.execute(plan [, arguments [, limit]])
  80           plpy.prepare prepares the execution plan for a query. It is
  81           called with a query string and a list of parameter types, if you
  82           have parameter references in the query. For example:
  83
  84 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["te
  85 xt"])
  86
  87           text is the type of the variable you will be passing for $1. The
  88           second argument is optional if you don't want to pass any
  89           parameters to the query.
  90
  91           After preparing a statement, you use a variant of the function
  92           plpy.execute to run it:
  93
  94 rv = plpy.execute(plan, ["name"], 5)
  95
  96           Pass the plan as the first argument (instead of the query
  97           string), and a list of values to substitute into the query as
  98           the second argument. The second argument is optional if the
  99           query does not expect any parameters. The third argument is the
 100           optional row limit as before.
 101
 102           Alternatively, you can call the execute method on the plan
 103           object:
 104
 105 rv = plan.execute(["name"], 5)
 106
 107           Query parameters and result row fields are converted between
 108           PostgreSQL and Python data types as described in Section 44.2.
 109
 110           When you prepare a plan using the PL/Python module it is
 111           automatically saved. Read the SPI documentation (Chapter 45) for
 112           a description of what this means. In order to make effective use
 113           of this across function calls one needs to use one of the
 114           persistent storage dictionaries SD or GD (see Section 44.3). For
 115           example:
 116
 117 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
 118     if "plan" in SD:
 119         plan = SD["plan"]
 120     else:
 121         plan = plpy.prepare("SELECT 1")
 122         SD["plan"] = plan
 123     # rest of function
 124 $$ LANGUAGE plpython3u;
 125
 126    plpy.cursor(query)
 127           plpy.cursor(plan [, arguments])
 128           The plpy.cursor function accepts the same arguments as
 129           plpy.execute (except for the row limit) and returns a cursor
 130           object, which allows you to process large result sets in smaller
 131           chunks. As with plpy.execute, either a query string or a plan
 132           object along with a list of arguments can be used, or the cursor
 133           function can be called as a method of the plan object.
 134
 135           The cursor object provides a fetch method that accepts an
 136           integer parameter and returns a result object. Each time you
 137           call fetch, the returned object will contain the next batch of
 138           rows, never larger than the parameter value. Once all rows are
 139           exhausted, fetch starts returning an empty result object. Cursor
 140           objects also provide an iterator interface, yielding one row at
 141           a time until all rows are exhausted. Data fetched that way is
 142           not returned as result objects, but rather as dictionaries, each
 143           dictionary corresponding to a single result row.
 144
 145           An example of two ways of processing data from a large table is:
 146
 147 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
 148 odd = 0
 149 for row in plpy.cursor("select num from largetable"):
 150     if row['num'] % 2:
 151          odd += 1
 152 return odd
 153 $$ LANGUAGE plpython3u;
 154
 155 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
 156 odd = 0
 157 cursor = plpy.cursor("select num from largetable")
 158 while True:
 159     rows = cursor.fetch(batch_size)
 160     if not rows:
 161         break
 162     for row in rows:
 163         if row['num'] % 2:
 164             odd += 1
 165 return odd
 166 $$ LANGUAGE plpython3u;
 167
 168 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
 169 odd = 0
 170 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"
 171 ])
 172 rows = list(plpy.cursor(plan, [2]))  # or: = list(plan.cursor([2]))
 173
 174 return len(rows)
 175 $$ LANGUAGE plpython3u;
 176
 177           Cursors are automatically disposed of. But if you want to
 178           explicitly release all resources held by a cursor, use the close
 179           method. Once closed, a cursor cannot be fetched from anymore.
 180
 181 Tip
 182
 183           Do not confuse objects created by plpy.cursor with DB-API
 184           cursors as defined by the Python Database API specification.
 185           They don't have anything in common except for the name.
 186
 187 44.6.2. Trapping Errors #
 188
 189    Functions accessing the database might encounter errors, which will
 190    cause them to abort and raise an exception. Both plpy.execute and
 191    plpy.prepare can raise an instance of a subclass of plpy.SPIError,
 192    which by default will terminate the function. This error can be handled
 193    just like any other Python exception, by using the try/except
 194    construct. For example:
 195 CREATE FUNCTION try_adding_joe() RETURNS text AS $$
 196     try:
 197         plpy.execute("INSERT INTO users(username) VALUES ('joe')")
 198     except plpy.SPIError:
 199         return "something went wrong"
 200     else:
 201         return "Joe added"
 202 $$ LANGUAGE plpython3u;
 203
 204    The actual class of the exception being raised corresponds to the
 205    specific condition that caused the error. Refer to Table A.1 for a list
 206    of possible conditions. The module plpy.spiexceptions defines an
 207    exception class for each PostgreSQL condition, deriving their names
 208    from the condition name. For instance, division_by_zero becomes
 209    DivisionByZero, unique_violation becomes UniqueViolation, fdw_error
 210    becomes FdwError, and so on. Each of these exception classes inherits
 211    from SPIError. This separation makes it easier to handle specific
 212    errors, for instance:
 213 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS
 214 $$
 215 from plpy import spiexceptions
 216 try:
 217     plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int",
 218  "int"])
 219     plpy.execute(plan, [numerator, denominator])
 220 except spiexceptions.DivisionByZero:
 221     return "denominator cannot equal zero"
 222 except spiexceptions.UniqueViolation:
 223     return "already have that fraction"
 224 except plpy.SPIError as e:
 225     return "other error, SQLSTATE %s" % e.sqlstate
 226 else:
 227     return "fraction inserted"
 228 $$ LANGUAGE plpython3u;
 229
 230    Note that because all exceptions from the plpy.spiexceptions module
 231    inherit from SPIError, an except clause handling it will catch any
 232    database access error.
 233
 234    As an alternative way of handling different error conditions, you can
 235    catch the SPIError exception and determine the specific error condition
 236    inside the except block by looking at the sqlstate attribute of the
 237    exception object. This attribute is a string value containing the
 238    “SQLSTATE” error code. This approach provides approximately the same
 239    functionality