Cursor classes#

The Cursor and AsyncCursor classes are the main objects to send commands to a PostgreSQL database session. They are normally created by the connection’s cursor() method.

Using the name parameter on cursor() will create a ServerCursor or AsyncServerCursor, which can be used to retrieve partial results from a database.

Other cursor classes can be created by directly instantiating them, or can be set as Connection.cursor_factory to require them on cursor() call.

This page describe the details of the Cursor class interface. Please refer to Cursor types for general information about the different types of cursors available in Psycopg.

The Cursor class#

class psycopg.Cursor(connection: Connection[Any], *, row_factory: RowFactory[Row] | None = None)#

This class implements a DBAPI-compliant interface. It is what the classic Connection.cursor() method returns. AsyncConnection.cursor() will create instead AsyncCursor objects, which have the same set of method but expose an asyncio interface and require async and await keywords to operate.

Cursors behave as context managers: on block exit they are closed and further operation will not be possible. Closing a cursor will not terminate a transaction or a session though.

connection: Connection#

The connection this cursor is using.

close() → None#

Close the current cursor and free associated resources.

Note

You can use:

with conn.cursor() as cur:
    ...

to close the cursor automatically when the block is exited. See Main objects in Psycopg 3.

closed#

True if the cursor is closed.

Methods to send commands

execute(query: Query, params: Params | None = None, *, prepare: bool | None = None, binary: bool | None = None) → Self#

Execute a query or command to the database.

Parameters:

  • query (str, bytes, sql.SQL, or sql.Composed) – The query to execute.

  • params (Sequence or Mapping) – The parameters to pass to the query, if any.

  • prepare – Force (True) or disallow (False) preparation of the query. By default (None) prepare automatically. See Prepared statements.

  • binary – Specify whether the server should return data in binary format (True) or in text format (False). By default (None) return data as requested by the cursor’s format.

Return the cursor itself, so that it will be possible to chain a fetch operation after the call.

See Passing parameters to SQL queries for all the details about executing queries.

Changed in version 3.1: The query argument must be a StringLiteral. If you need to compose a query dynamically, please use sql.SQL and related objects.

See PEP 675 for details.

executemany(query: Query, params_seq: Iterable[Params], *, returning: bool = False) → None#

Execute the same command with a sequence of input data.

Parameters:

  • query (str, bytes, sql.SQL, or sql.Composed) – The query to execute

  • params_seq (Sequence of Sequences or Mappings) – The parameters to pass to the query

  • returning (bool) – If True, fetch the results of the queries executed

This is more efficient than performing separate queries, but in case of several INSERT (and with some SQL creativity for massive UPDATE too) you may consider using copy().

If the queries return data you want to read (e.g. when executing an INSERT ... RETURNING or a SELECT with a side-effect), you can specify returning=True. This is equivalent of calling execute() as many times as the number of items in params_seq, and to store all the results in the cursor’s state.

Note

Using the usual fetchone(), fetchall(), you will be able to read the records returned by the first query executed only. In order to read the results of the following queries you can call nextset() to move to the following result set.

A typical use case for executemany(returning=True) might be to insert a bunch of records and to retrieve the primary keys inserted, taken from a PostgreSQL sequence. In order to do so, you may execute a query such as INSERT INTO table VALUES (...) RETURNING id. Because every INSERT is guaranteed to insert exactly a single record, you can obtain the list of the new ids using a pattern such as:

cur.executemany(query, records)
ids = []
while True:
    ids.append(cur.fetchone()[0])
    if not cur.nextset():
        break

Warning

More explicitly, fetchall() alone will not return all the values returned! You must iterate on the results using nextset().

If returning=False, the value of rowcount is set to the cumulated number of rows affected by queries. If returning=True, rowcount is set to the number of rows in the current result set (i.e. the first one, until nextset() gets called).

See Passing parameters to SQL queries for all the details about executing queries.

Changed in version 3.1:

  • Added returning parameter to receive query results.

  • Performance optimised by making use of the pipeline mode, when using libpq 14 or newer.

copy(statement: Query, params: Params | None = None, *, writer: Writer | None = None) → Iterator[Copy]#

Initiate a COPY operation and return an object to manage it.

Parameters:

  • statement (str, bytes, sql.SQL, or sql.Composed) – The copy operation to execute

  • params (Sequence or Mapping) – The parameters to pass to the statement, if any.

Note

The method must be called with:

with cursor.copy() as copy:
    ...

See Using COPY TO and COPY FROM for information about COPY.

Changed in version 3.1: Added parameters support.

stream(query: Query, params: Params | None = None, *, binary: bool | None = None, size: int = 1) → Iterator[Row]#

Iterate row-by-row on a result from the database.

Parameters:

size – if greater than 1, results will be retrieved by chunks of this size from the server (but still yielded row-by-row); this is only available from version 17 of the libpq.

This command is similar to execute + iter; however it supports endless data streams. The feature is not available in PostgreSQL, but some implementations exist: Materialize SUBSCRIBE and CockroachDB CHANGEFEED for instance.

The feature, and the API supporting it, are still experimental. Beware… 👀

The parameters are the same of execute(), except for size which can be used to set results retrieval by chunks instead of row-by-row.

Note

This size parameter is only available from libpq 17, you can use the has_stream_chunked capability to check if this is supported.

Warning

Failing to consume the iterator entirely will result in a connection left in transaction_status ACTIVE state: this connection will refuse to receive further commands (with a message such as another command is already in progress).

If there is a chance that the generator is not consumed entirely, in order to restore the connection to a working state you can call close on the generator object returned by stream(). The contextlib.closing function might be particularly useful to make sure that close() is called:

with closing(cur.stream("select generate_series(1, 10000)")) as gen:
    for rec in gen:
        something(rec)  # might fail

Without calling close(), in case of error, the connection will be ACTIVE and unusable. If close() is called, the connection might be INTRANS or INERROR, depending on whether the server managed to send the entire resultset to the client. An autocommit connection will be IDLE instead.

format#

The format of the data returned by the queries. It can be selected initially e.g. specifying Connection.cursor(binary=True) and changed during the cursor’s lifetime. It is also possible to override the value for single queries, e.g. specifying execute(binary=True).

Type:

pq.Format

Default:

TEXT

See also

Binary parameters and results

Methods to retrieve results

Fetch methods are only available if the last operation produced results, e.g. a SELECT or a command with RETURNING. They will raise an exception if used with operations that don’t return result, such as an INSERT with no RETURNING or an ALTER TABLE.

Note

Cursors are iterable objects, so just using the:

for record in cursor:
    ...

syntax will iterate on the records in the current recordset.

row_factory#

Writable attribute to control how result rows are formed.

The property affects the objects returned by the fetchone(), fetchmany(), fetchall() methods. The default (tuple_row) returns a tuple for each record fetched.

See Row factories for details.

fetchone() → Optional[Row]#

Return the next record from the current recordset.

Return None the recordset is finished.

Return type:

Row | None, with Row defined by row_factory

fetchmany(size: int = 0) → list[+Row]#

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

Return type:

Sequence[Row], with Row defined by row_factory

fetchall() → list[+Row]#

Return all the remaining records from the current recordset.

Return type:

Sequence[Row], with Row defined by row_factory

nextset() → bool | None#

Move to the result set of the next query executed through executemany() or to the next result set if execute() returned more than one.

Return True if a new result is available, which will be the one methods fetch*() will operate on.

scroll(value: int, mode: str = 'relative') → None#

Move the cursor in the result set to a new position according to mode.

If mode is 'relative' (default), value is taken as offset to the current position in the result set; if set to 'absolute', value states an absolute target position.

Raise IndexError in case a scroll operation would leave the result set. In this case the position will not change.

pgresult: Optional[psycopg.pq.PGresult]#

The result returned by the last query and currently exposed by the cursor, if available, else None.

It can be used to obtain low level info about the last query result and to access to features not currently wrapped by Psycopg.

Information about the data

description#

A list of Column objects describing the current resultset.

None if the current resultset didn’t return tuples.

statusmessage#

The command status tag from the last SQL command executed.

None if the cursor doesn’t have a result available.

This is the status tag you typically see in psql after a successful command, such as CREATE TABLE or UPDATE 42.

rowcount#

Number of records affected by the precedent operation.

From executemany(), unless called with returning=True, this is the cumulated number of rows affected by executed commands.

rownumber#

Index of the next row to fetch in the current result.

None if there is no result to fetch.

_query#

An helper object used to convert queries and parameters before sending them to PostgreSQL.

Note

This attribute is exposed because it might be helpful to debug problems when the communication between Python and PostgreSQL doesn’t work as expected. For this reason, the attribute is available when a query fails too.

Warning

You shouldn’t consider it part of the public interface of the object: it might change without warnings.

Except this warning, I guess.

If you would like to build reliable features using this object, please get in touch so we can try and design an useful interface for it.

Among the properties currently exposed by this object:

  • query (bytes): the query effectively sent to PostgreSQL. It will have Python placeholders (%s-style) replaced with PostgreSQL ones ($1, $2-style).

  • params (sequence of bytes): the parameters passed to PostgreSQL, adapted to the database format.

  • types (sequence of int): the OID of the parameters passed to PostgreSQL.

  • formats (sequence of pq.Format): whether the parameter format is text or binary.

The ClientCursor class#

See also

See Client-side-binding cursors for details.

class psycopg.ClientCursor(connection: Connection[Any], *, row_factory: RowFactory[Row] | None = None)#

This Cursor subclass has exactly the same interface of its parent class, but, instead of sending query and parameters separately to the server, it merges them on the client and sends them as a non-parametric query on the server. This allows, for instance, to execute parametrized data definition statements and other problematic queries.

New in version 3.1.

mogrify(query: Query, params: Params | None = None) → str#

Return the query and parameters merged.

Parameters are adapted and merged to the query the same way that execute() would do.

Parameters:

  • query (str, bytes, sql.SQL, or sql.Composed) – The query to execute.

  • params (Sequence or Mapping) – The parameters to pass to the query, if any.

The ServerCursor class#

See also

See Server-side cursors for details.

class psycopg.ServerCursor(connection: Connection[Any], name: str, *, row_factory: RowFactory[Row] | None = None, scrollable: bool | None = None, withhold: bool = False)#

This class also implements a DBAPI-compliant interface. It is created by Connection.cursor() specifying the name parameter. Using this object results in the creation of an equivalent PostgreSQL cursor in the server. DBAPI-extension methods (such as copy() or stream()) are not implemented on this object: use a normal Cursor instead.

Most attribute and methods behave exactly like in Cursor, here are documented the differences:

name#

The name of the cursor.

scrollable#

Whether the cursor is scrollable or not.

If None leave the choice to the server. Use True if you want to use scroll() on the cursor.

See also

The PostgreSQL DECLARE statement documentation for the description of [NO] SCROLL.

withhold#

If the cursor can be used after the creating transaction has committed.

See also

The PostgreSQL DECLARE statement documentation for the description of {WITH|WITHOUT} HOLD.

close() → None#

Close the current cursor and free associated resources.

Warning

Closing a server-side cursor is more important than closing a client-side one because it also releases the resources on the server, which otherwise might remain allocated until the end of the session (memory, locks). Using the pattern:

with conn.cursor():
    ...

is especially useful so that the cursor is closed at the end of the block.

execute(query: Union[LiteralString, bytes, SQL, Composed], params: Optional[Union[Sequence[Any], Mapping[str, Any]]] = None, *, binary: Optional[bool] = None, **kwargs: Any) → Self#

Open a cursor to execute a query to the database.

Parameters:

  • query (str, bytes, sql.SQL, or sql.Composed) – The query to execute.

  • params (Sequence or Mapping) – The parameters to pass to the query, if any.

  • binary – Specify whether the server should return data in binary format (True) or in text format (False). By default (None) return data as requested by the cursor’s format.

Create a server cursor with given name and the query in argument.

If using DECLARE is not appropriate (for instance because the cursor is returned by calling a stored procedure) you can avoid to use execute(), crete the cursor in other ways, and use directly the fetch*() methods instead. See “Stealing” an existing cursor for an example.

Using execute() more than once will close the previous cursor and open a new one with the same name.

executemany(query: Union[LiteralString, bytes, SQL, Composed], params_seq: Iterable[Union[Sequence[Any], Mapping[str, Any]]], *, returning: bool = True) → None#

Method not implemented for server-side cursors.

fetchone() → Optional[Row]#

Return the next record from the current recordset.

Return None the recordset is finished.

Return type:

Row | None, with Row defined by row_factory

fetchmany(size: int = 0) → list[+Row]#

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

Return type:

Sequence[Row], with Row defined by row_factory

fetchall() → list[+Row]#

Return all the remaining records from the current recordset.

Return type:

Sequence[Row], with Row defined by row_factory

These methods use the FETCH SQL statement to retrieve some of the records from the cursor’s current position.

Note

You can also iterate on the cursor to read its result one at time with:

for record in cur:
    ...

In this case, the records are not fetched one at time from the server but they are retrieved in batches of itersize to reduce the number of server roundtrips.

itersize: int#

Number of records to fetch at time when iterating on the cursor. The default is 100.

scroll(value: int, mode: str = 'relative') → None#

Move the cursor in the result set to a new position according to mode.

If mode is 'relative' (default), value is taken as offset to the current position in the result set; if set to 'absolute', value states an absolute target position.

Raise IndexError in case a scroll operation would leave the result set. In this case the position will not change.

This method uses the MOVE SQL statement to move the current position in the server-side cursor, which will affect following fetch*() operations. If you need to scroll backwards you should probably call cursor() using scrollable=True.

Note that PostgreSQL doesn’t provide a reliable way to report when a cursor moves out of bound, so the method might not raise IndexError when it happens, but it might rather stop at the cursor boundary.

The RawCursor and RawServerCursor class#

See also

See Raw query cursors for details.

class psycopg.RawCursor(connection: Connection[Any], *, row_factory: RowFactory[Row] | None = None)#

This Cursor subclass has the same interface of the parent class but supports placeholders in PostgreSQL format ($1, $2…) rather than in Python format (%s). Only positional parameters are supported.

New in version 3.2.

class psycopg.RawServerCursor(connection: Connection[Any], name: str, *, row_factory: RowFactory[Row] | None = None, scrollable: bool | None = None, withhold: bool = False)#

This ServerCursor subclass has the same interface of the parent class but supports placeholders in PostgreSQL format ($1, $2…) rather than in Python format (%s). Only positional parameters are supported.

New in version 3.2.

Async cursor classes#

Every Cursor class has an equivalent Async version exposing the same semantic with an async interface. The main interface is described in AsyncCursor.

class psycopg.AsyncCursor(connection: AsyncConnection[Any], *, row_factory: AsyncRowFactory[Row] | None = None)#

This class implements a DBAPI-inspired interface, with all the blocking methods implemented as coroutines. Unless specified otherwise, non-blocking methods are shared with the Cursor class.

The following methods have the same behaviour of the matching Cursor methods, but should be called using the await keyword.

connection: AsyncConnection#
async close() → None#

Close the current cursor and free associated resources.

Note

You can use:

async with conn.cursor():
    ...

to close the cursor automatically when the block is exited.

async execute(query: Query, params: Params | None = None, *, prepare: bool | None = None, binary: bool | None = None) → Self#

Execute a query or command to the database.

async executemany(query: Query, params_seq: Iterable[Params], *, returning: bool = False) → None#

Execute the same command with a sequence of input data.

copy(statement: Query, params: Params | None = None, *, writer: AsyncWriter | None = None) → AsyncIterator[AsyncCopy]#

Initiate a COPY operation and return an object to manage it.

Note

The method must be called with:

async with cursor.copy() as copy:
    ...
async stream(query: Query, params: Params | None = None, *, binary: bool | None = None, size: int = 1) → AsyncIterator[Row]#

Iterate row-by-row on a result from the database.

Parameters:

size – if greater than 1, results will be retrieved by chunks of this size from the server (but still yielded row-by-row); this is only available from version 17 of the libpq.

Note

The method must be called with:

async for record in cursor.stream(query):
    ...
async fetchone() → Optional[Row]#

Return the next record from the current recordset.

Return None the recordset is finished.

Return type:

Row | None, with Row defined by row_factory

async fetchmany(size: int = 0) → list[+Row]#

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

Return type:

Sequence[Row], with Row defined by row_factory

async fetchall() → list[+Row]#

Return all the remaining records from the current recordset.

Return type:

Sequence[Row], with Row defined by row_factory

async scroll(value: int, mode: str = 'relative') → None#

Move the cursor in the result set to a new position according to mode.

If mode is 'relative' (default), value is taken as offset to the current position in the result set; if set to 'absolute', value states an absolute target position.

Raise IndexError in case a scroll operation would leave the result set. In this case the position will not change.

Note

You can also use:

async for record in cursor:
    ...

to iterate on the async cursor results.

class psycopg.AsyncClientCursor(connection: AsyncConnection[Any], *, row_factory: AsyncRowFactory[Row] | None = None)#

This class is the async equivalent of ClientCursor. The differences w.r.t. the sync counterpart are the same described in AsyncCursor.

New in version 3.1.

class psycopg.AsyncServerCursor(connection: AsyncConnection[Any], name: str, *, row_factory: AsyncRowFactory[Row] | None = None, scrollable: bool | None = None, withhold: bool = False)#

This class implements a DBAPI-inspired interface as the AsyncCursor does, but wraps a server-side cursor like the ServerCursor class. It is created by AsyncConnection.cursor() specifying the name parameter.

The following are the methods exposing a different (async) interface from the ServerCursor counterpart, but sharing the same semantics.

async close() → None#

Close the current cursor and free associated resources.

Note

You can close the cursor automatically using:

async with conn.cursor("name") as cursor:
    ...
async execute(query: Union[LiteralString, bytes, SQL, Composed], params: Optional[Union[Sequence[Any], Mapping[str, Any]]] = None, *, binary: Optional[bool] = None, **kwargs: Any) → Self#

Execute a query or command to the database.

async executemany(query: Union[LiteralString, bytes, SQL, Composed], params_seq: Iterable[Union[Sequence[Any], Mapping[str, Any]]], *, returning: bool = True) → None#

Execute the same command with a sequence of input data.

async fetchone() → Optional[Row]#

Return the next record from the current recordset.

Return None the recordset is finished.

Return type:

Row | None, with Row defined by row_factory

async fetchmany(size: int = 0) → list[+Row]#

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

Return type:

Sequence[Row], with Row defined by row_factory

async fetchall() → list[+Row]#

Return all the remaining records from the current recordset.

Return type:

Sequence[Row], with Row defined by row_factory

Note

You can also iterate on the cursor using:

async for record in cur:
    ...
async scroll(value: int, mode: str = 'relative') → None#

Move the cursor in the result set to a new position according to mode.

If mode is 'relative' (default), value is taken as offset to the current position in the result set; if set to 'absolute', value states an absolute target position.

Raise IndexError in case a scroll operation would leave the result set. In this case the position will not change.

class psycopg.AsyncRawCursor(connection: AsyncConnection[Any], *, row_factory: AsyncRowFactory[Row] | None = None)#

This class is the async equivalent of RawCursor. The differences w.r.t. the sync counterpart are the same described in AsyncCursor.

New in version 3.2.

class psycopg.AsyncRawServerCursor(connection: AsyncConnection[Any], name: str, *, row_factory: AsyncRowFactory[Row] | None = None, scrollable: bool | None = None, withhold: bool = False)#

This class is the async equivalent of RawServerCursor. The differences w.r.t. the sync counterpart are the same described in AsyncServerCursor.

New in version 3.2.