week5/a.py

   1 '''
   2 AY250 HW5 A
   3 Build sqlite3 table of polls
   4 Author: Bryan Steinbach
   5 '''
   6
   7 import os
   8 import sqlite3
   9
  10 rawfn = 'senate_polls.raw'
  11 tablefn = 'poll'
  12
  13 if not os.path.exists(rawfn):
  14         print "Error, missing file %s"%rawfn
  15         exit(1)
  16
  17 if os.path.exists(tablefn):
  18         os.remove(tablefn)
  19
  20 conn = sqlite3.connect('poll')
  21
  22 c = conn.cursor()
  23
  24 months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
  25
  26 c.execute(''' create table polls
  27                 (state text, gop int, dem int, ind int, date1 text, date2 text, pollster text)''')
  28
  29 def format_date(month,day):
  30         moni = months.index(month)+1
  31         mons = str(moni)
  32         if len(mons) == 1:
  33                 mons = '0'+mons
  34
  35         return '2010-'+mons+'-'+day
  36
  37 with open(rawfn) as f:
  38         for line in f.readlines():
  39                 words = line.strip().split()
  40                 state,gop,dem,ind,mon1,day1,mon2,day2 = words[:8]
  41
  42                 date1 = format_date(mon1,day1)
  43                 date2 = format_date(mon2,day2)
  44
  45                 pollster = ' '.join(words[8:])
  46                 c.execute(''' insert into polls
  47                                 values (?,?,?,?,?,?,?) ''',(state,gop,dem,ind,date1,date2,pollster))
  48
  49 conn.commit()
  50
  51 # sanity check
  52 c.execute('select * from polls order by date1 desc')
  53 for i in range(10):
  54         print c.next()
  55
  56 c.close()