#!/usr/bin/python

import MySQLdb
import sys

query_string = "SELECT name, category FROM animal"

try:
   conn = MySQLdb.connect(host = "somehostname",
            user = "root",
            passwd = "",
            db = "test")
except MySQLdb.Error, e:
   print "Error %d: %s" % (e.args[0], e.args[1])
   sys.exit(1)

cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS animal")
cursor.execute("""
   CREATE TABLE animal
   (
      name CHAR(40),
      category CHAR(40)
   )
   """)

cursor.execute("""
   INSERT INTO animal (name, category)
   VALUES
      ('snake', 'reptile'),
      ('frog', 'amphibian'),
      ('tuna', 'fish'),
      ('racoon', 'mammal'),
      ('dolphin', 'mammal')
   """)

print "%d rows inserted" % cursor.rowcount

cursor.execute(query_string)
while (1):
   row = cursor.fetchone()
   if row == None:
      break
   print "%s, %s" % row
#   or... print "%s, %s" % (row[0], row[1])

print "%d rows were returned" % cursor.rowcount

# alternative way: (using same select still)
cursor.fetchall()
rows = cursor.fetchall()
for row in rows:
   print "%s, %s" % row
#   or... print "%s, %s" % (row[0], row[1])
print "%d rows were returned" % len(rows) 

cursor.close()

# now try with a dictionary query
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(query_string)

rows = cursor.fetchall()
for row in rows:
   print "%s, %s" % (row["name"], row["category"])
print "%d rows were returned" % cursor.rowcount

# now do a update query using the handy insertion variables
name_now = "snake"
name_new = "turtle"

cursor.execute("""
   UPDATE animal SET name = %s
   WHERE name = %s
   """, (name_new, name_now))
print "%d rows were returned" % cursor.rowcount

cursor.close()
conn.close()
sys.exit(0)

-- MattWalsh - 11 Sep 2002