execute
execute can execute a command one or multiple times and return the number of affected rows. This method is usually used
to execute insert, update or delete operations.
execute does not commit
A non-zero return value means the statement affected that many rows, not that the write is durable.
Whether the examples below persist depends entirely on your driver: sqlite3, psycopg2, and psycopg
commit when the connect(...) block exits, but pymssql and oracledb roll uncommitted work back and
mysql-connector-python discards it; BigQuery has no connection-level transaction at all, so every
statement is already durable. See Transactions for the per-driver table
and for commit() / transaction().
Parameters🔗
All command methods also accept keyword-only options=; see Command options.
| name | type | description | optional | default |
|---|---|---|---|---|
| sql | str |
the sql query str to execute |  |
|
| params | ListParamType, ParamType |
params to substitute in the query |  |
None |
param= remains accepted as a 1.x compatibility alias for params=. Pass only one of the two names.
Parameter Shapes🔗
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.
For one execution, pass one parameter record: a mapping, mapping subclass, mutable mapping, or object/dataclass with
attributes matching the placeholder names. Falsey values such as 0, False, "", and [] are bound normally.
For multiple executions, pass a top-level list to execute or execute_async. Each list item is one parameter record.
An empty top-level list runs zero commands and returns 0. A list inside one parameter record, such as
{"ids": []} or {"ids": [1, 2, 3]}, is one value, not executemany input. List expansion for IN clauses is reserved
for future support.
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)
Example - Execute Insert🔗
Single🔗
Execute the INSERT statement a single time.
import datetime
from pydapper import connect
with connect() as commands:
rowcount = commands.execute(
"insert into task (description, due_date, owner_id) values (?description?, ?due_date?, ?owner_id?)",
params={"description": "An insert example", "due_date": datetime.date.today(), "owner_id": 1},
)
print(rowcount)
# 1
Multiple🔗
Execute the INSERT statement multiple times, one for each object in the params list.
import datetime
from pydapper import connect
with connect() as commands:
rowcount = commands.execute(
"insert into task (description, due_date, owner_id) values (?description?, ?due_date?, ?owner_id?)",
params=[
{"description": "An insert example", "due_date": datetime.date.today(), "owner_id": 1},
{"description": "With multiple inserts!", "due_date": datetime.date.today(), "owner_id": 1},
],
)
print(rowcount)
# 2
Example - Execute Update🔗
Single🔗
Execute the UPDATE statement a single time.
from pydapper import connect
with connect() as commands:
rowcount = commands.execute(
"update task set description = ?desc? where id = ?id?", params={"desc": "A single update!", "id": 1}
)
print(rowcount)
# 1
Multiple🔗
Execute the UPDATE statement multiple times, one for each object in the params list.
from pydapper import connect
with connect() as commands:
rowcount = commands.execute(
"update task set description = ?desc? where id = ?id?",
params=[{"desc": "A single update!", "id": 1}, {"desc": "No wait, multiple updates!", "id": 2}],
)
print(rowcount)
# 2
Example - Execute Delete🔗
Single🔗
Execute the DELETE statement a single time.
from pydapper import connect
with connect() as commands:
rowcount = commands.execute("delete from task where id = ?id?", params={"id": 1})
print(rowcount)
# 1
Multiple🔗
Execute the DELETE statement multiple times, one for each object in the params list.
from pydapper import connect
with connect() as commands:
rowcount = commands.execute("delete from task where id = ?id?", params=[{"id": 2}, {"id": 3}])
print(rowcount)
# 2