Python3 + sqlite3: Where's the bug?

[inq1ltd]

On Thursday, December 20, 2012 03:52:39 PM Johannes Bauer wrote:
> 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
> 
> 
Joe,

Both of the following addresses will get you to the same place.

You will get an answer from the sqlite help site.

sqlite-users at sqlite.org

General Discussion of SQLite Database <sqlite-users at sqlite.org>


jd
inqvista.com


> 
> 
> #!/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>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20121220/f28aaf6c/attachment.html>