Pickling a database cursor?

[John Nagle]

On 11/20/2010 10:30 PM, Chris Rebert wrote:
> On Sat, Nov 20, 2010 at 10:08 PM, Navkirat Singh<navkirats at gmail.com>
> wrote:
>> Hi Guys,
>>
>> Is there any way to pickle a database cursor? I would like a
>> persistent cursor over multiple HTTP requests. Any help would be
>> awesome !
>
> You can't. It's like a file handle in that respect.
>
> Cheers, Chris -- http://blog.rebertia.com

     I've seen several related questions on this from the same
person.  A few hints:

     I suspect that what you're trying to do is to display a
sequence of records from a database in page-sized groups.
There's a way to do that.

     In SQL, look into the OFFSET and LIMIT clauses.  The idea
is that if you want the first 10 entries, your SQL statement
has OFFSET 0 and LIMIT 10.  For the next 10, you use OFFSET 10
and LIMIT 10, and so on.

     For this to work, your SQL statement needs a ORDER BY
clause, so the records come out in the same order each time.
And you need to define an INDEX on the fields used in the
ORDER BY clause, or the database engine has to sort the file
every time, which is really slow.

     The usual way to do this with web pages is to use
REST-type parameters in the URL.  So you'll have a URL
like

  http://www.example.com/cgi/getrecords.cgi?q=customerid&offset=0&limit=10

On the pages you return, you put URLs for "next page" (with a bigger
offset) and "previous page" (with a smaller offset).  That way,
the user can move forwards and backwards through the pages.
You have to read those parameters from the URL and put them
into the SQL.  (For OFFSET and LIMIT, you have to edit those parameters 
into the SQL string itself, because those numbers aren't placed
in quotes.  You can't use the second parameter to cursor.execute
for this. So, when you fetch those parameters, convert them
to numbers with "int(s)" before putting them into the SQL
statement.  Otherwise, you'll have an SQL injection vulnerability.)

Page through some Google search results and watch how the URLs
change. That's how Google does it.

Don't use cookies for position information. The browser's "Back"
button won't do what users expect if you do that.

				John Nagle