source: branches/4.x/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: 16.6 KB
Line 
1The DB wrapper class
2====================
3
4.. py:currentmodule:: pg
5
6.. class:: DB
7
8The :class:`pgobject` 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:`pgobject.close` and :meth:`pgobject.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    :rtype: str
67    :raises KeyError: the table does not have a primary key
68
69This method returns the primary key of a table. For composite primary
70keys, the return value will be a frozenset. Note that this raises a
71KeyError if the table does not have a primary key.
72
73get_databases -- get list of databases in the system
74----------------------------------------------------
75
76.. method:: DB.get_databases()
77
78    Get the list of databases in the system
79
80    :returns: all databases in the system
81    :rtype: list
82
83Although you can do this with a simple select, it is added here for
84convenience.
85
86get_relations -- get list of relations in connected database
87------------------------------------------------------------
88
89.. method:: DB.get_relations(kinds)
90
91    Get the list of relations in connected database
92
93    :param str kinds: a string or sequence of type letters
94    :returns: all relations of the given kinds in the database
95    :rtype: list
96
97The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
98``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
99If `kinds` is None or an empty string, all relations are returned (this is
100also the default). Although you can do this with a simple select, it is
101added here for convenience.
102
103get_tables -- get list of tables in connected database
104------------------------------------------------------
105
106.. method:: DB.get_tables()
107
108    Get the list of tables in connected database
109
110    :returns: all tables in connected database
111    :rtype: list
112
113This is a shortcut for ``get_relations('r')`` that has been added for
114convenience.
115
116get_attnames -- get the attribute names of a table
117--------------------------------------------------
118
119.. method:: DB.get_attnames(table)
120
121    Get the attribute names of a table
122
123    :param str table: name of table
124    :returns: a dictionary mapping attribute names to type names
125
126Given the name of a table, digs out the set of attribute names.
127
128Returns a dictionary of attribute names (the names are the keys,
129the values are the names of the attributes' types).
130
131By default, only a limited number of simple types will be returned.
132You can get the regular types after enabling this by calling the
133:meth:`DB.use_regtypes` method.
134
135get/set_parameter -- get or set  run-time parameters
136----------------------------------------------------
137
138.. method:: DB.get_parameter(parameter)
139
140    Get the value of run-time parameters
141
142    :param parameter: the run-time parameter(s) to get
143    :type param: str, tuple, list or dict
144    :returns: the current value(s) of the run-time parameter(s)
145    :rtype: str, list or dict
146    :raises TypeError: Invalid parameter type(s)
147    :raises ProgrammingError: Invalid parameter name(s)
148
149If the parameter is a string, the return value will also be a string
150that is the current setting of the run-time parameter with that name.
151
152You can get several parameters at once by passing a list or tuple of
153parameter names.  The return value will then be a corresponding list
154of parameter settings.  If you pass a dict as parameter instead, its
155values will be set to the parameter settings corresponding to its keys.
156
157By passing the special name `'all'` as the parameter, you can get a dict
158of all existing configuration parameters.
159
160.. versionadded:: 4.2
161
162.. method:: DB.set_parameter(self, parameter, [value], [local])
163
164    Set the value of run-time parameters
165
166    :param parameter: the run-time parameter(s) to set
167    :type param: string, tuple, list or dict
168    :param value: the value to set
169    :type param: str or None
170    :raises TypeError: Invalid parameter type(s)
171    :raises ValueError: Invalid value argument(s)
172    :raises ProgrammingError: Invalid parameter name(s) or values
173
174If the parameter and the value are strings, the run-time parameter
175will be set to that value.  If no value or *None* is passed as a value,
176then the run-time parameter will be restored to its default value.
177
178You can set several parameters at once by passing a list or tuple
179of parameter names, with a single value that all parameters should
180be set to or with a corresponding list or tuple of values.
181
182You can also pass a dict as parameters.  In this case, you should
183not pass a value, since the values will be taken from the dict.
184
185By passing the special name `'all'` as the parameter, you can reset
186all existing settable run-time parameters to their default values.
187
188If you set *local* to `True`, then the command takes effect for only the
189current transaction.  After :meth:`DB.commit` or :meth:`DB.rollback`,
190the session-level setting takes effect again.  Setting *local* to `True`
191will appear to have no effect if it is executed outside a transaction,
192since the transaction will end immediately.
193
194.. versionadded:: 4.2
195
196has_table_privilege -- check table privilege
197--------------------------------------------
198
199.. method:: DB.has_table_privilege(table, privilege)
200
201    Check whether current user has specified table privilege
202
203    :param str table: the name of the table
204    :param str privilege: privilege to be checked -- default is 'select'
205    :returns: whether current user has specified table privilege
206    :rtype: bool
207
208Returns True if the current user has the specified privilege for the table.
209
210.. versionadded:: 4.0
211
212begin/commit/rollback/savepoint/release -- transaction handling
213---------------------------------------------------------------
214
215.. method:: DB.begin([mode])
216
217    Begin a transaction
218
219    :param str mode: an optional transaction mode such as 'READ ONLY'
220
221    This initiates a transaction block, that is, all following queries
222    will be executed in a single transaction until :meth:`DB.commit`
223    or :meth:`DB.rollback` is called.
224
225.. versionadded:: 4.1
226
227.. method:: DB.start()
228
229    This is the same as the :meth:`DB.begin` method.
230
231.. method:: DB.commit()
232
233    Commit a transaction
234
235    This commits the current transaction. All changes made by the
236    transaction become visible to others and are guaranteed to be
237    durable if a crash occurs.
238
239.. method:: DB.end()
240
241    This is the same as the :meth:`DB.commit` method.
242
243.. versionadded:: 4.1
244
245.. method:: DB.rollback([name])
246
247    Roll back a transaction
248
249    :param str name: optionally, roll back to the specified savepoint
250
251    This rolls back the current transaction and causes all the updates
252    made by the transaction to be discarded.
253
254.. versionadded:: 4.1
255
256.. method:: DB.savepoint(name)
257
258    Define a new savepoint
259
260    :param str name: the name to give to the new savepoint
261
262    This establishes a new savepoint within the current transaction.
263
264.. versionadded:: 4.1
265
266.. method:: DB.release(name)
267
268    Destroy a savepoint
269
270    :param str name: the name of the savepoint to destroy
271
272    This destroys a savepoint previously defined in the current transaction.
273
274.. versionadded:: 4.1
275
276get -- get a row from a database table or view
277----------------------------------------------
278
279.. method:: DB.get(table, arg, [keyname])
280
281    Get a row from a database table or view
282
283    :param str table:  name of table or view
284    :param arg:  either a dictionary or the value to be looked up
285    :param str keyname: name of field to use as key (optional)
286    :returns: A dictionary - the keys are the attribute names,
287      the values are the row values.
288    :raises ProgrammingError: no primary key or missing privilege
289
290This method is the basic mechanism to get a single row. It assumes
291that the key specifies a unique row. If *keyname* is not specified,
292then the primary key for the table is used. If *arg* is a dictionary
293then the value for the key is taken from it and it is modified to
294include the new values, replacing existing values where necessary.
295For a composite key, *keyname* can also be a sequence of key names.
296The OID is also put into the dictionary if the table has one, but in
297order to allow the caller to work with multiple tables, it is munged
298as ``oid(schema.table)``.
299
300insert -- insert a row into a database table
301--------------------------------------------
302
303.. method:: DB.insert(table, [d], [key=val, ...])
304
305    Insert a row into a database table
306
307    :param str table: name of table
308    :param dict d: optional dictionary of values
309    :returns: the inserted values in the database
310    :rtype: dict
311    :raises ProgrammingError: missing privilege or conflict
312
313This method inserts a row into a table.  If the optional dictionary is
314not supplied then the required values must be included as keyword/value
315pairs.  If a dictionary is supplied then any keywords provided will be
316added to or replace the entry in the dictionary.
317
318The dictionary is then, if possible, reloaded with the values actually
319inserted in order to pick up values modified by rules, triggers, etc.
320
321Note: The method currently doesn't support insert into views
322although PostgreSQL does.
323
324update -- update a row in a database table
325------------------------------------------
326
327.. method:: DB.update(table, [d], [key=val, ...])
328
329    Update a row in a database table
330
331    :param str table: name of table
332    :param dict d: optional dictionary of values
333    :returns: the new row in the database
334    :rtype: dict
335    :raises ProgrammingError: no primary key or missing privilege
336
337Similar to insert but updates an existing row.  The update is based on the
338OID value as munged by get or passed as keyword, or on the primary key of
339the table.  The dictionary is modified, if possible, to reflect any changes
340caused by the update due to triggers, rules, default values, etc.
341
342Like insert, the dictionary is optional and updates will be performed
343on the fields in the keywords.  There must be an OID or primary key
344either in the dictionary where the OID must be munged, or in the keywords
345where it can be simply the string 'oid'.
346
347query -- execute a SQL command string
348-------------------------------------
349
350.. method:: DB.query(command, [arg1, [arg2, ...]])
351
352    Execute a SQL command string
353
354    :param str command: SQL command
355    :param arg*: optional positional arguments
356    :returns: result values
357    :rtype: :class:`pgqueryobject`, None
358    :raises TypeError: bad argument type, or too many arguments
359    :raises TypeError: invalid connection
360    :raises ValueError: empty SQL query or lost connection
361    :raises pg.ProgrammingError: error in query
362    :raises pg.InternalError: error during query processing
363
364Similar to the :class:`pgobject` function with the same name, except that
365positional arguments can be passed either as a single list or tuple, or as
366individual positional arguments.
367
368Example::
369
370    name = raw_input("Name? ")
371    phone = raw_input("Phone? ")
372    rows = db.query("update employees set phone=$2 where name=$1",
373        (name, phone)).getresult()[0][0]
374    # or
375    rows = db.query("update employees set phone=$2 where name=$1",
376         name, phone).getresult()[0][0]
377
378clear -- clear row values in memory
379-----------------------------------
380
381.. method:: DB.clear(table, [d])
382
383    Clear row values in memory
384
385    :param str table: name of table
386    :param dict d: optional dictionary of values
387    :returns: an empty row
388    :rtype: dict
389
390This method clears all the attributes to values determined by the types.
391Numeric types are set to 0, Booleans are set to ``'f'``, and everything
392else is set to the empty string.  If the optional dictionary is present,
393it is used as the row and any entries matching attribute names are cleared
394with everything else left unchanged.
395
396If the dictionary is not supplied a new one is created.
397
398delete -- delete a row from a database table
399--------------------------------------------
400
401.. method:: DB.delete(table, [d], [key=val, ...])
402
403    Delete a row from a database table
404
405    :param str table: name of table
406    :param dict d: optional dictionary of values
407    :rtype: None
408
409This method deletes the row from a table.  It deletes based on the OID value
410as munged by get or passed as keyword, or on the primary key of the table.
411The return value is the number of deleted rows (i.e. 0 if the row did not
412exist and 1 if the row was deleted).
413
414escape_literal -- escape a literal string for use within SQL
415------------------------------------------------------------
416
417.. method:: DB.escape_literal(string)
418
419    Escape a string for use within SQL as a literal constant
420
421    :param str string: the string that is to be escaped
422    :returns: the escaped string
423    :rtype: str
424
425This method escapes a string for use within an SQL command. This is useful
426when inserting data values as literal constants in SQL commands. Certain
427characters (such as quotes and backslashes) must be escaped to prevent them
428from being interpreted specially by the SQL parser.
429
430.. versionadded:: 4.1
431
432escape_identifier -- escape an identifier string for use within SQL
433-------------------------------------------------------------------
434
435.. method:: DB.escape_identifier(string)
436
437    Escape a string for use within SQL as an identifier
438
439    :param str string: the string that is to be escaped
440    :returns: the escaped string
441    :rtype: str
442
443This method escapes a string for use as an SQL identifier, such as a table,
444column, or function name. This is useful when a user-supplied identifier
445might contain special characters that would otherwise not be interpreted
446as part of the identifier by the SQL parser, or when the identifier might
447contain upper case characters whose case should be preserved.
448
449.. versionadded:: 4.1
450
451escape_bytea -- escape binary data for use within SQL
452-----------------------------------------------------
453
454.. method:: DB.escape_bytea(datastring)
455
456    Escape binary data for use within SQL as type ``bytea``
457
458    :param str datastring: string containing the binary data that is to be escaped
459    :returns: the escaped string
460    :rtype: str
461
462Similar to the module function with the same name, but the
463behavior of this method is adjusted depending on the connection properties
464(in particular, whether standard-conforming strings are enabled).
465
466unescape_bytea -- unescape data that has been retrieved as text
467---------------------------------------------------------------
468
469.. method:: DB.unescape_bytea(string)
470
471    Unescape ``bytea`` data that has been retrieved as text
472
473    :param datastring: the ``bytea`` data string that has been retrieved as text
474    :returns: byte string containing the binary data
475    :rtype: str
476
477See the module function with the same name.
478
479use_regtypes -- determine use of regular type names
480---------------------------------------------------
481
482.. method:: DB.use_regtypes([regtypes])
483
484    Determine whether regular type names shall be used
485
486    :param bool regtypes: if passed, set whether regular type names shall be used
487    :returns: whether regular type names are used
488
489The :meth:`DB.get_attnames` method can return either simplified "classic"
490type names (the default) or more specific "regular" type names. Which kind
491of type names is used can be changed by calling :meth:`DB.get_regtypes`.
492If you pass a boolean, it sets whether regular type names shall be used.
493The method can also be used to check through its return value whether
494currently regular type names are used.
495
496.. versionadded:: 4.1
Note: See TracBrowser for help on using the repository browser.