query_multiple_async
query_multiple_async can execute multiple queries with the same cursor and serialize the results. This method
will throw a ValueError if you don't supply the same number of queries and models, or if a tuple of mapper functions
does not match the number of queries. A single mapper function applies to every result set.
Parameters🔗
All command methods also accept keyword-only options=; see Command options.
| name | type | description | optional | default |
|---|---|---|---|---|
| queries | tuple[str, ...] |
the sql query strings to execute in order |  |
|
| params | ParamType |
params to substitute in the query |  |
None |
| models | tuple[Any, ...] |
callables to serialize each result set; each callable must accept column names as kwargs. | |
dict |
| mapper | Callable[[RawRow], Any] or tuple |
one mapper for every result set, or a tuple of mapper functions. Mutually exclusive with models. |
|
None |
param= remains accepted as a 1.x compatibility alias for params=. Pass only one of the two names.
Parameter Shapes🔗
params accepts one parameter record: a mapping, mapping subclass, mutable mapping, or object/dataclass with attributes
matching the placeholder names. Top-level list params are only for execute and execute_async; read and scalar methods
raise InvalidParameterShapeException for top-level lists before opening a cursor.
params=None, param=None, or omitting both names means there is no parameter object. If the SQL contains pydapper
placeholders such as ?id?, every referenced placeholder must be supplied or pydapper raises
MissingParameterException before calling the DBAPI. An empty mapping is a real parameter record with no keys. A list
inside one parameter record, such as {"ids": []} or {"ids": [1, 2, 3]}, is one value and is reserved for future IN
list expansion support.
Tuple-query APIs (query_multiple and query_multiple_async) apply this validation to the complete tuple: the
placeholders of every query are scanned and every referenced value is resolved before a cursor is acquired or the
first query executes, so a missing parameter in any query means no query reaches the database. This is client-side
prevalidation, not transaction atomicity; after validation succeeds, a later query can still fail at runtime after
earlier queries have executed.
One Statement Per Call🔗
A pydapper command executes exactly one SQL statement. SQL containing more than one statement raises
MultipleStatementsError before a cursor is acquired, so nothing reaches the driver — the refusal is the same on every
adapter regardless of what that adapter's DBAPI would have done with the text.
A single trailing ; followed only by whitespace and comments terminates one statement and is accepted. Anything else
after a top-level ; — including a second ; — is more than one statement and is refused.
Detection is the same lexical scan pydapper already uses to find ?name? placeholders, so a ; inside single-quoted
text, double-quoted text, a -- line comment, or a /* ... */ block comment is ordinary text and does not trip the
guard. It is not a SQL parser and has no dialect awareness: a top-level ; inside a PostgreSQL dollar-quoted body
(DO $$ ... ; ... $$) or an Oracle anonymous PL/SQL block (BEGIN ... ; ... END;) is refused too.
Known limits🔗
Because it is a lexer and not a parser, the guard is not a security boundary on its own — it is a guard rail. It keys
on a top-level ;, and its notion of "literal" and "comment" is one fixed ANSI-flavored set rather than each server's
own grammar. Two consequences worth knowing:
- A backslash before a closing quote currently hides a separator. The scanner treats
\as a string escape, which is MySQL's rule but not PostgreSQL's, SQLite's, Oracle's, or SQL Server's. On those,select 'a\'; drop table tis genuinely two statements and the guard does not catch it. This is the most important of the known gaps and is the headline item of #590. - SQL Server does not need a
;at all.select 1 as a insert into t values (1)is two statements in one T-SQL batch with no separator, and nothing lexical can see that. Detecting it needs a real parser, which pydapper deliberately does not have. - Dialect-specific literal forms are not recognized yet, so a
;inside a MySQL backtick identifier, a T-SQL bracket identifier, a MySQL#comment, or some BigQuery triple-quoted strings is currently misread — in either direction. Also tracked in #590.
Parameter binding, not this guard, is what actually protects you from injection: pydapper never formats a value into the SQL string.
Run scripts, PL/SQL blocks, and anything else that genuinely needs more than one statement against the DBAPI connection directly:
with pydapper.connect(dsn) as commands:
cursor = commands.connection.cursor()
cursor.execute(plsql_block)
Rows🔗
When rows are returned as dictionaries, they are insertion-ordered dict[str, Any] values. Key order follows the column order reported by the DB-API cursor.
Column names must be unique exactly as the driver reports them. If a result includes duplicate names, pydapper raises DuplicateColumnException with columns, duplicate_columns, and duplicate_indexes attributes. Alias joined columns instead of using ambiguous select * joins.
Missing keys on returned dict rows raise normal Python KeyError. When custom model construction is requested with model= or models=, pydapper uses the same column-name keyword argument mapping path for results with unique column names.
Use mapper= when column-name mapping is too restrictive. The mapper receives a RawRow with columns, values, and as_dict(). RawRow preserves duplicate column names and positional values in cursor order, so mapper functions can intentionally project joined rows, nested objects, aliases, or duplicate names. Positional indexing and slicing read from values; name indexing and RawRow.as_dict() require unique column names and raise DuplicateColumnException when a dict would be ambiguous.
Batch validation and atomicity🔗
Every query in the tuple is validated client-side before any database work: placeholders are scanned and every
referenced parameter value is resolved for the complete tuple before a cursor is acquired or the first query
executes. A missing parameter in any query raises MissingParameterException and no query reaches the database.
This is client-side prevalidation, not transaction atomicity. Once execution begins, the queries run sequentially on one cursor, and a later query can still fail at runtime (a driver error, no results, duplicate columns, or a mapper error) after earlier queries have executed. pydapper does not roll back or undo earlier queries in the tuple.
Example🔗
Query two tables and return the serialized results.
import asyncio
import datetime
from dataclasses import dataclass
from pydapper import connect_async
@dataclass
class Owner:
id: int
name: str
@dataclass
class Task:
id: int
description: str
due_date: datetime.date
owner_id: int
async def main():
async with connect_async() as commands:
task, owner = await commands.query_multiple_async(
("select * from task limit 1", "select * from owner limit 1"), models=(Task, Owner)
)
print(task)
# [Task(id=1, description='Set up a test database', due_date=datetime.date(2021, 12, 31), owner_id=1)]
print(owner)
# [Owner(id=1, name='Zach Schumacher')]
asyncio.run(main())
Example - Mapper Functions🔗
Project each result set with RawRow mapper functions.
import asyncio
from pydapper import RawRow
from pydapper import connect_async
def to_task_description(row: RawRow) -> str:
return row["description"]
def to_owner_name(row: RawRow) -> str:
return row["name"]
async def main():
async with connect_async() as commands:
task_descriptions, owner_names = await commands.query_multiple_async(
("select description from task limit 1", "select name from owner limit 1"),
mapper=(to_task_description, to_owner_name),
)
print(task_descriptions)
# ['Set up a test database']
print(owner_names)
# ['Zach Schumacher']
asyncio.run(main())