module documentation

The asyncpg dialect is SQLAlchemy's first Python asyncio dialect.

Using a special asyncio mediation layer, the asyncpg dialect is usable as the backend for the :ref:`SQLAlchemy asyncio <asyncio_toplevel>` extension package.

This dialect should normally be used only with the _asyncio.create_async_engine engine creation function:

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname")

The dialect can also be run as a "synchronous" dialect within the _sa.create_engine function, which will pass "await" calls into an ad-hoc event loop. This mode of operation is of limited use and is for special testing scenarios only. The mode can be enabled by adding the SQLAlchemy-specific flag async_fallback to the URL in conjunction with _sa.create_engine:

# for testing purposes only; do not use in production!
engine = create_engine("postgresql+asyncpg://user:pass@hostname/dbname?async_fallback=true")
New in version 1.4.

Note

By default asyncpg does not decode the json and jsonb types and returns them as strings. SQLAlchemy sets default type decoder for json and jsonb types using the python builtin json.loads function. The json implementation used can be changed by setting the attribute json_deserializer when creating the engine with create_engine or create_async_engine.

Prepared Statement Cache

The asyncpg SQLAlchemy dialect makes use of asyncpg.connection.prepare() for all statements. The prepared statement objects are cached after construction which appears to grant a 10% or more performance improvement for statement invocation. The cache is on a per-DBAPI connection basis, which means that the primary storage for prepared statements is within DBAPI connections pooled within the connection pool. The size of this cache defaults to 100 statements per DBAPI connection and may be adjusted using the prepared_statement_cache_size DBAPI argument (note that while this argument is implemented by SQLAlchemy, it is part of the DBAPI emulation portion of the asyncpg dialect, therefore is handled as a DBAPI argument, not a dialect argument):

engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=500")

To disable the prepared statement cache, use a value of zero:

engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=0")
New in version 1.4.0b2: Added prepared_statement_cache_size for asyncpg.

Warning

The asyncpg database driver necessarily uses caches for PostgreSQL type OIDs, which become stale when custom PostgreSQL datatypes such as ENUM objects are changed via DDL operations. Additionally, prepared statements themselves which are optionally cached by SQLAlchemy's driver as described above may also become "stale" when DDL has been emitted to the PostgreSQL database which modifies the tables or other objects involved in a particular prepared statement.

The SQLAlchemy asyncpg dialect will invalidate these caches within its local process when statements that represent DDL are emitted on a local connection, but this is only controllable within a single Python process / database engine. If DDL changes are made from other database engines and/or processes, a running application may encounter asyncpg exceptions InvalidCachedStatementError and/or InternalServerError("cache lookup failed for type <oid>") if it refers to pooled database connections which operated upon the previous structures. The SQLAlchemy asyncpg dialect will recover from these error cases when the driver raises these exceptions by clearing its internal caches as well as those of the asyncpg driver in response to them, but cannot prevent them from being raised in the first place if the cached prepared statement or asyncpg type caches have gone stale, nor can it retry the statement as the PostgreSQL transaction is invalidated when these errors occur.

Class ​Async​Adapt_asyncpg_connection Undocumented
Class ​Async​Adapt_asyncpg_cursor Undocumented
Class ​Async​Adapt_asyncpg_dbapi Undocumented
Class ​Async​Adapt_asyncpg_ss_cursor Undocumented
Class ​Async​Adapt​Fallback_asyncpg_connection Undocumented
Class ​Asyncpg​Big​Integer Undocumented
Class ​Asyncpg​Boolean Undocumented
Class ​Asyncpg​Date Undocumented
Class ​Asyncpg​Date​Time Undocumented
Class ​Async​Pg​Enum Undocumented
Class ​Asyncpg​Float Undocumented
Class ​Asyncpg​Integer Undocumented
Class ​Async​Pg​Interval Undocumented
Class ​Asyncpg​JSON Undocumented
Class ​Asyncpg​JSONB Undocumented
Class ​Asyncpg​JSONIndex​Type Undocumented
Class ​Asyncpg​JSONInt​Index​Type Undocumented
Class ​Asyncpg​JSONPath​Type Undocumented
Class ​Asyncpg​JSONStr​Index​Type Undocumented
Class ​Asyncpg​Numeric Undocumented
Class ​Asyncpg​OID Undocumented
Class ​Asyncpg​REGCLASS Undocumented
Class ​Asyncpg​Time Undocumented
Class ​Asyncpg​UUID Undocumented
Class ​PGCompiler_asyncpg Undocumented
Class ​PGDialect_asyncpg No class docstring; 0/2 property, 0/1 instance variable, 0/11 class variable, 3/13 methods, 0/2 class method documented
Class ​PGExecution​Context_asyncpg Undocumented
Class ​PGIdentifier​Preparer_asyncpg Undocumented
Variable ​_pg​_types Undocumented
Variable _python_​UUID Undocumented
_pg_types =

Undocumented

_python_UUID =

Undocumented