Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsreader4.netcologne.de!news.netcologne.de!feed.xsnews.nl!border-1.ams.xsnews.nl!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.001 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'subsequent': 0.04; 'subject:bug': 0.05; 'subject: + ': 0.07; 'subject:sqlite3': 0.07; 'cursor': 0.09; 'here?': 0.09; 'sqlite': 0.09; 'subject:Python3': 0.09; 'bug': 0.10; 'def': 0.10; 'skip:# 20': 0.13; 'value.': 0.15; '>>': 0.16; '(attached': 0.16; 'ah,': 0.16; 'chunks.': 0.16; 'possible?': 0.16; 'range(0,': 0.16; 'received:10.20.55.1': 0.16; 'received:bosimpout01.eigbox.net': 0.16; 'result:': 0.16; 'sqlite3': 0.16; 'traverse': 0.16; 'true:': 0.16; "where's": 0.16; 'wrote:': 0.17; 'integer': 0.17; 'yield': 0.17; 'creates': 0.18; 'discussion': 0.20; 'import': 0.21; 'supposed': 0.21; 'cc:2**0': 0.23; 'example': 0.23; '>': 0.23; 'demonstrate': 0.23; 'insert': 0.23; 'tables': 0.23; "i've": 0.23; 'header:In-Reply- To:1': 0.25; 'header:User-Agent:1': 0.26; 'values': 0.26; 'appreciated.': 0.26; '(most': 0.27; 'first,': 0.27; 'joe': 0.27; 'replace': 0.27; 'regular': 0.27; 'run': 0.28; 'case).': 0.29; 'wrap': 0.29; 'skip:& 10': 0.29; "i'm": 0.29; 'becomes': 0.30; 'minimal': 0.30; 'thursday,': 0.30; 'function': 0.30; 'error': 0.30; 'primary': 0.30; 'nicht': 0.31; 'december': 0.32; 'file': 0.32; 'addresses': 0.32; 'skip:s 30': 0.33; 'traceback': 0.33; 'problem': 0.33; 'to:addr:python-list': 0.33; 'skip:& 20': 0.33; "can't": 0.34; 'program,': 0.34; 'thanks': 0.34; 'needed': 0.35; 'table': 0.35; 'subject:?': 0.35; 'created': 0.36; 'das': 0.36; 'but': 0.36; 'url:org': 0.36; 'received:71': 0.36; 'url:rec- html40': 0.37; 'two': 0.37; 'quite': 0.37; 'subject:: ': 0.38; 'perform': 0.38; 'some': 0.38; 'nothing': 0.38; 'received:10': 0.38; 'sure': 0.38; 'instead': 0.39; 'to:addr:python.org': 0.39; 'skip:" 10': 0.40; 'help': 0.40; 'group,': 0.60; 'remove': 0.61; 'thomas': 0.62; 'behavior': 0.64; '20,': 0.65; 'receive': 0.71; 'unsere': 0.71; 'url:dtd': 0.81; '(id': 0.84; 'again!': 0.84; 'karl': 0.84; 'subject:Where': 0.84; 'heute': 0.91; 'journal': 0.91; 'url:strict': 0.91 X-Authority-Analysis: v=2.0 cv=INa1/HTG c=1 sm=1 a=Arb6fDRBJ4TDsmIwRljMHg==:17 a=spKFjZujUncA:10 a=2Cs5qbXtQ68A:10 a=noBxwO7XV6IA:10 a=OmMKortqy18A:10 a=tTExfe6FAAAA:8 a=t4AfNhiBbM8A:10 a=mLKua8RHAAAA:8 a=SP4CQPdRAAAA:8 a=8ONGxiEqAAAA:8 a=sngvqGMXbQnGHbHbyh4A:9 a=wPNLvfGTeEIA:10 a=P3uMDV8RXKwA:10 a=wQXOe_l8XVwA:10 a=SSmOFEACAAAA:8 a=rF4hPnLvp53LGsMO120A:9 a=_W_S_7VecoQA:10 a=frz4AuCg-hUA:10 a=qMDZ_Ezp4_0A:10 a=V0UXLeHvv3hhJHDj:21 a=anyYG9rjTBM1sAjEBQ8Cew==:117 X-EN-OrigOutIP: 10.20.18.11 X-EN-IMPSID: dryc1k0070EKspE01rycoX From: inq1ltd To: python-list@python.org Subject: Re: Python3 + sqlite3: Where's the bug? Date: Thu, 20 Dec 2012 10:57:02 -0500 User-Agent: KMail/4.7.2 (Linux/2.6.37.6-24-desktop; KDE/4.7.2; i686; ; ) In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="nextPart3934229.zGpmzZMY9L" Content-Transfer-Encoding: 7Bit X-EN-UserInfo: ea107384e720a598200e9790e8ca8002:9d89a6cbd5a73a41b134431a25286195 X-EN-AuthUser: inq1ltd@inqvista.com Sender: inq1ltd X-EN-OrigIP: 71.246.151.144 X-EN-OrigHost: pool-71-246-151-144.rich.east.verizon.net Cc: Johannes Bauer X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 427 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1356024160 news.xs4all.nl 6896 [2001:888:2000:d::a6]:50357 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:35206 --nextPart3934229.zGpmzZMY9L Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="iso-8859-1" On Thursday, December 20, 2012 03:52:39 PM Johannes Bauer wrote: > Hi group, >=20 > 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). >=20 > 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 uniq= ue > ints. >=20 > A fetchmanychks function is supposed to have the same behavior as > fetchall(), but instead perform the operation in many subsequent > fetchmany() chunks. >=20 > When I traverse the foo table using cursor cur1 and insert into the b= ar > table using cursor cur2, I receive at some point: >=20 > Traceback (most recent call last): > File "y.py", line 25, in > cur2.execute("INSERT INTO bar (id) VALUES (?);", (v,)) > sqlite3.IntegrityError: PRIMARY KEY must be unique >=20 > Which means that the fetchmany() read returns the *same* value again!= > How is this possible? If I either >=20 > - Remove the "db.commit()" > - Replace fetchmanychks(cur1) by cur1.fetchall() >=20 > 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). >=20 > Do I grossly misunderstand fetchmany() or where's my bug here? >=20 > Thanks in advance, > Joe >=20 >=20 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@sqlite.org General Discussion of SQLite Database jd inqvista.com >=20 >=20 > #!/usr/bin/python3.2 > import sqlite3 >=20 > db =3D sqlite3.connect("foobar.sqlite") > cur1 =3D db.cursor() > cur2 =3D db.cursor() >=20 > def fetchmanychks(cursor): > =09cursor.execute("SELECT id FROM foo;") > =09while True: > =09=09result =3D cursor.fetchmany() > =09=09if len(result) =3D=3D 0: > =09=09=09break > =09=09for x in result: > =09=09=09yield x >=20 > 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): > =09cur1.execute("INSERT INTO foo VALUES (?);", (i,)) > db.commit() >=20 > ctr =3D 0 > for (v, ) in fetchmanychks(cur1): > =09cur2.execute("INSERT INTO bar (id) VALUES (?);", (v,)) > =09ctr +=3D 1 > =09if ctr =3D=3D 100: > =09=09db.commit() > =09=09ctr =3D 0 >=20 > >> Wo hattest Du das Beben nochmal GENAU vorhergesagt? > >=20 > > Zumindest nicht =F6ffentlich! >=20 > Ah, der neueste und bis heute genialste Streich unsere gro=DFen > Kosmologen: Die Geheim-Vorhersage. > - Karl Kaos =FCber R=FCdiger Thomas in dsa --nextPart3934229.zGpmzZMY9L Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="iso-8859-1"

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<= /p>

>= ; can't quite wrap my head around. I'm pretty sure there's a bug in my<= /p>

>= ; 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 b= ar

>= ; 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,))<= /p>

>= ; 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<= /p>

>= ; 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= ,

 

Bot= h of the following addresses will get you to the same place.

 

You= will get an answer from the sqlite help site.

 

sql= ite-users@sqlite.org

 

Gen= eral Discussion of SQLite Database <sqlite-users@sqlite.org>

 

 

jd<= /p>

inq= vista.com

 

 

>= ;

>= ;

>= ; #!/usr/bin/python3.2

>= ; import sqlite3

>= ;

>= ; db =3D sqlite3.connect("foobar.sqlite")

>= ; cur1 =3D db.cursor()

>= ; cur2 =3D db.cursor()

>= ;

>= ; def fetchmanychks(cursor):

>= ; =09cursor.execute("SELECT id FROM foo;")

>= ; =09while True:

>= ; =09=09result =3D cursor.fetchmany()

>= ; =09=09if len(result) =3D=3D 0:

>= ; =09=09=09break

>= ; =09=09for x in result:

>= ; =09=09=09yield x

>= ;

>= ; cur1.execute("CREATE TABLE foo (id integer PRIMARY KEY);")<= /p>

>= ; cur1.execute("CREATE TABLE bar (id integer PRIMARY KEY);")<= /p>

>= ; for i in range(0, 200000, 5):

>= ; =09cur1.execute("INSERT INTO foo VALUES (?);", (i,))

>= ; db.commit()

>= ;

>= ; ctr =3D 0

>= ; for (v, ) in fetchmanychks(cur1):

>= ; =09cur2.execute("INSERT INTO bar (id) VALUES (?);", (v,))

>= ; =09ctr +=3D 1

>= ; =09if ctr =3D=3D 100:

>= ; =09=09db.commit()

>= ; =09=09ctr =3D 0

>= ;

>= ; >> Wo hattest Du das Beben nochmal GENAU vorhergesagt?

>= ; >

>= ; > Zumindest nicht =F6ffentlich!

>= ;

>= ; Ah, der neueste und bis heute genialste Streich unsere gro=DFen

>= ; Kosmologen: Die Geheim-Vorhersage.

>= ; - Karl Kaos =FCber R=FCdiger Thomas in dsa <hidbv3$om2$1@speranza= .aioe.org>

--nextPart3934229.zGpmzZMY9L--