Consider the following code:
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t1 (col1, col2, col3);')
conn.commit()
rows = [(k+1, k+2, k+3) for k in range(1000)]
conn.executemany('INSERT INTO t1 VALUES (?, ?, ?);', rows)
conn.commit()
curs = conn.execute('SELECT * FROM t1;')
next(curs)
print(conn.in_transaction)
For me this prints False, while my expectation is that it would return True, because I have not traversed all of the rows from the SELECT, which means that the statement has not fully executed. I didn't issue an explicit BEGIN. I know that in such cases the behavior depends on conn.isolation_level, but from what I understand from the docs, this attribute is only relevant when DML statements are issued. Does this mean that for all other statements when you execute them without an explicit BEGIN, an implicit transaction is opened? (i.e. that for non-DML statements we are in autocommit mode always) Maybe conn.in_transaction returns True only when in explicit transactions, but returns False when in implicit ones? If my SELECT started an implicit transaction I know it must be active because such transactions are automatically committed only when the last cursor closes (see Implicit versus explicit transactions), but I still have a cursor opened.
EDIT: There is another issue. I am free to make and commit changes to the database before my cursor is done (If you run the code below after the code above, it prints (-1, -2, -3), even if my SELECT was executed before the update)
conn.execute('UPDATE t1 SET col1=-1, col2=-2, col3=-3 WHERE col1=3')
conn.commit()
next(curs)
print(next(curs))
I would expect (3, 4, 5) to be printed, as that was the relation before the update. Is my expectation wrong?