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.get_event_loop().run_until_complete(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
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.IPv4Network, ipaddress.IPv6Network, ipaddress.IPv4Address, ipaddress.IPv6Address
macaddr str
circle asyncpg.Circle
date datetime.date
time offset-naïve datetime.time
time with timezone offset-aware datetime.time
timestamp offset-naïve datetime.datetime
timestamp with timezone offset-aware datetime.datetime
interval datetime.timedelta
float, double precision float [1]
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]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.get_event_loop().run_until_complete(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.asShape(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.get_event_loop().run_until_complete(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.get_event_loop().run_until_complete(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 con.set_builtin_type_codec(
        'hstore', codec_name='pg_contrib.hstore')
    result = await con.fetchval("SELECT 'a=>1,b=>2'::hstore")
    assert result == {'a': 1, 'b': 2}

asyncio.get_event_loop().run_until_complete(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_app():
    """Initialize the application server."""
    app = web.Application()
    # Create a database connection pool
    app['pool'] = await asyncpg.create_pool(database='postgres',
                                            user='postgres')
    # Configure service routes
    app.router.add_route('GET', '/{power:\d+}', handle)
    app.router.add_route('GET', '/', handle)
    return app


loop = asyncio.get_event_loop()
app = loop.run_until_complete(init_app())
web.run_app(app)

See Connection Pools API documentation for more information.