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=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 useAsyncConnection
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], context: Optional[psycopg.abc.AdaptContext] = 'None', **kwargs: Union[None, int, str]) psycopg.Connection[psycopg.rows.Row] ¶
- classmethod connect(conninfo: str = '', *, autocommit: bool = 'False', context: Optional[psycopg.abc.AdaptContext] = 'None', **kwargs: Union[None, int, str]) psycopg.Connection[Tuple[Any, ...]]
Connect to a database server and return a new
Connection
instance.- Return type
- Parameters
conninfo – The connection string (a
postgresql://
url or a list ofkey=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. Seetransactions
for details.row_factory – The row factory specifying what type of records to create fetching data (default:
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
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
- 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.
- broken: bool¶
True
if the connection was interrupted.A broken connection is always
closed
, but wasn’t closed in a clean way, such as usingclose()
or awith
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
(orserver_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, or factory function, returned by
cursor()
andexecute()
.Default is
psycopg.Cursor
.
- server_cursor_factory: Type[psycopg.ServerCursor[psycopg.rows.Row]]¶
The type, or factory function, returned by
cursor()
when a name is specified.Default is
psycopg.ServerCursor
.
- row_factory: psycopg.rows.RowFactory[psycopg.rows.Row]¶
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
[~Row]- Parameters
query (
str
,bytes
, orsql.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,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 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
- Return type
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()
orrollback()
.
- 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 details.
- isolation_level: Optional[IsolationLevel, None]¶
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, None]¶
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, None]¶
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 theConnection
.It can be used to send low level commands to PostgreSQL and access to features not currently wrapped by Psycopg.
- info: ConnectionInfo¶
A
ConnectionInfo
attribute to inspect connection properties.
- prepare_threshold: Optional[int, None]¶
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.Notifies are recevied after using
LISTEN
in a connection, when any sessions in the database generates aNOTIFY
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=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 theawait
keyword.- async classmethod connect(conninfo: str = '', *, autocommit: bool = 'False', row_factory: psycopg.rows.AsyncRowFactory[psycopg.rows.Row], context: Optional[psycopg.abc.AdaptContext] = 'None', **kwargs: Union[None, int, str]) psycopg.AsyncConnection[psycopg.rows.Row] ¶
- async classmethod connect(conninfo: str = '', *, autocommit: bool = 'False', context: Optional[psycopg.abc.AdaptContext] = 'None', **kwargs: Union[None, int, str]) psycopg.AsyncConnection[Tuple[Any, ...]]
- Return type
- 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
.
- row_factory: psycopg.rows.AsyncRowFactory[psycopg.rows.Row]¶
- async execute(query, params=None, *, prepare=None, binary=False)¶
- 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
Note
It must be called as:
async with conn.transaction() as tx: ...
- async notifies()¶
- Return type
- async set_autocommit(value)¶
Async version of the
autocommit
setter.
- async set_isolation_level(value)¶
Async version of the
isolation_level
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()
.
- class psycopg.ConnectionInfo(pgconn)¶
Allow access to information about the connection.
The object is usually returned by
Connection.info
.- dsn: str¶
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 thepassword
attribute).Note
The
get_parameters()
method returns the same information as a dict.
- status: pq.ConnStatus¶
The status of the connection. See :pq:`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
andBAD
. A good connection to the database has the statusOK
. Ordinarily, anOK
status will remain so untilConnection.close()
, but a communications failure might result in the status changing toBAD
prematurely.
- transaction_status: pq.TransactionStatus¶
The current in-transaction status of the server. See :pq:`PQtransactionStatus()`.
The status can be
IDLE
(currently idle),ACTIVE
(a command is in progress),INTRANS
(idle, in a valid transaction block), orINERROR
(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.
- backend_pid: int¶
The process ID (PID) of the backend process handling this connection. See :pq:`PQbackendPID()`.
- server_version: int¶
An integer representing the server version. See :pq:`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: str¶
The error message most recently generated by an operation on the connection. See :pq:`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
connect()
or from environment variables. The password is never returned (you can read it using thepassword
attribute).Note
The
dsn
attribute returns the same information in the form as a string.
- timezone: datetime.tzinfo¶
The Python timezone info of the connection’s timezone.
>>> conn.info.timezone zoneinfo.ZoneInfo(key='Europe/Rome')
- host: str¶
The server host name of the active connection. See :pq:`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 :pq:`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 :pq:`PQport()`.
- dbname: str¶
The database name of the connection. See :pq:`PQdb()`.
- user: str¶
The user name of the connection. See :pq:`PQuser()`.
- password: str¶
The password of the connection. See :pq:`PQpass()`.
- options: str¶
The command-line options passed in the connection request. See :pq:`PQoptions`.
- parameter_status(param_name)¶
Return a parameter setting of the connection.
Return
None
is the parameter is unknown.Example of parameters are
server_version
,standard_conforming_strings
… See :pq:`PQparameterStatus()` for all the available parameters.
- encoding: str¶
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 PostgreSQLSQL_ASCII
encoding has the special meaning of “no encoding”: see Strings adaptation for details.See also
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, None]¶
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 aboveraise Rollback(tx)
: roll back any operation that happened in theTransaction
tx (returned by a statement such aswith conn.transaction() as tx:
and all the blocks nested within. The program will continue after the tx block.