Connection classes#

The Connection and AsyncConnection classes are the main wrappers for a PostgreSQL database session. You can imagine them similar to a psql session.

One of the differences compared to psql is that a Connection usually handles a transaction automatically: other sessions will not be able to see the changes until you have committed them, more or less explicitly. Take a look to Transactions management for the details.

The `Connection` class#

class psycopg.Connection#

Wrapper for a connection to the database.

This class implements a DBAPI-compliant interface. It is what you want to use if you write a “classic”, blocking program (eventually using threads or Eventlet/gevent for concurrency). If your program uses asyncio you might want to use AsyncConnection instead.

Connections behave as context managers: on block exit, the current transaction will be committed (or rolled back, in case of exception) and the connection will be closed.

classmethod connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: int | None = 5, context: Optional[AdaptContext] = None, row_factory: Optional[RowFactory[Row]] = None, cursor_factory: Cursor[+Row]]] = None, **kwargs: Optional[Union[str, int]]) → Self#

Connect to a database server and return a new Connection instance.

Parameters:

conninfo – The connection string (a postgresql:// url or a list of key=value pairs) to specify where and how to connect.
kwargs – Further parameters specifying the connection string. They override the ones specified in conninfo.
autocommit – If True don’t start transactions automatically. See Transactions management for details.
row_factory – The row factory specifying what type of records to create fetching data (default: tuple_row()). See Row factories for details.
cursor_factory – Initial value for the cursor_factory attribute of the connection (new in Psycopg 3.1).
prepare_threshold – Initial value for the prepare_threshold attribute of the connection (new in Psycopg 3.1).

More specialized use:

Parameters:: context – A context to copy the initial adapters configuration from. It might be an AdaptersMap with customized loaders and dumpers, used as a template to create several connections. See Data adaptation configuration for further details.

This method is also aliased as psycopg.connect().

See also

the list of the accepted connection parameters
the environment variables affecting connection

Changed in version 3.1: added prepare_threshold and cursor_factory parameters.

close() → None#

Close the database connection.

Note

You can use:

with psycopg.connect() as conn:
    ...

to close the connection automatically when the block is exited. See Connection context.

closed#: True if the connection is closed.

broken#

True if the connection was interrupted.

A broken connection is always closed, but wasn’t closed in a clean way, such as using close() or a with block.

cursor(*, binary: bool = False, row_factory: Optional[RowFactory] = None) → Cursor#

cursor(name: str, *, binary: bool = False, row_factory: Optional[RowFactory] = None, scrollable: Optional[bool] = None, withhold: bool = False) → ServerCursor

Return a new cursor to send commands and queries to the connection.

Parameters:

name – If not specified create a client-side cursor, if specified create a server-side cursor. See Cursor types for details.
binary – If True return binary values from the database. All the types returned by the query must have a binary loader. See Binary parameters and results for details.
row_factory – If specified override the row_factory set on the connection. See Row factories for details.
scrollable – Specify the scrollable property of the server-side cursor created.
withhold – Specify the withhold property of the server-side cursor created.

Returns:

A cursor of the class specified by cursor_factory (or server_cursor_factory if name is specified).

Note

You can use:

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

to close the cursor automatically when the block is exited.

cursor_factory: type[Cursor[Row]]#

The type, or factory function, returned by cursor() and execute().

Default is psycopg.Cursor.

server_cursor_factory: type[ServerCursor[Row]]#

The type, or factory function, returned by cursor() when a name is specified.

Default is psycopg.ServerCursor.

row_factory: RowFactory[Row]#

The row factory defining the type of rows returned by fetchone() and the other cursor fetch methods.

The default is tuple_row, which means that the fetch methods will return simple tuples.

See also

See Row factories for details about defining the objects returned by cursors.

execute(query: Query, params: Params | None = None, *, prepare: bool | None = None, binary: bool = False) → Cursor[Row]#

Execute a query and return a cursor to read its results.

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 – If True the cursor will return binary values from the database. All the types returned by the query must have a binary loader. See Binary parameters and results for details.

The method simply creates a Cursor instance, execute() the query requested, and returns it.

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

pipeline() → Iterator[Pipeline]#

Context manager to switch the connection into pipeline mode.

The method is a context manager: you should call it using:

with conn.pipeline() as p:
    ...

At the end of the block, a synchronization point is established and the connection returns in normal mode.

You can call the method recursively from within a pipeline block. Innermost blocks will establish a synchronization point on exit, but pipeline mode will be kept until the outermost block exits.

See Pipeline mode support for details.

New in version 3.1.

Transaction management methods

For details see Transactions management.

commit() → None#: Commit any pending transaction to the database.

rollback() → None#: Roll back to the start of any pending transaction.

transaction(savepoint_name: str | None = None, force_rollback: bool = False) → Iterator[Transaction]#

Start a context block with a new transaction or nested transaction.

Parameters:

savepoint_name – Name of the savepoint used to manage a nested transaction. If None, one will be chosen automatically.
force_rollback – Roll back the transaction at the end of the block even if there were no error (e.g. to try a no-op process).

Return type:

Transaction

Note

The method must be called with a syntax such as:

with conn.transaction():
    ...

with conn.transaction() as tx:
    ...

The latter is useful if you need to interact with the Transaction object. See Transaction contexts for details.

Inside a transaction block it will not be possible to call commit() or rollback().

autocommit#

The autocommit state of the connection.

The property is writable for sync connections, read-only for async ones: you should call await set_autocommit (value) instead.

set_autocommit(value: bool) → None#: Method version of the autocommit setter.

New in version 3.2.

The following three properties control the characteristics of new transactions. See Transaction characteristics for details.

isolation_level#

The isolation level of the new transactions started on the connection.

None means use the default set in the default_transaction_isolation configuration parameter of the server.

set_isolation_level(value: psycopg.IsolationLevel | None) → None#: Method version of the isolation_level setter.

New in version 3.2.

read_only#

The read-only state of the new transactions started on the connection.

None means use the default set in the default_transaction_read_only configuration parameter of the server.

set_read_only(value: bool | None) → None#: Method version of the read_only setter.

New in version 3.2.

deferrable#

The deferrable state of the new transactions started on the connection.

None means use the default set in the default_transaction_deferrable configuration parameter of the server.

set_deferrable(value: bool | None) → None#: Method version of the deferrable setter.

New in version 3.2.

Checking and configuring the connection state

pgconn: psycopg.pq.PGconn#

The PGconn libpq connection wrapper underlying the Connection.

It can be used to send low level commands to PostgreSQL and access features not currently wrapped by Psycopg.

info#: A ConnectionInfo attribute to inspect connection properties.

prepare_threshold#

Number of times a query is executed before it is prepared.

If it is set to 0, every query is prepared the first time it is executed.
If it is set to None, prepared statements are disabled on the connection.

Default value: 5

See Prepared statements for details.

prepared_max#

Maximum number of prepared statements on the connection.

None means no max number of prepared statements. The default value is 100.

If more queries need to be prepared, old ones are deallocated.

Specifying None can be useful for middleware that don’t support deallocation; see prepared statements notes.

Changed in version 3.2: Added support for the None value.

Methods you can use to do something cool

cancel_safe(*, timeout: float = 30.0) → None#

Cancel the current operation on the connection.

Parameters:: timeout – raise a CancellationTimeout if the cancellation request does not succeed within timeout seconds.

Note that a successful cancel attempt on the client is not a guarantee that the server will successfully manage to cancel the operation.

This is a non-blocking version of cancel() which leverages a more secure and improved cancellation feature of the libpq, which is only available from version 17.

If the underlying libpq is older than version 17, the method will fall back to using the same implementation of cancel().

Note

You can use the has_cancel_safe capability to check if cancel_safe() will not fall back on the legacy libpq functions.

Warning

The timeout parameter has no effect for libpq older than version 17.

Warning

This method shouldn’t be used as a signal handler. Please use cancel() instead.

New in version 3.2.

cancel() → None#

Cancel the current operation on the connection.

Warning

The cancel() method is implemented using the PQcancel function, which is deprecated since PostgreSQL 17, and has a few shortcomings:

it is blocking even on async connections,
it might use an insecure connection even if the original connection was secure.

Therefore you should use the cancel_safe() method whenever possible.

Note

Unlike cancel_safe(), it is safe to call this method as a signal handler. This is pretty much the only case in which you might want to use this function.

notifies(*, timeout: Optional[float] = None, stop_after: Optional[int] = None) → Generator[Notify, None, None]#

Yield Notify objects as soon as they are received from the database.

Parameters:

timeout – maximum amount of time to wait for notifications. None means no timeout.
stop_after – stop after receiving this number of notifications. You might actually receive more than this number if more than one notifications arrives in the same packet.

Notifies are received after using LISTEN in a connection, when any sessions in the database generates a NOTIFY on one of the listened channels.

Changed in version 3.2: Added timeout and stop_after parameters.

add_notify_handler(callback: Callable[[Notify], None]) → None#

Parameters:: callback (Callable[[Notify], None]) – the callback to call upon notification received.

See Asynchronous notifications for details.

remove_notify_handler(callback: Callable[[Notify], None]) → None#

Unregister a notification callable previously registered.

Parameters:: callback (Callable[[Notify], None]) – the callback to remove.

add_notice_handler(callback: Callable[[Diagnostic], None]) → None#

Parameters:: callback (Callable[[Diagnostic], None]) – the callback to call upon message received.

See Server messages for details.

remove_notice_handler(callback: Callable[[Diagnostic], None]) → None#

Unregister a notice message callable previously registered.

Parameters:: callback (Callable[[Diagnostic], None]) – the callback to remove.

fileno() → int#

Return the file descriptor of the connection.

This function allows to use the connection as file-like object in functions waiting for readiness, such as the ones defined in the selectors module.

Two-Phase Commit support methods

New in version 3.1.

See also

Two-Phase Commit protocol support for an introductory explanation of these methods.

xid(format_id: int, gtrid: str, bqual: str) → Xid#

Returns a Xid to pass to the tpc_*() methods of this connection.

The argument types and constraints are explained in Two-Phase Commit protocol support.

The values passed to the method will be available on the returned object as the members format_id, gtrid, bqual.

tpc_begin(xid: psycopg.Xid | str) → None#

Begin a TPC transaction with the given transaction ID xid.

Parameters:: xid (Xid or str) – The id of the transaction

This method should be called outside of a transaction (i.e. nothing may have executed since the last commit() or rollback() and transaction_status is IDLE).

Furthermore, it is an error to call commit() or rollback() within the TPC transaction: in this case a ProgrammingError is raised.

The xid may be either an object returned by the xid() method or a plain string: the latter allows to create a transaction using the provided string as PostgreSQL transaction id. See also tpc_recover().

tpc_prepare() → None#

Perform the first phase of a transaction started with tpc_begin().

A ProgrammingError is raised if this method is used outside of a TPC transaction.

After calling tpc_prepare(), no statements can be executed until tpc_commit() or tpc_rollback() will be called.

See also

The PREPARE TRANSACTION PostgreSQL command.

tpc_commit(xid: Optional[Union[Xid, str]] = None) → None#

Commit a prepared two-phase transaction.

Parameters:: xid (Xid or str) – The id of the transaction

When called with no arguments, tpc_commit() commits a TPC transaction previously prepared with tpc_prepare().

If tpc_commit() is called prior to tpc_prepare(), a single phase commit is performed. A transaction manager may choose to do this if only a single resource is participating in the global transaction.

When called with a transaction ID xid, the database commits the given transaction. If an invalid transaction ID is provided, a ProgrammingError will be raised. This form should be called outside of a transaction, and is intended for use in recovery.

On return, the TPC transaction is ended.

See also

The COMMIT PREPARED PostgreSQL command.

tpc_rollback(xid: Optional[Union[Xid, str]] = None) → None#

Roll back a prepared two-phase transaction.

Parameters:: xid (Xid or str) – The id of the transaction

When called with no arguments, tpc_rollback() rolls back a TPC transaction. It may be called before or after tpc_prepare().

When called with a transaction ID xid, it rolls back the given transaction. If an invalid transaction ID is provided, a ProgrammingError is raised. This form should be called outside of a transaction, and is intended for use in recovery.

On return, the TPC transaction is ended.

See also

The ROLLBACK PREPARED PostgreSQL command.

tpc_recover() → list[psycopg.Xid]#

Returns a list of Xid representing pending transactions, suitable for use with tpc_commit() or tpc_rollback().

If a transaction was not initiated by Psycopg, the returned Xids will have attributes format_id and bqual set to None and the gtrid set to the PostgreSQL transaction ID: such Xids are still usable for recovery. Psycopg uses the same algorithm of the PostgreSQL JDBC driver to encode a XA triple in a string, so transactions initiated by a program using such driver should be unpacked correctly.

Xids returned by tpc_recover() also have extra attributes prepared, owner, database populated with the values read from the server.

See also

the pg_prepared_xacts system view.

The `AsyncConnection` class#

class psycopg.AsyncConnection#

Wrapper for a connection to the database.

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 Connection class.

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

async classmethod connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: int | None = 5, context: Optional[AdaptContext] = None, row_factory: Optional[AsyncRowFactory[Row]] = None, cursor_factory: AsyncCursor[+Row]]] = None, **kwargs: Optional[Union[str, int]]) → Self#: Connect to a database server and return a new AsyncConnection instance.

Changed in version 3.1: Automatically resolve domain names asynchronously. In previous versions, name resolution blocks, unless the hostaddr parameter is specified, or the resolve_hostaddr_async() function is used.

async close() → None#: Close the database connection.

Note

You can use async with to close the connection automatically when the block is exited, but be careful about the async quirkness: see with async connections for details.

cursor(*, binary: bool = False, row_factory: Optional[RowFactory] = None) → AsyncCursor#

cursor(name: str, *, binary: bool = False, row_factory: Optional[RowFactory] = None, scrollable: Optional[bool] = None, withhold: bool = False) → AsyncServerCursor

Note

You can use:

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

to close the cursor automatically when the block is exited.

cursor_factory: type[AsyncCursor[Row]]#: Default is psycopg.AsyncCursor.

server_cursor_factory: type[AsyncServerCursor[Row]]#: Default is psycopg.AsyncServerCursor.

row_factory: AsyncRowFactory[Row]#

async execute(query: Query, params: Params | None = None, *, prepare: bool | None = None, binary: bool = False) → AsyncCursor[Row]#: Execute a query and return a cursor to read its results.

pipeline() → AsyncIterator[AsyncPipeline]#

Context manager to switch the connection into pipeline mode.

Note

It must be called as:

async with conn.pipeline() as p:
    ...

async commit() → None#: Commit any pending transaction to the database.

async rollback() → None#: Roll back to the start of any pending transaction.

transaction(savepoint_name: str | None = None, force_rollback: bool = False) → AsyncIterator[AsyncTransaction]#

Start a context block with a new transaction or nested transaction.

Parameters:

savepoint_name – Name of the savepoint used to manage a nested transaction. If None, one will be chosen automatically.
force_rollback – Roll back the transaction at the end of the block even if there were no error (e.g. to try a no-op process).

Return type:

AsyncTransaction

Note

It must be called as:

async with conn.transaction() as tx:
    ...

async cancel_safe(*, timeout: float = 30.0) → None#

Cancel the current operation on the connection.

Parameters:: timeout – raise a CancellationTimeout if the cancellation request does not succeed within timeout seconds.

Note that a successful cancel attempt on the client is not a guarantee that the server will successfully manage to cancel the operation.

This is a non-blocking version of cancel() which leverages a more secure and improved cancellation feature of the libpq, which is only available from version 17.

If the underlying libpq is older than version 17, the method will fall back to using the same implementation of cancel().

New in version 3.2.

async notifies(*, timeout: Optional[float] = None, stop_after: Optional[int] = None) → AsyncGenerator[Notify, None]#

Yield Notify objects as soon as they are received from the database.

Parameters:

timeout – maximum amount of time to wait for notifications. None means no timeout.
stop_after – stop after receiving this number of notifications. You might actually receive more than this number if more than one notifications arrives in the same packet.

Changed in version 3.2: Added timeout and stop_after parameters.

async set_autocommit(value: bool) → None#: Method version of the autocommit setter.

async set_isolation_level(value: psycopg.IsolationLevel | None) → None#: Method version of the isolation_level setter.

async set_read_only(value: bool | None) → None#: Method version of the read_only setter.

async set_deferrable(value: bool | None) → None#: Method version of the deferrable setter.

async tpc_prepare() → None#: Perform the first phase of a transaction started with tpc_begin().

async tpc_commit(xid: Optional[Union[Xid, str]] = None) → None#: Commit a prepared two-phase transaction.

async tpc_rollback(xid: Optional[Union[Xid, str]] = None) → None#: Roll back a prepared two-phase transaction.

async tpc_recover() → list[psycopg.Xid]#

Connection classes#

The Connection class#

The AsyncConnection class#

The `Connection` class#

The `AsyncConnection` class#