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

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

Port all doc changes from 4.x branch to trunk

File size: 14.9 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    Note that *display_size*, *precision*, *scale* and *null_ok*
40    are not implemented.
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
99executemany -- execute many similar database operations
100-------------------------------------------------------
101
102.. method:: Cursor.executemany(operation, [seq_of_parameters])
103
104    Prepare and execute many similar database operations (queries or commands)
105
106    :param str operation: the database operation
107    :param seq_of_parameters: a sequence or mapping of parameter tuples or mappings
108    :returns: the cursor, so you can chain commands
109
110Prepare a database operation (query or command) and then execute it against
111all parameter tuples or mappings found in the sequence *seq_of_parameters*.
112
113Parameters are bounded to the query using Python extended format codes,
114e.g. ``" ... WHERE name=%(name)s"``.
115
116callproc -- Call a stored procedure
117-----------------------------------
118
119.. method:: Cursor.callproc(self, procname, [parameters]):
120
121    Call a stored database procedure with the given name
122
123    :param str procname: the name of the database function
124    :param parameters: a sequence of parameters (can be empty or omitted)
125
126This method calls a stored procedure (function) in the PostgreSQL database.
127
128The sequence of parameters must contain one entry for each input argument
129that the function expects. The result of the call is the same as this input
130sequence; replacement of output and input/output parameters in the return
131value is currently not supported.
132
133The function may also provide a result set as output. These can be requested
134through the standard fetch methods of the cursor.
135
136.. versionadded:: 5.0
137
138fetchone -- fetch next row of the query result
139----------------------------------------------
140
141.. method:: Cursor.fetchone()
142
143    Fetch the next row of a query result set
144
145    :returns: the next row of the query result set
146    :rtype: named tuple or None
147
148Fetch the next row of a query result set, returning a single named tuple,
149or ``None`` when no more data is available. The field names of the named
150tuple are the same as the column names of the database query as long as
151they are valid Python identifiers.
152
153An :exc:`Error` (or subclass) exception is raised if the previous call to
154:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
155any result set or no call was issued yet.
156
157.. versionchanged:: 5.0
158    Before version 5.0, this method returned ordinary tuples.
159
160fetchmany -- fetch next set of rows of the query result
161-------------------------------------------------------
162
163.. method:: Cursor.fetchmany([size=None], [keep=False])
164
165    Fetch the next set of rows of a query result
166
167    :param size: the number of rows to be fetched
168    :type size: int or None
169    :param keep: if set to true, will keep the passed arraysize
170    :tpye keep: bool
171    :returns: the next set of rows of the query result
172    :rtype: list of named tuples
173
174Fetch the next set of rows of a query result, returning a list of named
175tuples. An empty sequence is returned when no more rows are available.
176The field names of the named tuple are the same as the column names of
177the database query as long as they are valid Python identifiers.
178
179The number of rows to fetch per call is specified by the *size* parameter.
180If it is not given, the cursor's :attr:`arraysize` determines the number of
181rows to be fetched. If you set the *keep* parameter to True, this is kept as
182new :attr:`arraysize`.
183
184The method tries to fetch as many rows as indicated by the *size* parameter.
185If this is not possible due to the specified number of rows not being
186available, fewer rows may be returned.
187
188An :exc:`Error` (or subclass) exception is raised if the previous call to
189:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
190any result set or no call was issued yet.
191
192Note there are performance considerations involved with the *size* parameter.
193For optimal performance, it is usually best to use the :attr:`arraysize`
194attribute. If the *size* parameter is used, then it is best for it to retain
195the same value from one :meth:`Cursor.fetchmany` call to the next.
196
197.. versionchanged:: 5.0
198    Before version 5.0, this method returned ordinary tuples.
199
200fetchall -- fetch all rows of the query result
201----------------------------------------------
202
203.. method:: Cursor.fetchall()
204
205    Fetch all (remaining) rows of a query result
206
207    :returns: the set of all rows of the query result
208    :rtype: list of named tuples
209
210Fetch all (remaining) rows of a query result, returning them as list of
211named tuples. The field names of the named tuple are the same as the column
212names of the database query as long as they are valid Python identifiers.
213
214Note that the cursor's :attr:`arraysize` attribute can affect the performance
215of this operation.
216
217.. versionchanged:: 5.0
218    Before version 5.0, this method returned ordinary tuples.
219
220arraysize - the number of rows to fetch at a time
221-------------------------------------------------
222
223.. attribute:: Cursor.arraysize
224
225    The number of rows to fetch at a time
226
227This read/write attribute specifies the number of rows to fetch at a time with
228:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
229at a time.
230
231Methods and attributes that are not part of the standard
232--------------------------------------------------------
233
234.. note::
235
236   The following methods and attributes are not part of the DB-API 2 standard.
237
238.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size], [columns])
239
240    Copy data from an input stream to the specified table
241
242    :param stream: the input stream
243        (must be a file-like object, a string or an iterable returning strings)
244    :param str table: the name of a database table
245    :param str format: the format of the data in the input stream,
246        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
247    :param str sep: a single character separator
248        (the default is ``'\t'`` for text and ``','`` for csv)
249    :param str null: the textual representation of the ``NULL`` value,
250        can also be an empty string (the default is ``'\\N'``)
251    :param int size: the size of the buffer when reading file-like objects
252    :param list column: an optional list of column names
253    :returns: the cursor, so you can chain commands
254
255    :raises TypeError: parameters with wrong types
256    :raises ValueError: invalid parameters
257    :raises IOError: error when executing the copy operation
258
259This method can be used to copy data from an input stream on the client side
260to a database table on the server side using the ``COPY FROM`` command.
261The input stream can be provided in form of a file-like object (which must
262have a ``read()`` method), a string, or an iterable returning one row or
263multiple rows of input data on each iteration.
264
265The format must be text, csv or binary. The sep option sets the column
266separator (delimiter) used in the non binary formats. The null option sets
267the textual representation of ``NULL`` in the input.
268
269The size option sets the size of the buffer used when reading data from
270file-like objects.
271
272The copy operation can be restricted to a subset of columns. If no columns are
273specified, all of them will be copied.
274
275.. versionadded:: 5.0
276
277.. method:: Cursor.copy_to(stream, table, [format], [sep], [null], [decode], [columns])
278
279    Copy data from the specified table to an output stream
280
281    :param stream: the output stream (must be a file-like object or ``None``)
282    :param str table: the name of a database table or a ``SELECT`` query
283    :param str format: the format of the data in the input stream,
284        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
285    :param str sep: a single character separator
286        (the default is ``'\t'`` for text and ``','`` for csv)
287    :param str null: the textual representation of the ``NULL`` value,
288        can also be an empty string (the default is ``'\\N'``)
289    :param bool decode: whether decoded strings shall be returned
290        for non-binary formats (the default is True in Python 3)
291    :param list column: an optional list of column names
292    :returns: a generator if stream is set to ``None``, otherwise the cursor
293
294    :raises TypeError: parameters with wrong types
295    :raises ValueError: invalid parameters
296    :raises IOError: error when executing the copy operation
297
298This method can be used to copy data from a database table on the server side
299to an output stream on the client side using the ``COPY TO`` command.
300
301The output stream can be provided in form of a file-like object (which must
302have a ``write()`` method). Alternatively, if ``None`` is passed as the
303output stream, the method will return a generator yielding one row of output
304data on each iteration.
305
306Output will be returned as byte strings unless you set decode to true.
307
308Note that you can also use a ``SELECT`` query instead of the table name.
309
310The format must be text, csv or binary. The sep option sets the column
311separator (delimiter) used in the non binary formats. The null option sets
312the textual representation of ``NULL`` in the output.
313
314The copy operation can be restricted to a subset of columns. If no columns are
315specified, all of them will be copied.
316
317.. versionadded:: 5.0
318
319.. method:: Cursor.row_factory(row)
320
321    Process rows before they are returned
322
323    :param list row: the currently processed row of the result set
324    :returns: the transformed row that the fetch methods shall return
325
326This method is used for processing result rows before returning them through
327one of the fetch methods. By default, rows are returned as named tuples.
328You can overwrite this method with a custom row factory if you want to
329return the rows as different kids of objects. This same row factory will then
330be used for all result sets. If you overwrite this method, the method
331:meth:`Cursor.build_row_factory` for creating row factories dynamically
332will be ignored.
333
334Note that named tuples are very efficient and can be easily converted to
335dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
336to return rows as dicts, you can create a custom cursor class like this::
337
338    class DictCursor(pgdb.Cursor):
339
340        def row_factory(self, row):
341            return {key: value for key, value in zip(self.colnames, row)}
342
343    cur = DictCursor(con)  # get one DictCursor instance or
344    con.cursor_type = DictCursor  # always use DictCursor instances
345
346.. versionadded:: 4.0
347
348.. method:: Cursor.build_row_factory()
349
350    Build a row factory based on the current description
351
352    :returns: callable with the signature of :meth:`Cursor.row_factory`
353
354This method returns row factories for creating named tuples. It is called
355whenever a new result set is created, and :attr:`Cursor.row_factory` is
356then assigned the return value of this method. You can overwrite this method
357with a custom row factory builder if you want to use different row factories
358for different result sets. Otherwise, you can also simply overwrite the
359:meth:`Cursor.row_factory` method. This method will then be ignored.
360
361The default implementation that delivers rows as named tuples essentially
362looks like this::
363
364    def build_row_factory(self):
365        return namedtuple('Row', self.colnames, rename=True)._make
366
367.. versionadded:: 5.0
368
369.. attribute:: Cursor.colnames
370
371    The list of columns names of the current result set
372
373The values in this list are the same values as the *name* elements
374in the :attr:`Cursor.description` attribute. Always use the latter
375if you want to remain standard compliant.
376
377.. versionadded:: 5.0
378
379.. attribute:: Cursor.coltypes
380
381    The list of columns types of the current result set
382
383The values in this list are the same values as the *type_code* elements
384in the :attr:`Cursor.description` attribute. Always use the latter
385if you want to remain standard compliant.
386
387.. versionadded:: 5.0
Note: See TracBrowser for help on using the repository browser.