The interaction with the database normally starts with a call to :func:`connect() <asyncpg.connection.connect>`, which establishes a new database session and returns a new :class:`Connection <asyncpg.connection.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.
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.
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. |
asyncpg allows defining custom type conversion functions both for standard and user-defined types using the :meth:`Connection.set_type_codec() \ <asyncpg.connection.Connection.set_type_codec>` and :meth:`Connection.set_builtin_type_codec() \ <asyncpg.connection.Connection.set_builtin_type_codec>` methods.
The example below shows how to configure asyncpg to encode and decode JSON values using the :mod:`json <python: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())The example below shows how to configure asyncpg to encode and decode Python :class:`complex <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())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())By default asyncpg decodes numeric columns as Python :class:`Decimal <python:decimal.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())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()
<asyncpg.connection.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())To create transactions, the :meth:`Connection.transaction() <asyncpg.connection.Connection>` 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 :ref:`asyncpg-api-transaction` API documentation for more information.
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() <asyncpg.pool.create_pool>` function. The resulting :class:`Pool <asyncpg.pool.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 :ref:`asyncpg-api-pool` API documentation for more information.