Problem with sqlite3 cursor and imbricated for loop
Charles V.
Charles.Vejnar at unige.ch
Wed Nov 12 10:01:59 EST 2008
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
More information about the Python-list
mailing list