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

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

Return ordered dict for attributes is possible

Sometimes it's important to know the order of the columns in a table.
By returning an OrderedDict? instead of a dict in get_attnames, we can
deliver that information en passant, while staying backward compatible.

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