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 psycopg.Connection(pgconn, row_factory)

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: psycopg.rows.RowFactory[psycopg.rows.Row], **kwargs: Union[None, int, str]) Connection[Row]
classmethod connect(conninfo: str = '', *, autocommit: bool = 'False', **kwargs: Union[None, int, str]) Connection[TupleRow]

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 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 psycopg.connect().

See also

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: 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, scrollable: Optional[bool] = 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[psycopg.Cursor[psycopg.rows.Row]]

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

Default is psycopg.Cursor.

server_cursor_factory: Type[psycopg.ServerCursor[psycopg.rows.Row]]

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

Default is psycopg.ServerCursor.

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

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

Return type

Cursor[~Row]

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: rows.RowFactory[Row]

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

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.

The following three properties control the characteristics of new transactions. See Transaction characteristics for detils.

isolation_level: Optional[IsolationLevel]

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: Optional[bool]

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: Optional[bool]

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

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: ConnectionInfo

A ConnectionInfo attribute to inspect connection properties.

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

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

cancel()

Cancel the current operation on the connection.

notifies()

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

Return type

Iterator[Notify]

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)

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

Parameters

callback – a callable taking a Notify parameter.

remove_notify_handler(callback)

Unregister a notification callable previously registered.

See Asynchronous notifications for details.

add_notice_handler(callback)

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

Parameters

callback – a callable taking a Diagnostic object containing all the details about the notice.

remove_notice_handler(callback)

Unregister a notice message callable previously registered.

The AsyncConnection class

class psycopg.AsyncConnection(pgconn, row_factory)

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: psycopg.rows.RowFactory[psycopg.rows.Row], **kwargs: Union[None, int, str]) AsyncConnection[Row]
async classmethod connect(conninfo: str = '', *, autocommit: bool = 'False', **kwargs: Union[None, int, str]) AsyncConnection[TupleRow]
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[psycopg.AsyncCursor[psycopg.rows.Row]]

Default is psycopg.AsyncCursor.

server_cursor_factory: Type[psycopg.AsyncServerCursor[psycopg.rows.Row]]

Default is psycopg.AsyncServerCursor.

async execute(query, params=None, *, prepare=None)
Return type

AsyncCursor[~Row]

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

AsyncIterator[Notify]

async set_client_encoding(name)

Async version of the client_encoding setter.

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.

Connection support objects

class psycopg.Notify

An asynchronous notification received from the database.

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

channel: str

Alias for field number 0

payload: str

Alias for field number 1

pid: int

Alias for field number 2

class psycopg.ConnectionInfo(pgconn)

Allow access to information about the connection.

The object is usually returned by Connection.info.

status: pq.ConnStatus

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: pq.TransactionStatus

The current in-transaction status of the server. 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.

server_version: int

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 will be returned as 90305, version 10.2 as 100002.

backend_pid: int

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

error_message: str

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 or from environment variables. Don’t report the password (you can read it using the password attribute).

Return type

Dict[str, str]

timezone: datetime.tzinfo

The Python timezone info of the connection’s timezone.

host: str

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: str

The server IP address of the connection. See PQhostaddr.

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

port: int

The port of the active connection. See PQport.

dbname: str

The database name of the connection. See PQdb.

user: str

The user name of the connection. See PQuser.

password: str

The password of the connection. See PQpass.

options: str

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.

protocol_version: int

The frontend/backend protocol currently used. See PQprotocolVersion.

Objects involved in Transaction management

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(connection, savepoint_name=None, force_rollback=False)

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

savepoint_name: Optional[str]

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

connection: Connection[Any]

The connection the object is managing.

class psycopg.AsyncTransaction(connection, savepoint_name=None, force_rollback=False)

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

connection: AsyncConnection[Any]
exception psycopg.Rollback(transaction=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.