Hide navigation sidebar
Hide table of contents sidebar
Back to top
Toggle table of contents sidebar

# 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 psycopg.Cursor(connection, *, row_factory=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()

    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, params=None, *, prepare=None, binary=None)

    Execute a query or command to the database.

    Return type:

      `Self`

    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, params_seq, *, returning=False)

    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`; the results will be available in the cursor’s state and can be read using `fetchone()` and similar methods. Each input parameter will produce a separate result set: use `nextset()` to read the results of the queries after the first one.

    The value of `rowcount` is set to the cumulated number of rows affected by queries; except when using `returning=True`, in which case it 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, params=None, *, writer=None)

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

    Return type:

      Copy

    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, params=None, *, binary=None)

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

    Return type:

      `Iterator`[`TypeVar`(`Row`, covariant=True)]

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

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

    Return the next record from the current recordset.

    Return `None` the recordset is finished.

    Return type:

      Optional[Row], with Row defined by `row_factory`

  fetchmany(size=0)

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

    Return all the remaining records from the current recordset.

    Return type:

      Sequence[Row], with Row defined by `row_factory`

  nextset()

    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.

    Return type:

      `Optional`[`bool`]

  scroll(value, mode='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: psycopg.pq.PGresult | None

    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, *, row_factory=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, params=None)

    Return the query and parameters merged.

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

    Return type:

      `str`

    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, name, *, row_factory=None, scrollable=None, withhold=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()

    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, params=None, *, binary=None, **kwargs)

    Open a cursor to execute a query to the database.

    Return type:

      `Self`

    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, params_seq, *, returning=True)

    Method not implemented for server-side cursors.

  fetchone()

    Return the next record from the current recordset.

    Return `None` the recordset is finished.

    Return type:

      Optional[Row], with Row defined by `row_factory`

  fetchmany(size=0)

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

    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, mode='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.

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

class psycopg.AsyncCursor(connection, *, row_factory=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()

    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, binary=None)
    Return type:

      `Self`

  async executemany(query, params_seq, *, returning=False)
  copy(statement, params=None, *, writer=None)
    Return type:

      AsyncCopy

    Note

    The method must be called with:

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

  async stream(query, params=None, *, binary=None)
    Return type:

      `AsyncIterator`[`TypeVar`(`Row`, covariant=True)]

    Note

    The method must be called with:

        async for record in cursor.stream(query):
            ...

  async fetchone()
    Return type:

      `Optional`[`TypeVar`(`Row`, covariant=True)]

  async fetchmany(size=0)
    Return type:

      `List`[`TypeVar`(`Row`, covariant=True)]

  async fetchall()
    Return type:

      `List`[`TypeVar`(`Row`, covariant=True)]

  async scroll(value, mode='relative')

  Note

  You can also use:

      async for record in cursor:
          ...

  to iterate on the async cursor results.

## The `AsyncClientCursor` class

class psycopg.AsyncClientCursor(connection, *, row_factory=None)

  This class is the `async` equivalent of the `ClientCursor`. The difference are the same shown in `AsyncCursor`.

  New in version 3.1.

## The `AsyncServerCursor` class

class psycopg.AsyncServerCursor(connection, name, *, row_factory=None, scrollable=None, withhold=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()

    Note

    You can close the cursor automatically using:

        async with conn.cursor("name") as cursor:
            ...

  async execute(query, params=None, *, binary=None, **kwargs)
    Return type:

      `Self`

  async executemany(query, params_seq, *, returning=True)
  async fetchone()
    Return type:

      `Optional`[`TypeVar`(`Row`, covariant=True)]

  async fetchmany(size=0)
    Return type:

      `List`[`TypeVar`(`Row`, covariant=True)]

  async fetchall()
    Return type:

      `List`[`TypeVar`(`Row`, covariant=True)]

    Note

    You can also iterate on the cursor using:

        async for record in cur:
            ...

  async scroll(value, mode='relative')