SQLite Forum

RETURNING clause require cursor consumption
I was a bit surprised that using a RETURNING clause requires explicit consumption of the resulting statement object. Example using Python:

    conn = sqlite3.connect(':memory:')
    conn.execute('create table reg(id integer primary key, k text)')

    cur = conn.execute('insert into reg (k) values (?), (?), (?) returning id',
                       ('k1', 'k2', 'k3'))
    print(next(cur))  # First result row.

    cur = conn.execute('select * from reg')

The above code prints out:

    (1,)  <-- the first id inserted
    [(1, 'k1'), (2, 'k2')]  <-- first and second row

The python driver has called sqlite3_step twice under-the-hood for our INSERT, and as a result only the first 2 rows are inserted.

My expectation, and the way postgres seems to work, is that the INSERT is performed regardless of whether we consume all the returned rows. I want to raise this now, so if this is expected behavior, I can plan how to work around it in my library code.