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

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

Back port minor changes in the docs from trunk to 4.x

File size: 14.0 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    :raises ProgrammingError: no primary key or missing privilege
222
223This method is the basic mechanism to get a single row. It assumes
224that the key specifies a unique row. If *keyname* is not specified,
225then the primary key for the table is used. If *arg* is a dictionary
226then the value for the key is taken from it and it is modified to
227include the new values, replacing existing values where necessary.
228For a composite key, *keyname* can also be a sequence of key names.
229The OID is also put into the dictionary if the table has one, but in
230order to allow the caller to work with multiple tables, it is munged
231as ``oid(schema.table)``.
232
233insert -- insert a row into a database table
234--------------------------------------------
235
236.. method:: DB.insert(table, [d], [col=val, ...])
237
238    Insert a row into a database table
239
240    :param str table: name of table
241    :param dict d: optional dictionary of values
242    :returns: the inserted values in the database
243    :rtype: dict
244    :raises ProgrammingError: missing privilege or conflict
245
246This method inserts a row into a table.  If the optional dictionary is
247not supplied then the required values must be included as keyword/value
248pairs.  If a dictionary is supplied then any keywords provided will be
249added to or replace the entry in the dictionary.
250
251The dictionary is then, if possible, reloaded with the values actually
252inserted in order to pick up values modified by rules, triggers, etc.
253
254Note: The method currently doesn't support insert into views
255although PostgreSQL does.
256
257update -- update a row in a database table
258------------------------------------------
259
260.. method:: DB.update(table, [d], [col=val, ...])
261
262    Update a row in a database table
263
264    :param str table: name of table
265    :param dict d: optional dictionary of values
266    :returns: the new row in the database
267    :rtype: dict
268    :raises ProgrammingError: no primary key or missing privilege
269
270Similar to insert but updates an existing row.  The update is based on the
271OID value as munged by get or passed as keyword, or on the primary key of
272the table.  The dictionary is modified, if possible, to reflect any changes
273caused by the update due to triggers, rules, default values, etc.
274
275Like insert, the dictionary is optional and updates will be performed
276on the fields in the keywords.  There must be an OID or primary key
277either in the dictionary where the OID must be munged, or in the keywords
278where it can be simply the string 'oid'.
279
280query -- execute a SQL command string
281-------------------------------------
282
283.. method:: DB.query(command, [arg1, [arg2, ...]])
284
285    Execute a SQL command string
286
287    :param str command: SQL command
288    :param arg*: optional positional arguments
289    :returns: result values
290    :rtype: :class:`pgqueryobject`, None
291    :raises TypeError: bad argument type, or too many arguments
292    :raises TypeError: invalid connection
293    :raises ValueError: empty SQL query or lost connection
294    :raises pg.ProgrammingError: error in query
295    :raises pg.InternalError: error during query processing
296
297Similar to the :class:`pgobject` function with the same name, except that
298positional arguments can be passed either as a single list or tuple, or as
299individual positional arguments.
300
301Example::
302
303    name = raw_input("Name? ")
304    phone = raw_input("Phone? ")
305    rows = db.query("update employees set phone=$2 where name=$1",
306        (name, phone)).getresult()[0][0]
307    # or
308    rows = db.query("update employees set phone=$2 where name=$1",
309         name, phone).getresult()[0][0]
310
311clear -- clear row values in memory
312-----------------------------------
313
314.. method:: DB.clear(table, [a])
315
316    Clear row values in memory
317
318    :param str table: name of table
319    :param dict a: optional dictionary of values
320    :returns: an empty row
321    :rtype: dict
322
323This method clears all the attributes to values determined by the types.
324Numeric types are set to 0, Booleans are set to ``'f'``, dates are set
325to ``'now()'`` and everything else is set to the empty string.
326If the array argument is present, it is used as the array and any entries
327matching attribute names are cleared with everything else left unchanged.
328
329If the dictionary is not supplied a new one is created.
330
331delete -- delete a row from a database table
332--------------------------------------------
333
334.. method:: DB.delete(table, [d,] [key = val, ...])
335
336    Delete a row from a database table
337
338    :param str table: name of table
339    :param dict d: optional dictionary of values
340    :rtype: None
341
342This method deletes the row from a table.  It deletes based on the OID value
343as munged by get or passed as keyword, or on the primary key of the table.
344The return value is the number of deleted rows (i.e. 0 if the row did not
345exist and 1 if the row was deleted).
346
347escape_literal -- escape a literal string for use within SQL
348------------------------------------------------------------
349
350.. method:: DB.escape_literal(string)
351
352    Escape a string for use within SQL as a literal constant
353
354    :param str string: the string that is to be escaped
355    :returns: the escaped string
356    :rtype: str
357
358This method escapes a string for use within an SQL command. This is useful
359when inserting data values as literal constants in SQL commands. Certain
360characters (such as quotes and backslashes) must be escaped to prevent them
361from being interpreted specially by the SQL parser.
362
363.. versionadded:: 4.1
364
365escape_identifier -- escape an identifier string for use within SQL
366-------------------------------------------------------------------
367
368.. method:: DB.escape_identifier(string)
369
370    Escape a string for use within SQL as an identifier
371
372    :param str string: the string that is to be escaped
373    :returns: the escaped string
374    :rtype: str
375
376This method escapes a string for use as an SQL identifier, such as a table,
377column, or function name. This is useful when a user-supplied identifier
378might contain special characters that would otherwise not be interpreted
379as part of the identifier by the SQL parser, or when the identifier might
380contain upper case characters whose case should be preserved.
381
382.. versionadded:: 4.1
383
384escape_bytea -- escape binary data for use within SQL
385-----------------------------------------------------
386
387.. method:: DB.escape_bytea(datastring)
388
389    Escape binary data for use within SQL as type ``bytea``
390
391    :param str datastring: string containing the binary data that is to be escaped
392    :returns: the escaped string
393    :rtype: str
394
395Similar to the module function with the same name, but the
396behavior of this method is adjusted depending on the connection properties
397(in particular, whether standard-conforming strings are enabled).
398
399unescape_bytea -- unescape data that has been retrieved as text
400---------------------------------------------------------------
401
402.. method:: DB.unescape_bytea(string)
403
404    Unescape ``bytea`` data that has been retrieved as text
405
406    :param datastring: the ``bytea`` data string that has been retrieved as text
407    :returns: byte string containing the binary data
408    :rtype: str
409
410See the module function with the same name.
411
412use_regtypes -- determine use of regular type names
413---------------------------------------------------
414
415.. method:: DB.use_regtypes([regtypes])
416
417    Determine whether regular type names shall be used
418
419    :param bool regtypes: if passed, set whether regular type names shall be used
420    :returns: whether regular type names are used
421
422The :meth:`DB.get_attnames` method can return either simplified "classic"
423type names (the default) or more specific "regular" type names. Which kind
424of type names is used can be changed by calling :meth:`DB.get_regtypes`.
425If you pass a boolean, it sets whether regular type names shall be used.
426The method can also be used to check through its return value whether
427currently regular type names are used.
428
429.. versionadded:: 4.1
Note: See TracBrowser for help on using the repository browser.