In SQLAlchemy Core, how do you insert one row with insert().values() versus many rows passed as a list to Connection.execute()?
answer
- fixed values versus execution parameters
- a list of dicts means executemany
- the first dictionary picks the columns
- inserted_primary_key is single-row only
basics
~20 sFor one row, build insert(table).values(col=value) and execute it. For many rows, execute a bare insert(table) with a list of dictionaries: SQLAlchemy compiles one INSERT from the first dictionary's keys and runs it as an executemany.
solid answer
~40 s`insert(products).values(sku=..., name=...)` fixes one parameter set into the statement; `conn.execute(stmt)` runs it and `result.inserted_primary_key` gives the new key as a `Row`. For a batch you normally skip `values()` and call `conn.execute(insert(products), rows)` with a list of dicts. That is **executemany** execution: SQLAlchemy builds the VALUES clause from the keys of the **first** dictionary only and sends every parameter set against that one statement. A key that only later dicts carry is silently left out, and a later dict missing a key raises `StatementError` naming the parameter group. `values([...])` with a list is different again: one multi-row VALUES statement with a bound parameter per value, not the executemany path. Run either inside `with engine.begin() as conn:` so the work commits.
code
python · 36 linesfrom sqlalchemy import (Column, Integer, MetaData, String, Table,
create_engine, insert, select)
metadata = MetaData()
products = Table(
"products", metadata,
Column("id", Integer, primary_key=True),
Column("sku", String(32), unique=True, nullable=False),
Column("name", String(200), nullable=False),
Column("price_cents", Integer),
)
engine = create_engine("sqlite://")
metadata.create_all(engine)
with engine.begin() as conn:
# One row: values() fixes the parameters into the statement.
result = conn.execute(
insert(products).values(sku="SKU-1", name="Kettle", price_cents=2990)
)
print(result.inserted_primary_key) # (1,)
# Many rows: a list of dicts runs one INSERT as an executemany.
conn.execute(
insert(products),
[
{"sku": "SKU-2", "name": "Toaster"},
{"sku": "SKU-3", "name": "Blender", "price_cents": 5900},
],
)
# The first dict had no price_cents, so the column was left out
# of the VALUES clause and 5900 was never sent.
print(conn.execute(
select(products.c.sku, products.c.price_cents).order_by(products.c.id)
).all())
# [('SKU-1', 2990), ('SKU-2', None), ('SKU-3', None)]go deeper
Recall the two shapes: values() for one fixed row, a list of dicts passed to execute() for many. Know that inserted_primary_key gives the new key for a single row.
Explain that the first dictionary alone decides the VALUES clause, what happens to extra and missing keys, and why values() with a list is a different statement from executemany.
Show you guard a batch loader: normalise dict keys before the call, use returning() rather than inserted_primary_key for many rows, and read the SQL log to confirm one statement per batch.
Frame the team convention: batch writes go through one execute() call with uniform parameter dicts, and the loader owns key normalisation so silent column drops cannot reach production data.
## Two places an INSERT can get its values `insert(table)` returns an `Insert` construct, SQLAlchemy Core's object for an SQL INSERT against a `Table`. It can receive the row data in two different places: - **Fixed into the statement** with `Insert.values()`, which takes keyword arguments or a dict keyed by column name. - **Supplied at execution time** as the second argument of `Connection.execute()`: one dict for one row, or a list of dicts for many rows. When `values()` was never called, SQLAlchemy compiles the VALUES clause from the parameter keys handed to `execute()`. That is why a bare `insert(products)` is the usual starting point for a batch load, such as a nightly importer that writes 50,000 product rows. ## One row: `values()` and `inserted_primary_key` ```python with engine.begin() as conn: result = conn.execute(insert(products).values(sku="SKU-1", name="Kettle")) new_id = result.inserted_primary_key[0] ``` `execute()` returns a `CursorResult`. Its `inserted_primary_key` attribute is a `Row` holding the complete primary key of the new row, a tuple-like value because a primary key may span several columns. It applies only to a **single-row** INSERT that has no explicit `returning()`. After an executemany call it raises `InvalidRequestError` ("This statement was an executemany call; if primary key returning is supported, please use .inserted_primary_key_rows."), and after `returning()` you read the returned rows instead. ## Many rows: a list of dictionaries `conn.execute(insert(products), rows)`, where `rows` is a list of dicts, is **executemany** execution. What happens: 1. SQLAlchemy reads the keys of the **first** dictionary and compiles one statement, for example `INSERT INTO products (sku, name) VALUES (?, ?)`. 2. It does not scan the remaining dictionaries; the tutorial says it avoids that overhead on purpose. 3. On most dialects the parameter sets go to the driver's `cursor.executemany()`. When the INSERT carries `returning()`, SQLAlchemy's insertmanyvalues feature takes over and sends batched multi-row statements instead, so rows can come back. The SQL log shows the statement once, followed by a list of parameter tuples. For the importer, that is one Python call instead of 50,000. ## The first dictionary decides the columns Because only the first dict is read, uneven dictionaries misbehave in two different ways: - A key that appears **only in later dictionaries** is silently dropped. If the first product has no `price_cents` and the second has 5900, the column is missing from the VALUES clause and 5900 is never sent. - A key the first dict has but a **later dict lacks** stops the call with `StatementError` wrapping `InvalidRequestError`: "A value is required for bind parameter 'price_cents', in parameter group 1". - The cure is to normalise every dict to the same keys before the call, writing `None` explicitly where a value is absent. The ORM-enabled INSERT that runs through a `Session` does scan every parameter set; Core's does not. ## `values()` with a list is not executemany `Insert.values()` also accepts a list of dicts. That renders a single **multi-row VALUES** statement whose parameters are numbered per row (`:sku_m0`, `:sku_m1`, ...). The `values()` docstring warns that this is **not** the same as executemany and that passing the list to `execute()` is the normal, generally more efficient way to insert many rows. | Form | What is sent | Typical use | |---|---|---| | `conn.execute(insert(t).values(sku="A"))` | one statement, one parameter set | a single row | | `conn.execute(insert(t), [dict, dict, ...])` | one compiled statement, executemany (or insertmanyvalues batches with RETURNING) | batch loads | | `conn.execute(insert(t).values([dict, dict]))` | one statement holding every row, a parameter per value | a small, fixed set of rows | ## Committing the work Since SQLAlchemy 2.0 a `Connection` never commits on its own. Use `with engine.begin() as conn:`, which commits when the block exits without an error, or call `conn.commit()` on a connection opened with `engine.connect()`. An INSERT run on `engine.connect()` without a commit is rolled back when the connection closes. ## What to check when reviewing a batch insert - **One call per batch.** A Python loop around `conn.execute(insert(...).values(...))` sends one statement per row; the list form sends one compiled statement for the whole list. - **Uniform keys.** Every dict carries the same keys as the first one, including explicit `None` values, so nothing is dropped and no parameter group fails. - **Keys, not ids, for follow-up work.** If later code needs the new primary keys, the INSERT asks for them with `returning()` instead of reading `inserted_primary_key`. - **The log agrees.** With `echo=True`, a healthy batch shows one INSERT followed by a list of parameter tuples; one INSERT line per row means the loop is still there. - **Key order does not matter.** Dict keys are matched to columns by name, so the order of keys inside each dict has no effect on the statement.
- How do you get the new primary keys back from a 50,000-row Core INSERT?Add `returning(products.c.id, products.c.sku)` to the `insert()` and pass the list of dicts to `execute()`; iterate the result to read one row per inserted product. `inserted_primary_key` cannot help, because it raises `InvalidRequestError` after an executemany call. On backends with RETURNING, SQLAlchemy's insertmanyvalues feature batches the statement so the rows can be fetched.
- Why does a later dictionary missing a key fail loudly while an extra key passes silently?The compiled statement has a bound parameter for every key in the first dict. A later dict without one of those keys leaves a parameter unfilled, so SQLAlchemy raises `StatementError` naming the parameter group. An extra key in a later dict has no placeholder at all, so nothing reads it and it is quietly ignored.
- Calling conn.execute() once per row in a Python loop is how Core inserts a batch.
- SQLAlchemy scans every dictionary in the list to build the INSERT's column list.
- insert().values(list_of_dicts) runs the same executemany as passing the list to execute().
- inserted_primary_key returns every new id after an executemany INSERT.
- Connection.execute() commits an INSERT automatically.