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

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

Achieve 100% test coverage for pg module on the trunk

Note that some lines are only covered in certain Pg or Py versions,
so you need to run tests with different versions to be sure.

Also added another synonym for transaction methods,
you can now pick your favorite for all three of them.

File size: 22.3 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, set or dict.
171When passing a list of parameter names, the return value will be a
172corresponding list of parameter settings.  When passing a set of
173parameter names, a new dict will be returned, mapping these parameter
174names to their settings.  Finally, if you pass a dict as parameter,
175its values will be set to the current parameter settings corresponding
176to its keys.
177
178By passing the special name `'all'` as the parameter, you can get a dict
179of all existing configuration parameters.
180
181.. versionadded:: 4.2
182
183.. method:: DB.set_parameter(self, parameter, [value], [local])
184
185    Set the value of run-time parameters
186
187    :param parameter: the run-time parameter(s) to set
188    :type param: string, tuple, list or dict
189    :param value: the value to set
190    :type param: str or None
191    :raises TypeError: Invalid parameter type(s)
192    :raises ValueError: Invalid value argument(s)
193    :raises ProgrammingError: Invalid parameter name(s) or values
194
195If the parameter and the value are strings, the run-time parameter
196will be set to that value.  If no value or *None* is passed as a value,
197then the run-time parameter will be restored to its default value.
198
199You can set several parameters at once by passing a list of parameter
200names, together with a single value that all parameters should be
201set to or with a corresponding list of values.  You can also pass
202the parameters as a set if you only provide a single value.
203Finally, you can pass a dict with parameter names as keys.  In this
204case, you should not pass a value, since the values for the parameters
205will be taken from the dict.
206
207By passing the special name `'all'` as the parameter, you can reset
208all existing settable run-time parameters to their default values.
209
210If you set *local* to `True`, then the command takes effect for only the
211current transaction.  After :meth:`DB.commit` or :meth:`DB.rollback`,
212the session-level setting takes effect again.  Setting *local* to `True`
213will appear to have no effect if it is executed outside a transaction,
214since the transaction will end immediately.
215
216.. versionadded:: 4.2
217
218begin/commit/rollback/savepoint/release -- transaction handling
219---------------------------------------------------------------
220
221.. method:: DB.begin([mode])
222
223    Begin a transaction
224
225    :param str mode: an optional transaction mode such as 'READ ONLY'
226
227    This initiates a transaction block, that is, all following queries
228    will be executed in a single transaction until :meth:`DB.commit`
229    or :meth:`DB.rollback` is called.
230
231.. versionadded:: 4.1
232
233.. method:: DB.start()
234
235    This is the same as the :meth:`DB.begin` method.
236
237.. method:: DB.commit()
238
239    Commit a transaction
240
241    This commits the current transaction. All changes made by the
242    transaction become visible to others and are guaranteed to be
243    durable if a crash occurs.
244
245.. method:: DB.end()
246
247    This is the same as the :meth:`DB.commit` method.
248
249.. versionadded:: 4.1
250
251.. method:: DB.rollback([name])
252
253    Roll back a transaction
254
255    :param str name: optionally, roll back to the specified savepoint
256
257    This rolls back the current transaction and causes all the updates
258    made by the transaction to be discarded.
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, row, [keyname])
290
291    Get a row from a database table or view
292
293    :param str table: name of table or view
294    :param row: 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 *row* 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(table)``.
309
310insert -- insert a row into a database table
311--------------------------------------------
312
313.. method:: DB.insert(table, [row], [col=val, ...])
314
315    Insert a row into a database table
316
317    :param str table: name of table
318    :param dict row: optional dictionary of values
319    :param col: optional keyword arguments for updating the dictionary
320    :returns: the inserted values in the database
321    :rtype: dict
322    :raises ProgrammingError: missing privilege or conflict
323
324This method inserts a row into a table.  If the optional dictionary is
325not supplied then the required values must be included as keyword/value
326pairs.  If a dictionary is supplied then any keywords provided will be
327added to or replace the entry in the dictionary.
328
329The dictionary is then reloaded with the values actually inserted in order
330to pick up values modified by rules, triggers, etc.
331
332Note: The method currently doesn't support insert into views
333although PostgreSQL does.
334
335update -- update a row in a database table
336------------------------------------------
337
338.. method:: DB.update(table, [row], [col=val, ...])
339
340    Update a row in a database table
341
342    :param str table: name of table
343    :param dict row: optional dictionary of values
344    :param col: optional keyword arguments for updating the dictionary
345    :returns: the new row in the database
346    :rtype: dict
347    :raises ProgrammingError: no primary key or missing privilege
348
349Similar to insert but updates an existing row.  The update is based on the
350OID value as munged by get or passed as keyword, or on the primary key of
351the table.  The dictionary is modified to reflect any changes caused by the
352update due to triggers, rules, default values, etc.
353
354Like insert, the dictionary is optional and updates will be performed
355on the fields in the keywords.  There must be an OID or primary key
356either in the dictionary where the OID must be munged, or in the keywords
357where it can be simply the string 'oid'.
358
359upsert -- insert a row with conflict resolution
360-----------------------------------------------
361
362.. method:: DB.upsert(table, [row], [col=val, ...])
363
364    Insert a row into a database table with conflict resolution
365
366    :param str table: name of table
367    :param dict row: optional dictionary of values
368    :param col: optional keyword arguments for specifying the update
369    :returns: the new row in the database
370    :rtype: dict
371    :raises ProgrammingError: no primary key or missing privilege
372
373This method inserts a row into a table, but instead of raising a
374ProgrammingError exception in case a row with the same primary key already
375exists, an update will be executed instead.  This will be performed as a
376single atomic operation on the database, so race conditions can be avoided.
377
378Like the insert method, the first parameter is the name of the table and the
379second parameter can be used to pass the values to be inserted as a dictionary.
380
381Unlike the insert und update statement, keyword parameters are not used to
382modify the dictionary, but to specify which columns shall be updated in case
383of a conflict, and in which way:
384
385A value of `False` or `None` means the column shall not be updated,
386a value of `True` means the column shall be updated with the value that
387has been proposed for insertion, i.e. has been passed as value in the
388dictionary.  Columns that are not specified by keywords but appear as keys
389in the dictionary are also updated like in the case keywords had been passed
390with the value `True`.
391
392So if in the case of a conflict you want to update every column that has been
393passed in the dictionary `d` , you would call ``upsert(table, d)``.  If you
394don't want to do anything in case of a conflict, i.e. leave the existing row
395as it is, call ``upsert(table, d, **dict.fromkeys(d))``.
396
397If you need more fine-grained control of what gets updated, you can also pass
398strings in the keyword parameters.  These strings will be used as SQL
399expressions for the update columns.  In these expressions you can refer
400to the value that already exists in the table by writing the table prefix
401``included.`` before the column name, and you can refer to the value that
402has been proposed for insertion by writing ``excluded.`` as table prefix.
403
404The dictionary is modified in any case to reflect the values in the database
405after the operation has completed.
406
407.. note::
408
409    The method uses the PostgreSQL "upsert" feature which is only available
410    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
411    ProgrammingError if you use this method.
412
413.. versionadded:: 5.0
414
415query -- execute a SQL command string
416-------------------------------------
417
418.. method:: DB.query(command, [arg1, [arg2, ...]])
419
420    Execute a SQL command string
421
422    :param str command: SQL command
423    :param arg*: optional positional arguments
424    :returns: result values
425    :rtype: :class:`Query`, None
426    :raises TypeError: bad argument type, or too many arguments
427    :raises TypeError: invalid connection
428    :raises ValueError: empty SQL query or lost connection
429    :raises pg.ProgrammingError: error in query
430    :raises pg.InternalError: error during query processing
431
432Similar to the :class:`Connection` function with the same name, except that
433positional arguments can be passed either as a single list or tuple, or as
434individual positional arguments.
435
436Example::
437
438    name = input("Name? ")
439    phone = input("Phone? ")
440    rows = db.query("update employees set phone=$2 where name=$1",
441        (name, phone)).getresult()[0][0]
442    # or
443    rows = db.query("update employees set phone=$2 where name=$1",
444         name, phone).getresult()[0][0]
445
446clear -- clear row values in memory
447-----------------------------------
448
449.. method:: DB.clear(table, [row])
450
451    Clear row values in memory
452
453    :param str table: name of table
454    :param dict row: optional dictionary of values
455    :returns: an empty row
456    :rtype: dict
457
458This method clears all the attributes to values determined by the types.
459Numeric types are set to 0, Booleans are set to ``'f'``, and everything
460else is set to the empty string.  If the row argument is present, it is
461used as the row dictionary and any entries matching attribute names are
462cleared with everything else left unchanged.
463
464If the dictionary is not supplied a new one is created.
465
466delete -- delete a row from a database table
467--------------------------------------------
468
469.. method:: DB.delete(table, [row], [col=val, ...])
470
471    Delete a row from a database table
472
473    :param str table: name of table
474    :param dict d: optional dictionary of values
475    :param col: optional keyword arguments for updating the dictionary
476    :rtype: None
477
478This method deletes the row from a table.  It deletes based on the OID value
479as munged by get or passed as keyword, or on the primary key of the table.
480The return value is the number of deleted rows (i.e. 0 if the row did not
481exist and 1 if the row was deleted).
482
483truncate -- Quickly empty database tables
484-----------------------------------------
485
486.. method:: DB.truncate(self, table, [restart], [cascade], [only]):
487
488    Empty a table or set of tables
489
490    :param table: the name of the table(s)
491    :type table: str, list or set
492    :param bool restart: whether table sequences should be restarted
493    :param bool cascade: whether referenced tables should also be truncated
494    :param only: whether only parent tables should be truncated
495    :type only: bool or list
496
497This method quickly removes all rows from the given table or set
498of tables.  It has the same effect as an unqualified DELETE on each
499table, but since it does not actually scan the tables it is faster.
500Furthermore, it reclaims disk space immediately, rather than requiring
501a subsequent VACUUM operation. This is most useful on large tables.
502
503If *restart* is set to `True`, sequences owned by columns of the truncated
504table(s) are automatically restarted.  If *cascade* is set to `True`, it
505also truncates all tables that have foreign-key references to any of
506the named tables.  If the parameter *only* is not set to `True`, all the
507descendant tables (if any) will also be truncated. Optionally, a ``*``
508can be specified after the table name to explicitly indicate that
509descendant tables are included.  If the parameter *table* is a list,
510the parameter *only* can also be a list of corresponding boolean values.
511
512.. versionadded:: 4.2
513
514escape_literal -- escape a literal string for use within SQL
515------------------------------------------------------------
516
517.. method:: DB.escape_literal(string)
518
519    Escape a string for use within SQL as a literal constant
520
521    :param str string: the string that is to be escaped
522    :returns: the escaped string
523    :rtype: str
524
525This method escapes a string for use within an SQL command. This is useful
526when inserting data values as literal constants in SQL commands. Certain
527characters (such as quotes and backslashes) must be escaped to prevent them
528from being interpreted specially by the SQL parser.
529
530.. versionadded:: 4.1
531
532escape_identifier -- escape an identifier string for use within SQL
533-------------------------------------------------------------------
534
535.. method:: DB.escape_identifier(string)
536
537    Escape a string for use within SQL as an identifier
538
539    :param str string: the string that is to be escaped
540    :returns: the escaped string
541    :rtype: str
542
543This method escapes a string for use as an SQL identifier, such as a table,
544column, or function name. This is useful when a user-supplied identifier
545might contain special characters that would otherwise not be interpreted
546as part of the identifier by the SQL parser, or when the identifier might
547contain upper case characters whose case should be preserved.
548
549.. versionadded:: 4.1
550
551escape_string -- escape a string for use within SQL
552---------------------------------------------------
553
554.. method:: DB.escape_string(string)
555
556    Escape a string for use within SQL
557
558    :param str string: the string that is to be escaped
559    :returns: the escaped string
560    :rtype: str
561
562Similar to the module function with the same name, but the
563behavior of this method is adjusted depending on the connection properties
564(such as character encoding).
565
566escape_bytea -- escape binary data for use within SQL
567-----------------------------------------------------
568
569.. method:: DB.escape_bytea(datastring)
570
571    Escape binary data for use within SQL as type ``bytea``
572
573    :param str datastring: string containing the binary data that is to be escaped
574    :returns: the escaped string
575    :rtype: str
576
577Similar to the module function with the same name, but the
578behavior of this method is adjusted depending on the connection properties
579(in particular, whether standard-conforming strings are enabled).
580
581unescape_bytea -- unescape data that has been retrieved as text
582---------------------------------------------------------------
583
584.. method:: DB.unescape_bytea(string)
585
586    Unescape ``bytea`` data that has been retrieved as text
587
588    :param datastring: the ``bytea`` data string that has been retrieved as text
589    :returns: byte string containing the binary data
590    :rtype: bytes
591
592See the module function with the same name.
593
594use_regtypes -- determine use of regular type names
595---------------------------------------------------
596
597.. method:: DB.use_regtypes([regtypes])
598
599    Determine whether regular type names shall be used
600
601    :param bool regtypes: if passed, set whether regular type names shall be used
602    :returns: whether regular type names are used
603
604The :meth:`DB.get_attnames` method can return either simplified "classic"
605type names (the default) or more specific "regular" type names. Which kind
606of type names is used can be changed by calling :meth:`DB.get_regtypes`.
607If you pass a boolean, it sets whether regular type names shall be used.
608The method can also be used to check through its return value whether
609currently regular type names are used.
610
611.. versionadded:: 4.1
612
613notification_handler -- create a notification handler
614-----------------------------------------------------
615
616.. class:: DB.notification_handler(event, callback, [arg_dict], [timeout], [stop_event])
617
618    Create a notification handler instance
619
620    :param str event: the name of an event to listen for
621    :param callback: a callback function
622    :param dict arg_dict: an optional dictionary for passing arguments
623    :param timeout: the time-out when waiting for notifications
624    :type timeout: int, float or None
625    :param str stop_event: an optional different name to be used as stop event
626
627This method creates a :class:`pg.NotificationHandler` object using the
628:class:`DB` connection as explained under :doc:`notification`.
629
630.. versionadded:: 4.1.1
Note: See TracBrowser for help on using the repository browser.