def write_candidates_table(self, candidate_index):
   states_short, states_long = zip(*self.db.fetch('SELECT code, state_name FROM states'))
   for candidate, elections in candidate_index.iteritems():
     self.db.execute("""INSERT INTO candidate_names (candidate_name) VALUES ("%s")""" % candidate)
     for electorates in elections.split(','):
       electorate_years = electorates.split()
       state = [el for el in electorate_years if el.upper() in states_short]
       if len(state) == 1:
         state = state[0]
         state_ix = electorate_years.index(state)
         electorate = ' '.join(electorate_years[:state_ix])
         state_code = state.upper()
         years = electorate_years[state_ix+1:]
       else:
         state = [state for state in states_long if state in electorates]
         if len(state) == 1:
           state = state[0]
           electorate, allyears = electorates.split(state)
           state_code = states_short[states_long.index(state)]
           years = allyears.split()
           if len(electorate) == 0: electorate = state
         else:
           try:
             state = 'NULL'
             state_code = 'NULL'
             electorate, allyears = electorates.split(' ',1)
             years = allyears.split()
           except:
             continue
       for year in years:
         if not (year.startswith('1') or year.startswith('2')):
           continue # Catch bad parsing
         if year.endswith('b') or year.endswith('b*'):
           continue # Exclude bielections
         safe_year = year[:4]
         was_elected = 1 if year.endswith('*') else 0
         if electorate == 'Senate':
           election_id = utils.get_election_id(self.db, safe_year, 'senate')
           electorate_id = "NULL"
         else:
           election_id = utils.get_election_id(self.db, safe_year, 'house')
           electorate_id = utils.get_electorate_id(self.db, electorate, state_code, election_id) if election_id is not "NULL" else "NULL"
         if election_id is "NULL":
           logging.warn(candidate+' '+electorate+' '+state+' '+year)
         candidate_sql = """SELECT id FROM candidate_names WHERE candidate_name = "%s" """ % candidate
         candidate_name_id = utils.safe_id(self.db.fetch(candidate_sql))
         insert_str = """INSERT INTO candidacies (election_id, electorate_id, state_code, candidate_name_id, was_elected)
                         VALUES ({election_id}, {electorate_id}, '{state_code}', "{candidate_name_id}", {was_elected})"""
         insert_sql = insert_str.format(election_id=election_id, electorate_id=electorate_id, state_code=state_code, candidate_name_id=candidate_name_id, was_elected=was_elected)
         self.db.execute(insert_sql)
 def parse_electorate_file(self, fileinfo):
   fname = fileinfo['fname']
   state_code = fileinfo['state'].upper()
   election_id = utils.get_election_id(self.db, fileinfo['year'], fileinfo['chamber'])
   with open(fname, 'r') as f:
     lines = [line.strip() for line in f]
     breaks = [i for i,x in enumerate(lines) if '===' in x]
     electorate_chunks = [lines[breaks[i]-1:breaks[i+1]-1] 
                          for i in range(len(breaks[:-1]))]
     electorate_chunks.append(lines[breaks[-1]-1:])
     for chunk in electorate_chunks[2:]:
       headline = re.split('\s\s\s+', chunk[0])
       if len(headline) == 1:
         continue
       electorate_name = headline[0].split(',')[0].title()
       electorate_counts = self._parse_counts_line(re.split('[\s,]',headline[1]))
       electorate_enrolled = electorate_counts[0]
       if len(electorate_counts) > 1:
         electorate_ballots = electorate_counts[1]
       else:
         electorate_ballots = "NULL"
       sql = """
         INSERT INTO electorates (election_id, state_code, electorate_name, enrollments, ballots)
         VALUES (%d, '%s', "%s", %d, %s)
       """ % (election_id, state_code, electorate_name, electorate_enrolled, electorate_ballots)
       self.db.execute(sql)
 def parse_results_data(self, fileinfo):
   fname = fileinfo['fname']
   election_id = utils.get_election_id(self.db, fileinfo['year'], fileinfo['chamber'])
   with open(fname, 'r') as f:
     lines = [line.strip() for line in f]
     breaks = [i for i,x in enumerate(lines) if '===' in x]
     electorate_data = [lines[breaks[i]-1:breaks[i+1]-1] 
                        for i in range(len(breaks[:-1]))]
     electorate_data.append(lines[breaks[-1]-1:])
     party_dict = self._get_party_dict(electorate_data[0])
     for electorate in electorate_data[3:]:
       electorate_name, _, _ = self._get_electorate_info(electorate)
       if electorate_name is None:
         continue
       electorate_id = utils.get_electorate_id(self.db, electorate_name, fileinfo['state'], election_id)
       logging.info(electorate_name + ' ' +fileinfo['year'])
       ballot_counts = self._parse_ballot_counts(electorate)
       logging.info(ballot_counts)
       for candidate in ballot_counts.iterkeys():
         if candidate != 'Informal':
           ballot_name = ballot_counts[candidate]['full_name']
           candidate_name_id = utils.get_candidate_name_id(self.db, ballot_counts[candidate]['full_name'])
           candidacy_id = utils.get_candidacy_id(self.db, election_id, electorate_id, candidate_name_id)
           party_code = ballot_counts[candidate]['party']
           votes = ballot_counts[candidate]['votes']
           pct = ballot_counts[candidate]['pct']
           tpp_votes = ballot_counts[candidate]['tpp_votes']
           tpp_pct = ballot_counts[candidate]['tpp_pct']
         else:
           ballot_name = 'NULL'
           candidate_name_id = 'NULL'
           candidacy_id = 'NULL'
           party_code = 'NULL'
           votes = ballot_counts[candidate]['votes']
           pct = ballot_counts[candidate]['pct']
           tpp_votes = 'NULL'
           tpp_pct = 'NULL'
         sql = """
           INSERT INTO results (election_id, electorate_id, candidate_name_id, candidacy_id, ballot_name, party_code, votes, pct, tpp_votes, tpp_pct)
           VALUES (%s, %s, %s, %s, """ % (election_id, electorate_id, candidate_name_id, candidacy_id)
         sql += '"%s", ' % ballot_name if ballot_name != 'NULL' else 'NULL, '
         sql += '"%s", ' % party_code if party_code != 'NULL' else 'NULL, '
         sql += "%s, %s, %s, %s)" % (votes, pct, tpp_votes, tpp_pct)
         self.db.execute(sql)