Aiopg

Warnings

• breaking aiopg internally uses `psycopg2-binary` connections in `autocommit=True` mode for asynchronous operations. This means `conn.commit()` and `conn.rollback()` methods are disabled and will raise `psycopg2.ProgrammingError`. Transactions must be explicitly managed by executing `BEGIN` and `COMMIT`/`ROLLBACK` SQL statements manually.
  Fix: Do not call `conn.commit()` or `conn.rollback()`. Instead, explicitly execute SQL: `await cur.execute('BEGIN')`, then `await cur.execute('COMMIT')` or `await cur.execute('ROLLBACK')`.
• gotcha Almost all connection and cursor methods in aiopg are coroutines and *must* be `await`ed. However, `Cursor.mogrify()` specifically had its `await` requirement removed in a later version and should now be called synchronously.
  Fix: Ensure all `aiopg` connection and cursor methods are `await`ed, e.g., `await conn.execute(...)`, `await cur.fetchone()`. For `cur.mogrify()`, do not use `await`.
• deprecated Iteration protocol support in `cursor` and `ResultProxy` was deprecated in version 0.7.0. While still functional for older codebases, relying on direct iteration for results might lead to unexpected behavior or future breakage.
  Fix: Prefer explicit iteration using `async for row in cur:` for cursors, and `await result.fetchall()` or `async for row in result:` for `ResultProxy` objects, rather than synchronous iteration.
• gotcha aiopg requires Python 3.7+ and only supports the `async/await` syntax. Older `asyncio` patterns (e.g., `@asyncio.coroutine` decorators) are not supported.
  Fix: Ensure your project uses Python 3.7 or newer and exclusively uses `async def` and `await` for asynchronous code.
• gotcha When creating a cursor via `conn.cursor()`, some parameters like `name`, `scrollable`, and `withhold` are not supported by `psycopg2-binary` in asynchronous mode and will be ignored or raise errors if specified. Only `cursor_factory` and `timeout` are reliably supported.
  Fix: Avoid passing `name`, `scrollable`, or `withhold` to `conn.cursor()`. If custom cursor behavior is needed, use `cursor_factory`.

Install

• pip install aiopg Core library
• pip install aiopg[sa] With SQLAlchemy support

Imports

• aiopg

  import aiopg

• create_pool

  import aiopg
  await aiopg.create_pool(...)

• create_engine

  from aiopg.sa import create_engine

  For SQLAlchemy integration, create_engine is typically imported directly from aiopg.sa.

Quickstart

This quickstart demonstrates how to establish an asynchronous connection pool, acquire a connection, execute a simple query, and fetch results using aiopg. It includes error handling for already running event loops common in interactive environments. Ensure your PostgreSQL instance is running and accessible with the provided DSN, or set the AIOPG_DSN environment variable.

import asyncio
import aiopg
import os

dsn = os.environ.get('AIOPG_DSN', 'dbname=aiopg user=aiopg password=passwd host=127.0.0.1')

async def main():
    # Establish a connection pool
    async with aiopg.create_pool(dsn) as pool:
        # Acquire a connection from the pool
        async with pool.acquire() as conn:
            # Open a cursor to perform database operations
            async with conn.cursor() as cur:
                # Execute a simple query
                await cur.execute("SELECT 1")
                ret = []
                # Iterate over the results
                async for row in cur:
                    ret.append(row)
                assert ret == [(1,)]
                print(f"Query result: {ret}")

if __name__ == '__main__':
    # For Python 3.10+
    try:
        asyncio.run(main())
    except RuntimeError as e:
        if "cannot run an event loop while another loop is running" in str(e):
            # Handle cases where an event loop might already be running (e.g., in notebooks)
            loop = asyncio.get_event_loop()
            if loop.is_running():
                loop.create_task(main())
            else:
                loop.run_until_complete(main())
        else:
            raise