In SQLAlchemy 2.1, how does the insertmanyvalues feature let an executemany INSERT with returning() send back every new row?
answer
- executemany cannot fetch rows
- rewrite into multi-row VALUES
- pages of 1000 parameter sets
- unordered unless you ask
basics
~10 sBecause a DB-API executemany cannot return rows, SQLAlchemy 2.1 normally rewrites the INSERT ... RETURNING into batched multi-row VALUES statements, runs each with cursor.execute(), and joins their returned rows into one Result.
solid answer
~40 sThe DB-API's `cursor.executemany()` returns no rows, so a list of dicts plus `insert(...).returning(...)` would be stuck. SQLAlchemy's **insertmanyvalues** feature instead renders `INSERT ... VALUES (...), (...), ... RETURNING ...` for a page of parameter sets, runs it with `cursor.execute()`, repeats until the list is used up and concatenates the rows into one `Result`. A page holds at most `insertmanyvalues_page_size` sets (default 1000), further capped by the dialect's parameter limit. It works on SQLite 3.35+, PostgreSQL, SQL Server and MariaDB 10.5+, not MySQL, which lacks RETURNING. The rows are **unordered** unless `returning(..., sort_by_parameter_order=True)` is passed; then SQLAlchemy uses a sentinel-based form or falls back to one INSERT per row. Each batch is logged as `[insertmanyvalues 2/50 (unordered)]`.
code
python · 24 linesfrom sqlalchemy import (Column, Integer, MetaData, String, Table,
create_engine, insert)
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),
)
# A page size of 2 only to make the batches visible; the default is 1000.
engine = create_engine("sqlite://", insertmanyvalues_page_size=2)
metadata.create_all(engine)
engine.echo = True
rows = [{"sku": f"SKU-{n}", "name": f"Product {n}"} for n in range(5)]
with engine.begin() as conn:
result = conn.execute(
insert(products).returning(products.c.id, products.c.sku),
rows,
)
print(result.all())go deeper
Recall that SQLAlchemy can return generated ids from a many-row INSERT when you add returning() and pass a list of dicts to execute().
Explain the rewrite into batched multi-row VALUES statements, the default page size of 1000, the parameter cap, and which backends support it.
Show you read the batch labels in the log, know returned rows are unordered by default, and choose between sort_by_parameter_order and matching on a natural key.
Weigh batch size against statement size and lock time for large nightly loads, and decide whether loaders may depend on ordered RETURNING when some backends degrade to per-row inserts.
## The problem it solves The Python **DB-API** (PEP 249) gives drivers two ways to run a statement: `cursor.execute()` for one parameter set and `cursor.executemany()` for many. `executemany()` cannot hand back rows, and `cursor.lastrowid` describes one row only. So an importer that loads 50,000 products and needs every new `id` could not combine a list of dicts with `insert(products).returning(products.c.id)` without falling back to one statement per row. SQLAlchemy 2.0 introduced **insertmanyvalues**, and 2.1 keeps it. It is transparent: you write the same call and SQLAlchemy chooses how to run it. ## What SQLAlchemy actually sends Given `conn.execute(insert(products).returning(products.c.id, products.c.sku), rows)`: 1. It compiles the ordinary single-row form, `INSERT INTO products (sku, name) VALUES (?, ?) RETURNING id, sku`. 2. It rewrites that into a **batched** form with many VALUES tuples, one per parameter set in the current page. 3. It runs each batch with `cursor.execute()`, so the driver can return rows, until the list is used up. 4. It concatenates the RETURNING rows of every batch into a single `Result`. All batches run inside one `Connection.execute()` call. Each batch is **logged separately** and passed separately to cursor-level event listeners such as `before_cursor_execute`, so a log or statement counter shows several INSERTs for one call. ## Where it applies - An `insert()` with `returning()` (or `return_defaults()`) executed with a **list of dicts**, through `Connection.execute()` or `Session.execute()`; the ORM's flush uses it too when it adds many objects. - Backends: SQLite 3.35 and later, PostgreSQL, SQL Server and MariaDB 10.5 and later. MySQL has no RETURNING, so it gets no support; Oracle's drivers provide their own equivalent. - Without RETURNING, most dialects simply hand the list to the driver's `cursor.executemany()`; the psycopg2 dialect, and by default the SQL Server dialects, use the batched form even then. - `create_engine(..., use_insertmanyvalues=False)` turns the feature off for an engine, and `Table(..., implicit_returning=False)` keeps a table from using RETURNING implicitly. ## How big a batch is The batch size defaults to **1000** parameter sets, set engine-wide with `create_engine(insertmanyvalues_page_size=...)` or per statement with the execution option of the same name. A dialect-specific cap on bound parameters per statement can lower it further: at most 32700, and 999 on SQLite older than 3.32. A 50,000-row load with four columns therefore runs as 50 INSERT statements at the default. Larger pages mean fewer round trips; smaller pages keep very wide rows from producing oversized statements. ## Ordering of the returned rows | Situation | What SQLAlchemy does | Log label | |---|---|---| | plain `returning()` | batched; row order not guaranteed | `(unordered)` | | `returning(..., sort_by_parameter_order=True)` with a client-generated key such as a UUID | batched, rows matched back to inputs through that key | `(ordered)` | | the same flag on PostgreSQL or SQL Server with a SERIAL or IDENTITY key | a special INSERT ... SELECT form, then sorted by the generated key | `(ordered)` | | the same flag on SQLite with an autoincrement key | **non-batched**: one INSERT per row in a tight loop | `(ordered; batch not supported)` | The column used to match rows back to parameter sets is the **insert sentinel**, normally chosen automatically. The flag only promises that the returned rows line up with the input list; it says nothing about the order the database writes them. If the importer can match on a natural key instead, returning `sku` alongside `id` and building a dict from it avoids needing the flag at all. ## What 2.1 adds SQLAlchemy 2.1 lets a **monotonic server-side function** act as the sentinel. A primary key declared with `server_default=func.uuidv7(monotonic=True)` (PostgreSQL 18's `uuidv7()`) can now be generated by the database while batched INSERT ... RETURNING still returns rows in parameter order. Before 2.1, a server-generated key other than an integer identity pushed ordered RETURNING towards non-batched mode. ## Measuring it on a real load 1. Run one nightly batch with `echo=True` or a `before_cursor_execute` listener that counts statements; expect roughly `rows / page size` INSERTs per call. 2. If the count is close to the number of rows, look for `(ordered; batch not supported)` in the log: an ordering request on a table without a usable sentinel has turned batching off. 3. Compare timings at two page sizes before changing the engine-wide default, since the best value depends on row width and the network.
- The importer zips the returned ids with its input list by position. Is that safe?Not by default: batched RETURNING rows are labelled `(unordered)`. Pass `returning(products.c.id, sort_by_parameter_order=True)` so SQLAlchemy lines the rows up with the input, accepting that some backends, SQLite with an autoincrement key among them, fall back to one INSERT per row. Often simpler: return `sku` as well and match on it.
- How do you change the batch size, and when would you?Pass `insertmanyvalues_page_size` to `create_engine()` or as an execution option on one statement or call. Raise it when rows are narrow and round trips dominate; lower it when rows carry large values and statements get big. The dialect's bound-parameter cap still applies on top of whatever you set.
A courier with 50,000 letters could hand them over one by one, or bundle them into sacks of 1000 and get a signed receipt per sack. The receipts arrive all together, but a sack's receipt lists the letters in whatever order the sorter scanned them unless you ask for numbered order.
- An executemany INSERT can never return rows, so RETURNING needs a loop of single INSERTs.
- insertmanyvalues sends all 50,000 rows as one giant INSERT statement.
- Rows from a batched INSERT ... RETURNING always come back in input order.
- insertmanyvalues_page_size is a hard size that the dialect never lowers.
- MySQL gets batched INSERT ... RETURNING exactly like PostgreSQL.
Assumes SQLAlchemy 2.1; insertmanyvalues arrived in 2.0, so 1.4-era advice that an executemany INSERT cannot return rows is out of date.