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 Transaction management for the details.

The Connection class

class psycopg3.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, **kwargs: Any)psycopg3.Connection

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

TODO: connection_timeout to be implemented.

Connection parameters can be passed either as a conninfo string (a postgresql:// url or a list of key=value pairs) or as keywords. Keyword parameters override the ones specified in the connection string.

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

See also

close()

Close the database connection.

Note

You can use with connect(): … to close the connection automatically when the block is exited.

closed bool

True if the connection is closed.

cursor(name: str = '', format: psycopg3.pq.Format = <Format.TEXT: 0>)Cursor

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

Note

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

Transaction management methods

For details see Transaction management.

commit()

Commit any pending transaction to the database.

rollback()

Roll back to the start of any pending transaction.

transaction(savepoint_name: Optional[str] = None, force_rollback: bool = False)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).

Note

It must be called as with conn.transaction() as tx: ...

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

autocommit bool

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.

Checking and configuring the connection state

client_encoding str

The Python codec name of the connection’s client encoding.

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

info

TODO

Methods you can use to do something cool

notifies() → Iterator[psycopg3.Notify]

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

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

add_notify_handler(callback: Callable[[psycopg3.Notify], None])

Register a callable to be invoked whenever a notification is received.

remove_notify_handler(callback: Callable[[psycopg3.Notify], None])

Unregister a notification callable previously registered.

See Asynchronous notifications for details.

cancel()

Cancel the current operation on the connection.

add_notice_handler(callback: Callable[[psycopg3.errors.Diagnostic], None])

Register a callable to be invoked when a notice message is received.

The argument of the callback is a Diagnostic object containing all the details about the notice.

remove_notice_handler(callback: Callable[[psycopg3.errors.Diagnostic], None])

Unregister a notice message callable previously registered.

The AsyncConnection class

class psycopg3.AsyncConnection

Asynchronous 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, **kwargs: Any)psycopg3.AsyncConnection
async close()

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 and cursors for details.

async cursor(name: str = '', format: psycopg3.pq.Format = <Format.TEXT: 0>)AsyncCursor

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

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 commit()
async rollback()
transaction(savepoint_name: Optional[str] = None, force_rollback: bool = False)AsyncTransaction

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

Note

It must be called as async with conn.transaction() as tx: ....

notifies() → AsyncIterator[psycopg3.Notify]
async set_client_encoding(name: str)

Async version of the client_encoding setter.

async set_autocommit(value: bool)

Async version of the autocommit setter.

Connection support objects

class psycopg3.Notify

An asynchronous notification received from the database.

The object is usually returned by Connection.notifies().

channel: str

The name of the channel on which the notification was received.

payload: str

The message attached to the notification.

pid: int

The PID of the backend process which sent the notification.

Objects involved in Transaction management

class psycopg3.Transaction

Returned by Connection.transaction() to handle a transaction block.

property savepoint_name

The name of the savepoint; None if handling the main transaction.

connection Connection

The connection the object is managing.

class psycopg3.AsyncTransaction

Returned by AsyncConnection.transaction() to handle a transaction block.

exception psycopg3.Rollback(transaction: Optional[Union[Transaction, AsyncTransaction]] = None)

Exit the current Transaction context immediately and rollback any changes made within this context.

If a transaction context is specified in the constructor, rollback enclosing transactions contexts up to and including the one specified.

It can be used as

  • raise Rollback: roll back the operation that happened in the current transaction block and continue the program after the block.

  • raise Rollback(): same effect as above

  • raise Rollback(tx): roll back any operation that happened in the Transaction tx (returned by a statement such as with conn.transaction() as tx: and all the blocks nested within. The program will continue after the tx block.