#!/usr/bin/python
# test.py - create gene table and
# retrieve information from it

import sys
import MySQLdb

try:
	conn = MySQLdb.connect (host="127.0.0.1", db="bmi219")

	# Get a cursor we can work with
	cursor = conn.cursor()
	
	# Use the execute method to pass SQL commands to the database
	cursor.execute("DROP TABLE IF EXISTS `GENE`")
	# Note that we use triple quotes when we need multiple lines
	cursor.execute("""
		CREATE TABLE `GENE` 
		(
			`ID` char(16),
			`NAME` varchar(20),
			`PROTEIN` longtext,
			`START#` int,
			PRIMARY KEY (`ID`)
		)
	""")

	cursor.execute("INSERT INTO `GENE` VALUES ('G1','AMP','MAKK...',-5),('G2','TET','MYAK...',-10),('G3','NGF','MYAK...',-1)")

	print "Number of rows inserted: %d"%cursor.rowcount

	# OK, now lets try to get some data out
	cursor.execute("SELECT NAME, PROTEIN from GENE")
	while (1):
		row = cursor.fetchone ()
		if row == None:
			break
		print "%s, %s"%(row[0],row[1])

	print "Number of rows returned: %d"%cursor.rowcount

	# Another way to do the same thing
	cursor.execute("SELECT NAME, PROTEIN from GENE")
	rows = cursor.fetchall ()
	for row in rows:
		print "%s, %s"%(row[0],row[1])

	print "Number of rows returned: %d"%cursor.rowcount

	cursor.close()
	conn.commit ()
	conn.close()

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