def string(s):
  return str(s).strip()

# our parsing rules (start, end, data converting function)
columns = {
  'name'  :(0, 16, string),
  'bike'  :(17, 22, float),
  'swim'  :(23, 27, float),
  'run'   :(28, 32, float),
  'total' :(33, 38, float)
}

# the SQL to create our table
ddl = """
  create table triathlon (
    name varchar(16),
    bike decimal(4,2),
    swim decimal(4,2),
    run decimal(4,2),
    total decimal(4,2)
  )"""

def parse(fn):
  '''
  Parse the file and return a list of dictionaries containing
  the column/value pairs from the row.
  '''

  # open the file
  fp = open(fn, "r")

  # first read all the lines, then strip any whitespace
  #  finally, filter any rows that have no length
  data = filter(lambda x: x, map(lambda x: x.strip(), fp.readlines()))

  # we don't need the header columns, we already have that
  #  information captured in the columns dictionary
  data = data[2:]

  # it's important to close what we open
  fp.close()

  # a list of all the processed rows
  results = []

  # iterate the data
  while data:
    # build our datastore
    ds = {}

    # get the row to be processed
    row = data[0]

    # iterate the columns
    for name, rules in columns.items():

      # unfold our rules
      start, end, func = rules

      # store the results of applying our rule
      ds[name] = func(row[start:end])

    # add the processed row to the rest
    results.append(ds)

    # since we just worked on this row we can get rid of it
    del data[0]

  # return the results of parsing the file
  return results

def get_connection():
  """Open and return a database connection."""

  # the parameters for establishing a connection
  url = "jdbc:mysql://localhost/racing"
  username = "bzimmer"
  password = None
  jdbcdriver = "com.mysql.jdbc.Driver"

  # establish the connection and return it
  from com.ziclix.python.sql import zxJDBC
  db = zxJDBC.connect(url, username, password, jdbcdriver)

  return db

def store(data):
  """Store the data in the database."""

  # get a new connection
  db = get_connection()

  # open a cursor so we can perform our work
  cursor = db.cursor()

  try:

    try:
      # since we have all new results, drop the existing table
      cursor.execute("drop table triathlon")
    except:
      # it might not be there
      pass

    # create the table
    cursor.execute(ddl)

    for row in data:
      # build our sql dynamically
      items = row.items()

      keys = map(lambda x: x[0], items)
      values = map(lambda x: x[1], items)

      # this sql is for a prepared statement
      sql = "insert into triathlon (%s) values (%s)" % (",".join(keys), ",".join("?" * len(values)))

      # execute the sql
      cursor.execute(sql, values)

  finally:
    # close what we open
    cursor.close()
    db.close()

def strongest_bike():

  # get a new connection
  db = get_connection()

  # open a cursor so we can answer our question
  cursor = db.cursor()

  try:

    # find the answer
    cursor.execute("select name, bike/total from triathlon order by 2 desc")
    for row in cursor.fetchall():
      print row

  finally:
    # close what we open
    cursor.close()
    db.close()

def go(fn):
  # parse
  r = parse(fn)

  # store
  store(r)

  # query
  strongest_bike()