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, row_factory: psycopg3.proto.RowFactory = <function tuple_row>, **kwargs: Any)psycopg3.Connection

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

TODO: connection_timeout to be implemented.

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

  • row_factory – The row factory specifying what type of records to create fetching data (default: tuple_row()). See Row factories for details.

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.

broken bool

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

Note

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

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

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

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.

The cursor is what returned calling cursor() without parameters. The parameters are passed to its execute() and the cursor is returned.

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

row_factory RowFactory

Writable attribute to control how result rows are formed. See Row factories for details.

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.

The value returned is always normalized to the Python codec name:

conn.client_encoding = 'latin9'
conn.client_encoding
'iso8859-15'

and it reflects the current connection property, even if it is set outside Python:

conn.execute("SET client_encoding TO LATIN1")
conn.client_encoding
'iso8859-1'

A few PostgreSQL encodings are not available in Python and cannot be selected (currently EUC_TW, MULE_INTERNAL). The PostgreSQL SQL_ASCII encoding has the special meaning of “no encoding”: see Strings adaptation for details.

info

TODO

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.

prepare_threshold Optional[int]

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

  • If it is set to 0, every query is prepared the first time 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 int

Maximum number of prepared statements on the connection.

Default value: 100

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

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, row_factory: psycopg3.proto.RowFactory = <function tuple_row>, **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 for details.

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

Note

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

async execute(query, params=None, prepare=None)AsyncCursor
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.