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

try:
  conn = sqlite3.connect ('bmi219.db')

  # 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)
  """)

  cursor.execute("""
                INSERT INTO 'GENE' VALUES 
                  ('G2','TET','MYAK...',-10)
  """)

  cursor.execute("""
                INSERT INTO 'GENE' VALUES 
                  ('G3','NGF','MYAK...',-1)
  """)

  cursor.execute("SELECT NAME, PROTEIN from GENE")
  while (1):
    row = cursor.fetchone ()
    if row == None:
      break
    print "%s, %s"%(row[0],row[1])

  # 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])

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

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