> this may seem like a small issue, but I'm not sure if the solutions I've
> found on the web will do what I want in a low memory situation.  I'd like to
> iterate through a table one row at a time.  I can do that in pysqlite, but I
> don't see a method for determining I'm at the end of the file

> Can anyone point me in the correct direction?  Again, it's a low memory
> solution the the table could become quite large, so I don't want to load
> the whole thing with a teychall() call, and I'm not sure if a cursor won't
> take up too much memory as well.

For both pysqlite/sqlite3 and apsw the python cursor() object is a thin wrapper 
around an SQLite statement which is little more than a structure to hang the 
necessary methods to prepare/bind/step/return results from the statement and a 
small amount of state data to carry out those functions.  Neither the python 
cursor object nor the sqlite_statement objects consume much memory.


pysqlite/sqlite3:

you execute .fetchone() on the cursor:  It will return either a row tuple (or 
whatever the result is from your row_factory), or None if there are no more 
rows.

ie:

row = cr.execute('select * from table;').fetchone()
while row is not None:
    ... do stuff with the row
    row = cr.fetchone()

alternatively you could use .fetchmany(N) which returns a list of upto N rows 
and an empty list when there is no more data.  

rows = cr.execute('select * from table;').fetchmany(100)
while rows:
    for row in rows:
        ... do stuff with the row
    rows = cr.fetchmany(100)


apsw:

a cursor is an iterator and returns only one row at a time (executes 
sqlite3_step() and returns the result row tuple or throws a StopIteration 
exception when there are no more rows:

for row in cn.cursor().execute('select * from table;'):
    ... process each row


in either case, destroying the cursor object, looping until no more rows are 
returned, or calling .execute again on the same cursor object, finalizes the 
preceding statement (thus releasing any memory used internally in the 
processing of the statement).  Note that the statement itself will be kept in 
the statement cache for possible future re-use without having to regenerate the 
execution plan.




_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to