SQLite Forum

Statements outside of explicit transactions in SQLite
All statements which "write" to the database occur within the processing of that one statement.  The only statements which can "write" to the database are CREATE, INSERT, UPDATE, DELETE, ALTER, and DROP (I think I got them all).  

In each case the database change (the writing) is entirely contained within the single "execution" of the statement (that is, the statement runs to entirely to completion in a single sqlite3_step).  The execution state is not "held open" because there is no actual cursor involved (no result generator).

This is in contrast to a SELECT statement which is conceptually similar to a Python generator function.  The first sqlite3_step causes the statement to begin execution (and obtain a read lock), and it then yields a row, and each time it is subsequently stepped, until eventually it runs out of rows (raises a StopIteration exception) at which point the execution context is closed (and the read lock released, if autocommit is in effect).

That is, it ends up looking like this:

def Statement()
    beginingLockState = getReadLock()
    while (row := findNextResult()) is not None:
        yield row
    if autocommit is True:

There have been occasional requests to implement `INSERT ... RETURNING ...` and `UPDATE ... RETURNING ...` which would mean that those statements would generate result rows while executing their updates (although there are other methods which could be used to obtain the same effect without having to have the RETURNING clause that could already be implemented with existing facilities, doing that is complicated and some people are lazy).  

At present, however, RETURNING is not implemented so all SQL statements which can perform updates are single-step execution contexts and only the SELECT statement is a result generator.

Note that the PRAGMA statement is a special case and may fall into either class (or in some cases is performed at PREPARE time and does not require execution at all) depending on the particular PRAGMA.