mardi 23 décembre 2014

Python, SQLite3: fetchone() returns duplicates when a commit intervenes

This Python code creates a table, inserts three rows into it and iterates through the rows, with intervening commits before the cursor has been fully exhausted. Why does it return five rows instead of three? If the intervening commit is removed, the number of returned rows is three as expected. Or is it expected that a commit (which doesn't even touch the table in question) invalidates a cursor?



#!/usr/bin/env python3

import sqlite3 as sq

db = sq.connect(':memory:')

db.execute('CREATE TABLE tbl (col INTEGER)')
db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

# Read and print the values just inserted into tbl
for col in db.execute('SELECT col FROM tbl'):
print(col)
db.commit() # an intervening commit

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))


The output is:



count=3
(0,)
(1,)
(0,)
(1,)
(2,)
count=3


Generally, with N rows inserted, N+2 rows are returned by the iterator, apparently always with the first two duplicated.


Aucun commentaire:

Enregistrer un commentaire