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

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

Reorganize and improve the Sphinx docs

The Sphinx HTML docs can now also serve as home page for the project.
They can be built with the mkdocs script and can then be found in the
docs/_build/html directory (this needs sphinx and the cloud_sptheme).

The "actual" core docs can be found in docs/contents. The layout has
been modified to only show these pages with the typical documentation
relbars; the other pages of the homepage use a simpler layout. The
pg and pgdb documentation has been cut in chunks and can now be found
in docs/contents/pg and docs/contents/pgdb.

File size: 10.6 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_string -- escape a string for use within SQL
275---------------------------------------------------
276
277.. method:: DB.escape_string(string)
278
279    Escape a string for use within SQL
280
281    :param str string: the string that is to be escaped
282    :returns: the escaped string
283    :rtype: str
284
285Similar to the module function with the same name, but the
286behavior of this method is adjusted depending on the connection properties
287(such as character encoding).
288
289escape_bytea -- escape binary data for use within SQL
290-----------------------------------------------------
291
292.. method:: DB.escape_bytea(datastring)
293
294    Escape binary data for use within SQL as type ``bytea``
295
296    :param str datastring: string containing the binary data that is to be escaped
297    :returns: the escaped string
298    :rtype: str
299
300Similar to the module function with the same name, but the
301behavior of this method is adjusted depending on the connection properties
302(in particular, whether standard-conforming strings are enabled).
303
304unescape_bytea -- unescape data that has been retrieved as text
305---------------------------------------------------------------
306
307.. method:: DB.unescape_bytea(string)
308
309    Unescape ``bytea`` data that has been retrieved as text
310
311    :param datastring: the ``bytea`` data string that has been retrieved as text
312    :returns: byte string containing the binary data
313    :rtype: str
314
315See the module function with the same name.
Note: See TracBrowser for help on using the repository browser.