asyncpg Documentation

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 transactionorstatement, 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 asavepoint_.):

.. 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 inmytab. 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() <asyncpg.connection.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 theasync forstatement, 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() <asyncpg.connection.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 10as 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() <asyncpg.connection.Connection.cursor>or :meth:PreparedStatement.cursor() <asyncpg.prepared_stmt.PreparedStatement.cursor>are non-scrollable: they can only be read forwards. To create a scrollable cursor, use theDECLARE ... SCROLL CURSOR SQL statement directly.

.. warning::

Cursors created by a call to :meth:Connection.cursor() <asyncpg.connection.Connection.cursor>or :meth:PreparedStatement.cursor() <asyncpg.prepared_stmt.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 theDECLARE ... 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.Cursorwhich 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.Recordobject.Recordobjects 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 <Record oid=16388 rolname='elvis' rolsuper=True> >>> 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, returnNone.

.. 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 anAttributeError 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.Recorda named tuple because we want to keep theRecordmethod namespace separate from the column namespace. That said, you can provide a customRecordclass that implements dot-notation via therecord_classargument to :func:connect() <asyncpg.connection.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 <asyncpg-api-cursor>outside of a transaction? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Cursors created by a call to :meth:Connection.cursor() <asyncpg.connection.Connection.cursor>or :meth:PreparedStatement.cursor() \
<asyncpg.prepared_stmt.PreparedStatement.cursor>cannot be used outside of a transaction. Any such attempt will result inInterfaceError. To create a cursor usable outside of a transaction, use theDECLARE ... 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 existserrors, you are most likely not connecting to the PostgreSQL server directly, but viapgbouncer <https://pgbouncer.github.io/>_. 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 <asyncpg-connection-pool>functionality provided by asyncpg, it is a much better option for this purpose;

* disable automatic use of prepared statements by passingstatement_cache_size=0to :func:asyncpg.connect() <asyncpg.connection.connect>and :func:asyncpg.create_pool() <asyncpg.pool.create_pool>(and, obviously, avoid the use of :meth:Connection.prepare() <asyncpg.connection.Connection.prepare>);

* switch pgbouncer's pool_mode to session.

Why do I getPostgresSyntaxError when using expression IN $1? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

expression IN $1is not a valid PostgreSQL syntax. To check a value against a sequence useexpression = any($1::mytype[]), wheremytype 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'sasyncioframework.

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. Seehere for the
details <https://github.com/pythongssapi/python-gssapi/issues/200#issuecomment-1032934269>_.)

It is also possible to use GSSAPI on Windows:

* pip install gssapi* InstallKerberos for Windows <https://web.mit.edu/kerberos/dist/>_. * Set thegsslib parameter or the PGGSSLIBenvironment variable togssapi 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 theASYNCPG_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() <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.

.. 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-codecsfor more information.

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

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 inetvalues with prefix asIPvXNetwork instead of IPvXInterface.

.. [#f3] Inexact single-precision floatvalues 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 todouble 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() \
<asyncpg.connection.Connection.set_type_codec>and :meth:Connection.set_builtin_type_codec() \
<asyncpg.connection.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 <python: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 <python: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 PostGISgeometrytype. It works for any Python object that conforms to thegeo 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 <python:decimal.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 asdictobjects. Becausehstoreis 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>:

.. 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() <asyncpg.connection.Connection>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() <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.

.. 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.

---

Api/Index Copy Section

Faq Copy Section

Index Copy Section

Installation Copy Section

Usage Copy Section

Explore Related Technologies & Topics

Api/Index

`Faq`

`Index`

`Installation`

`Usage`