source: branches/4.x/docs/contents/pg/db_wrapper.rst @ 747

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

Improve the get/set_parameter methods

In addition to a list, also allow a set as parameter.

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