source: trunk/docs/pgdb.rst @ 683

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

Return rows as named tuples in pgdb

By default, we now return result rows as named tuples in pgdb.

Note that named tuples can be accessed like normal lists and tuples,
and easily converted to these. They can also be easily converted to
(ordered) dictionaries by calling row._asdict(). Therefore the need
for alternative Cursor types with different row types has been greatly
reduced, so I have simplified the implementation in the last revision
by removing the added Cursor classes and cursor() methods again,
leaving only the old row_factory method for customizing the returned
row types. I complemented this with a new build_row_factory method,
because different named tuple classes must be created for different
result sets, so a static row_factory method is not so appropriate.

Tests and documentation for these changes are included.

  • Property svn:keywords set to Author Date Id Revision
File size: 19.2 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.cursor()
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
203Attributes that are not part of the standard
204--------------------------------------------
205
206.. note::
207
208   The following attributes are not part of the DB-API 2 standard.
209
210.. attribute:: cursor_type
211
212    The default cursor type used by the connection
213
214If you want to use your own custom subclass of the :class:`Cursor` class
215with he connection, set this attribute to you custom cursor class. You will
216then get your custom cursor whenever you call :meth:`Connection.cursor`.
217
218
219Cursor -- The cursor object
220===========================
221
222.. class:: Cursor
223
224These objects represent a database cursor, which is used to manage the context
225of a fetch operation. Cursors created from the same connection are not
226isolated, i.e., any changes done to the database by a cursor are immediately
227visible by the other cursors. Cursors created from different connections can
228or can not be isolated, depending on the level of transaction isolation.
229The default PostgreSQL transaction isolation level is "read committed".
230
231Cursor objects respond to the following methods and attributes.
232
233Note that ``Cursor`` objects also implement both the iterator and the
234context manager protocol, i.e. you can iterate over them and you can use them
235in a ``with`` statement.
236
237description -- details regarding the result columns
238---------------------------------------------------
239
240.. attribute:: Cursor.description
241
242    This read-only attribute is a sequence of 7-item named tuples.
243
244    Each of these named tuples contains information describing
245    one result column:
246
247        - *name*
248        - *type_code*
249        - *display_size*
250        - *internal_size*
251        - *precision*
252        - *scale*
253        - *null_ok*
254
255    Note that *display_size*, *precision*, *scale* and *null_ok*
256    are not implemented.
257
258    This attribute will be ``None`` for operations that do not return rows
259    or if the cursor has not had an operation invoked via the
260    :meth:`Cursor.execute` or :meth:`Cursor.executemany` method yet.
261
262rowcount -- number of rows of the result
263----------------------------------------
264
265.. attribute:: Cursor.rowcount
266
267    This read-only attribute specifies the number of rows that the last
268    :meth:`Cursor.execute` or :meth:`Cursor.executemany` call produced
269    (for DQL statements like SELECT) or affected (for DML statements like
270    UPDATE or INSERT ). The attribute is -1 in case no such method call has
271    been performed on the cursor or the rowcount of the last operation
272    cannot be determined by the interface.
273
274close -- close the cursor
275-------------------------
276
277.. method:: Cursor.close()
278
279    Close the cursor now (rather than whenever it is deleted)
280
281    :rtype: None
282
283The cursor will be unusable from this point forward; an :exc:`Error`
284(or subclass) exception will be raised if any operation is attempted
285with the cursor.
286
287execute -- execute a database operation
288---------------------------------------
289
290.. method:: Cursor.execute(operation, [parameters])
291
292    Prepare and execute a database operation (query or command)
293
294    :param str operation: the database operation
295    :param parameters: a sequence or mapping of parameters
296    :returns: the cursor, so you can chain commands
297
298Parameters may be provided as sequence or mapping and will be bound to
299variables in the operation. Variables are specified using Python extended
300format codes, e.g. ``" ... WHERE name=%(name)s"``.
301
302A reference to the operation will be retained by the cursor. If the same
303operation object is passed in again, then the cursor can optimize its behavior.
304This is most effective for algorithms where the same operation is used,
305but different parameters are bound to it (many times).
306
307The parameters may also be specified as list of tuples to e.g. insert multiple
308rows in a single operation, but this kind of usage is deprecated:
309:meth:`Cursor.executemany` should be used instead.
310
311executemany -- execute many similar database operations
312-------------------------------------------------------
313
314.. method:: Cursor.executemany(operation, [seq_of_parameters])
315
316    Prepare and execute many similar database operations (queries or commands)
317
318    :param str operation: the database operation
319    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
320    :returns: the cursor, so you can chain commands
321
322Prepare a database operation (query or command) and then execute it against
323all parameter tuples or mappings found in the sequence *seq_of_parameters*.
324
325Parameters are bounded to the query using Python extended format codes,
326e.g. ``" ... WHERE name=%(name)s"``.
327
328fetchone -- fetch next row of the query result
329----------------------------------------------
330
331.. method:: Cursor.fetchone()
332
333    Fetch the next row of a query result set
334
335    :returns: the next row of the query result set
336    :rtype: named tuple or None
337
338Fetch the next row of a query result set, returning a single named tuple,
339or ``None`` when no more data is available. The field names of the named
340tuple are the same as the column names of the database query as long as
341they are valid Python identifiers.
342
343An :exc:`Error` (or subclass) exception is raised if the previous call to
344:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
345any result set or no call was issued yet.
346
347fetchmany -- fetch next set of rows of the query result
348-------------------------------------------------------
349
350.. method:: Cursor.fetchmany([size=None], [keep=False])
351
352    Fetch the next set of rows of a query result
353
354    :param size: the number of rows to be fetched
355    :type size: int or None
356    :param keep: if set to true, will keep the passed arraysize
357    :tpye keep: bool
358    :returns: the next set of rows of the query result
359    :rtype: list of named tuples
360
361Fetch the next set of rows of a query result, returning a list of named
362tuples. An empty sequence is returned when no more rows are available.
363The field names of the named tuple are the same as the column names of
364the database query as long as they are valid Python identifiers.
365
366The number of rows to fetch per call is specified by the *size* parameter.
367If it is not given, the cursor's :attr:`arraysize` determines the number of
368rows to be fetched. If you set the *keep* parameter to True, this is kept as
369new :attr:`arraysize`.
370
371The method tries to fetch as many rows as indicated by the *size* parameter.
372If this is not possible due to the specified number of rows not being
373available, fewer rows may be returned.
374
375An :exc:`Error` (or subclass) exception is raised if the previous call to
376:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
377any result set or no call was issued yet.
378
379Note there are performance considerations involved with the *size* parameter.
380For optimal performance, it is usually best to use the :attr:`arraysize`
381attribute. If the *size* parameter is used, then it is best for it to retain
382the same value from one :meth:`Cursor.fetchmany` call to the next.
383
384fetchall -- fetch all rows of the query result
385----------------------------------------------
386
387.. method:: Cursor.fetchall()
388
389    Fetch all (remaining) rows of a query result
390
391    :returns: the set of all rows of the query result
392    :rtype: list of named tuples
393
394Fetch all (remaining) rows of a query result, returning them as list of
395named tuples. The field names of the named tuple are the same as the column
396names of the database query as long as they are valid Python identifiers.
397
398Note that the cursor's :attr:`arraysize` attribute can affect the performance
399of this operation.
400
401arraysize - the number of rows to fetch at a time
402-------------------------------------------------
403
404.. attribute:: Cursor.arraysize
405
406    The number of rows to fetch at a time
407
408This read/write attribute specifies the number of rows to fetch at a time with
409:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
410at a time.
411
412Methods and attributes that are not part of the standard
413--------------------------------------------------------
414
415.. note::
416
417   The following methods and attributes are not part of the DB-API 2 standard.
418
419.. method:: Cursor.row_factory(row)
420
421    Process rows before they are returned
422
423    :param tuple row: the currently processed row of the result set
424    :returns: the transformed row that the fetch methods shall return
425
426This method is used for processing result rows before returning them through
427one of the fetch methods. By default, rows are returned as named tuples.
428You can overwrite this method with a custom row factory if you want to
429return the rows as different kids of objects. This same row factory will then
430be used for all result sets. If you overwrite this method, the method
431:meth:`Cursor.build_row_factory` for creating row factories dynamically
432will be ignored.
433
434Note that named tuples are very efficient and can be easily converted to
435dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
436to return rows as dicts, you can create a custom cursor class like this::
437
438    class DictCursor(pgdb.Cursor):
439
440        def row_factory(self, row):
441            return {key: value for key, value in zip(self.colnames, row)}
442
443    cur = DictCursor(con)  # get one DictCursor instance or
444    con.cursor_type = DictCursor  # always use DictCursor instances
445
446
447.. method:: Cursor.build_row_factory()
448
449    Build a row factory based on the current description
450
451    :returns: callable with the signature of :meth:`Cursor.row_factory`
452
453This method returns row factories for creating named tuples. It is called
454whenever a new result set is created, and :attr:`Cursor.row_factory` is
455then assigned the return value of this method. You can overwrite this method
456with a custom row factory builder if you want to use different row factories
457for different result sets. Otherwise, you can also simply overwrite the
458:meth:`Cursor.row_factory` method. This method will then be ignored.
459
460The default implementation that delivers rows as named tuples essentially
461looks like this::
462
463    def build_row_factory(self):
464        return namedtuple('Row', self.colnames, rename=True)._make
465
466.. attribute:: Cursor.colnames
467
468    The list of columns names of the current result set
469
470The values in this list are the same values as the *name* elements
471in the :attr:`Cursor.description` attribute. Always use the latter
472if you want to remain standard compliant.
473
474.. attribute:: Cursor.coltypes
475
476    The list of columns types of the current result set
477
478The values in this list are the same values as the *type_code* elements
479in the :attr:`Cursor.description` attribute. Always use the latter
480if you want to remain standard compliant.
481
482
483Type -- Type objects and constructors
484=====================================
485
486.. class:: Type
487
488The :attr:`Cursor.description` attribute returns information about each
489of the result columns of a query. The *type_code* must compare equal to one
490of the :class:`Type` objects defined below. Type objects can be equal to
491more than one type code (e.g. :class:`DATETIME` is equal to the type codes
492for date, time and timestamp columns).
493
494The :mod:`pgdb` module exports the following constructors and singletons:
495
496.. function:: Date(year, month, day)
497
498    Construct an object holding a date value
499
500.. function:: Time(hour, minute=0, second=0, microsecond=0)
501
502    Construct an object holding a time value
503
504.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
505
506    Construct an object holding a time stamp value
507
508.. function:: DateFromTicks(ticks)
509
510    Construct an object holding a date value from the given *ticks* value
511
512.. function:: TimeFromTicks(ticks)
513
514    Construct an object holding a time value from the given *ticks* value
515
516.. function:: TimestampFromTicks(ticks)
517
518    Construct an object holding a time stamp from the given *ticks* value
519
520.. function:: Binary(bytes)
521
522    Construct an object capable of holding a (long) binary string value
523
524.. class:: STRING
525
526    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
527
528.. class:: BINARY type
529
530    Used to describe (long) binary columns (``bytea``)
531
532.. class:: NUMBER
533
534    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
535
536.. class:: DATETIME
537
538    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
539
540.. class:: ROWID
541
542    Used to describe the ``oid`` column of PostgreSQL database tables
543
544.. note::
545
546  The following more specific types are not part of the DB-API 2 standard.
547
548.. class:: BOOL
549
550    Used to describe ``boolean`` columns
551
552.. class:: SMALLINT
553
554    Used to describe ``smallint`` columns
555
556.. class:: INTEGER
557
558    Used to describe ``integer`` columns
559
560.. class:: LONG
561
562    Used to describe ``bigint`` columns
563
564.. class:: FLOAT
565
566    Used to describe ``float`` columns
567
568.. class:: NUMERIC
569
570    Used to describe ``numeric`` columns
571
572.. class:: MONEY
573
574    Used to describe ``money`` columns
575
576.. class:: DATE
577
578    Used to describe ``date`` columns
579
580.. class:: TIME
581
582    Used to describe ``time`` columns
583
584.. class:: TIMESTAMP
585
586    Used to describe ``timestamp`` columns
587
588.. class:: INTERVAL
589
590    Used to describe date and time ``interval`` columns
Note: See TracBrowser for help on using the repository browser.