source: trunk/docs/contents/pgdb/cursor.rst @ 901

Last change on this file since 901 was 901, checked in by cito, 3 years ago

Improve creation of named tuples in Python 2.6 and 3.0

File size: 15.4 KB
Line 
1Cursor -- The cursor object
2===========================
3
4.. py:currentmodule:: pgdb
5
6.. class:: Cursor
7
8These objects represent a database cursor, which is used to manage the context
9of a fetch operation. Cursors created from the same connection are not
10isolated, i.e., any changes done to the database by a cursor are immediately
11visible by the other cursors. Cursors created from different connections can
12or can not be isolated, depending on the level of transaction isolation.
13The default PostgreSQL transaction isolation level is "read committed".
14
15Cursor objects respond to the following methods and attributes.
16
17Note that ``Cursor`` objects also implement both the iterator and the
18context manager protocol, i.e. you can iterate over them and you can use them
19in a ``with`` statement.
20
21description -- details regarding the result columns
22---------------------------------------------------
23
24.. attribute:: Cursor.description
25
26    This read-only attribute is a sequence of 7-item named tuples.
27
28    Each of these named tuples contains information describing
29    one result column:
30
31        - *name*
32        - *type_code*
33        - *display_size*
34        - *internal_size*
35        - *precision*
36        - *scale*
37        - *null_ok*
38
39    The values for *precision* and *scale* are only set for numeric types.
40    The values for *display_size* and *null_ok* are always ``None``.
41
42    This attribute will be ``None`` for operations that do not return rows
43    or if the cursor has not had an operation invoked via the
44    :meth:`Cursor.execute` or :meth:`Cursor.executemany` method yet.
45
46.. versionchanged:: 5.0
47    Before version 5.0, this attribute was an ordinary tuple.
48
49rowcount -- number of rows of the result
50----------------------------------------
51
52.. attribute:: Cursor.rowcount
53
54    This read-only attribute specifies the number of rows that the last
55    :meth:`Cursor.execute` or :meth:`Cursor.executemany` call produced
56    (for DQL statements like SELECT) or affected (for DML statements like
57    UPDATE or INSERT). It is also set by the :meth:`Cursor.copy_from` and
58    :meth':`Cursor.copy_to` methods. The attribute is -1 in case no such
59    method call has been performed on the cursor or the rowcount of the
60    last operation cannot be determined by the interface.
61
62close -- close the cursor
63-------------------------
64
65.. method:: Cursor.close()
66
67    Close the cursor now (rather than whenever it is deleted)
68
69    :rtype: None
70
71The cursor will be unusable from this point forward; an :exc:`Error`
72(or subclass) exception will be raised if any operation is attempted
73with the cursor.
74
75execute -- execute a database operation
76---------------------------------------
77
78.. method:: Cursor.execute(operation, [parameters])
79
80    Prepare and execute a database operation (query or command)
81
82    :param str operation: the database operation
83    :param parameters: a sequence or mapping of parameters
84    :returns: the cursor, so you can chain commands
85
86Parameters may be provided as sequence or mapping and will be bound to
87variables in the operation. Variables are specified using Python extended
88format codes, e.g. ``" ... WHERE name=%(name)s"``.
89
90A reference to the operation will be retained by the cursor. If the same
91operation object is passed in again, then the cursor can optimize its behavior.
92This is most effective for algorithms where the same operation is used,
93but different parameters are bound to it (many times).
94
95The parameters may also be specified as list of tuples to e.g. insert multiple
96rows in a single operation, but this kind of usage is deprecated:
97:meth:`Cursor.executemany` should be used instead.
98
99Note that in case this method raises a :exc:`DatabaseError`, you can get
100information about the error condition that has occurred by introspecting
101its :attr:`DatabaseError.sqlstate` attribute, which will be the ``SQLSTATE``
102error code associated with the error.  Applications that need to know which
103error condition has occurred should usually test the error code, rather than
104looking at the textual error message.
105
106executemany -- execute many similar database operations
107-------------------------------------------------------
108
109.. method:: Cursor.executemany(operation, [seq_of_parameters])
110
111    Prepare and execute many similar database operations (queries or commands)
112
113    :param str operation: the database operation
114    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
115    :returns: the cursor, so you can chain commands
116
117Prepare a database operation (query or command) and then execute it against
118all parameter tuples or mappings found in the sequence *seq_of_parameters*.
119
120Parameters are bounded to the query using Python extended format codes,
121e.g. ``" ... WHERE name=%(name)s"``.
122
123callproc -- Call a stored procedure
124-----------------------------------
125
126.. method:: Cursor.callproc(self, procname, [parameters]):
127
128    Call a stored database procedure with the given name
129
130    :param str procname: the name of the database function
131    :param parameters: a sequence of parameters (can be empty or omitted)
132
133This method calls a stored procedure (function) in the PostgreSQL database.
134
135The sequence of parameters must contain one entry for each input argument
136that the function expects. The result of the call is the same as this input
137sequence; replacement of output and input/output parameters in the return
138value is currently not supported.
139
140The function may also provide a result set as output. These can be requested
141through the standard fetch methods of the cursor.
142
143.. versionadded:: 5.0
144
145fetchone -- fetch next row of the query result
146----------------------------------------------
147
148.. method:: Cursor.fetchone()
149
150    Fetch the next row of a query result set
151
152    :returns: the next row of the query result set
153    :rtype: named tuple or None
154
155Fetch the next row of a query result set, returning a single named tuple,
156or ``None`` when no more data is available. The field names of the named
157tuple are the same as the column names of the database query as long as
158they are valid Python identifiers.
159
160An :exc:`Error` (or subclass) exception is raised if the previous call to
161:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
162any result set or no call was issued yet.
163
164.. versionchanged:: 5.0
165    Before version 5.0, this method returned ordinary tuples.
166
167fetchmany -- fetch next set of rows of the query result
168-------------------------------------------------------
169
170.. method:: Cursor.fetchmany([size=None], [keep=False])
171
172    Fetch the next set of rows of a query result
173
174    :param size: the number of rows to be fetched
175    :type size: int or None
176    :param keep: if set to true, will keep the passed arraysize
177    :tpye keep: bool
178    :returns: the next set of rows of the query result
179    :rtype: list of named tuples
180
181Fetch the next set of rows of a query result, returning a list of named
182tuples. An empty sequence is returned when no more rows are available.
183The field names of the named tuple are the same as the column names of
184the database query as long as they are valid Python identifiers.
185
186The number of rows to fetch per call is specified by the *size* parameter.
187If it is not given, the cursor's :attr:`arraysize` determines the number of
188rows to be fetched. If you set the *keep* parameter to True, this is kept as
189new :attr:`arraysize`.
190
191The method tries to fetch as many rows as indicated by the *size* parameter.
192If this is not possible due to the specified number of rows not being
193available, fewer rows may be returned.
194
195An :exc:`Error` (or subclass) exception is raised if the previous call to
196:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
197any result set or no call was issued yet.
198
199Note there are performance considerations involved with the *size* parameter.
200For optimal performance, it is usually best to use the :attr:`arraysize`
201attribute. If the *size* parameter is used, then it is best for it to retain
202the same value from one :meth:`Cursor.fetchmany` call to the next.
203
204.. versionchanged:: 5.0
205    Before version 5.0, this method returned ordinary tuples.
206
207fetchall -- fetch all rows of the query result
208----------------------------------------------
209
210.. method:: Cursor.fetchall()
211
212    Fetch all (remaining) rows of a query result
213
214    :returns: the set of all rows of the query result
215    :rtype: list of named tuples
216
217Fetch all (remaining) rows of a query result, returning them as list of
218named tuples. The field names of the named tuple are the same as the column
219names of the database query as long as they are valid as field names for
220named tuples, otherwise they are given positional names.
221
222Note that the cursor's :attr:`arraysize` attribute can affect the performance
223of this operation.
224
225.. versionchanged:: 5.0
226    Before version 5.0, this method returned ordinary tuples.
227
228arraysize - the number of rows to fetch at a time
229-------------------------------------------------
230
231.. attribute:: Cursor.arraysize
232
233    The number of rows to fetch at a time
234
235This read/write attribute specifies the number of rows to fetch at a time with
236:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
237at a time.
238
239Methods and attributes that are not part of the standard
240--------------------------------------------------------
241
242.. note::
243
244    The following methods and attributes are not part of the DB-API 2 standard.
245
246.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size], [columns])
247
248    Copy data from an input stream to the specified table
249
250    :param stream: the input stream
251        (must be a file-like object, a string or an iterable returning strings)
252    :param str table: the name of a database table
253    :param str format: the format of the data in the input stream,
254        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
255    :param str sep: a single character separator
256        (the default is ``'\t'`` for text and ``','`` for csv)
257    :param str null: the textual representation of the ``NULL`` value,
258        can also be an empty string (the default is ``'\\N'``)
259    :param int size: the size of the buffer when reading file-like objects
260    :param list column: an optional list of column names
261    :returns: the cursor, so you can chain commands
262
263    :raises TypeError: parameters with wrong types
264    :raises ValueError: invalid parameters
265    :raises IOError: error when executing the copy operation
266
267This method can be used to copy data from an input stream on the client side
268to a database table on the server side using the ``COPY FROM`` command.
269The input stream can be provided in form of a file-like object (which must
270have a ``read()`` method), a string, or an iterable returning one row or
271multiple rows of input data on each iteration.
272
273The format must be text, csv or binary. The sep option sets the column
274separator (delimiter) used in the non binary formats. The null option sets
275the textual representation of ``NULL`` in the input.
276
277The size option sets the size of the buffer used when reading data from
278file-like objects.
279
280The copy operation can be restricted to a subset of columns. If no columns are
281specified, all of them will be copied.
282
283.. versionadded:: 5.0
284
285.. method:: Cursor.copy_to(stream, table, [format], [sep], [null], [decode], [columns])
286
287    Copy data from the specified table to an output stream
288
289    :param stream: the output stream (must be a file-like object or ``None``)
290    :param str table: the name of a database table or a ``SELECT`` query
291    :param str format: the format of the data in the input stream,
292        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
293    :param str sep: a single character separator
294        (the default is ``'\t'`` for text and ``','`` for csv)
295    :param str null: the textual representation of the ``NULL`` value,
296        can also be an empty string (the default is ``'\\N'``)
297    :param bool decode: whether decoded strings shall be returned
298        for non-binary formats (the default is True in Python 3)
299    :param list column: an optional list of column names
300    :returns: a generator if stream is set to ``None``, otherwise the cursor
301
302    :raises TypeError: parameters with wrong types
303    :raises ValueError: invalid parameters
304    :raises IOError: error when executing the copy operation
305
306This method can be used to copy data from a database table on the server side
307to an output stream on the client side using the ``COPY TO`` command.
308
309The output stream can be provided in form of a file-like object (which must
310have a ``write()`` method). Alternatively, if ``None`` is passed as the
311output stream, the method will return a generator yielding one row of output
312data on each iteration.
313
314Output will be returned as byte strings unless you set decode to true.
315
316Note that you can also use a ``SELECT`` query instead of the table name.
317
318The format must be text, csv or binary. The sep option sets the column
319separator (delimiter) used in the non binary formats. The null option sets
320the textual representation of ``NULL`` in the output.
321
322The copy operation can be restricted to a subset of columns. If no columns are
323specified, all of them will be copied.
324
325.. versionadded:: 5.0
326
327.. method:: Cursor.row_factory(row)
328
329    Process rows before they are returned
330
331    :param list row: the currently processed row of the result set
332    :returns: the transformed row that the fetch methods shall return
333
334This method is used for processing result rows before returning them through
335one of the fetch methods. By default, rows are returned as named tuples.
336You can overwrite this method with a custom row factory if you want to
337return the rows as different kids of objects. This same row factory will then
338be used for all result sets. If you overwrite this method, the method
339:meth:`Cursor.build_row_factory` for creating row factories dynamically
340will be ignored.
341
342Note that named tuples are very efficient and can be easily converted to
343dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
344to return rows as dicts, you can create a custom cursor class like this::
345
346    class DictCursor(pgdb.Cursor):
347
348        def row_factory(self, row):
349            return {key: value for key, value in zip(self.colnames, row)}
350
351    cur = DictCursor(con)  # get one DictCursor instance or
352    con.cursor_type = DictCursor  # always use DictCursor instances
353
354.. versionadded:: 4.0
355
356.. method:: Cursor.build_row_factory()
357
358    Build a row factory based on the current description
359
360    :returns: callable with the signature of :meth:`Cursor.row_factory`
361
362This method returns row factories for creating named tuples. It is called
363whenever a new result set is created, and :attr:`Cursor.row_factory` is
364then assigned the return value of this method. You can overwrite this method
365with a custom row factory builder if you want to use different row factories
366for different result sets. Otherwise, you can also simply overwrite the
367:meth:`Cursor.row_factory` method. This method will then be ignored.
368
369The default implementation that delivers rows as named tuples essentially
370looks like this::
371
372    def build_row_factory(self):
373        return namedtuple('Row', self.colnames, rename=True)._make
374
375.. versionadded:: 5.0
376
377.. attribute:: Cursor.colnames
378
379    The list of columns names of the current result set
380
381The values in this list are the same values as the *name* elements
382in the :attr:`Cursor.description` attribute. Always use the latter
383if you want to remain standard compliant.
384
385.. versionadded:: 5.0
386
387.. attribute:: Cursor.coltypes
388
389    The list of columns types of the current result set
390
391The values in this list are the same values as the *type_code* elements
392in the :attr:`Cursor.description` attribute. Always use the latter
393if you want to remain standard compliant.
394
395.. versionadded:: 5.0
Note: See TracBrowser for help on using the repository browser.