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

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

Use simpler and more standard type names in pgdb

We already renamed the types provided by the classic module to be more simple
and intuitive. We now do the same for the types provided by the DB-API 2 module.

  • 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.cusor()
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 sequences.
229
230    Each of these sequences 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 *precision*, *scale* and *null_ok* are not implemented.
241
242    This attribute will be ``None`` for operations that do not return rows
243    or if the cursor has not had an operation invoked via the
244    :meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` method yet.
245
246rowcount -- number of rows of the result
247----------------------------------------
248
249.. attribute:: pgdbCursor.rowcount
250
251    This read-only attribute specifies the number of rows that the last
252    :meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` call produced
253    (for DQL statements like SELECT) or affected (for DML statements like
254    UPDATE or INSERT ). The attribute is -1 in case no such method call has
255    been performed on the cursor or the rowcount of the last operation
256    cannot be determined by the interface.
257
258close -- close the cursor
259-------------------------
260
261.. method:: pgdbCursor.close()
262
263    Close the cursor now (rather than whenever it is deleted)
264
265    :rtype: None
266
267The cursor will be unusable from this point forward; an :exc:`Error`
268(or subclass) exception will be raised if any operation is attempted
269with the cursor.
270
271execute -- execute a database operation
272---------------------------------------
273
274.. method:: pgdbCursor.execute(operation, [parameters])
275
276    Prepare and execute a database operation (query or command)
277
278    :param str operation: the database operation
279    :param parameters: a sequence or mapping of parameters
280    :returns: the cursor, so you can chain commands
281
282Parameters may be provided as sequence or mapping and will be bound to
283variables in the operation. Variables are specified using Python extended
284format codes, e.g. ``" ... WHERE name=%(name)s"``.
285
286A reference to the operation will be retained by the cursor. If the same
287operation object is passed in again, then the cursor can optimize its behavior.
288This is most effective for algorithms where the same operation is used,
289but different parameters are bound to it (many times).
290
291The parameters may also be specified as list of tuples to e.g. insert multiple
292rows in a single operation, but this kind of usage is deprecated:
293:meth:`pgdbCursor.executemany` should be used instead.
294
295executemany -- execute many similar database operations
296-------------------------------------------------------
297
298.. method:: pgdbCursor.executemany(operation, [seq_of_parameters])
299
300    Prepare and execute many similar database operations (queries or commands)
301
302    :param str operation: the database operation
303    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
304    :returns: the cursor, so you can chain commands
305
306Prepare a database operation (query or command) and then execute it against
307all parameter tuples or mappings found in the sequence *seq_of_parameters*.
308
309Parameters are bounded to the query using Python extended format codes,
310e.g. ``" ... WHERE name=%(name)s"``.
311
312fetchone -- fetch next row of the query result
313----------------------------------------------
314
315.. method:: pgdbCursor.fetchone()
316
317    Fetch the next row of a query result set
318
319    :returns: the next row of the query result set
320    :rtype: tuple or None
321
322Fetch the next row of a query result set, returning a single tuple,
323or ``None`` when no more data is available.
324
325An :exc:`Error` (or subclass) exception is raised if the previous call to
326:meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` did not produce
327any result set or no call was issued yet.
328
329fetchmany -- fetch next set of rows of the query result
330-------------------------------------------------------
331
332.. method:: pgdbCursor.fetchmany([size=None], [keep=False])
333
334    Fetch the next set of rows of a query result
335
336    :param size: the number of rows to be fetched
337    :type size: int or None
338    :param keep: if set to true, will keep the passed arraysize
339    :tpye keep: bool
340    :returns: the next set of rows of the query result
341    :rtype: list of tuples
342
343Fetch the next set of rows of a query result, returning a list of tuples.
344An empty sequence is returned when no more rows are available.
345
346The number of rows to fetch per call is specified by the *size* parameter.
347If it is not given, the cursor's :attr:`arraysize` determines the number of
348rows to be fetched. If you set the *keep* parameter to True, this is kept as
349new :attr:`arraysize`.
350
351The method tries to fetch as many rows as indicated by the *size* parameter.
352If this is not possible due to the specified number of rows not being
353available, fewer rows may be returned.
354
355An :exc:`Error` (or subclass) exception is raised if the previous call to
356:meth:`pgdbCursor.execute` or :meth:`pgdbCursor.executemany` did not produce
357any result set or no call was issued yet.
358
359Note there are performance considerations involved with the *size* parameter.
360For optimal performance, it is usually best to use the :attr:`arraysize`
361attribute. If the *size* parameter is used, then it is best for it to retain
362the same value from one :meth:`pgdbCursor.fetchmany` call to the next.
363
364fetchall -- fetch all rows of the query result
365----------------------------------------------
366
367.. method:: pgdbCursor.fetchall()
368
369    Fetch all (remaining) rows of a query result
370
371    :returns: the set of all rows of the query result
372    :rtype: list of tuples
373
374Fetch all (remaining) rows of a query result, returning them as list of tuples.
375Note that the cursor's :attr:`arraysize` attribute can affect the performance
376of this operation.
377
378row_factory -- process a row of the query result
379------------------------------------------------
380
381.. method:: pgdbCursor.row_factory(row)
382
383    Process rows before they are returned
384
385    :param tuple row: the currently processed row of the result set
386    :returns: the transformed row that the cursor methods shall return
387
388Note that this method is not part of the DB-API 2 standard.
389
390You can overwrite this method with a custom row factory, e.g.
391if you want to return rows as dicts instead of tuples::
392
393    class DictCursor(pgdb.pgdbCursor):
394
395        def row_factory(self, row):
396            return {desc[0]:value
397                for desc, value in zip(self.description, row)}
398
399    cur = DictCursor(con)
400
401arraysize - the number of rows to fetch at a time
402-------------------------------------------------
403
404.. attribute:: pgdbCursor.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:`pgdbCursor.fetchmany`. It defaults to 1 meaning to fetch a single row
410at a time.
411
412
413pgdbType -- Type objects and constructors
414=========================================
415
416.. class:: pgdbType
417
418The :attr:`pgdbCursor.description` attribute returns information about each
419of the result columns of a query. The *type_code* must compare equal to one
420of the :class:`pgdbType` objects defined below. Type objects can be equal to
421more than one type code (e.g. :class:`DATETIME` is equal to the type codes
422for date, time and timestamp columns).
423
424The :mod:`pgdb` module exports the following constructors and singletons:
425
426.. function:: Date(year, month, day)
427
428    Construct an object holding a date value
429
430.. function:: Time(hour, minute=0, second=0, microsecond=0)
431
432    Construct an object holding a time value
433
434.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
435
436    Construct an object holding a time stamp value
437
438.. function:: DateFromTicks(ticks)
439
440    Construct an object holding a date value from the given *ticks* value
441
442.. function:: TimeFromTicks(ticks)
443
444    Construct an object holding a time value from the given *ticks* value
445
446.. function:: TimestampFromTicks(ticks)
447
448    Construct an object holding a time stamp from the given *ticks* value
449
450.. function:: Binary(bytes)
451
452    Construct an object capable of holding a (long) binary string value
453
454.. class:: STRING
455
456    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
457
458.. class:: BINARY type
459
460    Used to describe (long) binary columns (``bytea``)
461
462.. class:: NUMBER
463
464    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
465
466.. class:: DATETIME
467
468    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
469
470.. class:: ROWID
471
472    Used to describe the ``oid`` column of PostgreSQL database tables
473
474The following more specific types are not part of the DB-API 2 standard:
475
476.. class:: BOOL
477
478    Used to describe ``boolean`` columns
479
480.. class:: SMALLINT
481
482    Used to describe ``smallint`` columns
483
484.. class:: INTEGER
485
486    Used to describe ``integer`` columns
487
488.. class:: LONG
489
490    Used to describe ``bigint`` columns
491
492.. class:: FLOAT
493
494    Used to describe ``float`` columns
495
496.. class:: NUMERIC
497
498    Used to describe ``numeric`` columns
499
500.. class:: MONEY
501
502    Used to describe ``money`` columns
503
504.. class:: DATE
505
506    Used to describe ``date`` columns
507
508.. class:: TIME
509
510    Used to describe ``time`` columns
511
512.. class:: TIMESTAMP
513
514    Used to describe ``timestamp`` columns
515
516.. class:: INTERVAL
517
518    Used to describe date and time ``interval`` columns
Note: See TracBrowser for help on using the repository browser.