TL;DR
Should it be possible to return resulting rows from Should sqlite3.Cursor.executemany() not discard resulting rows?
Background
In 2021, SQLite 3.35.0 added support for the RETURNING clause in DML[1] statements. Traditionally, an SQL DML statement would not return any values. With the RETURNING clause, resulting rows are now a thing for DML statements. Example:
CREATE TABLE t(t);
INSERT INTO t VALUES(1) RETURNING t;
The DB API 2.0 (PEP-249) defines the executemany cursor method pretty vague; there are no restrictions on which types of statements it can execute; the return value is not defined. When Gerhard Häring implemented the sqlite3 extension module almost 20 years ago, executemany()
was deliberately implemented to only execute DML statements, and to discard any resulting rows.
The Problem
The new RETURNING clause seems to be a very popular addition to SQLite, and for many users it is confusing that executemany("INSERT INTO t VALUES(?) RETURNING t", data)
does not return the expected resulting rows.
Possible Solutions
For now, I’ve updated the docs (see gh-103939) to make it clearer that executemany
discards any resulting rows, including DML statements with RETURNING clauses.
There is also the possibility of changing the semantics of executemany()
to not discard resulting rows, however changing semantics includes the risk of breaking existing code; the more subtle the semantic change, the more difficult it is to discover the bug. For now, I land on the conservative side; I prefer to keep the existing behaviour.
Thoughts?
-
INSERT, UPDATE, and DELETE ↩︎