source: trunk/docs/pgdb.rst @ 676

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

Add the missing docs for pgdb

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