source: trunk/docs/pgdb.rst @ 693

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

Support copy_from() and copy_to() in pgdb

  • Property svn:keywords set to Author Date Id Revision
File size: 23.7 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). It is also set by the :meth:`Cursor.copy_from` and
271    :meth':`Cursor.copy_to` methods. The attribute is -1 in case no such
272    method call has been performed on the cursor or the rowcount of the
273    last operation cannot be determined by the interface.
274
275close -- close the cursor
276-------------------------
277
278.. method:: Cursor.close()
279
280    Close the cursor now (rather than whenever it is deleted)
281
282    :rtype: None
283
284The cursor will be unusable from this point forward; an :exc:`Error`
285(or subclass) exception will be raised if any operation is attempted
286with the cursor.
287
288execute -- execute a database operation
289---------------------------------------
290
291.. method:: Cursor.execute(operation, [parameters])
292
293    Prepare and execute a database operation (query or command)
294
295    :param str operation: the database operation
296    :param parameters: a sequence or mapping of parameters
297    :returns: the cursor, so you can chain commands
298
299Parameters may be provided as sequence or mapping and will be bound to
300variables in the operation. Variables are specified using Python extended
301format codes, e.g. ``" ... WHERE name=%(name)s"``.
302
303A reference to the operation will be retained by the cursor. If the same
304operation object is passed in again, then the cursor can optimize its behavior.
305This is most effective for algorithms where the same operation is used,
306but different parameters are bound to it (many times).
307
308The parameters may also be specified as list of tuples to e.g. insert multiple
309rows in a single operation, but this kind of usage is deprecated:
310:meth:`Cursor.executemany` should be used instead.
311
312executemany -- execute many similar database operations
313-------------------------------------------------------
314
315.. method:: Cursor.executemany(operation, [seq_of_parameters])
316
317    Prepare and execute many similar database operations (queries or commands)
318
319    :param str operation: the database operation
320    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
321    :returns: the cursor, so you can chain commands
322
323Prepare a database operation (query or command) and then execute it against
324all parameter tuples or mappings found in the sequence *seq_of_parameters*.
325
326Parameters are bounded to the query using Python extended format codes,
327e.g. ``" ... WHERE name=%(name)s"``.
328
329callproc -- Call a stored procedure
330-----------------------------------
331
332.. method:: Cursor.callproc(self, procname, [parameters]):
333
334    Call a stored database procedure with the given name
335
336    :param str procname: the name of the database function
337    :param parameters: a sequence of parameters (can be empty or omitted)
338
339This method calls a stored procedure (function) in the PostgreSQL database.
340
341The sequence of parameters must contain one entry for each input argument
342that the function expects. The result of the call is the same as this input
343sequence; replacement of output and input/output parameters in the return
344value is currently not supported.
345
346The function may also provide a result set as output. These can be requested
347through the standard fetch methods of the cursor.
348
349fetchone -- fetch next row of the query result
350----------------------------------------------
351
352.. method:: Cursor.fetchone()
353
354    Fetch the next row of a query result set
355
356    :returns: the next row of the query result set
357    :rtype: named tuple or None
358
359Fetch the next row of a query result set, returning a single named tuple,
360or ``None`` when no more data is available. The field names of the named
361tuple are the same as the column names of the database query as long as
362they are valid Python identifiers.
363
364An :exc:`Error` (or subclass) exception is raised if the previous call to
365:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
366any result set or no call was issued yet.
367
368fetchmany -- fetch next set of rows of the query result
369-------------------------------------------------------
370
371.. method:: Cursor.fetchmany([size=None], [keep=False])
372
373    Fetch the next set of rows of a query result
374
375    :param size: the number of rows to be fetched
376    :type size: int or None
377    :param keep: if set to true, will keep the passed arraysize
378    :tpye keep: bool
379    :returns: the next set of rows of the query result
380    :rtype: list of named tuples
381
382Fetch the next set of rows of a query result, returning a list of named
383tuples. An empty sequence is returned when no more rows are available.
384The field names of the named tuple are the same as the column names of
385the database query as long as they are valid Python identifiers.
386
387The number of rows to fetch per call is specified by the *size* parameter.
388If it is not given, the cursor's :attr:`arraysize` determines the number of
389rows to be fetched. If you set the *keep* parameter to True, this is kept as
390new :attr:`arraysize`.
391
392The method tries to fetch as many rows as indicated by the *size* parameter.
393If this is not possible due to the specified number of rows not being
394available, fewer rows may be returned.
395
396An :exc:`Error` (or subclass) exception is raised if the previous call to
397:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
398any result set or no call was issued yet.
399
400Note there are performance considerations involved with the *size* parameter.
401For optimal performance, it is usually best to use the :attr:`arraysize`
402attribute. If the *size* parameter is used, then it is best for it to retain
403the same value from one :meth:`Cursor.fetchmany` call to the next.
404
405fetchall -- fetch all rows of the query result
406----------------------------------------------
407
408.. method:: Cursor.fetchall()
409
410    Fetch all (remaining) rows of a query result
411
412    :returns: the set of all rows of the query result
413    :rtype: list of named tuples
414
415Fetch all (remaining) rows of a query result, returning them as list of
416named tuples. The field names of the named tuple are the same as the column
417names of the database query as long as they are valid Python identifiers.
418
419Note that the cursor's :attr:`arraysize` attribute can affect the performance
420of this operation.
421
422arraysize - the number of rows to fetch at a time
423-------------------------------------------------
424
425.. attribute:: Cursor.arraysize
426
427    The number of rows to fetch at a time
428
429This read/write attribute specifies the number of rows to fetch at a time with
430:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
431at a time.
432
433Methods and attributes that are not part of the standard
434--------------------------------------------------------
435
436.. note::
437
438   The following methods and attributes are not part of the DB-API 2 standard.
439
440.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size], [columns])
441
442        Copy data from an input stream to the specified table
443
444    :param stream: the input stream
445        (must be a file-like object, a string or an iterable returning strings)
446    :param str table: the name of a database table
447    :param str format: the format of the data in the input stream,
448        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
449    :param str sep: a single character separator
450        (the default is ``'\t'`` for text and ``','`` for csv)
451    :param str null: the textual representation of the ``NULL`` value,
452        can also be an empty string (the default is ``'\\N'``)
453    :param int size: the size of the buffer when reading file-like objects
454    :param list column: an optional list of column names
455    :returns: the cursor, so you can chain commands
456
457    :raises TypeError: parameters with wrong types
458    :raises ValueError: invalid parameters
459    :raises IOError: error when executing the copy operation
460
461This method can be used to copy data from an input stream on the client side
462to a database table on the server side using the ``COPY FROM`` command.
463The input stream can be provided in form of a file-like object (which must
464have a ``read()`` method), a string, or an iterable returning one row or
465multiple rows of input data on each iteration.
466
467The format must be text, csv or binary. The sep option sets the column
468separator (delimiter) used in the non binary formats. The null option sets
469the textual representation of ``NULL`` in the input.
470
471The size option sets the size of the buffer used when reading data from
472file-like objects.
473
474The copy operation can be restricted to a subset of columns. If no columns are
475specified, all of them will be copied.
476
477.. method:: Cursor.copy_to(stream, table, [format], [sep], [null], [decode], [columns])
478
479        Copy data from the specified table to an output stream
480
481    :param stream: the output stream (must be a file-like object or ``None``)
482    :param str table: the name of a database table or a ``SELECT`` query
483    :param str format: the format of the data in the input stream,
484        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
485    :param str sep: a single character separator
486        (the default is ``'\t'`` for text and ``','`` for csv)
487    :param str null: the textual representation of the ``NULL`` value,
488        can also be an empty string (the default is ``'\\N'``)
489    :param bool decode: whether decoded strings shall be returned
490        for non-binary formats (the default is True in Python 3)
491    :param list column: an optional list of column names
492    :returns: a generator if stream is set to ``None``, otherwise the cursor
493
494    :raises TypeError: parameters with wrong types
495    :raises ValueError: invalid parameters
496    :raises IOError: error when executing the copy operation
497
498This method can be used to copy data from a database table on the server side
499to an output stream on the client side using the ``COPY TO`` command.
500
501The output stream can be provided in form of a file-like object (which must
502have a ``write()`` method). Alternatively, if ``None`` is passed as the
503output stream, the method will return a generator yielding one row of output
504data on each iteration.
505
506Output will be returned as byte strings unless you set decode to true.
507
508Note that you can also use a ``SELECT`` query instead of the table name.
509
510The format must be text, csv or binary. The sep option sets the column
511separator (delimiter) used in the non binary formats. The null option sets
512the textual representation of ``NULL`` in the output.
513
514The copy operation can be restricted to a subset of columns. If no columns are
515specified, all of them will be copied.
516
517.. method:: Cursor.row_factory(row)
518
519    Process rows before they are returned
520
521    :param list row: the currently processed row of the result set
522    :returns: the transformed row that the fetch methods shall return
523
524This method is used for processing result rows before returning them through
525one of the fetch methods. By default, rows are returned as named tuples.
526You can overwrite this method with a custom row factory if you want to
527return the rows as different kids of objects. This same row factory will then
528be used for all result sets. If you overwrite this method, the method
529:meth:`Cursor.build_row_factory` for creating row factories dynamically
530will be ignored.
531
532Note that named tuples are very efficient and can be easily converted to
533dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
534to return rows as dicts, you can create a custom cursor class like this::
535
536    class DictCursor(pgdb.Cursor):
537
538        def row_factory(self, row):
539            return {key: value for key, value in zip(self.colnames, row)}
540
541    cur = DictCursor(con)  # get one DictCursor instance or
542    con.cursor_type = DictCursor  # always use DictCursor instances
543
544
545.. method:: Cursor.build_row_factory()
546
547    Build a row factory based on the current description
548
549    :returns: callable with the signature of :meth:`Cursor.row_factory`
550
551This method returns row factories for creating named tuples. It is called
552whenever a new result set is created, and :attr:`Cursor.row_factory` is
553then assigned the return value of this method. You can overwrite this method
554with a custom row factory builder if you want to use different row factories
555for different result sets. Otherwise, you can also simply overwrite the
556:meth:`Cursor.row_factory` method. This method will then be ignored.
557
558The default implementation that delivers rows as named tuples essentially
559looks like this::
560
561    def build_row_factory(self):
562        return namedtuple('Row', self.colnames, rename=True)._make
563
564.. attribute:: Cursor.colnames
565
566    The list of columns names of the current result set
567
568The values in this list are the same values as the *name* elements
569in the :attr:`Cursor.description` attribute. Always use the latter
570if you want to remain standard compliant.
571
572.. attribute:: Cursor.coltypes
573
574    The list of columns types of the current result set
575
576The values in this list are the same values as the *type_code* elements
577in the :attr:`Cursor.description` attribute. Always use the latter
578if you want to remain standard compliant.
579
580
581Type -- Type objects and constructors
582=====================================
583
584.. class:: Type
585
586The :attr:`Cursor.description` attribute returns information about each
587of the result columns of a query. The *type_code* must compare equal to one
588of the :class:`Type` objects defined below. Type objects can be equal to
589more than one type code (e.g. :class:`DATETIME` is equal to the type codes
590for date, time and timestamp columns).
591
592The :mod:`pgdb` module exports the following constructors and singletons:
593
594.. function:: Date(year, month, day)
595
596    Construct an object holding a date value
597
598.. function:: Time(hour, minute=0, second=0, microsecond=0)
599
600    Construct an object holding a time value
601
602.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
603
604    Construct an object holding a time stamp value
605
606.. function:: DateFromTicks(ticks)
607
608    Construct an object holding a date value from the given *ticks* value
609
610.. function:: TimeFromTicks(ticks)
611
612    Construct an object holding a time value from the given *ticks* value
613
614.. function:: TimestampFromTicks(ticks)
615
616    Construct an object holding a time stamp from the given *ticks* value
617
618.. function:: Binary(bytes)
619
620    Construct an object capable of holding a (long) binary string value
621
622.. class:: STRING
623
624    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
625
626.. class:: BINARY type
627
628    Used to describe (long) binary columns (``bytea``)
629
630.. class:: NUMBER
631
632    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
633
634.. class:: DATETIME
635
636    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
637
638.. class:: ROWID
639
640    Used to describe the ``oid`` column of PostgreSQL database tables
641
642.. note::
643
644  The following more specific types are not part of the DB-API 2 standard.
645
646.. class:: BOOL
647
648    Used to describe ``boolean`` columns
649
650.. class:: SMALLINT
651
652    Used to describe ``smallint`` columns
653
654.. class:: INTEGER
655
656    Used to describe ``integer`` columns
657
658.. class:: LONG
659
660    Used to describe ``bigint`` columns
661
662.. class:: FLOAT
663
664    Used to describe ``float`` columns
665
666.. class:: NUMERIC
667
668    Used to describe ``numeric`` columns
669
670.. class:: MONEY
671
672    Used to describe ``money`` columns
673
674.. class:: DATE
675
676    Used to describe ``date`` columns
677
678.. class:: TIME
679
680    Used to describe ``time`` columns
681
682.. class:: TIMESTAMP
683
684    Used to describe ``timestamp`` columns
685
686.. class:: INTERVAL
687
688    Used to describe date and time ``interval`` columns
Note: See TracBrowser for help on using the repository browser.