Other top-level objects

Connection information

class psycopg.ConnectionInfo

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 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: OK and 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 session. See PQtransactionStatus().

The status can be IDLE (currently idle), ACTIVE (a command is in progress), INTRANS (idle, in a valid transaction block), or INERROR (idle, in a failed transaction block). 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.

pipeline_status

The current pipeline status of the client. See PQpipelineStatus().

backend_pid

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

vendor

A string representing the database vendor connected to.

Normally it is PostgreSQL; it may be different if connected to a different database.

New in version 3.1.

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. Starting from 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() → dict[str, str]

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 connect() or from environment variables. The password is never returned (you can read it using the password attribute).

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 from PostgreSQL 12 or newer. Raise NotSupportedError otherwise. You can use the has_hostaddr capability to check for support.

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: str) → str | None

Return a parameter setting of the connection.

Return None is the parameter is unknown.

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

See also

The PostgreSQL supported encodings.

Libpq capabilities information

class psycopg.Capabilities

An object to check if a feature is supported by the libpq available on the client.

An instance of this object is normally exposed by the module as the object psycopg.capabilities.

Every feature check is implemented by an has_SOMETHING() method. All the methods return a boolean value stating if the capability is supported, which can be used by a program to degrade gracefully:

if psycopg.capabilities.has_pipeline()
    with conn.pipeline():
        operations(conn)
else:
    logger.warning("slower")
    operations(conn)

If check is True, and the capability is not supported, raise a NotSupportedError instead of returning False, explaining why the feature is not supported. This allows to make a check at import time, crashing early and with a clear description of the problem.

>>> import psycopg
>>> psycopg.capabilities.has_pipeline(check=True)
Traceback (most recent call last):
  ...
psycopg.NotSupportedError: the feature 'Connection.pipeline()' is not available:
    the client libpq version (imported from system libraries) is 13.4; the
    feature requires libpq version 14.0 or newer

New in version 3.2.

has_encrypt_password(check: bool = False) → bool

Check if the PGconn.encrypt_password() method is implemented.

The feature requires libpq 10.0 and greater.

has_hostaddr(check: bool = False) → bool

Check if the ConnectionInfo.hostaddr attribute is implemented.

The feature requires libpq 12.0 and greater.

has_pipeline(check: bool = False) → bool

Check if the pipeline mode is supported.

The feature requires libpq 14.0 and greater.

has_set_trace_flags(check: bool = False) → bool

Check if the pq.PGconn.set_trace_flags() method is implemented.

The feature requires libpq 14.0 and greater.

has_cancel_safe(check: bool = False) → bool

Check if the Connection.cancel_safe() method is implemented.

The feature requires libpq 17.0 and greater.

Note

The cancel_safe() method is implemented anyway, but it will use the legacy PQcancel implementation.

has_stream_chunked(check: bool = False) → bool

Check if Cursor.stream() can handle a size parameter value greater than 1 to retrieve results by chunks.

The feature requires libpq 17.0 and greater.

has_send_close_prepared(check: bool = False) → bool

Check if the pq.PGconn.send_closed_prepared() method is implemented.

The feature requires libpq 17.0 and greater.

See also

Using prepared statements with PgBouncer

The description Column object

class psycopg.Column

An object describing a column of data from a database result, as described by the DBAPI, so it can also be unpacked as a 7-items tuple.

The object is returned by Cursor.description.

name

The name of the column.

type_code

The numeric OID of the column.

type_display

A pretty representation of the column type.

It is composed by the type name, followed by eventual modifiers and brackets to signify arrays, e.g. text, varchar(42), date[].

New in version 3.2.

display_size

The field size, for string types such as varchar(n).

internal_size

The internal field size for fixed-size types, None otherwise.

precision

The number of digits for fixed precision types.

scale

The number of digits after the decimal point if available.

Notifications

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.

Pipeline-related objects

See Pipeline mode support for details.

class psycopg.Pipeline(conn: Connection[Any])

Handler for connection in pipeline mode.

This objects is returned by Connection.pipeline().

sync() → None

Sync the pipeline, send any pending command and receive and process all available results.

classmethod is_supported() → bool

Return True if the psycopg libpq wrapper supports pipeline mode.

class psycopg.AsyncPipeline(conn: AsyncConnection[Any])

Handler for async connection in pipeline mode.

This objects is returned by AsyncConnection.pipeline().

async sync() → None

Transaction-related objects

See Transactions management for details about these objects.

class psycopg.IsolationLevel(value)

Enum representing the isolation level for a transaction.

The value is usually used with the Connection.isolation_level property.

Check the PostgreSQL documentation for a description of the effects of the different levels of transaction isolation.

READ_UNCOMMITTED = 1

READ_COMMITTED = 2

REPEATABLE_READ = 3

SERIALIZABLE = 4

class psycopg.Transaction

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

savepoint_name

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

connection

The connection the object is managing.

class psycopg.AsyncTransaction

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

connection

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

Two-Phase Commit related objects

class psycopg.Xid

A two-phase commit transaction identifier.

The object can also be unpacked as a 3-item tuple (format_id, gtrid, bqual).

See Two-Phase Commit protocol support for details.

format_id: int | None

Format Identifier of the two-phase transaction.

gtrid: str

Global Transaction Identifier of the two-phase transaction.

If the Xid doesn’t follow the XA standard, it will be the PostgreSQL ID of the transaction (in which case format_id and bqual will be None).

bqual: str | None

Branch Qualifier of the two-phase transaction.

prepared: dt.datetime | None = None

Timestamp at which the transaction was prepared for commit.

Only available on transactions recovered by tpc_recover().

owner: str | None = None

Named of the user that executed the transaction.

Only available on recovered transactions.

database: str | None = None

Named of the database in which the transaction was executed.

Only available on recovered transactions.