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

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

Add documentation for transaction handling methods

Also, the savepoint name is not optional.

File size: 14.1 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 -- the keys are the attribute names,
124     the values are the type names of the attributes.
125
126Given the name of a table, digs out the set of attribute names.
127
128has_table_privilege -- check table privilege
129--------------------------------------------
130
131.. method:: DB.has_table_privilege(table, privilege)
132
133    Check whether current user has specified table privilege
134
135    :param str table: the name of the table
136    :param str privilege: privilege to be checked -- default is 'select'
137    :returns: whether current user has specified table privilege
138    :rtype: bool
139
140Returns True if the current user has the specified privilege for the table.
141
142.. versionadded:: 4.0
143
144begin/commit/rollback/savepoint/release -- transaction handling
145---------------------------------------------------------------
146
147.. method:: DB.begin([mode])
148
149    Begin a transaction
150
151    :param str mode: an optional transaction mode such as 'READ ONLY'
152
153    This initiates a transaction block, that is, all following queries
154    will be executed in a single transaction until :meth:`DB.commit`
155    or :meth:`DB.rollback` is called.
156
157.. versionadded:: 4.1
158
159.. method:: DB.start()
160
161    This is the same as the :meth:`DB.begin` method.
162
163.. method:: DB.commit()
164
165    Commit a transaction
166
167    This commits the current transaction. All changes made by the
168    transaction become visible to others and are guaranteed to be
169    durable if a crash occurs.
170
171.. method:: DB.end()
172
173    This is the same as the :meth:`DB.commit` method.
174
175.. versionadded:: 4.1
176
177.. method:: DB.rollback([name])
178
179    Roll back a transaction
180
181    :param str name: optionally, roll back to the specified savepoint
182
183    This rolls back the current transaction and causes all the updates
184    made by the transaction to be discarded.
185
186.. versionadded:: 4.1
187
188.. method:: DB.savepoint(name)
189
190    Define a new savepoint
191
192    :param str name: the name to give to the new savepoint
193
194    This establishes a new savepoint within the current transaction.
195
196.. versionadded:: 4.1
197
198.. method:: DB.release(name)
199
200    Destroy a savepoint
201
202    :param str name: the name of the savepoint to destroy
203
204    This destroys a savepoint previously defined in the current transaction.
205
206.. versionadded:: 4.1
207
208get -- get a row from a database table or view
209----------------------------------------------
210
211.. method:: DB.get(table, arg, [keyname])
212
213    Get a row from a database table or view
214
215    :param str table:  name of table or view
216    :param arg:  either a dictionary or the value to be looked up
217    :param str keyname: name of field to use as key (optional)
218    :returns: A dictionary - the keys are the attribute names,
219      the values are the row values.
220
221This method is the basic mechanism to get a single row. It assumes
222that the key specifies a unique row. If *keyname* is not specified,
223then the primary key for the table is used. If *arg* is a dictionary
224then the value for the key is taken from it and it is modified to
225include the new values, replacing existing values where necessary.
226For a composite key, *keyname* can also be a sequence of key names.
227The OID is also put into the dictionary if the table has one, but in
228order to allow the caller to work with multiple tables, it is munged
229as ``oid(table)``.
230
231insert -- insert a row into a database table
232--------------------------------------------
233
234.. method:: DB.insert(table, [d,] [key = val, ...])
235
236    Insert a row into a database table
237
238    :param str table: name of table
239    :param dict d: optional dictionary of values
240    :returns: the inserted values
241    :rtype: dict
242
243This method inserts a row into a table.  If the optional dictionary is
244not supplied then the required values must be included as keyword/value
245pairs.  If a dictionary is supplied then any keywords provided will be
246added to or replace the entry in the dictionary.
247
248The dictionary is then reloaded with the values actually inserted in order
249to pick up values modified by rules, triggers, etc.
250
251Note: The method currently doesn't support insert into views
252although PostgreSQL does.
253
254update -- update a row in a database table
255------------------------------------------
256
257.. method:: DB.update(table, [d,] [key = val, ...])
258
259    Update a row in a database table
260
261    :param str table: name of table
262    :param dict d: optional dictionary of values
263    :returns: the new row
264    :rtype: dict
265
266Similar to insert but updates an existing row.  The update is based on the
267OID value as munged by get or passed as keyword, or on the primary key of
268the table.  The dictionary is modified to reflect any changes caused by the
269update due to triggers, rules, default values, etc.
270
271Like insert, the dictionary is optional and updates will be performed
272on the fields in the keywords.  There must be an OID or primary key
273either in the dictionary where the OID must be munged, or in the keywords
274where it can be simply the string 'oid'.
275
276query -- execute a SQL command string
277-------------------------------------
278
279.. method:: DB.query(command, [arg1, [arg2, ...]])
280
281    Execute a SQL command string
282
283    :param str command: SQL command
284    :param arg*: optional positional arguments
285    :returns: result values
286    :rtype: :class:`Query`, None
287    :raises TypeError: bad argument type, or too many arguments
288    :raises TypeError: invalid connection
289    :raises ValueError: empty SQL query or lost connection
290    :raises pg.ProgrammingError: error in query
291    :raises pg.InternalError: error during query processing
292
293Similar to the :class:`Connection` function with the same name, except that
294positional arguments can be passed either as a single list or tuple, or as
295individual positional arguments.
296
297Example::
298
299    name = input("Name? ")
300    phone = input("Phone? ")
301    rows = db.query("update employees set phone=$2 where name=$1",
302        (name, phone)).getresult()[0][0]
303    # or
304    rows = db.query("update employees set phone=$2 where name=$1",
305         name, phone).getresult()[0][0]
306
307clear -- clear row values in memory
308-----------------------------------
309
310.. method:: DB.clear(table, [a])
311
312    Clear row values in memory
313
314    :param str table: name of table
315    :param dict a: optional dictionary of values
316    :returns: an empty row
317    :rtype: dict
318
319This method clears all the attributes to values determined by the types.
320Numeric types are set to 0, Booleans are set to ``'f'``, dates are set
321to ``'now()'`` and everything else is set to the empty string.
322If the array argument is present, it is used as the array and any entries
323matching attribute names are cleared with everything else left unchanged.
324
325If the dictionary is not supplied a new one is created.
326
327delete -- delete a row from a database table
328--------------------------------------------
329
330.. method:: DB.delete(table, [d,] [key = val, ...])
331
332    Delete a row from a database table
333
334    :param str table: name of table
335    :param dict d: optional dictionary of values
336    :rtype: None
337
338This method deletes the row from a table.  It deletes based on the OID value
339as munged by get or passed as keyword, or on the primary key of the table.
340The return value is the number of deleted rows (i.e. 0 if the row did not
341exist and 1 if the row was deleted).
342
343escape_literal -- escape a literal string for use within SQL
344------------------------------------------------------------
345
346.. method:: DB.escape_literal(string)
347
348    Escape a string for use within SQL as a literal constant
349
350    :param str string: the string that is to be escaped
351    :returns: the escaped string
352    :rtype: str
353
354This method escapes a string for use within an SQL command. This is useful
355when inserting data values as literal constants in SQL commands. Certain
356characters (such as quotes and backslashes) must be escaped to prevent them
357from being interpreted specially by the SQL parser.
358
359.. versionadded:: 4.1
360
361escape_identifier -- escape an identifier string for use within SQL
362-------------------------------------------------------------------
363
364.. method:: DB.escape_identifier(string)
365
366    Escape a string for use within SQL as an identifier
367
368    :param str string: the string that is to be escaped
369    :returns: the escaped string
370    :rtype: str
371
372This method escapes a string for use as an SQL identifier, such as a table,
373column, or function name. This is useful when a user-supplied identifier
374might contain special characters that would otherwise not be interpreted
375as part of the identifier by the SQL parser, or when the identifier might
376contain upper case characters whose case should be preserved.
377
378.. versionadded:: 4.1
379
380escape_string -- escape a string for use within SQL
381---------------------------------------------------
382
383.. method:: DB.escape_string(string)
384
385    Escape a string for use within SQL
386
387    :param str string: the string that is to be escaped
388    :returns: the escaped string
389    :rtype: str
390
391Similar to the module function with the same name, but the
392behavior of this method is adjusted depending on the connection properties
393(such as character encoding).
394
395escape_bytea -- escape binary data for use within SQL
396-----------------------------------------------------
397
398.. method:: DB.escape_bytea(datastring)
399
400    Escape binary data for use within SQL as type ``bytea``
401
402    :param str datastring: string containing the binary data that is to be escaped
403    :returns: the escaped string
404    :rtype: str
405
406Similar to the module function with the same name, but the
407behavior of this method is adjusted depending on the connection properties
408(in particular, whether standard-conforming strings are enabled).
409
410unescape_bytea -- unescape data that has been retrieved as text
411---------------------------------------------------------------
412
413.. method:: DB.unescape_bytea(string)
414
415    Unescape ``bytea`` data that has been retrieved as text
416
417    :param datastring: the ``bytea`` data string that has been retrieved as text
418    :returns: byte string containing the binary data
419    :rtype: bytes
420
421See the module function with the same name.
422
423use_regtypes -- determine use of regular type names
424---------------------------------------------------
425
426.. method:: DB.use_regtypes([regtypes])
427
428    Determine whether regular type names shall be used
429
430    :param bool regtypes: if passed, set whether regular type names shall be used
431    :returns: whether regular type names are used
432
433The :meth:`DB.get_attnames` method can return either simplified "classic"
434type names (the default) or more specific "regular" type names. Which kind
435of type names is used can be changed by calling :meth:`DB.get_regtypes`.
436If you pass a boolean, it sets whether regular type names shall be used.
437The method can also be used to check through its return value whether
438currently regular type names are used.
439
440.. versionadded:: 4.1
Note: See TracBrowser for help on using the repository browser.