asyncpg Usage

The interaction with the database normally starts with a call to connect(), which establishes a new database session and returns a new Connection instance, which provides methods to run queries and manage transactions.

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 Custom Type Conversions for more information.

The table below shows the correspondence between PostgreSQL and Python types.

PostgreSQL Type	Python Type
anyarray	list
anyenum	str
anyrange	asyncpg.Range, tuple
anymultirange	list[asyncpg.Range ], list[tuple ] [1]
record	asyncpg.Record, tuple, Mapping
bit, varbit	asyncpg.BitString
bool	bool
box	asyncpg.Box
bytea	bytes
char, name, varchar, text, xml	str
cidr	ipaddress.IPv4Network, ipaddress.IPv6Network
inet	ipaddress.IPv4Interface, ipaddress.IPv6Interface, ipaddress.IPv4Address, ipaddress.IPv6Address [2]
macaddr	str
circle	asyncpg.Circle
date	datetime.date
time	offset-naïve datetime.time
time with time zone	offset-aware datetime.time
timestamp	offset-naïve datetime.datetime
timestamp with time zone	offset-aware datetime.datetime
interval	datetime.timedelta
float, double precision	float [3]
smallint, integer, bigint	int
numeric	Decimal
json, jsonb	str
line	asyncpg.Line
lseg	asyncpg.LineSegment
money	str
path	asyncpg.Path
point	asyncpg.Point
polygon	asyncpg.Polygon
uuid	uuid.UUID
tid	tuple

All other types are encoded and decoded as text by default.

[1]

Since version 0.25.0

[2]

Prior to version 0.20.0, asyncpg erroneously treated inet values with prefix as IPvXNetwork instead of IPvXInterface.

[3]

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.

Custom Type Conversions

asyncpg allows defining custom type conversion functions both for standard and user-defined types using the Connection.set_type_codec() and 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 json module.

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 complex values to a custom composite type in PostgreSQL.

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.

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 Decimal instances. The example below shows how to instruct asyncpg to use floats instead.

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 Connection.set_builtin_type_codec():

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())

Transactions

To create transactions, the Connection.transaction() method should be used.

The most common way to use transactions is through an async with statement:

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 Transactions API documentation for more information.

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 asyncpg.create_pool() function. The resulting 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.

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 Connection Pools API documentation for more information.