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

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

Removed misleading sentence from docs

The insert() method does in fact support inserting into views in newer
PostgreSQL versions or when the necessary rules have been created.

File size: 19.2 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.abort()
261
262    This is the same as the :meth:`DB.rollback` method.
263
264.. versionadded:: 4.2
265
266.. method:: DB.savepoint(name)
267
268    Define a new savepoint
269
270    :param str name: the name to give to the new savepoint
271
272    This establishes a new savepoint within the current transaction.
273
274.. versionadded:: 4.1
275
276.. method:: DB.release(name)
277
278    Destroy a savepoint
279
280    :param str name: the name of the savepoint to destroy
281
282    This destroys a savepoint previously defined in the current transaction.
283
284.. versionadded:: 4.1
285
286get -- get a row from a database table or view
287----------------------------------------------
288
289.. method:: DB.get(table, arg, [keyname])
290
291    Get a row from a database table or view
292
293    :param str table:  name of table or view
294    :param arg:  either a dictionary or the value to be looked up
295    :param str keyname: name of field to use as key (optional)
296    :returns: A dictionary - the keys are the attribute names,
297      the values are the row values.
298    :raises ProgrammingError: no primary key or missing privilege
299
300This method is the basic mechanism to get a single row.  It assumes
301that the key specifies a unique row.  If *keyname* is not specified,
302then the primary key for the table is used.  If *arg* is a dictionary
303then the value for the key is taken from it and it is modified to
304include the new values, replacing existing values where necessary.
305For a composite key, *keyname* can also be a sequence of key names.
306The OID is also put into the dictionary if the table has one, but in
307order to allow the caller to work with multiple tables, it is munged
308as ``oid(schema.table)``.
309
310insert -- insert a row into a database table
311--------------------------------------------
312
313.. method:: DB.insert(table, [d], [key=val, ...])
314
315    Insert a row into a database table
316
317    :param str table: name of table
318    :param dict d: optional dictionary of values
319    :returns: the inserted values in the database
320    :rtype: dict
321    :raises ProgrammingError: missing privilege or conflict
322
323This method inserts a row into a table.  If the optional dictionary is
324not supplied then the required values must be included as keyword/value
325pairs.  If a dictionary is supplied then any keywords provided will be
326added to or replace the entry in the dictionary.
327
328The dictionary is then, if possible, reloaded with the values actually
329inserted in order to pick up values modified by rules, triggers, etc.
330
331update -- update a row in a database table
332------------------------------------------
333
334.. method:: DB.update(table, [d], [key=val, ...])
335
336    Update a row in a database table
337
338    :param str table: name of table
339    :param dict d: optional dictionary of values
340    :returns: the new row in the database
341    :rtype: dict
342    :raises ProgrammingError: no primary key or missing privilege
343
344Similar to insert but updates an existing row.  The update is based on the
345OID value as munged by :meth:`DB.get` or passed as keyword, or on the primary
346key of the table.  The dictionary is modified, if possible, to reflect any
347changes caused by the update due to triggers, rules, default values, etc.
348
349Like insert, the dictionary is optional and updates will be performed
350on the fields in the keywords.  There must be an OID or primary key
351either in the dictionary where the OID must be munged, or in the keywords
352where it can be simply the string ``'oid'``.
353
354query -- execute a SQL command string
355-------------------------------------
356
357.. method:: DB.query(command, [arg1, [arg2, ...]])
358
359    Execute a SQL command string
360
361    :param str command: SQL command
362    :param arg*: optional positional arguments
363    :returns: result values
364    :rtype: :class:`pgqueryobject`, None
365    :raises TypeError: bad argument type, or too many arguments
366    :raises TypeError: invalid connection
367    :raises ValueError: empty SQL query or lost connection
368    :raises pg.ProgrammingError: error in query
369    :raises pg.InternalError: error during query processing
370
371Similar to the :class:`pgobject` function with the same name, except that
372positional arguments can be passed either as a single list or tuple, or as
373individual positional arguments.
374
375Example::
376
377    name = raw_input("Name? ")
378    phone = raw_input("Phone? ")
379    rows = db.query("update employees set phone=$2 where name=$1",
380        (name, phone)).getresult()[0][0]
381    # or
382    rows = db.query("update employees set phone=$2 where name=$1",
383         name, phone).getresult()[0][0]
384
385clear -- clear row values in memory
386-----------------------------------
387
388.. method:: DB.clear(table, [d])
389
390    Clear row values in memory
391
392    :param str table: name of table
393    :param dict d: optional dictionary of values
394    :returns: an empty row
395    :rtype: dict
396
397This method clears all the attributes to values determined by the types.
398Numeric types are set to 0, Booleans are set to ``'f'``, and everything
399else is set to the empty string.  If the optional dictionary is present,
400it is used as the row and any entries matching attribute names are cleared
401with everything else left unchanged.
402
403If the dictionary is not supplied a new one is created.
404
405delete -- delete a row from a database table
406--------------------------------------------
407
408.. method:: DB.delete(table, [d], [key=val, ...])
409
410    Delete a row from a database table
411
412    :param str table: name of table
413    :param dict d: optional dictionary of values
414    :rtype: None
415    :raises ProgrammingError: table has no primary key,
416        row is still referenced or missing privilege
417
418This method deletes the row from a table.  It deletes based on the OID value
419as munged by :meth:`DB.get` or passed as keyword, or on the primary key of
420the table.  The return value is the number of deleted rows (i.e. 0 if the
421row did not exist and 1 if the row was deleted).
422
423truncate -- Quickly empty database tables
424-----------------------------------------
425
426.. method:: DB.truncate(self, table, [restart], [cascade], [only]):
427
428    Empty a table or set of tables
429
430    :param table: the name of the table(s)
431    :type table: str, list or set
432    :param bool restart: whether table sequences should be restarted
433    :param bool cascade: whether referenced tables should also be truncated
434    :param only: whether only parent tables should be truncated
435    :type only: bool or list
436
437This method quickly removes all rows from the given table or set
438of tables.  It has the same effect as an unqualified DELETE on each
439table, but since it does not actually scan the tables it is faster.
440Furthermore, it reclaims disk space immediately, rather than requiring
441a subsequent VACUUM operation. This is most useful on large tables.
442
443If *restart* is set to `True`, sequences owned by columns of the truncated
444table(s) are automatically restarted.  If *cascade* is set to `True`, it
445also truncates all tables that have foreign-key references to any of
446the named tables.  If the parameter *only* is not set to `True`, all the
447descendant tables (if any) will also be truncated. Optionally, a ``*``
448can be specified after the table name to explicitly indicate that
449descendant tables are included.  If the parameter *table* is a list,
450the parameter *only* can also be a list of corresponding boolean values.
451
452.. versionadded:: 4.2
453
454escape_literal -- escape a literal string for use within SQL
455------------------------------------------------------------
456
457.. method:: DB.escape_literal(string)
458
459    Escape a string for use within SQL as a literal constant
460
461    :param str string: the string that is to be escaped
462    :returns: the escaped string
463    :rtype: str
464
465This method escapes a string for use within an SQL command. This is useful
466when inserting data values as literal constants in SQL commands. Certain
467characters (such as quotes and backslashes) must be escaped to prevent them
468from being interpreted specially by the SQL parser.
469
470.. versionadded:: 4.1
471
472escape_identifier -- escape an identifier string for use within SQL
473-------------------------------------------------------------------
474
475.. method:: DB.escape_identifier(string)
476
477    Escape a string for use within SQL as an identifier
478
479    :param str string: the string that is to be escaped
480    :returns: the escaped string
481    :rtype: str
482
483This method escapes a string for use as an SQL identifier, such as a table,
484column, or function name. This is useful when a user-supplied identifier
485might contain special characters that would otherwise not be interpreted
486as part of the identifier by the SQL parser, or when the identifier might
487contain upper case characters whose case should be preserved.
488
489.. versionadded:: 4.1
490
491escape_bytea -- escape binary data for use within SQL
492-----------------------------------------------------
493
494.. method:: DB.escape_bytea(datastring)
495
496    Escape binary data for use within SQL as type ``bytea``
497
498    :param str datastring: string containing the binary data that is to be escaped
499    :returns: the escaped string
500    :rtype: str
501
502Similar to the module function with the same name, but the
503behavior of this method is adjusted depending on the connection properties
504(in particular, whether standard-conforming strings are enabled).
505
506unescape_bytea -- unescape data that has been retrieved as text
507---------------------------------------------------------------
508
509.. method:: DB.unescape_bytea(string)
510
511    Unescape ``bytea`` data that has been retrieved as text
512
513    :param datastring: the ``bytea`` data string that has been retrieved as text
514    :returns: byte string containing the binary data
515    :rtype: str
516
517See the module function with the same name.
518
519use_regtypes -- determine use of regular type names
520---------------------------------------------------
521
522.. method:: DB.use_regtypes([regtypes])
523
524    Determine whether regular type names shall be used
525
526    :param bool regtypes: if passed, set whether regular type names shall be used
527    :returns: whether regular type names are used
528
529The :meth:`DB.get_attnames` method can return either simplified "classic"
530type names (the default) or more specific "regular" type names. Which kind
531of type names is used can be changed by calling :meth:`DB.get_regtypes`.
532If you pass a boolean, it sets whether regular type names shall be used.
533The method can also be used to check through its return value whether
534currently regular type names are used.
535
536.. versionadded:: 4.1
537
538notification_handler -- create a notification handler
539-----------------------------------------------------
540
541.. class:: DB.notification_handler(event, callback, [arg_dict], [timeout], [stop_event])
542
543    Create a notification handler instance
544
545    :param str event: the name of an event to listen for
546    :param callback: a callback function
547    :param dict arg_dict: an optional dictionary for passing arguments
548    :param timeout: the time-out when waiting for notifications
549    :type timeout: int, float or None
550    :param str stop_event: an optional different name to be used as stop event
551
552This method creates a :class:`pg.NotificationHandler` object using the
553:class:`DB` connection as explained under :doc:`notification`.
554
555.. versionadded:: 4.1.1
Note: See TracBrowser for help on using the repository browser.