source: trunk/docs/pgdb.rst @ 681

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

Let cursor.description return named tuples

  • Property svn:keywords set to Author Date Id Revision
File size: 16.3 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:`Connection`
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:`Connection` object using these parameters.
56If specified, the *dsn* parameter must be a string with the format
57``'host:base:user:passwd:opt'``. 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.
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
141Connection -- The connection object
142===================================
143
144.. class:: Connection
145
146These connection objects respond to the following methods.
147
148Note that ``pgdb.Connection`` 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:: Connection.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:: Connection.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:: Connection.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:: Connection.cusor()
194
195    Return a new cursor object using the connection
196
197    :returns: a connection object
198    :rtype: :class:`Cursor`
199
200This method returns a new :class:`Cursor` object that can be used to
201operate on the database in the way described in the next section.
202
203
204Cursor -- The cursor object
205===========================
206
207.. class:: 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 ``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 named tuples.
228
229    Each of these named tuples contains information describing
230    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:`Cursor.execute` or :meth:`Cursor.executemany` method yet.
246
247rowcount -- number of rows of the result
248----------------------------------------
249
250.. attribute:: Cursor.rowcount
251
252    This read-only attribute specifies the number of rows that the last
253    :meth:`Cursor.execute` or :meth:`Cursor.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:: Cursor.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:: Cursor.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:`Cursor.executemany` should be used instead.
295
296executemany -- execute many similar database operations
297-------------------------------------------------------
298
299.. method:: Cursor.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:: Cursor.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: tuple or None
322
323Fetch the next row of a query result set, returning a single tuple,
324or ``None`` when no more data is available.
325
326An :exc:`Error` (or subclass) exception is raised if the previous call to
327:meth:`Cursor.execute` or :meth:`Cursor.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:: Cursor.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 tuples
343
344Fetch the next set of rows of a query result, returning a list of tuples.
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:`Cursor.execute` or :meth:`Cursor.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:`Cursor.fetchmany` call to the next.
364
365fetchall -- fetch all rows of the query result
366----------------------------------------------
367
368.. method:: Cursor.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 tuples
374
375Fetch all (remaining) rows of a query result, returning them as list of tuples.
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:: Cursor.row_factory(row)
383
384    Process rows before they are returned
385
386    :param tuple row: the currently processed row of the result set
387    :returns: the transformed row that the cursor methods shall return
388
389Note that this method is not part of the DB-API 2 standard.
390
391You can overwrite this method with a custom row factory, e.g.
392if you want to return rows as dicts instead of tuples::
393
394    class DictCursor(pgdb.Cursor):
395
396        def row_factory(self, row):
397            return {desc[0]:value
398                for desc, value in zip(self.description, row)}
399
400    cur = DictCursor(con)
401
402arraysize - the number of rows to fetch at a time
403-------------------------------------------------
404
405.. attribute:: Cursor.arraysize
406
407    The number of rows to fetch at a time
408
409This read/write attribute specifies the number of rows to fetch at a time with
410:meth:`Cursor.fetchmany`. It defaults to 1 meaning to fetch a single row
411at a time.
412
413
414Type -- Type objects and constructors
415=====================================
416
417.. class:: Type
418
419The :attr:`Cursor.description` attribute returns information about each
420of the result columns of a query. The *type_code* must compare equal to one
421of the :class:`Type` objects defined below. Type objects can be equal to
422more than one type code (e.g. :class:`DATETIME` is equal to the type codes
423for date, time and timestamp columns).
424
425The :mod:`pgdb` module exports the following constructors and singletons:
426
427.. function:: Date(year, month, day)
428
429    Construct an object holding a date value
430
431.. function:: Time(hour, minute=0, second=0, microsecond=0)
432
433    Construct an object holding a time value
434
435.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
436
437    Construct an object holding a time stamp value
438
439.. function:: DateFromTicks(ticks)
440
441    Construct an object holding a date value from the given *ticks* value
442
443.. function:: TimeFromTicks(ticks)
444
445    Construct an object holding a time value from the given *ticks* value
446
447.. function:: TimestampFromTicks(ticks)
448
449    Construct an object holding a time stamp from the given *ticks* value
450
451.. function:: Binary(bytes)
452
453    Construct an object capable of holding a (long) binary string value
454
455.. class:: STRING
456
457    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
458
459.. class:: BINARY type
460
461    Used to describe (long) binary columns (``bytea``)
462
463.. class:: NUMBER
464
465    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
466
467.. class:: DATETIME
468
469    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
470
471.. class:: ROWID
472
473    Used to describe the ``oid`` column of PostgreSQL database tables
474
475The following more specific types are not part of the DB-API 2 standard:
476
477.. class:: BOOL
478
479    Used to describe ``boolean`` columns
480
481.. class:: SMALLINT
482
483    Used to describe ``smallint`` columns
484
485.. class:: INTEGER
486
487    Used to describe ``integer`` columns
488
489.. class:: LONG
490
491    Used to describe ``bigint`` columns
492
493.. class:: FLOAT
494
495    Used to describe ``float`` columns
496
497.. class:: NUMERIC
498
499    Used to describe ``numeric`` columns
500
501.. class:: MONEY
502
503    Used to describe ``money`` columns
504
505.. class:: DATE
506
507    Used to describe ``date`` columns
508
509.. class:: TIME
510
511    Used to describe ``time`` columns
512
513.. class:: TIMESTAMP
514
515    Used to describe ``timestamp`` columns
516
517.. class:: INTERVAL
518
519    Used to describe date and time ``interval`` columns
Note: See TracBrowser for help on using the repository browser.