Python sqlite3 executemany Inserts Rows but RETURNING Is Empty

If executemany() inserts records but fetchall() returns an empty list, do not rerun the batch on that observation alone. Python's sqlite3 documentation says the method discards result rows, including rows produced by a DML RETURNING clause. An empty fetched result therefore cannot distinguish a successful insertion from a missing insertion.

First inspect the target database through a query appropriate to your application's identifiers and transaction state. Retrying a write merely to obtain its IDs can create duplicates. A database change, a fetched result and a committed transaction are separate observations.

Reproduce three separate outcomes

The following standalone example uses an in-memory database and synthetic labels. Save it as returning-check.py and run it with the Python interpreter used by your application. It never opens an existing database. SQLite must support RETURNING; the SQLite documentation dates that support to version 3.35.0. Print sqlite3.sqlite_version to check the library linked to your Python runtime rather than assuming the separately installed command-line program uses the same library.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as con:
    con.execute("CREATE TABLE item(id INTEGER PRIMARY KEY, label TEXT UNIQUE)")
    cur = con.cursor()
    cur.execute("INSERT INTO item(label) VALUES (?)", ("seed",))
    old_id = cur.lastrowid
    cur.executemany("INSERT INTO item(label) VALUES (?) RETURNING id, label",
                    [("alpha",), ("beta",)])
    assert cur.fetchall() == []
    assert cur.lastrowid == old_id
    assert con.execute("SELECT count(*) FROM item").fetchone() == (3,)
    print("executemany: inserted 2; fetched 0; lastrowid unchanged")
    returned = []
    with con:
        for label in ["gamma", "delta"]:
            returned.extend(con.execute(
                "INSERT INTO item(label) VALUES (?) RETURNING id, label",
                (label,),
            ).fetchall())
    assert [row[1] for row in returned] == ["gamma", "delta"]
    assert len({row[0] for row in returned}) == 2
    print("execute loop:", returned)
    try:
        with con:
            for label in ["epsilon", "gamma"]:
                con.execute("INSERT INTO item(label) VALUES (?) RETURNING id", (label,)).fetchall()
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError("expected duplicate failure")
    assert con.execute("SELECT count(*) FROM item WHERE label='epsilon'").fetchone() == (0,)
    print("failed batch: earlier epsilon rolled back")

Our local run produced these observations:

executemany: inserted 2; fetched 0; lastrowid unchanged
execute loop: [(4, 'gamma'), (5, 'delta')]
failed batch: earlier epsilon rolled back

The first assertion reads back the actual table count. Two new rows exist even though the batch cursor supplies no result rows. The next assertion compares lastrowid with its saved value from a previous single insertion. Its unchanged value belongs to that earlier operation; it is not the ID of the last item in the batch. Do not turn it into a range of IDs by subtracting the input length.

Fetch each statement when each input needs an ID

The middle section executes one parameterized insertion per input and fully fetches that statement's result before reusing the connection shortcut. Each returned record contains both its generated ID and its label. The application can retain that explicit relationship instead of assuming a later query or bulk result happens to follow input order.

This fixture deliberately uses ordinary INSERT statements with unique labels. An application using conflict handling must define what zero returned rows means. A skipped input, an updated row and a newly inserted row require a deliberate result contract. Do not copy the fixture's two-row expectation into every upsert operation.

SQLite documents that result ordering for one statement affecting multiple rows is not guaranteed. If you choose one multi-row statement instead of the loop, include an appropriate stable identifier in the returned columns and associate records by that identifier. Merely zipping an arbitrary output sequence with the original inputs can attach the wrong IDs.

Separate the transaction decision from collecting IDs

In our third probe, the synthetic label epsilon is inserted before an existing label triggers a uniqueness error. After the exception leaves the transaction context, querying for epsilon finds no row. That observed rollback applies to this fixture and its runtime's connection settings.

Choose transaction boundaries for the real application's connection mode. Do not silently wrap borrowed connections or unrelated pending changes in a new ownership policy. In particular, collecting returned IDs does not itself prove durable commit. A later statement or commit can still fail. Publish results to downstream work only after the intended transaction succeeds.

Check the right boundary before changing the database

For this problem, capture the Python version, linked SQLite version, exact method called, statement shape and independently queried outcome. Keep private values out of diagnostics. If a historical example reports a different exception, reproduce its SQL and runtime separately; a method's discarded results are different from a statement rejected before execution.

This example checks a small local batch, an explicit per-input result mapping and one rollback path. It is not a throughput benchmark, a concurrent-writer test or a migration script. Existing guides cover connection cleanup and bound parameters for IN lists; neither addresses this discarded-result boundary.