Python3 + sqlite3: Where's the bug?

[Johannes Bauer]

Hi group,

I've run into a problem using Python3.2 and sqlite3 db access that I
can't quite wrap my head around. I'm pretty sure there's a bug in my
program, but I can't see where. Help is greatly appreciated. I've
created a minimal example to demonstrate the phaenomenon (attached at
bottom).

First, the program creates a db and inits two tables "foo" and "bar",
which both only have a "int" value. Then "foo" is populated with unique
ints.

A fetchmanychks function is supposed to have the same behavior as
fetchall(), but instead perform the operation in many subsequent
fetchmany() chunks.

When I traverse the foo table using cursor cur1 and insert into the bar
table using cursor cur2, I receive at some point:

Traceback (most recent call last):
  File "y.py", line 25, in <module>
    cur2.execute("INSERT INTO bar (id) VALUES (?);", (v,))
sqlite3.IntegrityError: PRIMARY KEY must be unique

Which means that the fetchmany() read returns the *same* value again!
How is this possible? If I either

- Remove the "db.commit()"
- Replace fetchmanychks(cur1) by cur1.fetchall()

it works without error -- but I want neither (I want regular commits
because sqlite3 becomes horribly slow when the journal becomes large and
the tables nothing to do with each other anyways and atomicity is not
needed in my case).

Do I grossly misunderstand fetchmany() or where's my bug here?

Thanks in advance,
Joe




#!/usr/bin/python3.2
import sqlite3

db = sqlite3.connect("foobar.sqlite")
cur1 = db.cursor()
cur2 = db.cursor()

def fetchmanychks(cursor):
	cursor.execute("SELECT id FROM foo;")
	while True:
		result = cursor.fetchmany()
		if len(result) == 0:
			break
		for x in result:
			yield x

cur1.execute("CREATE TABLE foo (id integer PRIMARY KEY);")
cur1.execute("CREATE TABLE bar (id integer PRIMARY KEY);")
for i in range(0, 200000, 5):
	cur1.execute("INSERT INTO foo VALUES (?);", (i,))
db.commit()

ctr = 0
for (v, ) in fetchmanychks(cur1):
	cur2.execute("INSERT INTO bar (id) VALUES (?);", (v,))
	ctr += 1
	if ctr == 100:
		db.commit()
		ctr = 0



-- 
>> Wo hattest Du das Beben nochmal GENAU vorhergesagt?
> Zumindest nicht öffentlich!
Ah, der neueste und bis heute genialste Streich unsere großen
Kosmologen: Die Geheim-Vorhersage.
 - Karl Kaos über Rüdiger Thomas in dsa <hidbv3$om2$1 at speranza.aioe.org>