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.

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 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: Query, params: Optional[Args] = 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

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.

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[Sequence[Any]]

Return the next record from the current recordset.

Return None the recordset is finished.

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

Return the next size records from the current recordset.

size default to self.arraysize if not specified.

fetchall() → List[Sequence[Any]]

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.

pgresult

The PGresult exposed by the cursor.

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.

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 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 to close the cursor automatically when the block is exited, but be careful about the async quirkness: see with async connections and cursors for details.

async execute(query: Query, params: Optional[Args] = 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: ...

async fetchone() → Optional[Sequence[Any]]
async fetchmany(size: int = 0) → List[Sequence[Any]]
async fetchall() → List[Sequence[Any]]

Note

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

Cursor support objects

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.

class psycopg3.Copy

Manage a COPY operation.

The object is normally returned by with Cursor.copy().

See Using COPY TO and COPY FROM for details.

read()bytes

Read a row of data after a COPY TO operation.

Return an empty bytes string when the data is finished.

Instead of using read() you can even iterate on the object to read its data row by row, using for row in copy: ....

write(buffer: Union[str, bytes])

Write a block of data after a COPY FROM operation.

If the COPY is in binary format buffer must be bytes. In text mode it can be either bytes or str.

write_row(row: Sequence[Any])

Write a record after a COPY FROM operation.

The data in the tuple will be converted as configured on the cursor; see Data adaptation configuration for details.

class psycopg3.AsyncCopy

Manage an asynchronous COPY operation.

The object is normally returned by async with AsyncCursor.copy(). Its methods are the same of the Copy object but offering an asyncio interface (await, async for, async with).

async read()bytes

Instead of using read() you can even iterate on the object to read its data row by row, using async for row in copy: ....

async write(buffer: Union[str, bytes])
async write_row(row: Sequence[Any])