Skip to content

asyncpg

Native execution adapter for asyncpg (postgresql+asyncpg:// URLs).

AsyncpgAdapter

Bases: DriverAdapter

Runs bound statements with asyncpg's own API, which takes $1-style SQL and positional arguments::

Text Only
1
await driver.fetch("SELECT users.id FROM users WHERE users.id = $1", 5)

asyncpg prepares and caches statements by SQL text on its own. It does not report result column types on fetch, so prepare() is called once per plan only to read them (see resolve_column_types).

asyncpg's executemany runs in an implicit transaction when none is open, so a failing row leaves no rows inserted (atomic_execute_many).

Source code in ormar/databases/native/adapters/asyncpg.py
Python
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
class AsyncpgAdapter(DriverAdapter):
    """
    Runs bound statements with asyncpg's own API, which takes ``$1``-style SQL
    and positional arguments::

        await driver.fetch("SELECT users.id FROM users WHERE users.id = $1", 5)

    asyncpg prepares and caches statements by SQL text on its own. It does
    not report result column types on ``fetch``, so ``prepare()`` is called
    once per plan only to read them (see ``resolve_column_types``).

    asyncpg's ``executemany`` runs in an implicit transaction when none is
    open, so a failing row leaves no rows inserted (``atomic_execute_many``).
    """

    atomic_execute_many = True

    def __init__(self, dialect: Dialect) -> None:
        """
        :param dialect: dialect of the connected database
        :type dialect: Dialect
        """
        super().__init__(dialect=dialect)
        self.driver_errors = tuple(
            dialect.dbapi._asyncpg_error_translate  # type: ignore[union-attr]
        )

    async def fetch_all(
        self, raw: Any, bound: BoundStatement, as_tuples: bool = False
    ) -> List[Any]:
        """
        Run a statement with ``fetch`` and turn asyncpg's ``Record`` rows into
        rows keyed by result label.

        :param raw: pooled raw connection
        :type raw: Any
        :param bound: statement to execute
        :type bound: BoundStatement
        :param as_tuples: return tuples of values in select order instead
        :type as_tuples: bool
        :return: rows
        :rtype: List[Any]
        """
        driver = raw.driver_connection
        async with execute_mutex(raw=raw):
            await self.resolve_column_types(driver=driver, bound=bound)
            rows = await driver.fetch(bound.sql, *bound.args)
        convert = row_to_tuple if as_tuples else row_to_dict
        return [convert(plan=bound.plan, values=row) for row in rows]

    async def execute(self, raw: Any, bound: BoundStatement) -> Any:
        """
        Run a write statement. An INSERT with ``RETURNING`` reads the returned pk
        with ``fetchrow``; other statements use ``execute``, whose status string
        carries the row count::

            await driver.execute("DELETE FROM users")  # -> "DELETE 3" -> 3

        :param raw: pooled raw connection
        :type raw: Any
        :param bound: statement to execute
        :type bound: BoundStatement
        :return: inserted primary key for INSERT, row count otherwise
        :rtype: Any
        """
        driver = raw.driver_connection
        async with execute_mutex(raw=raw):
            if bound.plan.is_insert and bound.plan.returning:
                await self.resolve_column_types(driver=driver, bound=bound)
                row = await driver.fetchrow(bound.sql, *bound.args)
                return inserted_primary_key(
                    bound=bound,
                    returned=first_value(bound=bound, row=row),
                    lastrowid=None,
                )
            status = await driver.execute(bound.sql, *bound.args)
        if bound.plan.is_insert:
            return inserted_primary_key(bound=bound, returned=None, lastrowid=None)
        return int(status.rsplit(" ", 1)[-1])

    async def execute_many(self, raw: Any, batch: BoundBatch) -> None:
        """
        Run a statement once per row with asyncpg's ``executemany``, which sends
        all rows in one pipelined call and is atomic on its own.

        :param raw: pooled raw connection
        :type raw: Any
        :param batch: statement and arguments per row
        :type batch: BoundBatch
        """
        async with execute_mutex(raw=raw):
            await raw.driver_connection.executemany(
                command=batch.sql, args=batch.args_list
            )

    async def stream(
        self, raw: Any, bound: BoundStatement, batch_size: int
    ) -> AsyncGenerator[Dict[str, Any], None]:
        """
        Stream rows through a PostgreSQL server-side cursor, fetching
        ``batch_size`` rows at a time; PostgreSQL only allows such cursors
        inside a transaction, which ``QuerySet.iterate()`` opens.

        The execution lock is held per fetch only, so a query issued inside
        the ``async for`` loop on the same connection can run between
        batches instead of waiting forever for the lock.

        :param raw: pooled raw connection
        :type raw: Any
        :param bound: statement to execute
        :type bound: BoundStatement
        :param batch_size: rows fetched per round trip
        :type batch_size: int
        :return: rows keyed by result label
        :rtype: AsyncGenerator[Dict[str, Any], None]
        """
        driver = raw.driver_connection
        async with execute_mutex(raw=raw):
            await self.resolve_column_types(driver=driver, bound=bound)
            cursor = await driver.cursor(bound.sql, *bound.args)
        while True:
            async with execute_mutex(raw=raw):
                rows = await cursor.fetch(n=batch_size)
            if not rows:
                return
            for row in rows:
                yield row_to_dict(plan=bound.plan, values=row)

    async def start_transaction(self, sa_connection: AsyncConnection) -> None:
        """
        Send ``BEGIN`` now for a transaction SQLAlchemy began.

        SQLAlchemy's asyncpg wrapper only marks the transaction as begun and
        sends ``BEGIN`` with its own first statement (no ``_transaction`` yet).
        Native statements skip the wrapper, so without this they would run
        and commit on their own, outside the transaction.

        :param sa_connection: connection the transaction was begun on
        :type sa_connection: AsyncConnection
        """
        adapted = (await sa_connection.get_raw_connection()).dbapi_connection
        if adapted._transaction is None:  # type: ignore[union-attr]
            await adapted._start_transaction()  # type: ignore[union-attr]

    def translate_error(
        self, error: BaseException, dbapi_connection: Any
    ) -> BaseException:
        """
        Map an asyncpg exception to the DBAPI class SQLAlchemy's asyncpg
        wrapper uses, e.g. ``UniqueViolationError`` to its ``IntegrityError``.
        The wrapper's ``_handle_exception`` raises the mapped exception, so it
        is caught and returned.

        :param error: exception raised by asyncpg
        :type error: BaseException
        :param dbapi_connection: SQLAlchemy's adapted asyncpg connection
        :type dbapi_connection: Any
        :return: DBAPI-level exception
        :rtype: BaseException
        """
        try:
            dbapi_connection._handle_exception(error=error)
        except Exception as translated:
            return translated
        return error  # pragma: no cover

    async def resolve_column_types(self, driver: Any, bound: BoundStatement) -> None:
        """
        On a plan's first execution, read its result column types from
        ``prepare()`` and look up its result processors.

        asyncpg reports types as PostgreSQL type OIDs (e.g. ``1700`` for
        ``NUMERIC``), the same codes SQLAlchemy's own path passes to the
        types, so both paths pick the same processors.

        :param driver: asyncpg connection
        :type driver: Any
        :param bound: statement being executed
        :type bound: BoundStatement
        """
        if bound.plan.result_processors is None:
            prepared = await driver.prepare(query=bound.sql)
            self.resolve_processors(
                bound=bound,
                coltypes=[attr.type.oid for attr in prepared.get_attributes()],
            )

__init__(dialect)

Parameters:

Name Type Description Default
dialect Dialect

dialect of the connected database

required
Source code in ormar/databases/native/adapters/asyncpg.py
Python
41
42
43
44
45
46
47
48
49
def __init__(self, dialect: Dialect) -> None:
    """
    :param dialect: dialect of the connected database
    :type dialect: Dialect
    """
    super().__init__(dialect=dialect)
    self.driver_errors = tuple(
        dialect.dbapi._asyncpg_error_translate  # type: ignore[union-attr]
    )

execute(raw, bound) async

Run a write statement. An INSERT with RETURNING reads the returned pk with fetchrow; other statements use execute, whose status string carries the row count::

Text Only
1
await driver.execute("DELETE FROM users")  # -> "DELETE 3" -> 3

Parameters:

Name Type Description Default
raw Any

pooled raw connection

required
bound BoundStatement

statement to execute

required

Returns:

Type Description
Any

inserted primary key for INSERT, row count otherwise

Source code in ormar/databases/native/adapters/asyncpg.py
Python
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
async def execute(self, raw: Any, bound: BoundStatement) -> Any:
    """
    Run a write statement. An INSERT with ``RETURNING`` reads the returned pk
    with ``fetchrow``; other statements use ``execute``, whose status string
    carries the row count::

        await driver.execute("DELETE FROM users")  # -> "DELETE 3" -> 3

    :param raw: pooled raw connection
    :type raw: Any
    :param bound: statement to execute
    :type bound: BoundStatement
    :return: inserted primary key for INSERT, row count otherwise
    :rtype: Any
    """
    driver = raw.driver_connection
    async with execute_mutex(raw=raw):
        if bound.plan.is_insert and bound.plan.returning:
            await self.resolve_column_types(driver=driver, bound=bound)
            row = await driver.fetchrow(bound.sql, *bound.args)
            return inserted_primary_key(
                bound=bound,
                returned=first_value(bound=bound, row=row),
                lastrowid=None,
            )
        status = await driver.execute(bound.sql, *bound.args)
    if bound.plan.is_insert:
        return inserted_primary_key(bound=bound, returned=None, lastrowid=None)
    return int(status.rsplit(" ", 1)[-1])

execute_many(raw, batch) async

Run a statement once per row with asyncpg's executemany, which sends all rows in one pipelined call and is atomic on its own.

Parameters:

Name Type Description Default
raw Any

pooled raw connection

required
batch BoundBatch

statement and arguments per row

required
Source code in ormar/databases/native/adapters/asyncpg.py
Python
104
105
106
107
108
109
110
111
112
113
114
115
116
117
async def execute_many(self, raw: Any, batch: BoundBatch) -> None:
    """
    Run a statement once per row with asyncpg's ``executemany``, which sends
    all rows in one pipelined call and is atomic on its own.

    :param raw: pooled raw connection
    :type raw: Any
    :param batch: statement and arguments per row
    :type batch: BoundBatch
    """
    async with execute_mutex(raw=raw):
        await raw.driver_connection.executemany(
            command=batch.sql, args=batch.args_list
        )

fetch_all(raw, bound, as_tuples=False) async

Run a statement with fetch and turn asyncpg's Record rows into rows keyed by result label.

Parameters:

Name Type Description Default
raw Any

pooled raw connection

required
bound BoundStatement

statement to execute

required
as_tuples bool

return tuples of values in select order instead

False

Returns:

Type Description
List[Any]

rows

Source code in ormar/databases/native/adapters/asyncpg.py
Python
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
async def fetch_all(
    self, raw: Any, bound: BoundStatement, as_tuples: bool = False
) -> List[Any]:
    """
    Run a statement with ``fetch`` and turn asyncpg's ``Record`` rows into
    rows keyed by result label.

    :param raw: pooled raw connection
    :type raw: Any
    :param bound: statement to execute
    :type bound: BoundStatement
    :param as_tuples: return tuples of values in select order instead
    :type as_tuples: bool
    :return: rows
    :rtype: List[Any]
    """
    driver = raw.driver_connection
    async with execute_mutex(raw=raw):
        await self.resolve_column_types(driver=driver, bound=bound)
        rows = await driver.fetch(bound.sql, *bound.args)
    convert = row_to_tuple if as_tuples else row_to_dict
    return [convert(plan=bound.plan, values=row) for row in rows]

resolve_column_types(driver, bound) async

On a plan's first execution, read its result column types from prepare() and look up its result processors.

asyncpg reports types as PostgreSQL type OIDs (e.g. 1700 for NUMERIC), the same codes SQLAlchemy's own path passes to the types, so both paths pick the same processors.

Parameters:

Name Type Description Default
driver Any

asyncpg connection

required
bound BoundStatement

statement being executed

required
Source code in ormar/databases/native/adapters/asyncpg.py
Python
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
async def resolve_column_types(self, driver: Any, bound: BoundStatement) -> None:
    """
    On a plan's first execution, read its result column types from
    ``prepare()`` and look up its result processors.

    asyncpg reports types as PostgreSQL type OIDs (e.g. ``1700`` for
    ``NUMERIC``), the same codes SQLAlchemy's own path passes to the
    types, so both paths pick the same processors.

    :param driver: asyncpg connection
    :type driver: Any
    :param bound: statement being executed
    :type bound: BoundStatement
    """
    if bound.plan.result_processors is None:
        prepared = await driver.prepare(query=bound.sql)
        self.resolve_processors(
            bound=bound,
            coltypes=[attr.type.oid for attr in prepared.get_attributes()],
        )

start_transaction(sa_connection) async

Send BEGIN now for a transaction SQLAlchemy began.

SQLAlchemy's asyncpg wrapper only marks the transaction as begun and sends BEGIN with its own first statement (no _transaction yet). Native statements skip the wrapper, so without this they would run and commit on their own, outside the transaction.

Parameters:

Name Type Description Default
sa_connection AsyncConnection

connection the transaction was begun on

required
Source code in ormar/databases/native/adapters/asyncpg.py
Python
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
async def start_transaction(self, sa_connection: AsyncConnection) -> None:
    """
    Send ``BEGIN`` now for a transaction SQLAlchemy began.

    SQLAlchemy's asyncpg wrapper only marks the transaction as begun and
    sends ``BEGIN`` with its own first statement (no ``_transaction`` yet).
    Native statements skip the wrapper, so without this they would run
    and commit on their own, outside the transaction.

    :param sa_connection: connection the transaction was begun on
    :type sa_connection: AsyncConnection
    """
    adapted = (await sa_connection.get_raw_connection()).dbapi_connection
    if adapted._transaction is None:  # type: ignore[union-attr]
        await adapted._start_transaction()  # type: ignore[union-attr]

stream(raw, bound, batch_size) async

Stream rows through a PostgreSQL server-side cursor, fetching batch_size rows at a time; PostgreSQL only allows such cursors inside a transaction, which QuerySet.iterate() opens.

The execution lock is held per fetch only, so a query issued inside the async for loop on the same connection can run between batches instead of waiting forever for the lock.

Parameters:

Name Type Description Default
raw Any

pooled raw connection

required
bound BoundStatement

statement to execute

required
batch_size int

rows fetched per round trip

required

Returns:

Type Description
AsyncGenerator[Dict[str, Any], None]

rows keyed by result label

Source code in ormar/databases/native/adapters/asyncpg.py
Python
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
async def stream(
    self, raw: Any, bound: BoundStatement, batch_size: int
) -> AsyncGenerator[Dict[str, Any], None]:
    """
    Stream rows through a PostgreSQL server-side cursor, fetching
    ``batch_size`` rows at a time; PostgreSQL only allows such cursors
    inside a transaction, which ``QuerySet.iterate()`` opens.

    The execution lock is held per fetch only, so a query issued inside
    the ``async for`` loop on the same connection can run between
    batches instead of waiting forever for the lock.

    :param raw: pooled raw connection
    :type raw: Any
    :param bound: statement to execute
    :type bound: BoundStatement
    :param batch_size: rows fetched per round trip
    :type batch_size: int
    :return: rows keyed by result label
    :rtype: AsyncGenerator[Dict[str, Any], None]
    """
    driver = raw.driver_connection
    async with execute_mutex(raw=raw):
        await self.resolve_column_types(driver=driver, bound=bound)
        cursor = await driver.cursor(bound.sql, *bound.args)
    while True:
        async with execute_mutex(raw=raw):
            rows = await cursor.fetch(n=batch_size)
        if not rows:
            return
        for row in rows:
            yield row_to_dict(plan=bound.plan, values=row)

translate_error(error, dbapi_connection)

Map an asyncpg exception to the DBAPI class SQLAlchemy's asyncpg wrapper uses, e.g. UniqueViolationError to its IntegrityError. The wrapper's _handle_exception raises the mapped exception, so it is caught and returned.

Parameters:

Name Type Description Default
error BaseException

exception raised by asyncpg

required
dbapi_connection Any

SQLAlchemy's adapted asyncpg connection

required

Returns:

Type Description
BaseException

DBAPI-level exception

Source code in ormar/databases/native/adapters/asyncpg.py
Python
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
def translate_error(
    self, error: BaseException, dbapi_connection: Any
) -> BaseException:
    """
    Map an asyncpg exception to the DBAPI class SQLAlchemy's asyncpg
    wrapper uses, e.g. ``UniqueViolationError`` to its ``IntegrityError``.
    The wrapper's ``_handle_exception`` raises the mapped exception, so it
    is caught and returned.

    :param error: exception raised by asyncpg
    :type error: BaseException
    :param dbapi_connection: SQLAlchemy's adapted asyncpg connection
    :type dbapi_connection: Any
    :return: DBAPI-level exception
    :rtype: BaseException
    """
    try:
        dbapi_connection._handle_exception(error=error)
    except Exception as translated:
        return translated
    return error  # pragma: no cover