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=None)#

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, row_factory=None, context=None, **kwargs)#

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

Return type:

Connection[Any]

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: ~psycopg.rows.tuple_row()). See Row factories for details.

More specialized use:

Parameters:: context – A context to copy the initial adapters configuration from. It might be an ~psycopg.adapt.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

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, 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.
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, ~Cursor.execute() the query requested, and returns it.

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

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 ~AsyncConnection.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 ~pq.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)#

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

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

The !AsyncConnection class#

class psycopg.AsyncConnection(pgconn, row_factory=None)#

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, context=None, row_factory=None, **kwargs)#

Return type:: AsyncConnection[Any]

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, 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[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)]

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 ~Connection.autocommit setter.

async set_isolation_level(value)#: Async version of the ~Connection.isolation_level setter.

async set_read_only(value)#: Async version of the ~Connection.read_only setter.

async set_deferrable(value)#: Async version of the ~Connection.deferrable setter.

Connection support objects#

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

class psycopg.ConnectionInfo(pgconn)#

Allow access to information about the connection.

The object is usually returned by Connection.info.

dsn#

Return the connection string to connect to the database.

The string contains all the parameters set to a non-default value, which might come either from the connection string and parameters passed to ~Connection.connect() or from environment variables. The password is never returned (you can read it using the password attribute).

Note

The get_parameters() method returns the same information as a dict.

status#

The status of the connection. See PQstatus().

The status can be one of a number of values. However, only two of these are seen outside of an asynchronous connection procedure: ~pq.ConnStatus.OK and ~pq.ConnStatus.BAD. A good connection to the database has the status !OK. Ordinarily, an !OK status will remain so until Connection.close(), but a communications failure might result in the status changing to !BAD prematurely.

transaction_status#

The current in-transaction status of the server. See PQtransactionStatus().

The status can be ~pq.TransactionStatus.IDLE (currently idle), ~pq.TransactionStatus.ACTIVE (a command is in progress), ~pq.TransactionStatus.INTRANS (idle, in a valid transaction block), or ~pq.TransactionStatus.INERROR (idle, in a failed transaction block). ~pq.TransactionStatus.UNKNOWN is reported if the connection is bad. !ACTIVE is reported only when a query has been sent to the server and not yet completed.

backend_pid#: The process ID (PID) of the backend process handling this connection. See PQbackendPID().

server_version#

An integer representing the server version. See PQserverVersion().

The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. After PostgreSQL 10 the minor version was dropped, so the second group of digits is always 00. For example, version 9.3.5 is returned as 90305, version 10.2 as 100002.

error_message#: The error message most recently generated by an operation on the connection. See PQerrorMessage().

get_parameters()#

Return the connection parameters values.

Return all the parameters set to a non-default value, which might come either from the connection string and parameters passed to ~Connection.connect() or from environment variables. The password is never returned (you can read it using the password attribute).

Return type:: Dict[str, str]

Note

The dsn attribute returns the same information in the form as a string.

timezone#

The Python timezone info of the connection’s timezone.

>>> conn.info.timezone
zoneinfo.ZoneInfo(key='Europe/Rome')

host#

The server host name of the active connection. See PQhost().

This can be a host name, an IP address, or a directory path if the connection is via Unix socket. (The path case can be distinguished because it will always be an absolute path, beginning with /.)

hostaddr#

The server IP address of the connection. See PQhostaddr().

Only available if the libpq used is at least from PostgreSQL 12. Raise ~psycopg.NotSupportedError otherwise.

port#: The port of the active connection. See PQport().

dbname#: The database name of the connection. See PQdb().

user#: The user name of the connection. See PQuser().

password#: The password of the connection. See PQpass().

options#: The command-line options passed in the connection request. See PQoptions.

parameter_status(param_name)#

Return a parameter setting of the connection.

Return None is the parameter is unknown.

Return type:: Optional[str]

Example of parameters are server_version, standard_conforming_strings… See PQparameterStatus() for all the available parameters.

encoding#

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

The value returned is always normalized to the Python codec ~codecs.CodecInfo.name:

conn.execute("SET client_encoding TO LATIN9")
conn.info.encoding
'iso8859-15'

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.

Connection classes#

The Connection class#

The !AsyncConnection class#

Connection support objects#

The `Connection` class#