skip to content

In SQLAlchemy 2.1, how does the insertmanyvalues feature let an executemany INSERT with returning() send back every new row?

level: middleimportance: should knowfreq 30%

answer

  1. executemany cannot fetch rows
  2. rewrite into multi-row VALUES
  3. pages of 1000 parameter sets
  4. unordered unless you ask

basics

~10 s

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

The 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 lines
python
from 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

for a junior

Recall that SQLAlchemy can return generated ids from a many-row INSERT when you add returning() and pass a list of dicts to execute().

for a middle

Explain the rewrite into batched multi-row VALUES statements, the default page size of 1000, the parameter cap, and which backends support it.

for a senior

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.

for a principal

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.