skip to content

In SQLAlchemy Core, how does bindparam() let one update() apply per-row values to many rows, and why avoid column names?

level: middleimportance: nice to knowfreq 25%

answer

  1. named placeholders, list of dicts
  2. SET built from parameter keys
  3. the b_ prefix
  4. a WHERE without bindparam hits every row

basics

~20 s

Put 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 s

Write `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 lines
python
from 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

for a junior

Recall that bindparam() creates a named placeholder and that a list of dicts keyed by those names runs one UPDATE many times.

for a middle

Explain how parameter keys that match columns join the SET clause at execution time, which is why WHERE placeholders need names like b_sku.

for a senior

Show you review executemany updates for missing WHERE placeholders and stray column-named keys, and know rowcount will not confirm the result.

for a principal

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.