source: trunk/docs/contents/pg/db_wrapper.rst @ 745

Last change on this file since 745 was 745, checked in by cito, 4 years ago

Add methods get/set_parameter to DB wrapper class

These methods can be used to get/set/reset run-time parameters,
even several at once.

Since this is pretty useful and will not break anything, I have
also back ported these additions to the 4.x branch.

Everything is well documented and tested, of course.

File size: 19.9 KB
Line 
1The DB wrapper class
2====================
3
4.. py:currentmodule:: pg
5
6.. class:: DB
7
8The :class:`Connection` methods are wrapped in the class :class:`DB`.
9The preferred way to use this module is as follows::
10
11    import pg
12
13    db = pg.DB(...)  # see below
14
15    for r in db.query(  # just for example
16        """SELECT foo,bar
17         FROM foo_bar_table
18         WHERE foo !~ bar"""
19        ).dictresult():
20
21        print '%(foo)s %(bar)s' % r
22
23This class can be subclassed as in this example::
24
25    import pg
26
27    class DB_ride(pg.DB):
28        """Ride database wrapper
29
30        This class encapsulates the database functions and the specific
31        methods for the ride database."""
32
33    def __init__(self):
34        """Open a database connection to the rides database"""
35        pg.DB.__init__(self, dbname='ride')
36        self.query("SET DATESTYLE TO 'ISO'")
37
38    [Add or override methods here]
39
40The following describes the methods and variables of this class.
41
42Initialization
43--------------
44The :class:`DB` class is initialized with the same arguments as the
45:func:`connect` function described above. It also initializes a few
46internal variables. The statement ``db = DB()`` will open the local
47database with the name of the user just like ``connect()`` does.
48
49You can also initialize the DB class with an existing :mod:`pg` or :mod:`pgdb`
50connection. Pass this connection as a single unnamed parameter, or as a
51single parameter named ``db``. This allows you to use all of the methods
52of the DB class with a DB-API 2 compliant connection. Note that the
53:meth:`Connection.close` and :meth:`Connection.reopen` methods are inoperative
54in this case.
55
56pkey -- return the primary key of a table
57-----------------------------------------
58
59.. method:: DB.pkey(table)
60
61    Return the primary key of a table
62
63    :param str table: name of table
64    :returns: Name of the field which is the primary key of the table
65    :rtype: str
66    :raises KeyError: the table does not have a primary key
67
68This method returns the primary key of a table. For composite primary
69keys, the return value will be a frozenset. Note that this raises a
70KeyError if the table does not have a primary key.
71
72get_databases -- get list of databases in the system
73----------------------------------------------------
74
75.. method:: DB.get_databases()
76
77    Get the list of databases in the system
78
79    :returns: all databases in the system
80    :rtype: list
81
82Although you can do this with a simple select, it is added here for
83convenience.
84
85get_relations -- get list of relations in connected database
86------------------------------------------------------------
87
88.. method:: DB.get_relations(kinds)
89
90    Get the list of relations in connected database
91
92    :param str kinds: a string or sequence of type letters
93    :returns: all relations of the given kinds in the database
94    :rtype: list
95
96The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
97``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
98If `kinds` is None or an empty string, all relations are returned (this is
99also the default). Although you can do this with a simple select, it is
100added here for convenience.
101
102get_tables -- get list of tables in connected database
103------------------------------------------------------
104
105.. method:: DB.get_tables()
106
107    Get the list of tables in connected database
108
109    :returns: all tables in connected database
110    :rtype: list
111
112This is a shortcut for ``get_relations('r')`` that has been added for
113convenience.
114
115get_attnames -- get the attribute names of a table
116--------------------------------------------------
117
118.. method:: DB.get_attnames(table)
119
120    Get the attribute names of a table
121
122    :param str table: name of table
123    :returns: a dictionary mapping attribute names to type names
124
125Given the name of a table, digs out the set of attribute names.
126
127Returns a dictionary of attribute names (the names are the keys,
128the values are the names of the attributes' types).
129
130If your Python version supports this, the dictionary will be an
131OrderedDictionary with the column names in the right order.
132
133By default, only a limited number of simple types will be returned.
134You can get the regular types after enabling this by calling the
135:meth:`DB.use_regtypes` method.
136
137has_table_privilege -- check table privilege
138--------------------------------------------
139
140.. method:: DB.has_table_privilege(table, privilege)
141
142    Check whether current user has specified table privilege
143
144    :param str table: the name of the table
145    :param str privilege: privilege to be checked -- default is 'select'
146    :returns: whether current user has specified table privilege
147    :rtype: bool
148
149Returns True if the current user has the specified privilege for the table.
150
151.. versionadded:: 4.0
152
153get/set_parameter -- get or set  run-time parameters
154----------------------------------------------------
155
156.. method:: DB.get_parameter(parameter)
157
158    Get the value of run-time parameters
159
160    :param parameter: the run-time parameter(s) to get
161    :type param: str, tuple, list or dict
162    :returns: the current value(s) of the run-time parameter(s)
163    :rtype: str, list or dict
164    :raises TypeError: Invalid parameter type(s)
165    :raises ProgrammingError: Invalid parameter name(s)
166
167If the parameter is a string, the return value will also be a string
168that is the current setting of the run-time parameter with that name.
169
170You can get several parameters at once by passing a list or tuple of
171parameter names.  The return value will then be a corresponding list
172of parameter settings.  If you pass a dict as parameter instead, its
173values will be set to the parameter settings corresponding to its keys.
174
175By passing the special name `'all'` as the parameter, you can get a dict
176of all existing configuration parameters.
177
178.. versionadded:: 4.2
179
180.. method:: DB.set_parameter(self, parameter, [value], [local])
181
182    Set the value of run-time parameters
183
184    :param parameter: the run-time parameter(s) to set
185    :type param: string, tuple, list or dict
186    :param value: the value to set
187    :type param: str or None
188    :raises TypeError: Invalid parameter type(s)
189    :raises ValueError: Invalid value argument(s)
190    :raises ProgrammingError: Invalid parameter name(s) or values
191
192If the parameter and the value are strings, the run-time parameter
193will be set to that value.  If no value or *None* is passed as a value,
194then the run-time parameter will be restored to its default value.
195
196You can set several parameters at once by passing a list or tuple
197of parameter names, with a single value that all parameters should
198be set to or with a corresponding list or tuple of values.
199
200You can also pass a dict as parameters.  In this case, you should
201not pass a value, since the values will be taken from the dict.
202
203By passing the special name `'all'` as the parameter, you can reset
204all existing settable run-time parameters to their default values.
205
206If you set *local* to `True`, then the command takes effect for only the
207current transaction.  After :meth:`DB.commit` or :meth:`DB.rollback`,
208the session-level setting takes effect again.  Setting *local* to `True`
209will appear to have no effect if it is executed outside a transaction,
210since the transaction will end immediately.
211
212.. versionadded:: 4.2
213
214begin/commit/rollback/savepoint/release -- transaction handling
215---------------------------------------------------------------
216
217.. method:: DB.begin([mode])
218
219    Begin a transaction
220
221    :param str mode: an optional transaction mode such as 'READ ONLY'
222
223    This initiates a transaction block, that is, all following queries
224    will be executed in a single transaction until :meth:`DB.commit`
225    or :meth:`DB.rollback` is called.
226
227.. versionadded:: 4.1
228
229.. method:: DB.start()
230
231    This is the same as the :meth:`DB.begin` method.
232
233.. method:: DB.commit()
234
235    Commit a transaction
236
237    This commits the current transaction. All changes made by the
238    transaction become visible to others and are guaranteed to be
239    durable if a crash occurs.
240
241.. method:: DB.end()
242
243    This is the same as the :meth:`DB.commit` method.
244
245.. versionadded:: 4.1
246
247.. method:: DB.rollback([name])
248
249    Roll back a transaction
250
251    :param str name: optionally, roll back to the specified savepoint
252
253    This rolls back the current transaction and causes all the updates
254    made by the transaction to be discarded.
255
256.. versionadded:: 4.1
257
258.. method:: DB.savepoint(name)
259
260    Define a new savepoint
261
262    :param str name: the name to give to the new savepoint
263
264    This establishes a new savepoint within the current transaction.
265
266.. versionadded:: 4.1
267
268.. method:: DB.release(name)
269
270    Destroy a savepoint
271
272    :param str name: the name of the savepoint to destroy
273
274    This destroys a savepoint previously defined in the current transaction.
275
276.. versionadded:: 4.1
277
278get -- get a row from a database table or view
279----------------------------------------------
280
281.. method:: DB.get(table, row, [keyname])
282
283    Get a row from a database table or view
284
285    :param str table: name of table or view
286    :param row: either a dictionary or the value to be looked up
287    :param str keyname: name of field to use as key (optional)
288    :returns: A dictionary - the keys are the attribute names,
289      the values are the row values.
290    :raises ProgrammingError: no primary key or missing privilege
291
292This method is the basic mechanism to get a single row. It assumes
293that the key specifies a unique row. If *keyname* is not specified,
294then the primary key for the table is used. If *row* is a dictionary
295then the value for the key is taken from it and it is modified to
296include the new values, replacing existing values where necessary.
297For a composite key, *keyname* can also be a sequence of key names.
298The OID is also put into the dictionary if the table has one, but in
299order to allow the caller to work with multiple tables, it is munged
300as ``oid(table)``.
301
302insert -- insert a row into a database table
303--------------------------------------------
304
305.. method:: DB.insert(table, [row], [col=val, ...])
306
307    Insert a row into a database table
308
309    :param str table: name of table
310    :param dict row: optional dictionary of values
311    :param col: optional keyword arguments for updating the dictionary
312    :returns: the inserted values in the database
313    :rtype: dict
314    :raises ProgrammingError: missing privilege or conflict
315
316This method inserts a row into a table.  If the optional dictionary is
317not supplied then the required values must be included as keyword/value
318pairs.  If a dictionary is supplied then any keywords provided will be
319added to or replace the entry in the dictionary.
320
321The dictionary is then reloaded with the values actually inserted in order
322to pick up values modified by rules, triggers, etc.
323
324Note: The method currently doesn't support insert into views
325although PostgreSQL does.
326
327update -- update a row in a database table
328------------------------------------------
329
330.. method:: DB.update(table, [row], [col=val, ...])
331
332    Update a row in a database table
333
334    :param str table: name of table
335    :param dict row: optional dictionary of values
336    :param col: optional keyword arguments for updating the dictionary
337    :returns: the new row in the database
338    :rtype: dict
339    :raises ProgrammingError: no primary key or missing privilege
340
341Similar to insert but updates an existing row.  The update is based on the
342OID value as munged by get or passed as keyword, or on the primary key of
343the table.  The dictionary is modified to reflect any changes caused by the
344update due to triggers, rules, default values, etc.
345
346Like insert, the dictionary is optional and updates will be performed
347on the fields in the keywords.  There must be an OID or primary key
348either in the dictionary where the OID must be munged, or in the keywords
349where it can be simply the string 'oid'.
350
351upsert -- insert a row with conflict resolution
352-----------------------------------------------
353
354.. method:: DB.upsert(table, [row], [col=val, ...])
355
356    Insert a row into a database table with conflict resolution
357
358    :param str table: name of table
359    :param dict row: optional dictionary of values
360    :param col: optional keyword arguments for specifying the update
361    :returns: the new row in the database
362    :rtype: dict
363    :raises ProgrammingError: no primary key or missing privilege
364
365This method inserts a row into a table, but instead of raising a
366ProgrammingError exception in case a row with the same primary key already
367exists, an update will be executed instead.  This will be performed as a
368single atomic operation on the database, so race conditions can be avoided.
369
370Like the insert method, the first parameter is the name of the table and the
371second parameter can be used to pass the values to be inserted as a dictionary.
372
373Unlike the insert und update statement, keyword parameters are not used to
374modify the dictionary, but to specify which columns shall be updated in case
375of a conflict, and in which way:
376
377A value of `False` or `None` means the column shall not be updated,
378a value of `True` means the column shall be updated with the value that
379has been proposed for insertion, i.e. has been passed as value in the
380dictionary.  Columns that are not specified by keywords but appear as keys
381in the dictionary are also updated like in the case keywords had been passed
382with the value `True`.
383
384So if in the case of a conflict you want to update every column that has been
385passed in the dictionary `d` , you would call ``upsert(table, d)``.  If you
386don't want to do anything in case of a conflict, i.e. leave the existing row
387as it is, call ``upsert(table, d, **dict.fromkeys(d))``.
388
389If you need more fine-grained control of what gets updated, you can also pass
390strings in the keyword parameters.  These strings will be used as SQL
391expressions for the update columns.  In these expressions you can refer
392to the value that already exists in the table by writing the table prefix
393``included.`` before the column name, and you can refer to the value that
394has been proposed for insertion by writing ``excluded.`` as table prefix.
395
396The dictionary is modified in any case to reflect the values in the database
397after the operation has completed.
398
399.. note::
400
401    The method uses the PostgreSQL "upsert" feature which is only available
402    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
403    ProgrammingError if you use this method.
404
405.. versionadded:: 5.0
406
407query -- execute a SQL command string
408-------------------------------------
409
410.. method:: DB.query(command, [arg1, [arg2, ...]])
411
412    Execute a SQL command string
413
414    :param str command: SQL command
415    :param arg*: optional positional arguments
416    :returns: result values
417    :rtype: :class:`Query`, None
418    :raises TypeError: bad argument type, or too many arguments
419    :raises TypeError: invalid connection
420    :raises ValueError: empty SQL query or lost connection
421    :raises pg.ProgrammingError: error in query
422    :raises pg.InternalError: error during query processing
423
424Similar to the :class:`Connection` function with the same name, except that
425positional arguments can be passed either as a single list or tuple, or as
426individual positional arguments.
427
428Example::
429
430    name = input("Name? ")
431    phone = input("Phone? ")
432    rows = db.query("update employees set phone=$2 where name=$1",
433        (name, phone)).getresult()[0][0]
434    # or
435    rows = db.query("update employees set phone=$2 where name=$1",
436         name, phone).getresult()[0][0]
437
438clear -- clear row values in memory
439-----------------------------------
440
441.. method:: DB.clear(table, [row])
442
443    Clear row values in memory
444
445    :param str table: name of table
446    :param dict row: optional dictionary of values
447    :returns: an empty row
448    :rtype: dict
449
450This method clears all the attributes to values determined by the types.
451Numeric types are set to 0, Booleans are set to ``'f'``, and everything
452else is set to the empty string.  If the row argument is present, it is
453used as the row dictionary and any entries matching attribute names are
454cleared with everything else left unchanged.
455
456If the dictionary is not supplied a new one is created.
457
458delete -- delete a row from a database table
459--------------------------------------------
460
461.. method:: DB.delete(table, [row], [col=val, ...])
462
463    Delete a row from a database table
464
465    :param str table: name of table
466    :param dict d: optional dictionary of values
467    :param col: optional keyword arguments for updating the dictionary
468    :rtype: None
469
470This method deletes the row from a table.  It deletes based on the OID value
471as munged by get or passed as keyword, or on the primary key of the table.
472The return value is the number of deleted rows (i.e. 0 if the row did not
473exist and 1 if the row was deleted).
474
475escape_literal -- escape a literal string for use within SQL
476------------------------------------------------------------
477
478.. method:: DB.escape_literal(string)
479
480    Escape a string for use within SQL as a literal constant
481
482    :param str string: the string that is to be escaped
483    :returns: the escaped string
484    :rtype: str
485
486This method escapes a string for use within an SQL command. This is useful
487when inserting data values as literal constants in SQL commands. Certain
488characters (such as quotes and backslashes) must be escaped to prevent them
489from being interpreted specially by the SQL parser.
490
491.. versionadded:: 4.1
492
493escape_identifier -- escape an identifier string for use within SQL
494-------------------------------------------------------------------
495
496.. method:: DB.escape_identifier(string)
497
498    Escape a string for use within SQL as an identifier
499
500    :param str string: the string that is to be escaped
501    :returns: the escaped string
502    :rtype: str
503
504This method escapes a string for use as an SQL identifier, such as a table,
505column, or function name. This is useful when a user-supplied identifier
506might contain special characters that would otherwise not be interpreted
507as part of the identifier by the SQL parser, or when the identifier might
508contain upper case characters whose case should be preserved.
509
510.. versionadded:: 4.1
511
512escape_string -- escape a string for use within SQL
513---------------------------------------------------
514
515.. method:: DB.escape_string(string)
516
517    Escape a string for use within SQL
518
519    :param str string: the string that is to be escaped
520    :returns: the escaped string
521    :rtype: str
522
523Similar to the module function with the same name, but the
524behavior of this method is adjusted depending on the connection properties
525(such as character encoding).
526
527escape_bytea -- escape binary data for use within SQL
528-----------------------------------------------------
529
530.. method:: DB.escape_bytea(datastring)
531
532    Escape binary data for use within SQL as type ``bytea``
533
534    :param str datastring: string containing the binary data that is to be escaped
535    :returns: the escaped string
536    :rtype: str
537
538Similar to the module function with the same name, but the
539behavior of this method is adjusted depending on the connection properties
540(in particular, whether standard-conforming strings are enabled).
541
542unescape_bytea -- unescape data that has been retrieved as text
543---------------------------------------------------------------
544
545.. method:: DB.unescape_bytea(string)
546
547    Unescape ``bytea`` data that has been retrieved as text
548
549    :param datastring: the ``bytea`` data string that has been retrieved as text
550    :returns: byte string containing the binary data
551    :rtype: bytes
552
553See the module function with the same name.
554
555use_regtypes -- determine use of regular type names
556---------------------------------------------------
557
558.. method:: DB.use_regtypes([regtypes])
559
560    Determine whether regular type names shall be used
561
562    :param bool regtypes: if passed, set whether regular type names shall be used
563    :returns: whether regular type names are used
564
565The :meth:`DB.get_attnames` method can return either simplified "classic"
566type names (the default) or more specific "regular" type names. Which kind
567of type names is used can be changed by calling :meth:`DB.get_regtypes`.
568If you pass a boolean, it sets whether regular type names shall be used.
569The method can also be used to check through its return value whether
570currently regular type names are used.
571
572.. versionadded:: 4.1
Note: See TracBrowser for help on using the repository browser.