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

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

Mention the SQLSTATE error code in the pgdb docs

File size: 15.3 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
99Note that in case this method raises a :exception:`DatabaseError`, you can
100get information 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 Python identifiers.
220
221Note that the cursor's :attr:`arraysize` attribute can affect the performance
222of this operation.
223
224.. versionchanged:: 5.0
225    Before version 5.0, this method returned ordinary tuples.
226
227arraysize - the number of rows to fetch at a time
228-------------------------------------------------
229
230.. attribute:: Cursor.arraysize
231
232    The number of rows to fetch at a time
233
234This read/write attribute specifies the number of rows to fetch at a time with
235:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
236at a time.
237
238Methods and attributes that are not part of the standard
239--------------------------------------------------------
240
241.. note::
242
243   The following methods and attributes are not part of the DB-API 2 standard.
244
245.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size], [columns])
246
247    Copy data from an input stream to the specified table
248
249    :param stream: the input stream
250        (must be a file-like object, a string or an iterable returning strings)
251    :param str table: the name of a database table
252    :param str format: the format of the data in the input stream,
253        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
254    :param str sep: a single character separator
255        (the default is ``'\t'`` for text and ``','`` for csv)
256    :param str null: the textual representation of the ``NULL`` value,
257        can also be an empty string (the default is ``'\\N'``)
258    :param int size: the size of the buffer when reading file-like objects
259    :param list column: an optional list of column names
260    :returns: the cursor, so you can chain commands
261
262    :raises TypeError: parameters with wrong types
263    :raises ValueError: invalid parameters
264    :raises IOError: error when executing the copy operation
265
266This method can be used to copy data from an input stream on the client side
267to a database table on the server side using the ``COPY FROM`` command.
268The input stream can be provided in form of a file-like object (which must
269have a ``read()`` method), a string, or an iterable returning one row or
270multiple rows of input data on each iteration.
271
272The format must be text, csv or binary. The sep option sets the column
273separator (delimiter) used in the non binary formats. The null option sets
274the textual representation of ``NULL`` in the input.
275
276The size option sets the size of the buffer used when reading data from
277file-like objects.
278
279The copy operation can be restricted to a subset of columns. If no columns are
280specified, all of them will be copied.
281
282.. versionadded:: 5.0
283
284.. method:: Cursor.copy_to(stream, table, [format], [sep], [null], [decode], [columns])
285
286    Copy data from the specified table to an output stream
287
288    :param stream: the output stream (must be a file-like object or ``None``)
289    :param str table: the name of a database table or a ``SELECT`` query
290    :param str format: the format of the data in the input stream,
291        can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
292    :param str sep: a single character separator
293        (the default is ``'\t'`` for text and ``','`` for csv)
294    :param str null: the textual representation of the ``NULL`` value,
295        can also be an empty string (the default is ``'\\N'``)
296    :param bool decode: whether decoded strings shall be returned
297        for non-binary formats (the default is True in Python 3)
298    :param list column: an optional list of column names
299    :returns: a generator if stream is set to ``None``, otherwise the cursor
300
301    :raises TypeError: parameters with wrong types
302    :raises ValueError: invalid parameters
303    :raises IOError: error when executing the copy operation
304
305This method can be used to copy data from a database table on the server side
306to an output stream on the client side using the ``COPY TO`` command.
307
308The output stream can be provided in form of a file-like object (which must
309have a ``write()`` method). Alternatively, if ``None`` is passed as the
310output stream, the method will return a generator yielding one row of output
311data on each iteration.
312
313Output will be returned as byte strings unless you set decode to true.
314
315Note that you can also use a ``SELECT`` query instead of the table name.
316
317The format must be text, csv or binary. The sep option sets the column
318separator (delimiter) used in the non binary formats. The null option sets
319the textual representation of ``NULL`` in the output.
320
321The copy operation can be restricted to a subset of columns. If no columns are
322specified, all of them will be copied.
323
324.. versionadded:: 5.0
325
326.. method:: Cursor.row_factory(row)
327
328    Process rows before they are returned
329
330    :param list row: the currently processed row of the result set
331    :returns: the transformed row that the fetch methods shall return
332
333This method is used for processing result rows before returning them through
334one of the fetch methods. By default, rows are returned as named tuples.
335You can overwrite this method with a custom row factory if you want to
336return the rows as different kids of objects. This same row factory will then
337be used for all result sets. If you overwrite this method, the method
338:meth:`Cursor.build_row_factory` for creating row factories dynamically
339will be ignored.
340
341Note that named tuples are very efficient and can be easily converted to
342dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
343to return rows as dicts, you can create a custom cursor class like this::
344
345    class DictCursor(pgdb.Cursor):
346
347        def row_factory(self, row):
348            return {key: value for key, value in zip(self.colnames, row)}
349
350    cur = DictCursor(con)  # get one DictCursor instance or
351    con.cursor_type = DictCursor  # always use DictCursor instances
352
353.. versionadded:: 4.0
354
355.. method:: Cursor.build_row_factory()
356
357    Build a row factory based on the current description
358
359    :returns: callable with the signature of :meth:`Cursor.row_factory`
360
361This method returns row factories for creating named tuples. It is called
362whenever a new result set is created, and :attr:`Cursor.row_factory` is
363then assigned the return value of this method. You can overwrite this method
364with a custom row factory builder if you want to use different row factories
365for different result sets. Otherwise, you can also simply overwrite the
366:meth:`Cursor.row_factory` method. This method will then be ignored.
367
368The default implementation that delivers rows as named tuples essentially
369looks like this::
370
371    def build_row_factory(self):
372        return namedtuple('Row', self.colnames, rename=True)._make
373
374.. versionadded:: 5.0
375
376.. attribute:: Cursor.colnames
377
378    The list of columns names of the current result set
379
380The values in this list are the same values as the *name* elements
381in the :attr:`Cursor.description` attribute. Always use the latter
382if you want to remain standard compliant.
383
384.. versionadded:: 5.0
385
386.. attribute:: Cursor.coltypes
387
388    The list of columns types of the current result set
389
390The values in this list are the same values as the *type_code* elements
391in the :attr:`Cursor.description` attribute. Always use the latter
392if you want to remain standard compliant.
393
394.. versionadded:: 5.0
Note: See TracBrowser for help on using the repository browser.