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