Walking through a mysql db

[Scott David Daniels]

Jeff Elkins wrote:
> On Saturday 04 June 2005 09:24 am, Jeff Elkins wrote:
...
>>Now, how do I step through the dataset one row at a time?  My form has 
>>'next' and 'back' buttons, and I'd like them to step forward or back,
>>fetching the appropriate row in the table. I've tried setting
>>cursor.rownumber by incrementing it prior to the fetchone() w/o effect.

Conceptually RDB queries produce sets, not lists.  The "row number" is
more an artifact than a property, and (in general) the only way to get
to the fifty-third is to step through the first fifty-two.  If you need
to go forward and back, pull the results into a python list (using
fetchmany or fetchall) and walk through that list.

> Within this same app, I've got a search function working, but I need the 
> rownumber when a record is fetched.
> 
>   sql = """select * from address where %s = %%s""" % arg1.lower()
>             cursor.execute(sql, (arg2,))
>              item = cursor.fetchone ()
>              index = cursor.rownumber
> 
> At this point, the record is on the screen, but cursor.rownumber doesn't 
> reflect the actual rownumber...it always returns 1. How can I obtain the 
> actual row number for the displayed record?

The "normal" way to do this is to make sure your query includes the
key of the table you are querying.  It is generally considered bad
style (in the DB world) to use "SELECT *" above.  Name the fields you
are grabbing, and your code will survive more schema changes.  The
contents of the table's key column(s) _is_ the unique identifier of
that row, not a "row number" (which may well change on a backup-restore
for example).

--Scott David Daniels at Acm.Org
Scott.Daniels at Acm.Org