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-us...@sqlite.org
General Discussion of SQLite Database <sqlite-us...@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...@speranza.aioe.org>--
http://mail.python.org/mailman/listinfo/python-list
