sqlite3: executescript can't process iterdump in batches anymore

[Bluehorn]

Bug report

Bug description:

We've recently run into issues with some database migration code in our application, where we are copying a SQLite database by passing iterdump output into executescript. We tried to mimic what sqlite3 source.db .dump | sqlite3 target.db would do in Python.

While our unit tests were passing, the code was actually broken for large databases: It would error out with

sqlite3.OperationalError: cannot commit - no transaction is active

Here is a simplified version of the code:

import os.path
import shutil
import sqlite3
from itertools import islice
from tempfile import mkdtemp

ROW_COUNT = 1000


def main():
    folder = mkdtemp()
    try:
        print("Running executescript experiment in {0!r}".format(folder))
        source = os.path.join(folder, "source.db")
        target = os.path.join(folder, "target.db")
        source_conn = sqlite3.connect(source)
        populate_db(source_conn)
        target_conn = sqlite3.connect(target)
        migrate_db(source_conn, target_conn)

    finally:
        print("Deleting temporary folder {0!r}".format(folder))
        shutil.rmtree(folder)


def populate_db(conn):
    print("Populating source database with trivial data")
    conn.execute("create table customers (id integer primary key, name varchar)")
    conn.executemany(
        "insert into customers (name) values (?)",
        (("name #{0}".format(n),) for n in range(ROW_COUNT)),
    )
    conn.commit()


def migrate_db(source_conn, target_conn):
    print("Copying source database using iterdump")
    source_dump = source_conn.iterdump()
    batch_size = ROW_COUNT // 10

    while True:
        batch = list(islice(source_dump, batch_size))
        if not batch:
            break
        target_conn.executescript("\n".join(batch))


if __name__ == "__main__":
    main()

This code used to work just fine for years and has been carried over from Python 2.7.12 in our application.

The issue is that the iterdump output contains begin transaction and commit. Somehow, with Python 3.x the transaction handling has changed. It also seems to run the script in autocommit mode now, which slows it down to a crawl:

~/tmp/2026-09-09$ /usr/bin/time python2 original.py 
Running executescript experiment in '/tmp/tmpQP7tzC'
Populating source database with trivial data
Copying source database using iterdump
Deleting temporary folder '/tmp/tmpQP7tzC'
4.11user 0.33system 0:04.58elapsed 97%CPU (0avgtext+0avgdata 106808maxresident)k
0inputs+82328outputs (0major+134840minor)pagefaults 0swaps

~/tmp/2026-09-09$ uv run --python 3.15 python3 original.py .py 
Running executescript experiment in '/tmp/tmpdo9knqhb'
Populating source database with trivial data
Copying source database using iterdump
^C^\Command exited with non-zero status 131
3.55user 7.60system 2:23.77elapsed 7%CPU (0avgtext+0avgdata 40388maxresident)k
0inputs+1060696outputs (0major+14132minor)pagefaults 0swaps

I think the use case I understood for executescript is therefore basically dead: Execute many SQL statements (a script of SQL) with near native (as in: sqlite3 shell) performance.

The tricky change compared to the Python 2 state of affairs is that executescript now commits any ongoing transaction that is active on the SQLite level. So the first batch is quickly processed but calling executescript for the 2nd batch will commit the transaction opened for the first batch, switching SQLite to autocommit mode and processes each statement in its own transaction.

Workaround: don't use executescript but execute the statements one by one using plain execute. This works but slows the process to 50% original speed due to the Python overhead for each statement.

CPython versions tested on:

3.15

Operating systems tested on:

Linux

[akhilesoni]

create a temporary folder and two sqlite databases. add sample customer data to the source database.
copy the source data to the target database in batches. use iterdump() to get sql statements and executescript() to run them, close the connections and delete the temporary folder