Re: Walking through a mysql db

Discussion in 'Python' started by Jeff Elkins, Jun 4, 2005.

  1. Jeff Elkins

    Jeff Elkins Guest

    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
    Jeff Elkins, Jun 4, 2005
    #1
    1. Advertising

  2. 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
    Scott David Daniels, Jun 4, 2005
    #2
    1. Advertising

  3. On Sat, 4 Jun 2005 11:23:47 -0400, Jeff Elkins
    <> 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.

    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Home Page: <http://www.dm.net/~wulfraed/> <
    > Overflow Page: <http://wlfraed.home.netcom.com/> <
    Dennis Lee Bieber, Jun 4, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeff Elkins

    Walking through a mysql db

    Jeff Elkins, Jun 4, 2005, in forum: Python
    Replies:
    2
    Views:
    359
    wes weston
    Jun 4, 2005
  2. Thierry Lam

    Walking through directories and files

    Thierry Lam, Sep 16, 2005, in forum: Python
    Replies:
    1
    Views:
    282
    Fredrik Lundh
    Sep 16, 2005
  3. gaga
    Replies:
    19
    Views:
    537
  4. Xeno Campanoli
    Replies:
    3
    Views:
    101
    Xeno Campanoli
    Jun 15, 2007
  5. Replies:
    7
    Views:
    106
Loading...

Share This Page