Frequently Asked Questions — asyncpg Documentation (original) (raw)

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 asasyncpgsa or databases.

Can I use dot-notation with asyncpg.Record? It looks cleaner.

We decided against making asyncpg.Record a named tuple because we want to keep the Record method namespace separate from the column namespace. That said, you can provide a custom Recordclass that implements dot-notation via the record_class argument toconnect() or any of the Record-returning methods.

class MyRecord(asyncpg.Record): def getattr(self, name): return self[name]

Why can’t I use a cursor outside of a transaction?

Cursors created by a call toConnection.cursor() orPreparedStatement.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.

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 exists errors, you are most likely not connecting to the PostgreSQL server directly, but viapgbouncer. 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 connection poolfunctionality provided by asyncpg, it is a much better option for this purpose;
disable automatic use of prepared statements by passingstatement_cache_size=0to asyncpg.connect() andasyncpg.create_pool()(and, obviously, avoid the use ofConnection.prepare());
switch pgbouncer’s pool_mode to session.

Why do I get `PostgresSyntaxError` when using `expression IN $1`?

expression IN <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mi mathvariant="normal">‘</mi><mi>i</mi><mi>s</mi><mi>n</mi><mi>o</mi><mi>t</mi><mi>a</mi><mi>v</mi><mi>a</mi><mi>l</mi><mi>i</mi><mi>d</mi><mi>P</mi><mi>o</mi><mi>s</mi><mi>t</mi><mi>g</mi><mi>r</mi><mi>e</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mi>s</mi><mi>y</mi><mi>n</mi><mi>t</mi><mi>a</mi><mi>x</mi><mi mathvariant="normal">.</mi><mi>T</mi><mi>o</mi><mi>c</mi><mi>h</mi><mi>e</mi><mi>c</mi><mi>k</mi><mi>a</mi><mi>v</mi><mi>a</mi><mi>l</mi><mi>u</mi><mi>e</mi><mi>a</mi><mi>g</mi><mi>a</mi><mi>i</mi><mi>n</mi><mi>s</mi><mi>t</mi><mi>a</mi><mi>s</mi><mi>e</mi><mi>q</mi><mi>u</mi><mi>e</mi><mi>n</mi><mi>c</mi><mi>e</mi><mi>u</mi><mi>s</mi><mi>e</mi><mi mathvariant="normal">‘</mi><mi>e</mi><mi>x</mi><mi>p</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>s</mi><mi>i</mi><mi>o</mi><mi>n</mi><mo>=</mo><mi>a</mi><mi>n</mi><mi>y</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">1 is not a valid PostgreSQL syntax. To check a value against a sequence use expression = any(</annotation></semantics></math>1‘isnotavalidPostgreSQLsyntax.Tocheckavalueagainstasequenceuse‘expression=any(1::mytype[]), where mytype is the array element type.