### Api/Index .. _asyncpg-api-reference: ============= API Reference ============= .. module:: asyncpg :synopsis: A fast PostgreSQL Database Client Library for Python/asyncio .. currentmodule:: asyncpg .. _asyncpg-api-connection: Connection ========== .. autofunction:: asyncpg.connection.connect .. autoclass:: asyncpg.connection.Connection :members: .. _asyncpg-api-prepared-stmt: Prepared Statements =================== Prepared statements are a PostgreSQL feature that can be used to optimize the performance of queries that are executed more than once. When a query is *prepared* by a call to :meth:`Connection.prepare`, the server parses, analyzes and compiles the query allowing to reuse that work once there is a need to run the same query again. .. code-block:: pycon >>> import asyncpg, asyncio >>> async def run(): ... conn = await asyncpg.connect() ... stmt = await conn.prepare('''SELECT 2 ^ $1''') ... print(await stmt.fetchval(10)) ... print(await stmt.fetchval(20)) ... >>> asyncio.run(run()) 1024.0 1048576.0 .. note:: asyncpg automatically maintains a small LRU cache for queries executed during calls to the :meth:`~Connection.fetch`, :meth:`~Connection.fetchrow`, or :meth:`~Connection.fetchval` methods. .. warning:: If you are using pgbouncer with ``pool_mode`` set to ``transaction`` or ``statement``, prepared statements will not work correctly. See :ref:`asyncpg-prepared-stmt-errors` for more information. .. autoclass:: asyncpg.prepared_stmt.PreparedStatement() :members: .. _asyncpg-api-transaction: Transactions ============ The most common way to use transactions is through an ``async with`` statement: .. code-block:: python async with connection.transaction(): await connection.execute("INSERT INTO mytable VALUES(1, 2, 3)") asyncpg supports nested transactions (a nested transaction context will create a `savepoint`_.): .. code-block:: python async with connection.transaction(): await connection.execute('CREATE TABLE mytab (a int)') try: # Create a nested transaction: async with connection.transaction(): await connection.execute('INSERT INTO mytab (a) VALUES (1), (2)') # This nested transaction will be automatically rolled back: raise Exception except: # Ignore exception pass # Because the nested transaction was rolled back, there # will be nothing in `mytab`. assert await connection.fetch('SELECT a FROM mytab') == [] Alternatively, transactions can be used without an ``async with`` block: .. code-block:: python tr = connection.transaction() await tr.start() try: ... except: await tr.rollback() raise else: await tr.commit() See also the :meth:`Connection.transaction() ` function. .. _savepoint: https://www.postgresql.org/docs/current/static/sql-savepoint.html .. autoclass:: asyncpg.transaction.Transaction() :members: .. describe:: async with c: start and commit/rollback the transaction or savepoint block automatically when entering and exiting the code inside the context manager block. .. _asyncpg-api-cursor: Cursors ======= Cursors are useful when there is a need to iterate over the results of a large query without fetching all rows at once. The cursor interface provided by asyncpg supports *asynchronous iteration* via the ``async for`` statement, and also a way to read row chunks and skip forward over the result set. To iterate over a cursor using a connection object use :meth:`Connection.cursor() `. To make the iteration efficient, the cursor will prefetch records to reduce the number of queries sent to the server: .. code-block:: python async def iterate(con: Connection): async with con.transaction(): # Postgres requires non-scrollable cursors to be created # and used in a transaction. async for record in con.cursor('SELECT generate_series(0, 100)'): print(record) Or, alternatively, you can iterate over the cursor manually (cursor won't be prefetching any rows): .. code-block:: python async def iterate(con: Connection): async with con.transaction(): # Postgres requires non-scrollable cursors to be created # and used in a transaction. # Create a Cursor object cur = await con.cursor('SELECT generate_series(0, 100)') # Move the cursor 10 rows forward await cur.forward(10) # Fetch one row and print it print(await cur.fetchrow()) # Fetch a list of 5 rows and print it print(await cur.fetch(5)) It's also possible to create cursors from prepared statements: .. code-block:: python async def iterate(con: Connection): # Create a prepared statement that will accept one argument stmt = await con.prepare('SELECT generate_series(0, $1)') async with con.transaction(): # Postgres requires non-scrollable cursors to be created # and used in a transaction. # Execute the prepared statement passing `10` as the # argument -- that will generate a series or records # from 0..10. Iterate over all of them and print every # record. async for record in stmt.cursor(10): print(record) .. note:: Cursors created by a call to :meth:`Connection.cursor() ` or :meth:`PreparedStatement.cursor() ` are *non-scrollable*: they can only be read forwards. To create a scrollable cursor, use the ``DECLARE ... SCROLL CURSOR`` SQL statement directly. .. warning:: Cursors created by a call to :meth:`Connection.cursor() ` or :meth:`PreparedStatement.cursor() ` cannot be used outside of a transaction. Any such attempt will result in :exc:`~asyncpg.exceptions.InterfaceError`. To create a cursor usable outside of a transaction, use the ``DECLARE ... CURSOR WITH HOLD`` SQL statement directly. .. autoclass:: asyncpg.cursor.CursorFactory() :members: .. describe:: async for row in c Execute the statement and iterate over the results asynchronously. .. describe:: await c Execute the statement and return an instance of :class:`~asyncpg.cursor.Cursor` which can be used to navigate over and fetch subsets of the query results. .. autoclass:: asyncpg.cursor.Cursor() :members: .. _asyncpg-api-pool: Connection Pools ================ .. autofunction:: asyncpg.pool.create_pool .. autoclass:: asyncpg.pool.Pool() :members: .. _asyncpg-api-record: Record Objects ============== Each row (or composite type value) returned by calls to ``fetch*`` methods is represented by an instance of the :class:`~asyncpg.Record` object. ``Record`` objects are a tuple-/dict-like hybrid, and allow addressing of items either by a numeric index or by a field name: .. code-block:: pycon >>> import asyncpg >>> import asyncio >>> loop = asyncio.get_event_loop() >>> conn = loop.run_until_complete(asyncpg.connect()) >>> r = loop.run_until_complete(conn.fetchrow(''' ... SELECT oid, rolname, rolsuper FROM pg_roles WHERE rolname = user''')) >>> r >>> r['oid'] 16388 >>> r[0] 16388 >>> dict(r) {'oid': 16388, 'rolname': 'elvis', 'rolsuper': True} >>> tuple(r) (16388, 'elvis', True) .. note:: ``Record`` objects currently cannot be created from Python code. .. class:: Record() A read-only representation of PostgreSQL row. .. describe:: len(r) Return the number of fields in record *r*. .. describe:: r[field] Return the field of *r* with field name or index *field*. .. describe:: name in r Return ``True`` if record *r* has a field named *name*. .. describe:: iter(r) Return an iterator over the *values* of the record *r*. .. describe:: get(name[, default]) Return the value for *name* if the record has a field named *name*, else return *default*. If *default* is not given, return ``None``. .. versionadded:: 0.18 .. method:: values() Return an iterator over the record values. .. method:: keys() Return an iterator over the record field names. .. method:: items() Return an iterator over ``(field, value)`` pairs. .. class:: ConnectionSettings() A read-only collection of Connection settings. .. describe:: settings.setting_name Return the value of the "setting_name" setting. Raises an ``AttributeError`` if the setting is not defined. Example: .. code-block:: pycon >>> connection.get_settings().client_encoding 'UTF8' Data Types ========== .. automodule:: asyncpg.types :members: --- ### Faq .. _asyncpg-faq: Frequently Asked Questions ========================== Does asyncpg support DB-API? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ No. DB-API is a synchronous API, while asyncpg is based around an asynchronous I/O model. Thus, full drop-in compatibility with DB-API is not possible and we decided to design asyncpg API in a way that is better aligned with PostgreSQL architecture and terminology. We will release a synchronous DB-API-compatible version of asyncpg at some point in the future. Can I use asyncpg with SQLAlchemy ORM? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Yes. SQLAlchemy version 1.4 and later supports the asyncpg dialect natively. Please refer to its documentation for details. Older SQLAlchemy versions may be used in tandem with a third-party adapter such as asyncpgsa_ or databases_. Can I use dot-notation with :class:`asyncpg.Record`? It looks cleaner. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ We decided against making :class:`asyncpg.Record` a named tuple because we want to keep the ``Record`` method namespace separate from the column namespace. That said, you can provide a custom ``Record`` class that implements dot-notation via the ``record_class`` argument to :func:`connect() ` or any of the Record-returning methods. .. code-block:: python class MyRecord(asyncpg.Record): def __getattr__(self, name): return self[name] Why can't I use a :ref:`cursor ` outside of a transaction? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Cursors created by a call to :meth:`Connection.cursor() ` or :meth:`PreparedStatement.cursor() \ ` cannot be used outside of a transaction. Any such attempt will result in ``InterfaceError``. To create a cursor usable outside of a transaction, use the ``DECLARE ... CURSOR WITH HOLD`` SQL statement directly. .. _asyncpg-prepared-stmt-errors: Why am I getting prepared statement errors? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If you are getting intermittent ``prepared statement "__asyncpg_stmt_xx__" does not exist`` or ``prepared statement “__asyncpg_stmt_xx__” already exists`` errors, you are most likely not connecting to the PostgreSQL server directly, but via `pgbouncer `_. pgbouncer, when in the ``"transaction"`` or ``"statement"`` pooling mode, does not support prepared statements. You have several options: * if you are using pgbouncer only to reduce the cost of new connections (as opposed to using pgbouncer for connection pooling from a large number of clients in the interest of better scalability), switch to the :ref:`connection pool ` functionality provided by asyncpg, it is a much better option for this purpose; * disable automatic use of prepared statements by passing ``statement_cache_size=0`` to :func:`asyncpg.connect() ` and :func:`asyncpg.create_pool() ` (and, obviously, avoid the use of :meth:`Connection.prepare() `); * switch pgbouncer's ``pool_mode`` to ``session``. Why do I get ``PostgresSyntaxError`` when using ``expression IN $1``? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ``expression IN $1`` is not a valid PostgreSQL syntax. To check a value against a sequence use ``expression = any($1::mytype[])``, where ``mytype`` is the array element type. .. _asyncpgsa: https://github.com/CanopyTax/asyncpgsa .. _databases: https://github.com/encode/databases --- ### Index .. image:: https://github.com/MagicStack/asyncpg/workflows/Tests/badge.svg :target: https://github.com/MagicStack/asyncpg/actions?query=workflow%3ATests+branch%3Amaster :alt: GitHub Actions status .. image:: https://img.shields.io/pypi/status/asyncpg.svg?maxAge=2592000?style=plastic :target: https://pypi.python.org/pypi/asyncpg ======= asyncpg ======= **asyncpg** is a database interface library designed specifically for PostgreSQL and Python/asyncio. asyncpg is an efficient, clean implementation of PostgreSQL server binary protocol for use with Python's ``asyncio`` framework. **asyncpg** requires Python 3.9 or later and is supported for PostgreSQL versions 9.5 to 18. Other PostgreSQL versions or other databases implementing the PostgreSQL protocol *may* work, but are not being actively tested. Contents -------- .. toctree:: :maxdepth: 2 installation usage api/index faq --- ### Installation .. _asyncpg-installation: Installation ============ **asyncpg** has no external dependencies when not using GSSAPI/SSPI authentication. The recommended way to install it is to use **pip**: .. code-block:: bash $ pip install asyncpg If you need GSSAPI/SSPI authentication, the recommended way is to use .. code-block:: bash $ pip install 'asyncpg[gssauth]' This installs SSPI support on Windows and GSSAPI support on non-Windows platforms. SSPI and GSSAPI interoperate as clients and servers: an SSPI client can authenticate to a GSSAPI server and vice versa. On Linux installing GSSAPI requires a working C compiler and Kerberos 5 development files. The latter can be obtained by installing **libkrb5-dev** package on Debian/Ubuntu or **krb5-devel** on RHEL/Fedora. (This is needed because PyPI does not have Linux wheels for **gssapi**. See `here for the details `_.) It is also possible to use GSSAPI on Windows: * `pip install gssapi` * Install `Kerberos for Windows `_. * Set the ``gsslib`` parameter or the ``PGGSSLIB`` environment variable to `gssapi` when connecting. Building from source -------------------- If you want to build **asyncpg** from a Git checkout you will need: * To have cloned the repo with `--recurse-submodules`. * A working C compiler. * CPython header files. These can usually be obtained by installing the relevant Python development package: **python3-dev** on Debian/Ubuntu, **python3-devel** on RHEL/Fedora. Once the above requirements are satisfied, run the following command in the root of the source checkout: .. code-block:: bash $ pip install -e . A debug build containing more runtime checks can be created by setting the ``ASYNCPG_DEBUG`` environment variable when building: .. code-block:: bash $ env ASYNCPG_DEBUG=1 pip install -e . Running tests ------------- If you want to run tests you must have PostgreSQL installed. To execute the testsuite run: .. code-block:: bash $ python setup.py test --- ### Usage .. _asyncpg-examples: asyncpg Usage ============= The interaction with the database normally starts with a call to :func:`connect() `, which establishes a new database session and returns a new :class:`Connection ` instance, which provides methods to run queries and manage transactions. .. code-block:: python import asyncio import asyncpg import datetime async def main(): # Establish a connection to an existing database named "test" # as a "postgres" user. conn = await asyncpg.connect('postgresql://postgres@localhost/test') # Execute a statement to create a new table. await conn.execute(''' CREATE TABLE users( id serial PRIMARY KEY, name text, dob date ) ''') # Insert a record into the created table. await conn.execute(''' INSERT INTO users(name, dob) VALUES($1, $2) ''', 'Bob', datetime.date(1984, 3, 1)) # Select a row from the table. row = await conn.fetchrow( 'SELECT * FROM users WHERE name = $1', 'Bob') # *row* now contains # asyncpg.Record(id=1, name='Bob', dob=datetime.date(1984, 3, 1)) # Close the connection. await conn.close() asyncio.run(main()) .. note:: asyncpg uses the native PostgreSQL syntax for query arguments: ``$n``. Type Conversion --------------- asyncpg automatically converts PostgreSQL types to the corresponding Python types and vice versa. All standard data types are supported out of the box, including arrays, composite types, range types, enumerations and any combination of them. It is possible to supply codecs for non-standard types or override standard codecs. See :ref:`asyncpg-custom-codecs` for more information. The table below shows the correspondence between PostgreSQL and Python types. +----------------------+-----------------------------------------------------+ | PostgreSQL Type | Python Type | +======================+=====================================================+ | ``anyarray`` | :class:`list ` | +----------------------+-----------------------------------------------------+ | ``anyenum`` | :class:`str ` | +----------------------+-----------------------------------------------------+ | ``anyrange`` | :class:`asyncpg.Range `, | | | :class:`tuple ` | +----------------------+-----------------------------------------------------+ | ``anymultirange`` | ``list[``:class:`asyncpg.Range\ | | | ` ``]``, | | | ``list[``:class:`tuple ` ``]`` [#f1]_ | +----------------------+-----------------------------------------------------+ | ``record`` | :class:`asyncpg.Record`, | | | :class:`tuple `, | | | :class:`Mapping ` | +----------------------+-----------------------------------------------------+ | ``bit``, ``varbit`` | :class:`asyncpg.BitString `| +----------------------+-----------------------------------------------------+ | ``bool`` | :class:`bool ` | +----------------------+-----------------------------------------------------+ | ``box`` | :class:`asyncpg.Box ` | +----------------------+-----------------------------------------------------+ | ``bytea`` | :class:`bytes ` | +----------------------+-----------------------------------------------------+ | ``char``, ``name``, | :class:`str ` | | ``varchar``, | | | ``text``, | | | ``xml`` | | +----------------------+-----------------------------------------------------+ | ``cidr`` | :class:`ipaddress.IPv4Network\ | | | `, | | | :class:`ipaddress.IPv6Network\ | | | ` | +----------------------+-----------------------------------------------------+ | ``inet`` | :class:`ipaddress.IPv4Interface\ | | | `, | | | :class:`ipaddress.IPv6Interface\ | | | `, | | | :class:`ipaddress.IPv4Address\ | | | `, | | | :class:`ipaddress.IPv6Address\ | | | ` [#f2]_ | +----------------------+-----------------------------------------------------+ | ``macaddr`` | :class:`str ` | +----------------------+-----------------------------------------------------+ | ``circle`` | :class:`asyncpg.Circle ` | +----------------------+-----------------------------------------------------+ | ``date`` | :class:`datetime.date ` | +----------------------+-----------------------------------------------------+ | ``time`` | offset-naïve :class:`datetime.time \ | | | ` | +----------------------+-----------------------------------------------------+ | ``time with | offset-aware :class:`datetime.time \ | | time zone`` | ` | +----------------------+-----------------------------------------------------+ | ``timestamp`` | offset-naïve :class:`datetime.datetime \ | | | ` | +----------------------+-----------------------------------------------------+ | ``timestamp with | offset-aware :class:`datetime.datetime \ | | time zone`` | ` | +----------------------+-----------------------------------------------------+ | ``interval`` | :class:`datetime.timedelta \ | | | ` | +----------------------+-----------------------------------------------------+ | ``float``, | :class:`float ` [#f3]_ | | ``double precision`` | | +----------------------+-----------------------------------------------------+ | ``smallint``, | :class:`int ` | | ``integer``, | | | ``bigint`` | | +----------------------+-----------------------------------------------------+ | ``numeric`` | :class:`Decimal ` | +----------------------+-----------------------------------------------------+ | ``json``, ``jsonb`` | :class:`str ` | +----------------------+-----------------------------------------------------+ | ``line`` | :class:`asyncpg.Line ` | +----------------------+-----------------------------------------------------+ | ``lseg`` | :class:`asyncpg.LineSegment \ | | | ` | +----------------------+-----------------------------------------------------+ | ``money`` | :class:`str ` | +----------------------+-----------------------------------------------------+ | ``path`` | :class:`asyncpg.Path ` | +----------------------+-----------------------------------------------------+ | ``point`` | :class:`asyncpg.Point ` | +----------------------+-----------------------------------------------------+ | ``polygon`` | :class:`asyncpg.Polygon ` | +----------------------+-----------------------------------------------------+ | ``uuid`` | :class:`uuid.UUID ` | +----------------------+-----------------------------------------------------+ | ``tid`` | :class:`tuple ` | +----------------------+-----------------------------------------------------+ All other types are encoded and decoded as text by default. .. [#f1] Since version 0.25.0 .. [#f2] Prior to version 0.20.0, asyncpg erroneously treated ``inet`` values with prefix as ``IPvXNetwork`` instead of ``IPvXInterface``. .. [#f3] Inexact single-precision ``float`` values may have a different representation when decoded into a Python float. This is inherent to the implementation of limited-precision floating point types. If you need the decimal representation to match, cast the expression to ``double`` or ``numeric`` in your query. .. _asyncpg-custom-codecs: Custom Type Conversions ----------------------- asyncpg allows defining custom type conversion functions both for standard and user-defined types using the :meth:`Connection.set_type_codec() \ ` and :meth:`Connection.set_builtin_type_codec() \ ` methods. Example: automatic JSON conversion ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The example below shows how to configure asyncpg to encode and decode JSON values using the :mod:`json ` module. .. code-block:: python import asyncio import asyncpg import json async def main(): conn = await asyncpg.connect() try: await conn.set_type_codec( 'json', encoder=json.dumps, decoder=json.loads, schema='pg_catalog' ) data = {'foo': 'bar', 'spam': 1} res = await conn.fetchval('SELECT $1::json', data) finally: await conn.close() asyncio.run(main()) Example: complex types ~~~~~~~~~~~~~~~~~~~~~~ The example below shows how to configure asyncpg to encode and decode Python :class:`complex ` values to a custom composite type in PostgreSQL. .. code-block:: python import asyncio import asyncpg async def main(): conn = await asyncpg.connect() try: await conn.execute( ''' CREATE TYPE mycomplex AS ( r float, i float );''' ) await conn.set_type_codec( 'complex', encoder=lambda x: (x.real, x.imag), decoder=lambda t: complex(t[0], t[1]), format='tuple', ) res = await conn.fetchval('SELECT $1::mycomplex', (1+2j)) finally: await conn.close() asyncio.run(main()) Example: automatic conversion of PostGIS types ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The example below shows how to configure asyncpg to encode and decode the PostGIS ``geometry`` type. It works for any Python object that conforms to the `geo interface specification`_ and relies on Shapely_, although any library that supports reading and writing the WKB format will work. .. _Shapely: https://github.com/Toblerity/Shapely .. _geo interface specification: https://gist.github.com/sgillies/2217756 .. code-block:: python import asyncio import asyncpg import shapely.geometry import shapely.wkb from shapely.geometry.base import BaseGeometry async def main(): conn = await asyncpg.connect() try: def encode_geometry(geometry): if not hasattr(geometry, '__geo_interface__'): raise TypeError('{g} does not conform to ' 'the geo interface'.format(g=geometry)) shape = shapely.geometry.shape(geometry) return shapely.wkb.dumps(shape) def decode_geometry(wkb): return shapely.wkb.loads(wkb) await conn.set_type_codec( 'geometry', # also works for 'geography' encoder=encode_geometry, decoder=decode_geometry, format='binary', ) data = shapely.geometry.Point(-73.985661, 40.748447) res = await conn.fetchrow( '''SELECT 'Empire State Building' AS name, $1::geometry AS coordinates ''', data) print(res) finally: await conn.close() asyncio.run(main()) Example: decoding numeric columns as floats ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ By default asyncpg decodes numeric columns as Python :class:`Decimal ` instances. The example below shows how to instruct asyncpg to use floats instead. .. code-block:: python import asyncio import asyncpg async def main(): conn = await asyncpg.connect() try: await conn.set_type_codec( 'numeric', encoder=str, decoder=float, schema='pg_catalog', format='text' ) res = await conn.fetchval("SELECT $1::numeric", 11.123) print(res, type(res)) finally: await conn.close() asyncio.run(main()) Example: decoding hstore values ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ hstore_ is an extension data type used for storing key/value pairs. asyncpg includes a codec to decode and encode hstore values as ``dict`` objects. Because ``hstore`` is not a builtin type, the codec must be registered on a connection using :meth:`Connection.set_builtin_type_codec() `: .. code-block:: python import asyncpg import asyncio async def run(): conn = await asyncpg.connect() # Assuming the hstore extension exists in the public schema. await conn.set_builtin_type_codec( 'hstore', codec_name='pg_contrib.hstore') result = await conn.fetchval("SELECT 'a=>1,b=>2,c=>NULL'::hstore") assert result == {'a': '1', 'b': '2', 'c': None} asyncio.run(run()) .. _hstore: https://www.postgresql.org/docs/current/static/hstore.html Transactions ------------ To create transactions, the :meth:`Connection.transaction() ` method should be used. The most common way to use transactions is through an ``async with`` statement: .. code-block:: python async with connection.transaction(): await connection.execute("INSERT INTO mytable VALUES(1, 2, 3)") .. note:: When not in an explicit transaction block, any changes to the database will be applied immediately. This is also known as *auto-commit*. See the :ref:`asyncpg-api-transaction` API documentation for more information. .. _asyncpg-connection-pool: Connection Pools ---------------- For server-type type applications, that handle frequent requests and need the database connection for a short period time while handling a request, the use of a connection pool is recommended. asyncpg provides an advanced pool implementation, which eliminates the need to use an external connection pooler such as PgBouncer. To create a connection pool, use the :func:`asyncpg.create_pool() ` function. The resulting :class:`Pool ` object can then be used to borrow connections from the pool. Below is an example of how **asyncpg** can be used to implement a simple Web service that computes the requested power of two. .. code-block:: python import asyncio import asyncpg from aiohttp import web async def handle(request): """Handle incoming requests.""" pool = request.app['pool'] power = int(request.match_info.get('power', 10)) # Take a connection from the pool. async with pool.acquire() as connection: # Open a transaction. async with connection.transaction(): # Run the query passing the request argument. result = await connection.fetchval('select 2 ^ $1', power) return web.Response( text="2 ^ {} is {}".format(power, result)) async def init_db(app): """Initialize a connection pool.""" app['pool'] = await asyncpg.create_pool(database='postgres', user='postgres') yield await app['pool'].close() def init_app(): """Initialize the application server.""" app = web.Application() # Create a database context app.cleanup_ctx.append(init_db) # Configure service routes app.router.add_route('GET', '/{power:\d+}', handle) app.router.add_route('GET', '/', handle) return app app = init_app() web.run_app(app) See :ref:`asyncpg-api-pool` API documentation for more information. ---