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

# 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(pgconn, row_factory=<function tuple_row>)

  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='', *, autocommit=False, prepare_threshold=5, row_factory=None, cursor_factory=None, context=None, **kwargs)

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

    Return type:

      `Self`

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

    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: RowFactory | None = None) → Cursor
  cursor(name: str, *, binary: bool = False, row_factory: RowFactory | None = None, scrollable: bool | None = 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`[`TypeVar`(`Row`, covariant=True)]]

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

    Default is `psycopg.Cursor`.

  server_cursor_factory: `Type`[`ServerCursor`[`TypeVar`(`Row`, covariant=True)]]

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

    Default is `psycopg.ServerCursor`.

  row_factory: `RowFactory`[`TypeVar`(`Row`, covariant=True)]

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

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

    Return type:

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

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

    Switch the connection into pipeline mode.

    Return type:

      `Iterator`[`Pipeline`]

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

    Commit any pending transaction to the database.

  rollback()

    Roll back to the start of any pending transaction.

  transaction(savepoint_name=None, force_rollback=False)

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

    Parameters:

      - savepoint_name (`Optional`[`str`]) – Name of the savepoint used to manage a nested transaction. If `None`, one will be chosen automatically.

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

  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.

  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.

  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.

  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.

    Default value: 100

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

  Methods you can use to do something cool

  cancel()

    Cancel the current operation on the connection.

  notifies()

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

    Return type:

      `Generator`[`Notify`, `None`, `None`]

    Notifies are received 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)

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

    Parameters:

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

    See Asynchronous notifications for details.

  remove_notify_handler(callback)

    Unregister a notification callable previously registered.

    Parameters:

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

  add_notice_handler(callback)

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

    Parameters:

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

    See Server messages for details.

  remove_notice_handler(callback)

    Unregister a notice message callable previously registered.

    Parameters:

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

  fileno()

    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.

    Return type:

      `int`

  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, gtrid, bqual)

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

    Return type:

      `Xid`

  tpc_begin(xid)

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

    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=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=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()
    Return type:

      `List`[`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(pgconn, row_factory=<function tuple_row>)

  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='', *, autocommit=False, prepare_threshold=5, context=None, row_factory=None, cursor_factory=None, **kwargs)
    Return type:

      `Self`

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

    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: RowFactory | None = None) → AsyncCursor
  cursor(name: str, *, binary: bool = False, row_factory: RowFactory | None = None, scrollable: bool | None = 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`[`TypeVar`(`Row`, covariant=True)]]

    Default is `psycopg.AsyncCursor`.

  server_cursor_factory: `Type`[`AsyncServerCursor`[`TypeVar`(`Row`, covariant=True)]]

    Default is `psycopg.AsyncServerCursor`.

  row_factory: `AsyncRowFactory`[`TypeVar`(`Row`, covariant=True)]
  async execute(query, params=None, *, prepare=None, binary=False)
    Return type:

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

  pipeline()

    Context manager to switch the connection into pipeline mode.

    Return type:

      `AsyncIterator`[`AsyncPipeline`]

    Note

    It must be called as:

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

  async commit()
  async rollback()
  transaction(savepoint_name=None, force_rollback=False)

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

    Return type:

      AsyncTransaction

    Note

    It must be called as:

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

  async notifies()
    Return type:

      `AsyncGenerator`[`Notify`, `None`]

  async set_autocommit(value)

    Async version of the `autocommit` setter.

  async set_isolation_level(value)

    Async version of the `isolation_level` setter.

  async set_read_only(value)

    Async version of the `read_only` setter.

  async set_deferrable(value)

    Async version of the `deferrable` setter.

  async tpc_prepare()
  async tpc_commit(xid=None)
  async tpc_rollback(xid=None)
  async tpc_recover()
    Return type:

      `List`[`Xid`]