The connection class

class connection

Handles the connection to a PostgreSQL database instance. It encapsulates a database session.

Connections are created using the factory function connect().

Connections are thread safe and can be shared among many thread. See Thread safety for details.

cursor([name][, cursor_factory])

Return a new cursor object using the connection.

If name is specified, the returned cursor will be a server side (or named) cursor. Otherwise the cursor will be client side. See Server side cursors for further details.

The cursor_factory argument can be used to create non-standard cursors. The class returned should be a subclass of psycopg2.extensions.cursor. See Connection and cursor factories for details.

DB API extension

The name and cursor_factory parameters are Psycopg extensions to the DB API 2.0.

commit()

Commit any pending transaction to the database. Psycopg can be set to perform automatic commits at each operation, see set_isolation_level().

rollback()

Roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

close()

Close the connection now (rather than whenever __del__() is called). The connection will be unusable from this point forward; an InterfaceError will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. Note that closing a connection without committing the changes first will cause an implicit rollback to be performed (unless a different isolation level has been selected: see set_isolation_level()).

Excetptions as connection class attributes

The connection also exposes as attributes the same exceptions available in the psycopg2 module. See Exceptions.

DB API extension

The above methods are the only ones defined by the DB API 2.0 protocol. The Psycopg connection objects exports the following additional methods and attributes.

closed

Read-only attribute reporting whether the database connection is open (0) or closed (1).

reset()

Reset the connection to the default.

The method rolls back an eventual pending transaction and executes the PostgreSQL RESET and SET SESSION AUTHORIZATION to revert the session to the default values.

New in version 2.0.12.

dsn

Read-only string containing the connection string used by the connection.

isolation_level

set_isolation_level(level)

Read or set the transaction isolation level for the current session. The level defines the different phenomena that can happen in the database between concurrent transactions.

The value set or read is an integer: symbolic constants are defined in the module psycopg2.extensions: see Isolation level constants for the available values.

The default level is READ COMMITTED: at this level a transaction is automatically started the first time a database command is executed. If you want an autocommit mode, switch to ISOLATION_LEVEL_AUTOCOMMIT before executing any command:

>>> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

See also Transactions control.

encoding

set_client_encoding(enc)

Read or set the client encoding for the current session. The default is the encoding defined by the database. It should be one of the characters set supported by PostgreSQL

notices

A list containing all the database messages sent to the client during the session.

>>> cur.execute("CREATE TABLE foo (id serial PRIMARY KEY);")
>>> pprint(conn.notices)
['NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"\n',
 'NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"\n']

To avoid a leak in case excessive notices are generated, only the last 50 messages are kept.

You can configure what messages to receive using PostgreSQL logging configuration parameters such as log_statement, client_min_messages, log_min_duration_statement etc.

notifies

List containing asynchronous notifications received by the session.

Received notifications have the form of a 2 items tuple (pid,name), where pid is the PID of the backend that sent the notification and name is the signal name specified in the NOTIFY command.

For other details see Asynchronous notifications.

get_backend_pid()

Returns the process ID (PID) of the backend server process handling this connection.

Note that the PID belongs to a process executing on the database server host, not the local host!

See also

libpq docs for PQbackendPID() for details.

New in version 2.0.8.

get_parameter_status(parameter)

Look up a current parameter setting of the server.

Potential values for parameter are: server_version, server_encoding, client_encoding, is_superuser, session_authorization, DateStyle, TimeZone, integer_datetimes, and standard_conforming_strings.

If server did not report requested parameter, return None.

See also

libpq docs for PQparameterStatus() for details.

New in version 2.0.12.

get_transaction_status()

Return the current session transaction status as an integer. Symbolic constants for the values are defined in the module psycopg2.extensions: see Transaction status constants for the available values.

See also

libpq docs for PQtransactionStatus() for details.

protocol_version

A read-only integer representing frontend/backend protocol being used. It can be 2 or 3.

See also

libpq docs for PQprotocolVersion() for details.

New in version 2.0.12.

server_version

A read-only integer representing the backend version.

The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105.

See also

libpq docs for PQserverVersion() for details.

New in version 2.0.12.

status

A read-only integer representing the status of the connection. Symbolic constants for the values are defined in the module psycopg2.extensions: see Connection status constants for the available values.

lobject([oid[, mode[, new_oid[, new_file[, lobject_factory]]]]])

Return a new database large object. See Access to PostgreSQL large objects for an overview.

Parameters:

• oid – The OID of the object to read or write. 0 to create a new large object and and have its OID assigned automatically.
• mode – Access mode to the object: can be r, w, rw or n (meaning don’t open it).
• new_oid – Create a new object using the specified OID. The function raises OperationalError if the OID is already in use. Default is 0, meaning assign a new one automatically.
• new_file – The name of a file to be imported in the the database (using the lo_import() function)
• lobject_factory – Subclass of lobject to be instantiated.

Return type:

lobject

New in version 2.0.8.

Methods related to asynchronous support.

New in version 2.2.0.

See also

Asynchronous support and Support to coroutine libraries.

async

Read only attribute: 1 if the connection is asynchronous, 0 otherwse.

poll()

Used during an asynchronous connection attempt, or when a cursor is executing a query on an asynchronous connection, make communication proceed if it wouldn’t block.

Return one of the constants defined in Poll constants. If it returns POLL_OK then the connection has been estabilished or the query results are available on the client. Otherwise wait until the file descriptor returned by fileno() is ready to read or to write, as explained in Asynchronous support. poll() should be also used by the function installed by set_wait_callback() as explained in Support to coroutine libraries.

poll() is also used to receive asynchronous notifications from the database: see Asynchronous notifications from further details.

fileno()

Return the file descriptor underlying the connection: useful to read its status during asynchronous communication.

isexecuting()

Return True if the connection is executing an asynchronous operation.