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

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

Add system parameter to get_relations()

Also fix a regression in the 4.x branch when using temporary tables,
related to filtering system tables (as discussed on the mailing list).

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