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_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)