I recently created an infinite loop by accident when trying to modify the content of rows that were being sequentially read from a SQLite table. I want to replicate the issue here with a minimal example.

Create and connect to a SQLite database.

import os
from sqlite3 import connect

db_connection = connect(os.environ["DB_PATH"])

Create a test table.

create_table_statement = """CREATE TABLE IF NOT EXISTS test(
    id TEXT NOT NULL PRIMARY KEY,
    value INTEGER
);"""

cursor = db_connection.cursor()
cursor.execute(create_table_statement)

<sqlite3.Cursor at 0x10d6ec420>

Check if the table was created.

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
assert cursor.fetchall()[0][0] == "test"

Insert sample data.

inser_sql = "INSERT OR REPLACE INTO test (id,value) VALUES (?,?)"
data = [("1", 1), ("2", 2), ("3", 3)]

for d in data:
    cursor.execute(inser_sql, d)
    db_connection.commit()

Check if the sample data were sucessfully inserted.

from pandas import read_sql_query

read_sql_query("SELECT * FROM test", db_connection)
id value
0 1 1
1 2 2
2 3 3

Loop from the data. Note that the loop here is finite.

for d in cursor.execute("SELECT * FROM test"):
    print(d)

('1', 1)
('2', 2)
('3', 3)

Modify the table while looping through its data. This leads to an infinite loop and should be avoided.

for idx, d in enumerate(cursor.execute("SELECT * FROM test")):
    new_cursor = db_connection.cursor()
    new_cursor.execute(inser_sql, (d[0], d[1]+1))
    db_connection.commit()
    print(d)
    if idx == 7:
        break 

('1', 1)
('2', 2)
('3', 3)
('1', 2)
('2', 3)
('3', 4)
('1', 3)
('2', 4)

Clean up environment

os.remove(os.environ["DB_PATH"])