asyncpg Usage — asyncpg Documentation (original) (raw)

The interaction with the database normally starts with a call toconnect(), which establishes a new database session and returns a newConnection 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.

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

Custom Type Conversions

asyncpg allows defining custom type conversion functions both for standard and user-defined types using the Connection.set_type_codec() andConnection.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 PythonDecimal 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 dictobjects. 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, theConnection.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 theasyncpg.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.