Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Re: Walking through a mysql db

Reply
Thread Tools

Re: Walking through a mysql db

 
 
Jeff Elkins
Guest
Posts: n/a
 
      06-04-2005
On Saturday 04 June 2005 09:24 am, Jeff Elkins wrote:
> I'm writing a small wxpython app to display and update a dataset. So far, I
> get the first record for display:
>
> try:
> cursor = conn.cursor ()
> cursor.execute ("SELECT * FROM dataset")
> item = cursor.fetchone ()
>
> 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.


Thanks for the responses! The buttons are working now.

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?

Jeff


 
Reply With Quote
 
 
 
 
Scott David Daniels
Guest
Posts: n/a
 
      06-04-2005
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 http://www.velocityreviews.com/forums/(E-Mail Removed)
(E-Mail Removed)
 
Reply With Quote
 
 
 
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      06-04-2005
On Sat, 4 Jun 2005 11:23:47 -0400, Jeff Elkins
<(E-Mail Removed)> declaimed the following in comp.lang.python:

> 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?
>

And what use do you intend for this row number? It is only
applicable to the cursor, so far as I know -- and I believe MySQL
doesn't have true cursors (where the DBMS maintains the "current row");
the cursor is emulated by the dbapi interface.

If you need a unique ID number so that later operations can
identify the record, you need to actually store such /in/ the table
(autoincrement, unique primary key) and use that field in a subsequent
SQL statement to identify the same record.

--
> ================================================== ============ <
> (E-Mail Removed) | Wulfraed Dennis Lee Bieber KD6MOG <
> (E-Mail Removed) | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Errors in walking through XML aneely@canada.com Javascript 7 09-20-2008 09:29 PM
Walking through Tate and Hibbs, running into things that don't work...startingon page 7 Xeno Campanoli Ruby 3 06-15-2007 06:12 PM
Walking through directories and files Thierry Lam Python 1 09-16-2005 02:41 PM
Walking through a mysql db Jeff Elkins Python 2 06-04-2005 01:52 PM
walking through an array of char pointers gaga C Programming 19 08-23-2004 05:13 AM



Advertisments