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

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

Add version information for new/changed features to docs

File size: 12.2 KB
Line 
1The DB wrapper class
2====================
3
4.. class:: DB
5
6The :class:`pgobject` methods are wrapped in the class :class:`DB`.
7The preferred way to use this module is as follows::
8
9    import pg
10
11    db = pg.DB(...)  # see below
12
13    for r in db.query(  # just for example
14        """SELECT foo,bar
15         FROM foo_bar_table
16         WHERE foo !~ bar"""
17        ).dictresult():
18
19        print '%(foo)s %(bar)s' % r
20
21This class can be subclassed as in this example::
22
23    import pg
24
25    class DB_ride(pg.DB):
26        """Ride database wrapper
27
28        This class encapsulates the database functions and the specific
29        methods for the ride database."""
30
31    def __init__(self):
32        """Open a database connection to the rides database"""
33        pg.DB.__init__(self, dbname='ride')
34        self.query("SET DATESTYLE TO 'ISO'")
35
36    [Add or override methods here]
37
38The following describes the methods and variables of this class.
39
40Initialization
41--------------
42The :class:`DB` class is initialized with the same arguments as the
43:func:`connect` function described above. It also initializes a few
44internal variables. The statement ``db = DB()`` will open the local
45database with the name of the user just like ``connect()`` does.
46
47You can also initialize the DB class with an existing :mod:`pg` or :mod:`pgdb`
48connection. Pass this connection as a single unnamed parameter, or as a
49single parameter named ``db``. This allows you to use all of the methods
50of the DB class with a DB-API 2 compliant connection. Note that the
51:meth:`pgobject.close` and :meth:`pgobject.reopen` methods are inoperative
52in this case.
53
54pkey -- return the primary key of a table
55-----------------------------------------
56
57.. method:: DB.pkey(table)
58
59    Return the primary key of a table
60
61    :param str table: name of table
62    :returns: Name of the field which is the primary key of the table
63    :rtype: str
64
65This method returns the primary key of a table. For composite primary
66keys, the return value will be a frozenset. Note that this raises an
67exception if the table does not have a primary key.
68
69get_databases -- get list of databases in the system
70----------------------------------------------------
71
72.. method:: DB.get_databases()
73
74    Get the list of databases in the system
75
76    :returns: all databases in the system
77    :rtype: list
78
79Although you can do this with a simple select, it is added here for
80convenience.
81
82get_relations -- get list of relations in connected database
83------------------------------------------------------------
84
85.. method:: DB.get_relations(kinds)
86
87    Get the list of relations in connected database
88
89    :param str kinds: a string or sequence of type letters
90    :returns: all relations of the given kinds in the database
91    :rtype: list
92
93The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
94``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
95If `kinds` is None or an empty string, all relations are returned (this is
96also the default). Although you can do this with a simple select, it is
97added here for convenience.
98
99get_tables -- get list of tables in connected database
100------------------------------------------------------
101
102.. method:: DB.get_tables()
103
104    Get the list of tables in connected database
105
106    :returns: all tables in connected database
107    :rtype: list
108
109This is a shortcut for ``get_relations('r')`` that has been added for
110convenience.
111
112get_attnames -- get the attribute names of a table
113--------------------------------------------------
114
115.. method:: DB.get_attnames(table)
116
117    Get the attribute names of a table
118
119    :param str table: name of table
120    :returns: A dictionary -- the keys are the attribute names,
121     the values are the type names of the attributes.
122
123Given the name of a table, digs out the set of attribute names.
124
125has_table_privilege -- check whether current user has specified table privilege
126-------------------------------------------------------------------------------
127
128.. method:: DB.has_table_privilege(table, privilege)
129
130    Check whether current user has specified table privilege
131
132    :param str table: the name of the table
133    :param str privilege: privilege to be checked -- default is 'select'
134    :returns: whether current user has specified table privilege
135    :rtype: bool
136
137Returns True if the current user has the specified privilege for the table.
138
139get -- get a row from a database table or view
140----------------------------------------------
141
142.. method:: DB.get(table, arg, [keyname])
143
144    Get a row from a database table or view
145
146    :param str table:  name of table or view
147    :param arg:  either a dictionary or the value to be looked up
148    :param str keyname: name of field to use as key (optional)
149    :returns: A dictionary - the keys are the attribute names,
150      the values are the row values.
151
152This method is the basic mechanism to get a single row. It assumes
153that the key specifies a unique row. If *keyname* is not specified,
154then the primary key for the table is used. If *arg* is a dictionary
155then the value for the key is taken from it and it is modified to
156include the new values, replacing existing values where necessary.
157For a composite key, *keyname* can also be a sequence of key names.
158The OID is also put into the dictionary if the table has one, but in
159order to allow the caller to work with multiple tables, it is munged
160as ``oid(schema.table)``.
161
162insert -- insert a row into a database table
163--------------------------------------------
164
165.. method:: DB.insert(table, [d,] [key = val, ...])
166
167    Insert a row into a database table
168
169    :param str table: name of table
170    :param dict d: optional dictionary of values
171    :returns: the inserted values
172    :rtype: dict
173
174This method inserts a row into a table.  If the optional dictionary is
175not supplied then the required values must be included as keyword/value
176pairs.  If a dictionary is supplied then any keywords provided will be
177added to or replace the entry in the dictionary.
178
179The dictionary is then, if possible, reloaded with the values actually
180inserted in order to pick up values modified by rules, triggers, etc.
181
182Note: The method currently doesn't support insert into views
183although PostgreSQL does.
184
185update -- update a row in a database table
186------------------------------------------
187
188.. method:: DB.update(table, [d,] [key = val, ...])
189
190    Update a row in a database table
191
192    :param str table: name of table
193    :param dict d: optional dictionary of values
194    :returns: the new row
195    :rtype: dict
196
197Similar to insert but updates an existing row.  The update is based on the
198OID value as munged by get or passed as keyword, or on the primary key of
199the table.  The dictionary is modified, if possible, to reflect any changes
200caused by the update due to triggers, rules, default values, etc.
201
202Like insert, the dictionary is optional and updates will be performed
203on the fields in the keywords.  There must be an OID or primary key
204either in the dictionary where the OID must be munged, or in the keywords
205where it can be simply the string 'oid'.
206
207query -- execute a SQL command string
208-------------------------------------
209
210.. method:: DB.query(command, [arg1, [arg2, ...]])
211
212    Execute a SQL command string
213
214    :param str command: SQL command
215    :param arg*: optional positional arguments
216    :returns: result values
217    :rtype: :class:`pgqueryobject`, None
218    :raises TypeError: bad argument type, or too many arguments
219    :raises TypeError: invalid connection
220    :raises ValueError: empty SQL query or lost connection
221    :raises pg.ProgrammingError: error in query
222    :raises pg.InternalError: error during query processing
223
224Similar to the :class:`pgobject` function with the same name, except that
225positional arguments can be passed either as a single list or tuple, or as
226individual positional arguments.
227
228Example::
229
230    name = raw_input("Name? ")
231    phone = raw_input("Phone? ")
232    rows = db.query("update employees set phone=$2 where name=$1",
233        (name, phone)).getresult()[0][0]
234    # or
235    rows = db.query("update employees set phone=$2 where name=$1",
236         name, phone).getresult()[0][0]
237
238clear -- clear row values in memory
239-----------------------------------
240
241.. method:: DB.clear(table, [a])
242
243    Clear row values in memory
244
245    :param str table: name of table
246    :param dict a: optional dictionary of values
247    :returns: an empty row
248    :rtype: dict
249
250This method clears all the attributes to values determined by the types.
251Numeric types are set to 0, Booleans are set to ``'f'``, dates are set
252to ``'now()'`` and everything else is set to the empty string.
253If the array argument is present, it is used as the array and any entries
254matching attribute names are cleared with everything else left unchanged.
255
256If the dictionary is not supplied a new one is created.
257
258delete -- delete a row from a database table
259--------------------------------------------
260
261.. method:: DB.delete(table, [d,] [key = val, ...])
262
263    Delete a row from a database table
264
265    :param str table: name of table
266    :param dict d: optional dictionary of values
267    :rtype: None
268
269This method deletes the row from a table.  It deletes based on the OID value
270as munged by get or passed as keyword, or on the primary key of the table.
271The return value is the number of deleted rows (i.e. 0 if the row did not
272exist and 1 if the row was deleted).
273
274escape_literal -- escape a literal string for use within SQL
275------------------------------------------------------------
276
277.. method:: DB.escape_literal(string)
278
279    Escape a string for use within SQL as a literal constant
280
281    :param str string: the string that is to be escaped
282    :returns: the escaped string
283    :rtype: str
284
285This method escapes a string for use within an SQL command. This is useful
286when inserting data values as literal constants in SQL commands. Certain
287characters (such as quotes and backslashes) must be escaped to prevent them
288from being interpreted specially by the SQL parser.
289
290.. versionadded:: 4.1
291
292escape_identifier -- escape an identifier string for use within SQL
293-------------------------------------------------------------------
294
295.. method:: DB.escape_identifier(string)
296
297    Escape a string for use within SQL as an identifier
298
299    :param str string: the string that is to be escaped
300    :returns: the escaped string
301    :rtype: str
302
303This method escapes a string for use as an SQL identifier, such as a table,
304column, or function name. This is useful when a user-supplied identifier
305might contain special characters that would otherwise not be interpreted
306as part of the identifier by the SQL parser, or when the identifier might
307contain upper case characters whose case should be preserved.
308
309.. versionadded:: 4.1
310
311escape_bytea -- escape binary data for use within SQL
312-----------------------------------------------------
313
314.. method:: DB.escape_bytea(datastring)
315
316    Escape binary data for use within SQL as type ``bytea``
317
318    :param str datastring: string containing the binary data that is to be escaped
319    :returns: the escaped string
320    :rtype: str
321
322Similar to the module function with the same name, but the
323behavior of this method is adjusted depending on the connection properties
324(in particular, whether standard-conforming strings are enabled).
325
326unescape_bytea -- unescape data that has been retrieved as text
327---------------------------------------------------------------
328
329.. method:: DB.unescape_bytea(string)
330
331    Unescape ``bytea`` data that has been retrieved as text
332
333    :param datastring: the ``bytea`` data string that has been retrieved as text
334    :returns: byte string containing the binary data
335    :rtype: str
336
337See the module function with the same name.
338
339use_regtypes -- determine use of regular type names
340---------------------------------------------------
341
342.. method:: DB.use_regtypes([regtypes])
343
344    Determine whether regular type names shall be used
345
346    :param bool regtypes: if passed, set whether regular type names shall be used
347    :returns: whether regular type names are used
348
349The :meth:`DB.get_attnames` method can return either simplified "classic"
350type names (the default) or more specific "regular" type names. Which kind
351of type names is used can be changed by calling :meth:`DB.get_regtypes`.
352If you pass a boolean, it sets whether regular type names shall be used.
353The method can also be used to check through its return value whether
354currently regular type names are used.
355
356.. versionadded:: 4.1
Note: See TracBrowser for help on using the repository browser.