In SQLAlchemy Core, how does bindparam() let one update() apply per-row values to many rows, and why avoid column names?
answer
- named placeholders, list of dicts
- SET built from parameter keys
- the b_ prefix
- a WHERE without bindparam hits every row
basics
~20 sPut bindparam() placeholders in where() and values(), then pass a list of dicts keyed by those names to execute(). Name them unlike any column, because parameter keys that match columns are added to the SET clause automatically.
solid answer
~40 sWrite `update(products).where(products.c.sku == bindparam('b_sku')).values(price_cents=bindparam('b_price'))` and call `conn.execute(stmt, [{'b_sku': 'SKU-1', 'b_price': 150}, ...])`: one UPDATE, run as an executemany. The names matter because `Insert` and `Update` build their VALUES or SET clause at execution time from parameter keys that match column names. A WHERE placeholder called `sku` collides with the automatic `sku` SET parameter, and SQLAlchemy raises `CompileError` ("bindparam() name 'sku' is reserved for automatic usage in the VALUES or SET clause"), suggesting `b_sku`. Two quieter traps: an extra key named like a column, such as `active`, is added to SET; and a statement whose WHERE has no placeholder updates the same rows for every dict. insertmanyvalues does not batch UPDATEs.
code
python · 32 linesfrom sqlalchemy import (Column, Integer, MetaData, String, Table, bindparam,
create_engine, insert, update)
metadata = MetaData()
products = Table(
"products", metadata,
Column("id", Integer, primary_key=True),
Column("sku", String(32), unique=True, nullable=False),
Column("price_cents", Integer, nullable=False),
)
engine = create_engine("sqlite://")
metadata.create_all(engine)
with engine.begin() as conn:
conn.execute(insert(products), [
{"sku": "SKU-1", "price_cents": 100},
{"sku": "SKU-2", "price_cents": 200},
])
stmt = (
update(products)
.where(products.c.sku == bindparam("b_sku"))
.values(price_cents=bindparam("b_price"))
)
print(stmt)
engine.echo = True
with engine.begin() as conn:
conn.execute(stmt, [
{"b_sku": "SKU-1", "b_price": 150},
{"b_sku": "SKU-2", "b_price": 250},
])go deeper
Recall that bindparam() creates a named placeholder and that a list of dicts keyed by those names runs one UPDATE many times.
Explain how parameter keys that match columns join the SET clause at execution time, which is why WHERE placeholders need names like b_sku.
Show you review executemany updates for missing WHERE placeholders and stray column-named keys, and know rowcount will not confirm the result.
Decide when per-row executemany updates are acceptable and when a set-based statement, such as UPDATE ... FROM a Values list on PostgreSQL, should be the team default.
## An executemany UPDATE `bindparam(name)` creates a **named bound parameter**: a placeholder whose value is supplied when the statement runs. Putting placeholders where literal values would go lets one compiled `Update` serve many parameter sets: ```python stmt = ( update(products) .where(products.c.sku == bindparam("b_sku")) .values(price_cents=bindparam("b_price")) ) conn.execute(stmt, [ {"b_sku": "SKU-1", "b_price": 150}, {"b_sku": "SKU-2", "b_price": 250}, ]) ``` SQLAlchemy compiles `UPDATE products SET price_cents=? WHERE products.sku = ?` once and passes all parameter sets to the driver's `cursor.executemany()`. The engine log shows a single statement followed by a list of tuples. ## How the SET clause is formed at execution time `Insert` and `Update` do not only use what `values()` fixed. At execution, SQLAlchemy also looks at the **parameter keys** you pass: - A key that matches a column name becomes a SET (or VALUES) entry for that column. - That is why `conn.execute(update(products).where(products.c.sku == bindparam("b_sku")), [{"b_sku": "SKU-1", "price_cents": 150}])` works without any `values()` call. - It also means an **extra** column-named key is not ignored. Passing `{"b_sku": "SKU-1", "b_price": 150, "active": False}` to the statement above adds `active=?` to the SET clause and deactivates the product. ## The reserved-name collision If a `bindparam()` outside the SET clause is named after a column and that name also arrives as a parameter key, SQLAlchemy cannot tell the WHERE placeholder from the automatic SET parameter. It raises `CompileError` with this message: "bindparam() name 'sku' is reserved for automatic usage in the VALUES or SET clause of this insert/update statement. Please use a name other than column name when using bindparam() with insert() or update() (for example, 'b_sku')." The tutorial's own example avoids the issue with names such as `oldname` and `newname`; a `b_` prefix is the convention the error message proposes. A `bindparam()` used inside `values()` for its own column, such as `values(price_cents=bindparam("price_cents"))`, does not collide, but distinct names everywhere keep the rule simple. ## Seeing the SET clause before it runs Because the SET clause depends on the keys passed at execution, `print(stmt)` alone can mislead. `Update.compile()` accepts a `column_keys` list that simulates those keys: ```python stmt = update(products).where(products.c.id == bindparam("b_id")).values( price_cents=bindparam("b_price") ) print(stmt.compile(column_keys=["sku", "b_id", "b_price"])) # UPDATE products SET sku=:sku, price_cents=:b_price WHERE products.id = :b_id ``` The extra `sku` key shows up as a SET entry, exactly what would happen at execution. This is a quick way to review a job whose parameter dicts are built far from the statement. ## Two traps in review 1. **No placeholder in WHERE.** `update(products).values(price_cents=bindparam("b_price"))` run with two dicts updates **every** row twice; the last dict wins. Each dict only changes which values are bound, so per-row targeting needs a placeholder in WHERE. 2. **Unreliable counts.** `CursorResult.rowcount` after an executemany is usually -1, because most drivers do not sum per-set counts. ## Building the parameter dicts Stray SET columns and name collisions start where the dicts are built: - Map each input record to exactly the placeholder names the statement uses, for example `{"b_sku": rec.sku, "b_price": rec.price_cents}`. - Avoid passing a whole record dict straight through: any field that happens to share a column's name becomes a SET entry. - Keep the placeholder names next to the statement, in one function, so a rename on one side cannot silently drift from the other. ## Cost and alternatives | Approach | Statements sent | Notes | |---|---|---| | executemany UPDATE with `bindparam()` | one statement, run per parameter set by the driver | works on every backend | | loop of `conn.execute()` calls | one per row, each with Python overhead | slowest | | PostgreSQL `UPDATE ... FROM` a `Values` construct | one | `Values(...).data([...])` joined in WHERE; backend-specific | The insertmanyvalues feature rewrites **INSERT** statements only, so it does not reduce round trips for an executemany UPDATE. For the importer's price corrections, the executemany form is usually enough; for very large sets on PostgreSQL, the `Values` construct shown in the update tutorial sends one statement.
- Why would the price-correction job silently deactivate products if its dicts carried an active key?At execution SQLAlchemy turns every parameter key that matches a column, and is not a placeholder name, into a SET entry. An `active` key therefore adds `active=?` to the UPDATE. Build parameter dicts with only the placeholder names the statement uses, and review the SQL log for unexpected SET columns.
- Does adding returning() let this executemany UPDATE be batched by insertmanyvalues?No. insertmanyvalues rewrites INSERT statements only, and asking `returning()` for `sort_by_parameter_order=True` on an UPDATE raises `ArgumentError`. An executemany UPDATE goes through the driver's `executemany()`; for one round trip on PostgreSQL, join a `Values` construct in an `UPDATE ... FROM` instead.
- bindparam() names can freely match column names in an executemany UPDATE.
- An executemany update() with no placeholder in WHERE still updates one row per dict.
- An extra column-named key in the parameter dicts is ignored by update().
- insertmanyvalues batches executemany UPDATE statements into one round trip.
- rowcount after an executemany UPDATE is the summed total on every driver.