#!/usr/bin/python
import sys
import MySQLdb

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

try:
  cursor = conn.cursor()
  cursor.execute("""
      CREATE TABLE genres (
        id     INT,
        name varchar(64)
      )
    """)
  cursor.execute("""
      INSERT INTO genres (name)
      VALUES ('classical'), ('easy listening'), ('ambient'), ('new age')
    """)
  print "Number of rows inserted: %d" % cursor.rowcount

  cursor.execute ("SELECT name FROM genres")
  while (1):
    row = cursor.fetchone()
    if row == None:
      break
    print "%s, %s" % (row[0])
  print "Number of rows returned: %d" % cursor.rowcount

  cursor.execute("SELECT name FROM genres")
  rows = cursor.fetchall()
  for row in rows:
    print "%s, %s" % (row[0], row[1])
  print "Number of rows returned: %d" % cursor.rowcount

  cursor.execute("""
        UPDATE genres SET name = 'hip-hop'
        WHERE name = 'rap'
    """)
  print "Number of rows updated: %d" % cursor.rowcount

  cursor.execute ("""
        UPDATE genres SET name = %s
        WHERE name = %s
      """, ("rap", "hip-hop"))
  print "Number of rows updated: %d" % cursor.rowcount

# create a dictionary cursor so that column values
# can be accessed by name rather than by position
  cursor.close ()
  cursor = conn.cursor(MySQLdb.cursors.DictCursor)
  cursor.execute("SELECT name FROM genres")
  result_set = cursor.fetchall()
  for row in result_set:
    print "%s, %s" % (row["name"])
  print "Number of rows returned: %d" % cursor.rowcount

  cursor.close ()

except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit (1)

conn.commit ()
conn.close ()