Re: [PLUG] MySQL & Python Help Anyone?

[Walt Mankowski <waltman@pobox.com>]

On Sat, Aug 16, 2008 at 08:27:11PM -0400, Casey Bralla wrote:
> I'm working on a python application which accesses data in a MySQL database.  
> I'm hoping somebody here is knowledgeable of this.
> 
> I want to select a sequence of records in a specific order.  I can select a 
> specific record without any problem, but when I select the next one, it's not 
> in the order I expect.   Here's a simplified version of my code:
> 
> 
> import MySQLdb	# Load the MySQL Python module
> # Connect to the server
> Database=MySQLdb.connect(db = MyDatabase, user = DatabaseUser, passwd = 
> DatabasePassword, host = DatabaseHost)
> #
> # Fetch the first value
> DatabaseCursor = Database.cursor()
> Result=DatabaseCursor.execute("SELECT  Item  FROM  Database ORDER BY 
> DesiredOrder)
> # So far, so good!

Umm, no.  So far, so bad.  The execute method doesn't actually return
any rows from the database.  Rather, it returns the total number of
records affected by the sql statement.  In this case, since it's a
select statement, it returns the number of rows returned by the select
statement.

> #
> # Now try to get the next record IN THE SAME ORDER!
> Result = DatabaseCursor.fetchone()
> # :(

Sorry, but this is also wrong.  Result should now be set to the
*first* row returned.  It's not the *next* record, since, as mentioned
above, you haven't retrieved any rows yet.

You can retrieve subsequent records by continuing to call fetchone().
Alternatively you can call fetchall() to get all of the rows.

Here's an example showing how it works.  I created a simple table:

  use test;

  drop table if exists animals;

  create table animals (
    name varchar(10),
    legs tinyint);

  insert into animals (name, legs) values ('owl', 2);
  insert into animals (name, legs) values ('dog', 4);
  insert into animals (name, legs) values ('ant', 6);
  insert into animals (name, legs) values ('tick', 8);
  insert into animals (name, legs) values ('pentapus', 5);

Then I wrote the following little python script to retrieve the rows:

  import MySQLdb

  conn = MySQLdb.connect (user = "waltman", db = "test")
  cursor = conn.cursor()

  # fetch one at a time, ordered by legs
  num_rows = cursor.execute("SELECT name, legs FROM animals ORDER BY legs")
  print num_rows, "rows returned"

  row = cursor.fetchone()
  while row != None:
      print row
      row = cursor.fetchone()

  print

  # fetch all at once, ordered by name
  cursor.execute("SELECT name, legs FROM animals ORDER BY name")
  rows = cursor.fetchall()
  for r in rows:
      print r

  cursor.close ()
  conn.close ()

I hope this helps.

Walt

Attachment: signature.asc
Description: Digital signature

___________________________________________________________________________
Philadelphia Linux Users Group         --        http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion  --   http://lists.phillylinux.org/mailman/listinfo/plug