source: branches/4.x/docs/pgdb.rst @ 684

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

Some fixes in the documentation

  • Property svn:keywords set to Author Date Id Revision
File size: 16.4 KB
Line 
1----------------------------------------------
2:mod:`pgdb` --- The DB-API Compliant Interface
3----------------------------------------------
4
5.. module:: pgdb
6
7.. contents:: Contents
8
9
10Introduction
11============
12
13You may either choose to use the "classic" PyGreSQL interface
14provided by the :mod:`pg` module or else the
15DB-API 2.0 compliant interface provided by the :mod:`pgdb` module.
16
17`DB-API 2.0 <http://www.python.org/dev/peps/pep-0249/>`_
18(Python Database API Specification v2.0)
19is a specification for connecting to databases (not only PostGreSQL)
20from Python that has been developed by the Python DB-SIG in 1999.
21
22The following documentation covers only the newer :mod:`pgdb` API.
23
24The authoritative programming information for the DB-API is :pep:`0249`
25
26A useful tutorial-like `introduction to the DB-API
27<http://www2.linuxjournal.com/lj-issues/issue49/2605.html>`_
28has been written by Andrew M. Kuchling for the LINUX Journal in 1998.
29
30
31Module functions and constants
32==============================
33
34The :mod:`pgdb` module defines a :func:`connect` function that allows to
35connect to a database, some global constants describing the capabilities
36of the module as well as several exception classes.
37
38connect -- Open a PostgreSQL connection
39---------------------------------------
40
41.. function:: connect([dsn], [user], [password], [host], [database])
42
43    Return a new connection to the database
44
45    :param str dsn: data source name as string
46    :param str user: the database user name
47    :param str password: the database password
48    :param str host: the hostname of the database
49    :param database: the name of the database
50    :returns: a connection object
51    :rtype: :class:`pgdbCnx`
52    :raises pgdb.OperationalError: error connecting to the database
53
54This function takes parameters specifying how to connect to a PostgreSQL
55database and returns a :class:`pgdbCnx` object using these parameters.
56If specified, the *dsn* parameter must be a string with the format
57``'host:base:user:passwd:opt:tty'``. All of the parts specified in the *dsn*
58are optional. You can also specify the parameters individually using keyword
59arguments, which always take precedence. The *host* can also contain a port
60if specified in the format ``'host:port'``. In the *opt* part of the *dsn*
61you can pass command-line options to the server, the *tty* part is used to
62send server debug output.
63
64Example::
65
66    con = connect(dsn='myhost:mydb', user='guido', password='234$')
67
68
69Module constants
70----------------
71
72.. data:: apilevel
73
74    The string constant ``'2.0'``, stating that the module is DB-API 2.0 level
75    compliant.
76
77.. data:: threadsafety
78
79    The integer constant 1, stating that the module itself is thread-safe,
80    but the connections are not thread-safe, and therefore must be protected
81    with a lock if you want to use them from different threads.
82
83.. data:: paramstyle
84
85   The string constant ``pyformat``, stating that parameters should be passed
86   using Python extended format codes, e.g. ``" ... WHERE name=%(name)s"``.
87
88Errors raised by this module
89----------------------------
90
91The errors that can be raised by the :mod:`pgdb` module are the following:
92
93.. exception:: Warning
94
95    Exception raised for important warnings like data truncations while
96    inserting.
97
98.. exception:: Error
99
100    Exception that is the base class of all other error exceptions. You can
101    use this to catch all errors with one single except statement.
102    Warnings are not considered errors and thus do not use this class as base.
103
104.. exception:: InterfaceError
105
106    Exception raised for errors that are related to the database interface
107    rather than the database itself.
108
109.. exception:: DatabaseError
110
111    Exception raised for errors that are related to the database.
112
113.. exception:: DataError
114
115    Exception raised for errors that are due to problems with the processed
116    data like division by zero or numeric value out of range.
117
118.. exception:: OperationalError
119
120    Exception raised for errors that are related to the database's operation
121    and not necessarily under the control of the programmer, e.g. an unexpected
122    disconnect occurs, the data source name is not found, a transaction could
123    not be processed, or a memory allocation error occurred during processing.
124
125.. exception:: IntegrityError
126
127    Exception raised when the relational integrity of the database is affected,
128    e.g. a foreign key check fails.
129
130.. exception:: ProgrammingError
131
132    Exception raised for programming errors, e.g. table not found or already
133    exists, syntax error in the SQL statement or wrong number of parameters
134    specified.
135
136.. exception:: NotSupportedError
137
138    Exception raised in case a method or database API was used which is not
139    supported by the database.
140
141
142pgdbCnx -- The connection object
143================================
144
145.. class:: pgdbCnx
146
147These connection objects respond to the following methods.
148
149Note that ``pgdb.pgdbCnx`` objects also implement the context manager protocol,
150i.e. you can use them in a ``with`` statement.
151
152close -- close the connection
153-----------------------------
154
155.. method:: pgdbCnx.close()
156
157    Close the connection now (rather than whenever it is deleted)
158
159    :rtype: None
160
161The connection will be unusable from this point forward; an :exc:`Error`
162(or subclass) exception will be raised if any operation is attempted with
163the connection. The same applies to all cursor objects trying to use the
164connection. Note that closing a connection without committing the changes
165first will cause an implicit rollback to be performed.
166
167commit -- commit the connection
168-------------------------------
169
170.. method:: pgdbCnx.commit()
171
172    Commit any pending transaction to the database
173
174    :rtype: None
175
176Note that connections always use a transaction, there is no auto-commit.
177
178rollback -- roll back the connection
179------------------------------------
180
181.. method:: pgdbCnx.rollback()
182
183    Roll back any pending transaction to the database
184
185    :rtype: None
186
187This method causes the database to roll back to the start of any pending
188transaction. Closing a connection without committing the changes first will
189cause an implicit rollback to be performed.
190
191cursor -- return a new cursor object
192------------------------------------
193
194.. method:: pgdbCnx.cursor()
195
196    Return a new cursor object using the connection
197
198    :returns: a connection object
199    :rtype: :class:`pgdbCursor`
200
201This method returns a new :class:`pgdbCursor` object that can be used to
202operate on the database in the way described in the next section.
203
204
205pgdbCursor -- The cursor object
206===============================
207
208.. class:: pgdbCursor
209
210These objects represent a database cursor, which is used to manage the context
211of a fetch operation. Cursors created from the same connection are not
212isolated, i.e., any changes done to the database by a cursor are immediately
213visible by the other cursors. Cursors created from different connections can
214or can not be isolated, depending on the level of transaction isolation.
215The default PostgreSQL transaction isolation level is "read committed".
216
217Cursor objects respond to the following methods and attributes.
218
219Note that ``pgdbCursor`` objects also implement both the iterator and the
220context manager protocol, i.e. you can iterate over them and you can use them
221in a ``with`` statement.
222
223description -- details regarding the result columns
224---------------------------------------------------
225
226.. attribute:: pgdbCursor.description
227
228    This read-only attribute is a sequence of 7-item tuples.
229
230    Each of these tuples contains information describing one result column:
231
232    - *name*
233    - *type_code*
234    - *display_size*
235    - *internal_size*
236    - *precision*
237    - *scale*
238    - *null_ok*
239
240    Note that *display_size*, *precision*, *scale* and *null_ok*
241    are not implemented.
242
243    This attribute will be ``None`` for operations that do not return rows
244    or if the cursor has not had an operation invoked via the
245    :meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` method yet.
246
247rowcount -- number of rows of the result
248----------------------------------------
249
250.. attribute:: pgdbCursor.rowcount
251
252    This read-only attribute specifies the number of rows that the last
253    :meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` call produced
254    (for DQL statements like SELECT) or affected (for DML statements like
255    UPDATE or INSERT ). The attribute is -1 in case no such method call has
256    been performed on the cursor or the rowcount of the last operation
257    cannot be determined by the interface.
258
259close -- close the cursor
260-------------------------
261
262.. method:: pgdbCursor.close()
263
264    Close the cursor now (rather than whenever it is deleted)
265
266    :rtype: None
267
268The cursor will be unusable from this point forward; an :exc:`Error`
269(or subclass) exception will be raised if any operation is attempted
270with the cursor.
271
272execute -- execute a database operation
273---------------------------------------
274
275.. method:: pgdbCursor.execute(operation, [parameters])
276
277    Prepare and execute a database operation (query or command)
278
279    :param str operation: the database operation
280    :param parameters: a sequence or mapping of parameters
281    :returns: the cursor, so you can chain commands
282
283Parameters may be provided as sequence or mapping and will be bound to
284variables in the operation. Variables are specified using Python extended
285format codes, e.g. ``" ... WHERE name=%(name)s"``.
286
287A reference to the operation will be retained by the cursor. If the same
288operation object is passed in again, then the cursor can optimize its behavior.
289This is most effective for algorithms where the same operation is used,
290but different parameters are bound to it (many times).
291
292The parameters may also be specified as list of tuples to e.g. insert multiple
293rows in a single operation, but this kind of usage is deprecated:
294:meth:`pgdbCursor.executemany` should be used instead.
295
296executemany -- execute many similar database operations
297-------------------------------------------------------
298
299.. method:: pgdbCursor.executemany(operation, [seq_of_parameters])
300
301    Prepare and execute many similar database operations (queries or commands)
302
303    :param str operation: the database operation
304    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
305    :returns: the cursor, so you can chain commands
306
307Prepare a database operation (query or command) and then execute it against
308all parameter tuples or mappings found in the sequence *seq_of_parameters*.
309
310Parameters are bounded to the query using Python extended format codes,
311e.g. ``" ... WHERE name=%(name)s"``.
312
313fetchone -- fetch next row of the query result
314----------------------------------------------
315
316.. method:: pgdbCursor.fetchone()
317
318    Fetch the next row of a query result set
319
320    :returns: the next row of the query result set
321    :rtype: list or None
322
323Fetch the next row of a query result set, returning a single list,
324or ``None`` when no more data is available.
325
326An :exc:`Error` (or subclass) exception is raised if the previous call to
327:meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` did not produce
328any result set or no call was issued yet.
329
330fetchmany -- fetch next set of rows of the query result
331-------------------------------------------------------
332
333.. method:: pgdbCursor.fetchmany([size=None], [keep=False])
334
335    Fetch the next set of rows of a query result
336
337    :param size: the number of rows to be fetched
338    :type size: int or None
339    :param keep: if set to true, will keep the passed arraysize
340    :tpye keep: bool
341    :returns: the next set of rows of the query result
342    :rtype: list of lists
343
344Fetch the next set of rows of a query result, returning a list of lists.
345An empty sequence is returned when no more rows are available.
346
347The number of rows to fetch per call is specified by the *size* parameter.
348If it is not given, the cursor's :attr:`arraysize` determines the number of
349rows to be fetched. If you set the *keep* parameter to True, this is kept as
350new :attr:`arraysize`.
351
352The method tries to fetch as many rows as indicated by the *size* parameter.
353If this is not possible due to the specified number of rows not being
354available, fewer rows may be returned.
355
356An :exc:`Error` (or subclass) exception is raised if the previous call to
357:meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` did not produce
358any result set or no call was issued yet.
359
360Note there are performance considerations involved with the *size* parameter.
361For optimal performance, it is usually best to use the :attr:`arraysize`
362attribute. If the *size* parameter is used, then it is best for it to retain
363the same value from one :meth:`pgdbCursor.fetchmany` call to the next.
364
365fetchall -- fetch all rows of the query result
366----------------------------------------------
367
368.. method:: pgdbCursor.fetchall()
369
370    Fetch all (remaining) rows of a query result
371
372    :returns: the set of all rows of the query result
373    :rtype: list of list
374
375Fetch all (remaining) rows of a query result, returning them as list of lists.
376Note that the cursor's :attr:`arraysize` attribute can affect the performance
377of this operation.
378
379row_factory -- process a row of the query result
380------------------------------------------------
381
382.. method:: pgdbCursor.row_factory(row)
383
384    Process rows before they are returned
385
386    :param list row: the currently processed row of the result set
387    :returns: the transformed row that the cursor methods shall return
388
389.. note::
390
391    This method is not part of the DB-API 2 standard.
392
393You can overwrite this method with a custom row factory, e.g.
394if you want to return rows as dicts instead of lists::
395
396    class DictCursor(pgdb.pgdbCursor):
397
398        def row_factory(self, row):
399            return dict((d[0], v) for d, v in zip(self.description, row))
400
401    cur = DictCursor(con)
402
403arraysize - the number of rows to fetch at a time
404-------------------------------------------------
405
406.. attribute:: pgdbCursor.arraysize
407
408    The number of rows to fetch at a time
409
410This read/write attribute specifies the number of rows to fetch at a time with
411:meth:`pgdbCursor.fetchmany`. It defaults to 1 meaning to fetch a single row
412at a time.
413
414
415pgdbType -- Type objects and constructors
416=========================================
417
418.. class:: pgdbType
419
420The :attr:`pgdbCursor.description` attribute returns information about each
421of the result columns of a query. The *type_code* must compare equal to one
422of the :class:`pgdbType` objects defined below. Type objects can be equal to
423more than one type code (e.g. :class:`DATETIME` is equal to the type codes
424for date, time and timestamp columns).
425
426The :mod:`pgdb` module exports the following constructors and singletons:
427
428.. function:: Date(year, month, day)
429
430    Construct an object holding a date value
431
432.. function:: Time(hour, minute=0, second=0, microsecond=0)
433
434    Construct an object holding a time value
435
436.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
437
438    Construct an object holding a time stamp value
439
440.. function:: DateFromTicks(ticks)
441
442    Construct an object holding a date value from the given *ticks* value
443
444.. function:: TimeFromTicks(ticks)
445
446    Construct an object holding a time value from the given *ticks* value
447
448.. function:: TimestampFromTicks(ticks)
449
450    Construct an object holding a time stamp from the given *ticks* value
451
452.. function:: Binary(bytes)
453
454    Construct an object capable of holding a (long) binary string value
455
456.. class:: STRING
457
458    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
459
460.. class:: BINARY type
461
462    Used to describe (long) binary columns (``bytea``)
463
464.. class:: NUMBER
465
466    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
467
468.. class:: DATETIME
469
470    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
471
472.. class:: ROWID
473
474    Used to describe the ``oid`` column of PostgreSQL database tables
475
476.. note:
477
478    The following more specific types are not part of the DB-API 2 standard.
479
480.. class:: BOOL
481
482    Used to describe ``boolean`` columns
483
484.. class:: SMALLINT
485
486    Used to describe ``smallint`` columns
487
488.. class:: INTEGER
489
490    Used to describe ``integer`` columns
491
492.. class:: LONG
493
494    Used to describe ``bigint`` columns
495
496.. class:: FLOAT
497
498    Used to describe ``float`` columns
499
500.. class:: NUMERIC
501
502    Used to describe ``numeric`` columns
503
504.. class:: MONEY
505
506    Used to describe ``money`` columns
507
508.. class:: DATE
509
510    Used to describe ``date`` columns
511
512.. class:: TIME
513
514    Used to describe ``time`` columns
515
516.. class:: TIMESTAMP
517
518    Used to describe ``timestamp`` columns
519
520.. class:: INTERVAL
521
522    Used to describe date and time ``interval`` columns
Note: See TracBrowser for help on using the repository browser.