Problem with sqlite3 cursor and imbricated for loop

[Charles V.]

Hi,

I hope this is not already known. But Google wasn't any help. So here begins a
script to explain my problem.

-------------------------
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')
c.execute("insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("insert into stocks values ('2006-01-06','BUY','RHAT',100,20.0)")
c.execute("insert into stocks values ('2006-01-07','BUY','RHAT',100,15.0)")
c.execute("insert into stocks values ('2006-01-08','BUY','RHAT',100,10.0)")
conn.commit()
c.execute("select * from stocks")
for s in c:
print s[0]
c.execute("select * from stocks where price<20")
for s in c:
print ' '+s[0]
c.close()
-------------------------

It is a adapted copy of the example in the Python documentation. But I was
expecting the output as with MySQL engine but, here, I get only:
2006-01-05
2006-01-07
2006-01-08

It seems the second call to execute modify the first cursor. Is it normal ?
How am I suppose to write this ?

Thanks

Charles

[Michiel Overtoom]

Charles V. wrote:

> It seems the second call to execute modify the first cursor. Is it normal ?
> How am I suppose to write this ?

Maybe introduce a second cursor?

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()

d = conn.cursor() # second cursor

c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')
c.execute("insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("insert into stocks values ('2006-01-06','BUY','RHAT',100,20.0)")
c.execute("insert into stocks values ('2006-01-07','BUY','RHAT',100,15.0)")
c.execute("insert into stocks values ('2006-01-08','BUY','RHAT',100,10.0)")
conn.commit()
c.execute("select * from stocks")
for s in c:
print s[0]

d.execute("select * from stocks where price<20") # use the second cursor
for s in d:
print ' '+s[0]
c.close()

Outputs:

2006-01-05
2006-01-07
2006-01-08
2006-01-06
2006-01-07
2006-01-08
2006-01-07
2006-01-07
2006-01-08
2006-01-08
2006-01-07
2006-01-08

--
"The ability of the OSS process to collect and harness
the collective IQ of thousands of individuals across
the Internet is simply amazing." - Vinod Vallopillil
http://www.catb.org/~esr/halloween/halloween4.html

[Charles V.]

Hi,

Thank for replying.

> Either use a second cursor OR ensure you fetch all the data from the
> first .execute() first:

Are these really the only solutions ? I was expecting the same behavior than
MySQLdb module, which is, as sqlite3, DB-API 2.0 compatible.

It means a program written for MySQLdb won't be compatible with sqlite3 (even
if I am using standard SQL). In fact I don't really understand why the
iterator isn't in some way "encapsulated". Does anybody tried to implemented
this functionality ?

Cheers,

Charles

[Gerhard Häring]

Charles V. wrote:
> Hi,
>
> Thank for replying.
>
>> Either use a second cursor OR ensure you fetch all the data from the
>> first .execute() first:
>
> Are these really the only solutions ?

Yes.

> I was expecting the same behavior than
> MySQLdb module, which is, as sqlite3, DB-API 2.0 compatible.

Both may be standard compliant, but if you're depending on
implementation details, you may still get different behaviour.
I'm pretty sure that MySQLdb always fetches the entire resultset from
the server. The sqlite3 module uses what would have been called
"server-side cursors" in real databases, i. e. it only fetches rows on
demand. To fetch everything in one go with the sqlite3 module, you have
to call fetchall() explicitly.

> It means a program written for MySQLdb won't be compatible with sqlite3 (even
> if I am using standard SQL). In fact I don't really understand why the

> iterator isn't in some way "encapsulated". [...]

I feel with you. The fact that cursors, and not connection objects have
the executeXXX methods is totally braindead.

That's why pysqlite (sqlite3) has alternative nonstandard executeXXX
methods in the connection object that return cursors.

-- Gerhard

[Steve Holden]

Charles V. wrote:
> Hi,
>
> Thank for replying.
>
>> Either use a second cursor OR ensure you fetch all the data from the
>> first .execute() first:
>

> Are these really the only solutions ? I was expecting the same behavior than

> MySQLdb module, which is, as sqlite3, DB-API 2.0 compatible.
>

> It means a program written for MySQLdb won't be compatible with sqlite3 (even
> if I am using standard SQL). In fact I don't really understand why the

> iterator isn't in some way "encapsulated". Does anybody tried to implemented
> this functionality ?

If you are trying to tell us that your code "works" under MySQL but not
under SQLite I want to see the proof. Otherwise I don;t believe you.

regards
Steve

PS: What does "imbricated" mean?
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

[Steve Holden]

Gerhard Häring wrote:
> Charles V. wrote:
>> Hi,
>>
>> Thank for replying.
>>
>>> Either use a second cursor OR ensure you fetch all the data from the
>>> first .execute() first:
>>
>> Are these really the only solutions ?
>

> Yes.

>
>> I was expecting the same behavior than MySQLdb module, which is, as
>> sqlite3, DB-API 2.0 compatible.
>

> Both may be standard compliant, but if you're depending on
> implementation details, you may still get different behaviour.
> I'm pretty sure that MySQLdb always fetches the entire resultset from
> the server. The sqlite3 module uses what would have been called
> "server-side cursors" in real databases, i. e. it only fetches rows on
> demand. To fetch everything in one go with the sqlite3 module, you have
> to call fetchall() explicitly.
>

>> It means a program written for MySQLdb won't be compatible with
>> sqlite3 (even if I am using standard SQL). In fact I don't really

>> understand why the iterator isn't in some way "encapsulated". [...]
>
> I feel with you. The fact that cursors, and not connection objects have
> the executeXXX methods is totally braindead.
>

So you'd rather have to use separate connections? That would make
isloated transaction processing a little tricky ...

> That's why pysqlite (sqlite3) has alternative nonstandard executeXXX
> methods in the connection object that return cursors.
>

It's also why SQLite's not a real RDBMS. Fortunately this doesn't stop
it being very useful.

regards
Steve

[Charles V.]

Hi,

> Both may be standard compliant, but if you're depending on
> implementation details, you may still get different behaviour.
> I'm pretty sure that MySQLdb always fetches the entire resultset from
> the server. The sqlite3 module uses what would have been called
> "server-side cursors" in real databases, i. e. it only fetches rows on
> demand. To fetch everything in one go with the sqlite3 module, you have
> to call fetchall() explicitly.

You are right: the default Cursor in MySQLdb fetches the complete set on the
client (It explains why I have a "correct" answer with MySQLdb). As having
multiple cursor isn't an option for me and using non-standard execute on the
connection neither, I tried to modify the Cursor class to store results on
the client side.

----------------
class NewCursor(sqlite3.Cursor):
def __iter__(self):
return iter(self.fetchall())

conn = sqlite3.connect(':memory:')

c = conn.cursor(NewCursor)

c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')
c.execute("insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("insert into stocks values ('2006-01-06','BUY','RHAT',100,20.0)")
c.execute("insert into stocks values ('2006-01-07','BUY','RHAT',100,15.0)")
c.execute("insert into stocks values ('2006-01-08','BUY','RHAT',100,10.0)")
conn.commit()
c.execute("select * from stocks")
for s in c:
print s[0]

c.execute("select * from stocks where price<20")

for sp in c:
print ' '+sp[0]
c.close()
----------------

This solution gives the correct answer:

2006-01-05
2006-01-07
2006-01-08
2006-01-06
2006-01-07
2006-01-08
2006-01-07
2006-01-07
2006-01-08
2006-01-08
2006-01-07
2006-01-08

Do you think it is a good solution (any drawbacks ?) ?

Thanks again.

Charles

[Gerhard Häring]

Steve Holden wrote:
> [...]
>> I feel with you. The fact that cursors, and not connection objects have
>> the executeXXX methods is totally braindead.
>>
> So you'd rather have to use separate connections? That would make
> isloated transaction processing a little tricky ...

No, I just find code like:

con = ...connect(...)
cursor1 = con.cursor()
cursor1.execute("select ...")
for row in cursor1:
cursor2 = con.cursor()
cursor2.execute("...)

quite verbose compared to:

con = ...connect()
for row in con.execute("select ...")
con.execute("...")

Both of which work with pysqlite (*).

Granted, the second form works way better with a reference-counting
garbage-collector like CPython has ;-)

>> That's why pysqlite (sqlite3) has alternative nonstandard executeXXX
>> methods in the connection object that return cursors.
>>
> It's also why SQLite's not a real RDBMS. Fortunately this doesn't stop
> it being very useful.

What does pysqlite extending the DB-API have to do with *SQLite* not
being a "real" RDBMS?

-- Gerhard

(*) And writing a wrapper for other modules that does the same is trivial.

[Gerhard Häring]

Charles V. wrote:
> Hi,
>
>> Both may be standard compliant, but if you're depending on
>> implementation details, you may still get different behaviour.
>> I'm pretty sure that MySQLdb always fetches the entire resultset from
>> the server. The sqlite3 module uses what would have been called
>> "server-side cursors" in real databases, i. e. it only fetches rows on
>> demand. To fetch everything in one go with the sqlite3 module, you have
>> to call fetchall() explicitly.
>
> You are right: the default Cursor in MySQLdb fetches the complete set on the
> client (It explains why I have a "correct" answer with MySQLdb). As having
> multiple cursor isn't an option for me and using non-standard execute on the
> connection neither, I tried to modify the Cursor class to store results on
> the client side.
>
> ----------------
> class NewCursor(sqlite3.Cursor):
> def __iter__(self):
> return iter(self.fetchall())
>
> conn = sqlite3.connect(':memory:')

> [...[]

> Do you think it is a good solution (any drawbacks ?) ?

It's at least incomplete. fetchone() and fetchmany() won't work any longer.

-- Gerhard

[Steve Holden]

Gerhard Häring wrote:
> Steve Holden wrote:
[...]

>>>
>> It's also why SQLite's not a real RDBMS. Fortunately this doesn't stop
>> it being very useful.
>
> What does pysqlite extending the DB-API have to do with *SQLite* not
> being a "real" RDBMS?
>

Nothing at all. I was just being pissy. Sorry about that.

[jhermann]

c.execute("select * from stocks")

for s in list(c):

print s[0]
c.execute("select * from stocks where price<20")
for sp in c:
print ' '+sp[0]
c.close()

The simple addition of list() should do away with the dependency on
mysql's implementation, since it forces the instant fetch of all data.
Whether that is better than to use a 2nd cursor is probably a matter
of taste.

Using "*" in selects is always bad, though. ;)