New in version 0.7.1.
The high-level API is a wrapper over the mid-level connection API, combined with a connection pool.
The high-level API provides a connection pool that automatically maintains a certain amount of idle connections to the PostgreSQL server. As only one query can be issued to a server at any one time, this provides an effective way of performinng multiple queries concurrently.
These connections are PERSISTENT connections. The pool does not reap connections for idle connections. If you use something ala pgbouncer, that automatically closes idle connections, and your application is relatively low activity, there will be a cascading failure as broken connections are checked out of the pool and aren’t reconnected until an obvious error happens upon trying to query on a disconnected connection.
To create a connection pool, use
open_pool(*args, **kwargs) as pool¶
Opens a new connection pool to a PostgreSQL server. This is an asynchronous context manager.
This takes the same arguments and keyworrd arguments as
open_database_connection(), except for the optional
int) – The ideal number of connections to keep open at any one time. The pool may shrink slightly as connections are closed due to network errors and aren’t immediately re-opened.
By default, the connection count is (CPU_COUNT * 2) + 1.
- Return type
Your connection count should be relatively low. The default is a very good idea for nearly all applications. Don’t change it unless you have the benchmarks to prove it’s a good idea. See: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
PooledDatabaseInterface(count, nursery, conn_args, conn_kwargs)¶
Connection pool based PostgreSQL interface.
The number of the connections that haven’t currently been checked out.
- Return type
The connection pool object has a similar high-level query API to the mid-level API.
execute(query, *params, **kwargs)¶
Executes a query on the next available connection. See
AsyncPostgresConnection.execute()for more information.
- Return type
fetch(query, *params, **kwargs)¶
Fetches the result of a query on the next available connection. See
AsyncPostgresConnection.fetch()for more information.
As two subsequent queries may not be on the same connection, transactions get tricky. For that end,
the pool has a special
PooledDatabaseInterface.checkout_in_transaction() method which
checks out a connection for exclusive usage in a transaction block.
Checks out a new connection that automatically runs a transaction. This method MUST be used if you wish to execute something in a transaction.
async with pool.checkout_in_transaction() as conn: await conn.fetch("insert into ...")
The transaction will be automatically committed or rolled back as appropriate at the end of the
async with block, and the connection will not be reused until the checkout is done.
You can add converters like the other two APIs using
This will add it to all open connections, as well as any future connections that may be opened.
Registers a converter for all the connections on this pool.
If you wish to automatically add the array converter for converting PostgreSQL arrays of a custom
type that is converted, use
Registers a converter, and adds the array type converter to it too.