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.

A Connection can create several cursors, but only one at time can perform operations, so they are not the best way to achieve parallelism (you may want to operate with several connections instead). All the cursors on the same connection have a view of the same session, so they can see each other’s uncommitted data.

The Cursor class

class psycopg3.Cursor

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()

Close the current cursor and free associated resources.

Note

you can use with conn.cursor(): … to close the cursor automatically when the block is exited.

closed bool

True if the cursor is closed.

Methods to send commands

execute(query, params=None, *, prepare=None)Cursor

Execute a query or command to the database.

Parameters
  • query (str, bytes, or sql.Composable) – 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.

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.

executemany(query: Query, params_seq: Sequence[Args])

Execute the same command with a sequence of input data.

Parameters
  • query (str, bytes, or sql.Composable) – The query to execute

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

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().

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

copy(statement: Query)Copy

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

Parameters

statement (str, bytes, or sql.Composable) – The copy operation to execute

Note

it must be called as with cur.copy() as copy: ...

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

stream(query, params=None) → Iterable[Sequence[Any]]

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

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 TAIL and CockroachDB CHANGEFEED for instance.

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

The parameters are the same of execute().

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.

Type

pq.Format

TODO

Add execute(binary=True) too?

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 for record in cursor syntax will iterate on the records in the current recordset.

fetchone() → Optional[Row]

Return the next record from the current recordset.

Return None the recordset is finished.

fetchmany(size: int = 0) → Sequence[Row]

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

fetchall() → Sequence[Row]

Return all the remaining records from the current recordset.

nextset() → Optional[bool]

Move 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')

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[psycopg3.pq.PGresult]

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

Information about the data

description: Optional[List[Column]]

A list of objects describing each column of the current queryset.

None if the last operation didn’t return a queryset.

rowcount int

Number of records affected by the precedent operation.

rownumber int

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

None if there is no result to fetch.

query Optional[bytes]

The last query sent to the server, if available.

The query will be in PostgreSQL format (with $1, $2… parameters), the parameters will not be merged to the query: see params.

params Optional[List[Optional[bytes]]]

The last set of parameters sent to the server, if available.

The parameters are adapted to PostgreSQL format.

The ServerCursor class

class psycopg3.ServerCursor

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 str

The name of the cursor.

close()

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 with conn.cursor(): ... pattern is especially useful so that the cursor is closed at the end of the block.

execute(query, params=None, *, scrollable=None, hold=False)ServerCursor

Open a cursor to execute a query to the database.

Parameters
  • query (str, bytes, or sql.Composable) – The query to execute.

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

  • scrollable (Optional[bool]) – if True make the cursor scrollable, if False not. if None leave the choice to the server.

  • hold (bool) – if True allow the cursor to be used after the transaction creating it has committed.

Create a server cursor with given name and the query in argument. If using DECLARE is not appropriate 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.

See also

The PostgreSQL DECLARE statement documetation describe in details all the parameters.

executemany(query: Query, params_seq: Sequence[Args])

Method not implemented for server-side cursors.

fetchone() → Optional[Row]
fetchmany(size: int = 0) → Sequence[Row]
fetchall() → Sequence[Row]

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')

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 use scrollable=True in execute().

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 AsyncCursor class

class psycopg3.AsyncCursor

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()

Note

You can use async with conn.cursor(): ... to close the cursor automatically when the block is exited.

async execute(query, params=None, *, prepare=None)AsyncCursor
async executemany(query: Query, params_seq: Sequence[Args])
copy(statement: Query)AsyncCopy

Note

It must be called as async with cur.copy() as copy: ...

stream(query, params=None) → AsyncIterable[Sequence[Any]]

Note

It must be called as async for record in cur.stream(query): ...

async fetchone() → Optional[Row]
async fetchmany(size: int = 0) → List[Row]
async fetchall() → List[Row]
async scroll(value: int, mode: str = 'relative')

Note

You can also use async for record in cursor: ... to iterate on the async cursor results.

The AsyncServerCursor class

class psycopg3.AsyncServerCursor

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()

Note

You can close the cursor automatically using async with conn.cursor(name): ...

async execute(query, params=None, *, scrollable=None, hold=False)AsyncServerCursor
async executemany(query: Query, params_seq: Sequence[Args])
async fetchone() → Optional[Row]
async fetchmany(size: int = 0) → Sequence[Row]
async fetchall() → Sequence[Row]

Note

You can also iterate on the cursor using async for record in cur: ....

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

The description Column object

class psycopg3.Column

An object describing a column of data from a database result, as described by the DBAPI, so it can also be unpacked as a 7-items tuple.

The object is returned by Cursor.description.

property name

The name of the column.

property type_code

The numeric OID of the column.

property display_size

The field size, for varchar(n), None otherwise.

property internal_size

The interal field size for fixed-size types, None otherwise.

property precision

The number of digits for fixed precision types.

property scale

The number of digits after the decimal point if available.

TODO: probably better than precision for datetime objects? review.