persistent database results for web page

[Gordon McMillan]

James Vincent asks:
> 
>  As I learn to hook Python into my database I am faced with a 
> simple yet vexing problem. I would like to query a database and give
> the results to a user, however, the query will run for a very long
> time. When it finishes it will return many more rows of data than
> will fit on a page (several thousand).  I have a nice rolo-dex table
> thingy for html that lets you select the next n results, but it
> requires generating the data again (very bad). Is there a way to run
> a query once and store the results either with the client or on the
> server in a very simple way that would then allow easy perusal of
> the result set? Any advice is appreciated.

Sure there are ways. The real problem with this approach is deciding 
when cached temporary data has gone stale, either because of the 
volatility of the data, or the user has gone on to better things 
<wink wink nudge nudge>.

Another approach is to save the key of the last row fetched in a 
hidden form and reissue the query with a "AND [whatever key] > 
[whatever value]". Depending on your DB and the query this may not be 
as bad as it sounds.

Most DBs will begin returning data (if you haven't messed them 
up with an ORDER BY or other post-processing clause) before they've 
finished scanning. If you only fetch your N rows and then close the 
query, the DB should quit looking for more rows.

If you still want to persue persisting the results, I'd probably use 
cPickle and a filename based on some kind of session id. But I'd 
venture that finding a clean-up algorithm that keeps everyone (your 
users and your sysadmin) satisfied won't be easy.


- Gordon