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

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

Simplify caching and handling of class names

The caches now use the class names as keys as they are passed in.
We do not automatically calculate the qualified name any more,
since this causes too much overhead. Also, we fill the pkey cache
not pro-actively with tables from all possible schemes any more.

Most of the internal auxiliary functions for handling class names
could be discarded by making good use of quote_ident and reglass.

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