Cursor – The cursor object

class pgdb.Cursor

These objects represent a database cursor, which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. Cursors created from different connections can or can not be isolated, depending on the level of transaction isolation. The default PostgreSQL transaction isolation level is “read committed”.

Cursor objects respond to the following methods and attributes.

Note that Cursor objects also implement both the iterator and the context manager protocol, i.e. you can iterate over them and you can use them in a with statement.

description – details regarding the result columns

Cursor.description

This read-only attribute is a sequence of 7-item named tuples.

Each of these named tuples contains information describing one result column:

  • name

  • type_code

  • display_size

  • internal_size

  • precision

  • scale

  • null_ok

The values for precision and scale are only set for numeric types. The values for display_size and null_ok are always None.

This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the Cursor.execute() or Cursor.executemany() method yet.

Changed in version 5.0: Before version 5.0, this attribute was an ordinary tuple.

rowcount – number of rows of the result

Cursor.rowcount

This read-only attribute specifies the number of rows that the last Cursor.execute() or Cursor.executemany() call produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE or INSERT). It is also set by the Cursor.copy_from() and Cursor.copy_to() methods. The attribute is -1 in case no such method call has been performed on the cursor or the rowcount of the last operation cannot be determined by the interface.

close – close the cursor

Cursor.close()

Close the cursor now (rather than whenever it is deleted)

Return type:

None

The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.

execute – execute a database operation

Cursor.execute(operation[, parameters])

Prepare and execute a database operation (query or command)

Parameters:

  • operation (str) – the database operation

  • parameters – a sequence or mapping of parameters

Returns:

the cursor, so you can chain commands

Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified using Python extended format codes, e.g. " ... WHERE name=%(name)s".

A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor can optimize its behavior. This is most effective for algorithms where the same operation is used, but different parameters are bound to it (many times).

The parameters may also be specified as list of tuples to e.g. insert multiple rows in a single operation, but this kind of usage is deprecated: Cursor.executemany() should be used instead.

Note that in case this method raises a DatabaseError, you can get information about the error condition that has occurred by introspecting its DatabaseError.sqlstate attribute, which will be the SQLSTATE error code associated with the error. Applications that need to know which error condition has occurred should usually test the error code, rather than looking at the textual error message.

executemany – execute many similar database operations

Cursor.executemany(operation[, seq_of_parameters])

Prepare and execute many similar database operations (queries or commands)

Parameters:

  • operation (str) – the database operation

  • seq_of_parameters – a sequence or mapping of parameter tuples or mappings

Returns:

the cursor, so you can chain commands

Prepare a database operation (query or command) and then execute it against all parameter tuples or mappings found in the sequence seq_of_parameters.

Parameters are bound to the query using Python extended format codes, e.g. " ... WHERE name=%(name)s".

callproc – Call a stored procedure

Cursor.callproc(self, procname, [parameters]):

Call a stored database procedure with the given name

Parameters:

  • procname (str) – the name of the database function

  • parameters – a sequence of parameters (can be empty or omitted)

This method calls a stored procedure (function) in the PostgreSQL database.

The sequence of parameters must contain one entry for each input argument that the function expects. The result of the call is the same as this input sequence; replacement of output and input/output parameters in the return value is currently not supported.

The function may also provide a result set as output. These can be requested through the standard fetch methods of the cursor.

Added in version 5.0.

fetchone – fetch next row of the query result

Cursor.fetchone()

Fetch the next row of a query result set

Returns:

the next row of the query result set

Return type:

namedtuple or None

Fetch the next row of a query result set, returning a single named tuple, or None when no more data is available. The field names of the named tuple are the same as the column names of the database query as long as they are valid Python identifiers.

An Error (or subclass) exception is raised if the previous call to Cursor.execute() or Cursor.executemany() did not produce any result set or no call was issued yet.

Changed in version 5.0: Before version 5.0, this method returned ordinary tuples.

fetchmany – fetch next set of rows of the query result

Cursor.fetchmany([size=None][, keep=False])

Fetch the next set of rows of a query result

Parameters:

  • size (int or None) – the number of rows to be fetched

  • keep – if set to true, will keep the passed arraysize

Tpye keep:

bool

Returns:

the next set of rows of the query result

Return type:

list of namedtuples

Fetch the next set of rows of a query result, returning a list of named tuples. An empty sequence is returned when no more rows are available. The field names of the named tuple are the same as the column names of the database query as long as they are valid Python identifiers.

The number of rows to fetch per call is specified by the size parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. If you set the keep parameter to True, this is kept as new arraysize.

The method tries to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.

An Error (or subclass) exception is raised if the previous call to Cursor.execute() or Cursor.executemany() did not produce any result set or no call was issued yet.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one Cursor.fetchmany() call to the next.

Changed in version 5.0: Before version 5.0, this method returned ordinary tuples.

fetchall – fetch all rows of the query result

Cursor.fetchall()

Fetch all (remaining) rows of a query result

Returns:

the set of all rows of the query result

Return type:

list of namedtuples

Fetch all (remaining) rows of a query result, returning them as list of named tuples. The field names of the named tuple are the same as the column names of the database query as long as they are valid as field names for named tuples, otherwise they are given positional names.

Note that the cursor’s arraysize attribute can affect the performance of this operation.

Changed in version 5.0: Before version 5.0, this method returned ordinary tuples.

arraysize - the number of rows to fetch at a time

Cursor.arraysize

The number of rows to fetch at a time

This read/write attribute specifies the number of rows to fetch at a time with Cursor.fetchmany(). It defaults to 1, meaning to fetch a single row at a time.

Methods and attributes that are not part of the standard

Note

The following methods and attributes are not part of the DB-API 2 standard.

Cursor.copy_from(stream, table[, format][, sep][, null][, size][, columns])

Copy data from an input stream to the specified table

Parameters:

  • stream – the input stream (must be a file-like object, a string or an iterable returning strings)

  • table (str) – the name of a database table

  • format (str) – the format of the data in the input stream, can be 'text' (the default), 'csv', or 'binary'

  • sep (str) – a single character separator (the default is '\t' for text and ',' for csv)

  • null (str) – the textual representation of the NULL value, can also be an empty string (the default is '\\N')

  • size (int) – the size of the buffer when reading file-like objects

  • column (list) – an optional list of column names

Returns:

the cursor, so you can chain commands

Raises:

  • TypeError – parameters with wrong types

  • ValueError – invalid parameters

  • IOError – error when executing the copy operation

This method can be used to copy data from an input stream on the client side to a database table on the server side using the COPY FROM command. The input stream can be provided in form of a file-like object (which must have a read() method), a string, or an iterable returning one row or multiple rows of input data on each iteration.

The format must be text, csv or binary. The sep option sets the column separator (delimiter) used in the non binary formats. The null option sets the textual representation of NULL in the input.

The size option sets the size of the buffer used when reading data from file-like objects.

The copy operation can be restricted to a subset of columns. If no columns are specified, all of them will be copied.

Added in version 5.0.

Cursor.copy_to(stream, table[, format][, sep][, null][, decode][, columns])

Copy data from the specified table to an output stream

Parameters:

  • stream – the output stream (must be a file-like object or None)

  • table (str) – the name of a database table or a SELECT query

  • format (str) – the format of the data in the input stream, can be 'text' (the default), 'csv', or 'binary'

  • sep (str) – a single character separator (the default is '\t' for text and ',' for csv)

  • null (str) – the textual representation of the NULL value, can also be an empty string (the default is '\\N')

  • decode (bool) – whether decoded strings shall be returned for non-binary formats (the default is True)

  • column (list) – an optional list of column names

Returns:

a generator if stream is set to None, otherwise the cursor

Raises:

  • TypeError – parameters with wrong types

  • ValueError – invalid parameters

  • IOError – error when executing the copy operation

This method can be used to copy data from a database table on the server side to an output stream on the client side using the COPY TO command.

The output stream can be provided in form of a file-like object (which must have a write() method). Alternatively, if None is passed as the output stream, the method will return a generator yielding one row of output data on each iteration.

Output will be returned as byte strings unless you set decode to true.

Note that you can also use a SELECT query instead of the table name.

The format must be text, csv or binary. The sep option sets the column separator (delimiter) used in the non binary formats. The null option sets the textual representation of NULL in the output.

The copy operation can be restricted to a subset of columns. If no columns are specified, all of them will be copied.

Added in version 5.0.

Cursor.row_factory(row)

Process rows before they are returned

Parameters:

row (list) – the currently processed row of the result set

Returns:

the transformed row that the fetch methods shall return

This method is used for processing result rows before returning them through one of the fetch methods. By default, rows are returned as named tuples. You can overwrite this method with a custom row factory if you want to return the rows as different kids of objects. This same row factory will then be used for all result sets. If you overwrite this method, the method Cursor.build_row_factory() for creating row factories dynamically will be ignored.

Note that named tuples are very efficient and can be easily converted to dicts by calling row._asdict(). If you still want to return rows as dicts, you can create a custom cursor class like this:

class DictCursor(pgdb.Cursor):

    def row_factory(self, row):
        return {key: value for key, value in zip(self.colnames, row)}

cur = DictCursor(con)  # get one DictCursor instance or
con.cursor_type = DictCursor  # always use DictCursor instances

Added in version 4.0.

Cursor.build_row_factory()

Build a row factory based on the current description

Returns:

callable with the signature of Cursor.row_factory()

This method returns row factories for creating named tuples. It is called whenever a new result set is created, and Cursor.row_factory is then assigned the return value of this method. You can overwrite this method with a custom row factory builder if you want to use different row factories for different result sets. Otherwise, you can also simply overwrite the Cursor.row_factory() method. This method will then be ignored.

The default implementation that delivers rows as named tuples essentially looks like this:

def build_row_factory(self):
    return namedtuple('Row', self.colnames, rename=True)._make

Added in version 5.0.

Cursor.colnames

The list of columns names of the current result set

The values in this list are the same values as the name elements in the Cursor.description attribute. Always use the latter if you want to remain standard compliant.

Added in version 5.0.

Cursor.coltypes

The list of columns types of the current result set

The values in this list are the same values as the type_code elements in the Cursor.description attribute. Always use the latter if you want to remain standard compliant.

Added in version 5.0.