Connection クラス#
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 トランザクションの管理 for the details.
The Connection
class#
- class psycopg.Connection#
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: RowFactory[Row], prepare_threshold: Optional[int] = 5, cursor_factory: Optional[Type[Cursor[Row]]] = None, context: Optional[AdaptContext] = None, **kwargs: Union[None, int, str]) Connection[Row] #
- classmethod connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: Optional[int] = 5, cursor_factory: Optional[Type[Cursor[Any]]] = None, context: Optional[AdaptContext] = None, **kwargs: Union[None, int, str]) Connection[Tuple[Any, ...]]
Connect to a database server and return a new
Connection
instance.- パラメータ:
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. See トランザクションの管理 for details.row_factory -- The row factory specifying what type of records to create fetching data (default:
tuple_row()
). See 行ファクトリ for details.cursor_factory -- Initial value for the
cursor_factory
attribute of the connection (new in Psycopg 3.1).prepare_threshold -- Initial value for the
prepare_threshold
attribute of the connection (new in Psycopg 3.1).
More specialized use:
- パラメータ:
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 データ適応の設定 for further details.
This method is also aliased as
psycopg.connect()
.参考
the list of the accepted connection parameters
the environment variables affecting connection
バージョン 3.1 で変更: added
prepare_threshold
andcursor_factory
parameters.
- close()#
Close the database connection.
注釈
You can use:
with psycopg.connect() as conn: ...
to close the connection automatically when the block is exited. See コネクション コンテクスト.
- closed#
True
if the connection is closed.
- broken#
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.
- パラメータ:
name -- If not specified create a client-side cursor, if specified create a server-side cursor. See カーソルの型 for details.
binary -- If
True
return binary values from the database. All the types returned by the query must have a binary loader. See バイナリ パラメータと結果 for details.row_factory -- If specified override the
row_factory
set on the connection. See 行ファクトリ for details.scrollable -- Specify the
scrollable
property of the server-side cursor created.withhold -- Specify the
withhold
property of the server-side cursor created.
- 戻り値:
A cursor of the class specified by
cursor_factory
(orserver_cursor_factory
ifname
is specified).
注釈
You can use:
with conn.cursor() as cur: ...
to close the cursor automatically when the block is exited.
- cursor_factory: Type[Cursor[Row]]#
The type, or factory function, returned by
cursor()
andexecute()
.Default is
psycopg.Cursor
.
- server_cursor_factory: Type[ServerCursor[Row]]#
The type, or factory function, returned by
cursor()
when a name is specified.Default is
psycopg.ServerCursor
.
- row_factory: RowFactory[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 行ファクトリ for details about defining the objects returned by cursors.
- execute(query: Union[LiteralString, bytes, sql.SQL, sql.Composed], params: Optional[Union[Sequence[Any], Mapping[str, Any]]] = None, *, prepare: Optional[bool] = None, binary: bool = False) Cursor[Row] #
Execute a query and return a cursor to read its results.
- パラメータ:
query (
str
,bytes
,sql.SQL
, orsql.Composed
) -- 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 prepare されたステートメント.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 バイナリ パラメータと結果 for details.
The method simply creates a
Cursor
instance,execute()
the query requested, and returns it.See SQL クエリにパラメータを渡す for all the details about executing queries.
- pipeline() Iterator[Pipeline] #
Switch the connection into pipeline mode.
The method is a context manager: you should call it using:
with conn.pipeline() as p: ...
At the end of the block, a synchronization point is established and the connection returns in normal mode.
You can call the method recursively from within a pipeline block. Innermost blocks will establish a synchronization point on exit, but pipeline mode will be kept until the outermost block exits.
See パイプライン モードのサポート for details.
バージョン 3.1 で追加.
Transaction management methods
For details see トランザクションの管理.
- commit()#
Commit any pending transaction to the database.
- rollback()#
Roll back to the start of any pending transaction.
- transaction(savepoint_name: Optional[str] = None, force_rollback: bool = False) Iterator[Transaction] #
Start a context block with a new transaction or nested transaction.
- パラメータ:
savepoint_name -- Name of the savepoint used to manage a nested transaction. If
None
, one will be chosen automatically.force_rollback -- Roll back the transaction at the end of the block even if there were no error (e.g. to try a no-op process).
- 戻り値の型:
注釈
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()
orrollback()
.
- autocommit#
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#
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
PGconn
libpq connection wrapper underlying theConnection
.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 prepare されたステートメント 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() Generator[Notify, None, None] #
Yield
Notify
objects as soon as they are received from the database.Notifies are received after using
LISTEN
in a connection, when any sessions in the database generates aNOTIFY
on one of the listened channels.
- add_notify_handler(callback: Callable[[Notify], None])#
Register a callable to be invoked whenever a notification is received.
- パラメータ:
callback (Callable[[Notify], None]) -- the callback to call upon notification received.
See 非同期通知 for details.
- remove_notify_handler(callback: Callable[[Notify], None])#
Unregister a notification callable previously registered.
- パラメータ:
callback (Callable[[Notify], None]) -- the callback to remove.
- add_notice_handler(callback: Callable[[Diagnostic], None])#
Register a callable to be invoked when a notice message is received.
- パラメータ:
callback (Callable[[Diagnostic], None]) -- the callback to call upon message received.
See サーバー メッセージ for details.
- remove_notice_handler(callback: Callable[[Diagnostic], None])#
Unregister a notice message callable previously registered.
- パラメータ:
callback (Callable[[Diagnostic], None]) -- the callback to remove.
- fileno() int #
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.
Two-Phase Commit support methods
バージョン 3.1 で追加.
参考
Two-Phase Commit protocol support for an introductory explanation of these methods.
- xid(format_id: int, gtrid: str, bqual: str) Xid #
Returns a
Xid
to pass to thetpc_*()
methods of this connection.The argument types and constraints are explained in Two-Phase Commit protocol support.
The values passed to the method will be available on the returned object as the members
format_id
,gtrid
,bqual
.
- tpc_begin(xid: Union[Xid, str])#
Begin a TPC transaction with the given transaction ID
xid
.This method should be called outside of a transaction (i.e. nothing may have executed since the last
commit()
orrollback()
andtransaction_status
isIDLE
).Furthermore, it is an error to call
commit()
orrollback()
within the TPC transaction: in this case aProgrammingError
is raised.The
xid
may be either an object returned by thexid()
method or a plain string: the latter allows to create a transaction using the provided string as PostgreSQL transaction id. See alsotpc_recover()
.
- tpc_prepare()#
Perform the first phase of a transaction started with
tpc_begin()
.A
ProgrammingError
is raised if this method is used outside of a TPC transaction.After calling
tpc_prepare()
, no statements can be executed untiltpc_commit()
ortpc_rollback()
will be called.参考
The
PREPARE TRANSACTION
PostgreSQL command.
- tpc_commit(xid: Optional[Union[Xid, str]] = None)#
Commit a prepared two-phase transaction.
When called with no arguments,
tpc_commit()
commits a TPC transaction previously prepared withtpc_prepare()
.If
tpc_commit()
is called prior totpc_prepare()
, a single phase commit is performed. A transaction manager may choose to do this if only a single resource is participating in the global transaction.When called with a transaction ID
xid
, the database commits the given transaction. If an invalid transaction ID is provided, aProgrammingError
will be raised. This form should be called outside of a transaction, and is intended for use in recovery.On return, the TPC transaction is ended.
参考
The
COMMIT PREPARED
PostgreSQL command.
- tpc_rollback(xid: Optional[Union[Xid, str]] = None)#
Roll back a prepared two-phase transaction.
When called with no arguments,
tpc_rollback()
rolls back a TPC transaction. It may be called before or aftertpc_prepare()
.When called with a transaction ID
xid
, it rolls back the given transaction. If an invalid transaction ID is provided, aProgrammingError
is raised. This form should be called outside of a transaction, and is intended for use in recovery.On return, the TPC transaction is ended.
参考
The
ROLLBACK PREPARED
PostgreSQL command.
- tpc_recover() List[Xid] #
Returns a list of
Xid
representing pending transactions, suitable for use withtpc_commit()
ortpc_rollback()
.If a transaction was not initiated by Psycopg, the returned Xids will have attributes
format_id
andbqual
set toNone
and thegtrid
set to the PostgreSQL transaction ID: such Xids are still usable for recovery. Psycopg uses the same algorithm of the PostgreSQL JDBC driver to encode a XA triple in a string, so transactions initiated by a program using such driver should be unpacked correctly.Xids returned by
tpc_recover()
also have extra attributesprepared
,owner
,database
populated with the values read from the server.参考
the
pg_prepared_xacts
system view.
The AsyncConnection
class#
- class psycopg.AsyncConnection#
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, prepare_threshold: Optional[int] = 5, row_factory: AsyncRowFactory[Row], cursor_factory: Optional[Type[AsyncCursor[Row]]] = None, context: Optional[AdaptContext] = None, **kwargs: Union[None, int, str]) AsyncConnection[Row] #
- async classmethod connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: Optional[int] = 5, cursor_factory: Optional[Type[AsyncCursor[Any]]] = None, context: Optional[AdaptContext] = None, **kwargs: Union[None, int, str]) AsyncConnection[Tuple[Any, ...]]
バージョン 3.1 で変更: Automatically resolve domain names asynchronously. In previous versions, name resolution blocks, unless the
hostaddr
parameter is specified, or theresolve_hostaddr_async()
function is used.
- async close()#
注釈
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 コネクション 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
注釈
You can use:
async with conn.cursor() as cur: ...
to close the cursor automatically when the block is exited.
- cursor_factory: Type[AsyncCursor[Row]]#
Default is
psycopg.AsyncCursor
.
- server_cursor_factory: Type[AsyncServerCursor[Row]]#
Default is
psycopg.AsyncServerCursor
.
- row_factory: AsyncRowFactory[Row]#
- async execute(query: Union[LiteralString, bytes, sql.SQL, sql.Composed], params: Optional[Union[Sequence[Any], Mapping[str, Any]]] = None, *, prepare: Optional[bool] = None, binary: bool = False) AsyncCursor[Row] #
- pipeline() AsyncIterator[AsyncPipeline] #
Context manager to switch the connection into pipeline mode.
注釈
It must be called as:
async with conn.pipeline() as p: ...
- async commit()#
- async rollback()#
- transaction(savepoint_name: Optional[str] = None, force_rollback: bool = False) AsyncIterator[AsyncTransaction] #
Start a context block with a new transaction or nested transaction.
- 戻り値の型:
注釈
It must be called as:
async with conn.transaction() as tx: ...
- async notifies() AsyncGenerator[Notify, None] #
- async set_autocommit(value: bool)#
Async version of the
autocommit
setter.
- async set_isolation_level(value: Optional[IsolationLevel])#
Async version of the
isolation_level
setter.
- async set_deferrable(value: Optional[bool])#
Async version of the
deferrable
setter.
- async tpc_prepare()#