SQLite Forum

RETURNING clause require cursor consumption
Login
This change is working well for me. Here's a small test program I used to check the behaviors:

Inserting multiple rows working as expected:

```
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.

# Prints (1,)

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

# All inserts have been processed, output is now:
# Prints: [(1, 'k1'), (2, 'k2'), (3, 'k3')]
```

Similar workflow with an UPDATE this time also works as expected:

```
cur = conn.execute('update reg set k=k||? where k in (?, ?, ?) returning id, k',
                   ('x', 'k1', 'k3', 'k2'))
print(next(cur))  # First result row.

# Prints: (1, 'k1x')

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

# All updates were processed. Output is:
# [(1, 'k1x'), (2, 'k2x'), (3, 'k3x')]
```

Do an upsert which will be executed the first time, and not the 2nd time. Both operations working as expected:

```
# Check behavior w/upsert
conn.execute('create table r2(id integer primary key, k text unique, v real)')
conn.execute('insert into r2 (k, v) values (?, ?), (?, ?)',
             ('k1', 1, 'k2', 2))

for _ in range(2):
    curs = conn.execute('insert into r2 (k, v) values (?, ?) '
                        'on conflict(k) do update set v = v + ? '
                        'where v <= ? '
                        'returning id, k, v', ('k1', 3, 0.5, 1))
    print(curs.lastrowid)
    try:
        print(next(curs))
    except StopIteration:
        pass

# Correctly prints:
# 2
# (1, 'k1', 1.5)
# 2
# <no output here, since no rows via RETURNING>
```