def _factory_election_data(websafe_election_key):
""" Factory database with data from settings.py """
# create or update election
if websafe_election_key is not None:
election_key = ndb.Key(urlsafe=websafe_election_key)
election = election_key.get()
else:
election = Election()
election.populate(**ELECTION_DATA)
election_key = election.put()
for pos_data_orig in POSITION_DATA:
# avoid directly delete on setting objects
pos_data = pos_data_orig.copy()
position_data = pos_data['data']
del pos_data['data']
position_name = pos_data['name']
position = Position.query(ancestor=election_key).\
filter(Position.name == position_name).get()
if position is None:
logger.debug('creating new position entity')
position_id = ndb.Model.allocate_ids(
size=1, parent=election_key)[0]
position_key = ndb.Key(Position, position_id, parent=election_key)
position = Position(key=position_key)
position.populate(**pos_data)
position_key = position.put()
# remove all roles under position
ndb.delete_multi(position.candidate_keys)
# create all roles from data
candidates = []
for index, data_dict in enumerate(position_data):
candidate_id = ndb.Model.allocate_ids(
size=1, parent=position_key)[0]
candidate_key = ndb.Key(
Candidate, candidate_id, parent=position_key)
candidate = Candidate(key=candidate_key)
data_dict['voting_index'] = index
candidate.populate(**data_dict)
candidates.append(candidate)
position.candidate_keys = ndb.put_multi(candidates)
position.put()
return "update all data successfully"
def vote_check(self, args, by):
if self.is_target_user:
try:
account = self.irc.users[args[1]]['account'].lower()
except KeyError:
return self.irc.notice(by, 'Can\'t start vote: User not found '
'or not identified.')
user = self.irc.usermap.get(account)
if not user or not user.get('lines'):
return self.irc.notice(by, 'Can\'t start vote: User has never '
'interacted with the channel.')
try:
x = Effective.select().where((Effective.vote_type == self.name) &
(Effective.vote_target == self.get_target(args))) \
.get()
try:
elecid = x.election.id
except Election.DoesNotExist:
elecid = "Unknown election"
return self.irc.notice(by, 'Can\'t start vote: There\'s an identical motion already active (\002{0}\002).'.format(elecid))
except Effective.DoesNotExist:
pass
try:
x = Election.select().where((Election.vote_type == self.name) &
(Election.vote_target == self.get_target(args)) &
(Election.status == 3) &
(Election.close > (datetime.utcnow() - timedelta(seconds=self.cooldown)))).get()
return self.irc.notice(by, 'Can\'t start vote: There was a similar vote that failed not too long ago (\002{0}\002).'.format(x.id))
except Election.DoesNotExist:
pass
if self.required_time != 0:
reqtime = datetime.utcnow() - timedelta(seconds=self.required_time)
if user['first_seen'] > reqtime:
return self.irc.notice(by, "Can't start vote: User at issue "
"has not been present long enough for "
"consideration.")
if user['last_seen'] < reqtime:
return self.irc.notice(by, "Can't start vote: User at issue "
"has not been active recently.")
if user['lines'] < self.required_lines:
return self.irc.notice(by, "Can't start vote: User at issue "
"has {0} of {1} required lines"
.format(user['lines'],
self.required_lines))
return True # True = check passed
def push_election(data):
entry = dict()
entry["class_name"] = data["type"]["class"]
# Accounts for party being present if not general election
if data["type"]["class"].lower() != "general":
entry["party"] = data["type"]["party"]
entry["office"] = data["office"]
# Two different formats for district number across JSONs
if type(data["district"]) == int:
entry["district_number"] = data["district"]
else:
entry["district_number"] = data["district"]["number"]
entry["election_day"] = data["dates"]["election_day"]
entry["early_start"] = data["dates"]["early_start"]
entry["early_end"] = data["dates"]["early_end"]
entry["video_url"] = data["video_url"]
# Assign correct politician id in election results
if "results" in data:
entry["total_voters"] = data["results"]["total_voters"]
# Get winner
winner_name = data["results"]["winner"]["name"]
winner_pol = db.session.query(Politician).filter_by(name=winner_name).first()
if winner_pol:
data["results"]["winner"]["id"] = winner_pol.id
for pol in data["results"]["vote_counts"]:
pol_name = pol["name"]
pol_orm = db.session.query(Politician).filter_by(name=pol_name).first()
if pol_orm:
pol["id"] = pol_orm.id
entry["results"] = data["results"]
else:
return None
election_db_instance = Election(**entry)
# Past election results append politicians to relationship
if "results" in entry:
for pol in entry["results"]["vote_counts"]:
pol_name = pol["name"]
pol_orm = db.session.query(Politician).filter_by(name=pol_name).first()
if pol_orm:
election_db_instance.politicians.append(pol_orm)
# Upcoming election results append politicians to relationship
if "candidates" in data:
for pol in data["candidates"]:
pol_name = pol["name"]
pol_orm = db.session.query(Politician).filter_by(name=pol_name).first()
if pol_orm:
election_db_instance.politicians.append(pol_orm)
db.session.add(election_db_instance)
def test_election(self):
"""Test the public ``add_candidate'' function provided by the
Election() class.
"""
el = Election()
con1 = Constituency("test")
con2 = Constituency("test2")
party1 = Party("party1")
party2 = Party("party2")
candidate1 = Candidate("candidate1", party1, con1)
candidate2 = Candidate("candidate2", party2, con1)
candidate3 = Candidate("candidate3", party2, con2)
el.add_candidate(candidate1)
self.assertTrue(candidate1 in el.candidates)
self.assertTrue(party1 in el.parties)
self.assertTrue(con1 in el.constituencies)
with self.assertRaises(AssertionError):
el.add_candidate(candidate1)
el.add_candidate(candidate2)
self.assertTrue(candidate2 in el.candidates)
self.assertTrue(party1 in el.parties)
self.assertTrue(con1 in el.constituencies)
self.assertEqual(len(el.candidates), 2)
self.assertEqual(len(el.parties), 2)
self.assertEqual(len(el.constituencies), 1)
el.add_candidate(candidate3)
self.assertTrue(candidate3 in el.candidates)
self.assertTrue(party2 in el.parties)
self.assertTrue(con2 in el.constituencies)
self.assertEqual(len(el.candidates), 3)
self.assertEqual(len(el.parties), 2)
self.assertEqual(len(el.constituencies), 2)
def _closevote(self, voteid):
""" Called when a vote is to be closed """
vote = Election.get(Election.id == voteid)
vclass = VOTE_NAMES[vote.vote_type](self)
suffrages = Suffrage.select().where(Suffrage.election == vote)
if suffrages.count() < vclass.quorum:
self.msg(
_("\002#{0}\002: Failed to reach quorum: \002{1}\002 of "
"\002{2}\002 required votes.").format(
voteid, suffrages.count(), vclass.quorum))
vote.status = 2 # Closed - quorum
vote.save()
return
yeas = 0
nays = 0
for sf in suffrages:
if sf.yea:
yeas += 1
else:
nays += 1
perc = int((yeas / suffrages.count()) * 100)
if (perc < 75 and vclass.supermajority) or (perc < 51):
self.msg(
_("\002#{0}\002: \002{1}\002: \037{2}\037. \002\00300,04The nays have it.\003\002 "
"Yeas: \00303{3}\003. Nays: \00304{4}\003. \00304{5}\003% of approval (required at least \002{6}%)"
).format(voteid, vote.vote_type, vote.vote_target, yeas,
nays, perc, 75 if vclass.supermajority else 51))
vote.status = 3 # closed - not approved
vote.save()
return
self.msg(
_("\002#{0}\002: \002{1}\002: \037{2}\037. \002\00300,03The yeas have it.\003\002 Yeas: \00303{3}\003. Nays: \00304{4}\003. "
"\00303{5}\003% of approval (required at least \002{6}%)").
format(voteid, vote.vote_type, vote.vote_target, yeas, nays, perc,
75 if vclass.supermajority else 51))
vote.status = 1 # closed - passed
vote.save()
vclass.on_pass(vote.vote_target)
act = Effective(vote_type=vote.vote_type,
close=datetime.utcnow() +
timedelta(seconds=vclass.duration),
vote_target=vote.vote_target,
election=vote)
act.save()
self.eventloop.schedule_in(vclass.duration, self._expire, act.id)
def makeANewElection(name, heading, para, category, start_time, end_time,
in_favour, not_in_favour, status, total_votes, org_name,
creator_username):
election = Election(name=name,
heading=heading,
para=para,
category=category,
start_time=start_time,
end_time=end_time,
in_favour=in_favour,
not_in_favour=not_in_favour,
status=status,
total_votes=total_votes,
organisation_name=org_name,
creator_username=creator_username)
session.add(election)
session.commit()
return jsonify(Election=election.serialize)
def _update_election_status():
""" iterate all elections and update can_vote status
return: number of votes running
"""
qry = Election.query()
election_iterator = qry.iter()
cnt = 0
for election in election_iterator:
now = datetime.utcnow()
if election.start_date <= now and now <= election.end_date:
election.can_see_results = True
election.can_vote = True
cnt = cnt + 1
else:
election.can_vote = False
election.put()
return cnt
def fill_election_table():
"""
fills the election table with data from elections.json
:return:
"""
counter = 0
for state_key in ELECTION_JSON.keys():
state_elections = ELECTION_JSON[state_key][0]
for key in state_elections.keys():
counter += 1
# print("Election %i == %s" % (counter, key))
temp_election = Election(
name=key,
date=state_elections[key]['date'],
level=state_elections[key]['type'],
descriptive_name=descriptive_name_parser(key))
database.session.add(temp_election)
database.session.commit()
def election_task(data):
if not data:
print("invalid json")
return False
try:
check_election_data(data, True)
except Exception as e:
print("ERROR", e)
return False
e = Election(id=data['id'],
title=data['title'][:255],
description=data['description'],
questions=dumps(data['questions']),
start_date=data['start_date'],
end_date=data['end_date'],
callback_url=data['callback_url'],
num_parties=len(data['authorities']),
threshold_parties=len(data['authorities']),
status='creating')
db.session.add(e)
for auth_data in data['authorities']:
authority = Authority(name=auth_data['name'],
ssl_cert=auth_data['ssl_cert'],
orchestra_url=auth_data['orchestra_url'],
election_id=data['id'])
db.session.add(authority)
db.session.commit()
task = SimpleTask(receiver_url=app.config.get('ROOT_URL', ''),
action="create_election",
queue="launch_task",
data={'election_id': data['id']})
task.create_and_send()
return task
def create_models(request):
json_file = json.load(request.FILES['file'])
type = request.POST['options']
for obj in json_file:
new_model = {'election_type': type}
votes = []
for k, v in obj.iteritems():
if 'Pkt' in k:
try:
new_model['notes'].append({k: v})
except:
new_model['notes'] = [{k: v}]
continue
k_coded = coder[k.encode('utf-8')]
if 'kw' in k_coded or 'prez' in k_coded:
vote = Vote(political_party=k, amount=v)
vote.save()
votes.append(vote)
else:
if 'Gmina' == k:
new_model['district'] = v
else:
new_model[coder[k.encode('utf-8')]] = v
try:
new_model['notes'] = json.dumps(new_model['notes'])
except:
pass
if type != 'candidate':
election_model = Election(**new_model)
election_model.save()
elif type == 'candidate':
election_model = Candidate(**new_model)
election_model.save()
continue
for v in votes:
v.election = election_model
v.save()
def get_election_by_uuid(uuid):
if not uuid:
raise Exception(_("no election ID"))
return Election.get_by_uuid(uuid)
def election_task(data):
if not data:
print("invalid json")
return False
try:
check_election_data(data, True)
except Exception, e:
print("ERROR", e)
return False
e = Election(id=data['id'],
title=data['title'][:255],
description=data['description'],
questions=dumps(data['questions']),
start_date=data['start_date'],
end_date=data['end_date'],
callback_url=data['callback_url'],
num_parties=len(data['authorities']),
threshold_parties=len(data['authorities']),
status='creating')
db.session.add(e)
for auth_data in data['authorities']:
authority = Authority(name=auth_data['name'],
ssl_cert=auth_data['ssl_cert'],
orchestra_url=auth_data['orchestra_url'],
election_id=data['id'])
db.session.add(authority)
db.session.commit()
task = SimpleTask(receiver_url=app.config.get('ROOT_URL', ''),
def vote_info(self, by, voteid):
try:
elec = Election.get(Election.id == voteid)
except Election.DoesNotExist:
return self.notice(by, 'Failed: Vote not found')
vtype = VOTE_NAMES[elec.vote_type](self)
if elec.status == 0:
tdel = elec.close - datetime.utcnow()
ostr = self._resolve_time(tdel, 'left')
else:
tdel = datetime.utcnow() - elec.close
ostr = self._resolve_time(tdel, 'ago')
self.notice(
by, "Information on vote #{0}: \002{1}\002 ({2})".format(
elec.id, self._resolve_status(elec.status), ostr))
votes = Suffrage.select().where(Suffrage.election == elec)
yeacount = 0
yeas = ""
naycount = 0
nays = ""
for vot in votes:
if vot.yea:
yeacount += 1
yeas += vot.emitted_by.name + " "
else:
naycount += 1
nays += vot.emitted_by.name + " "
votecount = yeacount + naycount
perc = int((yeacount / votecount) * 100) if votecount != 0 else 0
percneeded = 75 if vtype.supermajority else 50
if elec.status == 1:
try:
eff = Effective.get(Effective.election == elec)
tdel = eff.close - datetime.utcnow()
ostr = self._resolve_time(tdel, 'left')
self.notice(by, " - \002\00303ACTIVE\003\002 {0}".format(ostr))
except Effective.DoesNotExist:
self.notice(
by, " - \002\00304NOT EFFECTIVE ANYMORE\003\002 (expired)")
pass
elif elec.status == 0:
if votecount < vtype.quorum:
self.notice(
by,
" - \002\00307Needs {0} more votes for quorum\002".format(
vtype.quorum - votecount))
else:
if perc < percneeded:
self.notice(
by,
" - \002\00304Motion is not passing ({0}% of approval, needs {1}%)\002"
.format(perc, percneeded))
else:
self.notice(
by,
" - \002\00303Motion is passing ({0}% of approval, needs {1}%)\002"
.format(perc, percneeded))
if yeacount == 0:
yeas = " - "
if naycount == 0:
nays = " - "
self.notice(
by, " - \002\00303YEA\003\002 - \002{0}\002: {1}".format(
yeacount, yeas))
self.notice(
by, " - \002\00304NAY\003\002 - \002{0}\002: {1}".format(
naycount, nays))
def election_from_csv(filename):
"""
Create an Election object from a csv file of election data.
"""
election = None
with open(filename, 'rb') as csvfile:
reader = csv.reader(csvfile, delimiter=',', quotechar='"')
# create an Election object
election = Election()
constituencies = {}
parties = {}
candidates = {}
for row in reader:
# extract the fields from the CSV data
constituencyName = row[0]
candidateName = row[1]
partyName = row[2]
voteCount = int(row[3])
# reconcile the constituency
if constituencyName not in constituencies:
con = Constituency(constituencyName)
constituencies[constituencyName] = con
else:
con = constituencies[constituencyName]
# reconcile the party
if partyName not in parties:
par = Party(partyName)
parties[partyName] = par
else:
par = parties[partyName]
# reconcile the candidate
if (candidateName, partyName, constituencyName) \
not in candidates:
can = Candidate(candidateName,
par,
con,
voteCount)
candidates[(candidateName,
partyName,
constituencyName)] = can
else:
raise ValueError(
"""A candidate with the same details already exists""")
# add the candidate to the constituency
con.add_candidate(can)
# add the candidate to the party
par.add_candidate(can)
# add the candidate to the election
election.add_candidate(can)
return election
from tools.django_tools import BakeView
from django import forms
import datetime
from models import Election, Council, Postcode
from django.forms.extras.widgets import SelectDateWidget
from amend_pdf import create_pdf
from jsignature.forms import JSignatureField
from jsignature.utils import draw_signature
from jsignature.widgets import JSignatureWidget
LocalBake = BakeView.local()
valid_elections = Election.valid_elections()
election_choices = ()
if len(valid_elections) > 1:
election_choices += ((-1,"May Elections And EU Referendum"),)
for e in valid_elections:
election_choices += ((e.id, e.name),)
election_choices += ( (0,"All Future Elections"),)
years = range(1900,2001)[::-1] #2001 minus 16 years + 1 for scotland
def on_message(self, target, by, message):
try:
account = self.users[by]['account']
except KeyError:
print("{0}: Not identified/not found".format(by))
return
if not account:
return # Unregistered users don't exist
account = account.lower()
if target == config.CHANNEL:
self.count_line(account)
message = message.strip().lower()
if not message.startswith('!'):
return
command = message[1:].split()[0]
args = message.split()[1:]
if command == 'vote':
if not args:
return
args[0] = args[0].strip('#')
if args[0] in list(VOTE_NAMES): # creating a vote!
self.start_vote(by, args)
elif args[0] == "list" and target == config.CHANNEL:
print('list ', by)
if by not in self.channels[config.CHANNEL]['modes'].get(
'v', []):
if by not in self.channels[config.CHANNEL]['modes'].get(
'o', []):
return self.notice(
by, 'Failed: You are not enfranchised.')
vpar, unk = votelistParser.parse_known_args(args[1:])
if not vpar.type:
votes = Election.select().where(Election.status == 0) \
.order_by(Election.id.desc()).limit(5)
else:
if vpar.type not in list(VOTE_NAMES):
return self.notice(by, 'Failed: Unknown vote type')
votes = Election.select() \
.where(Election.vote_type == vpar.type) \
.order_by(Election.id.desc()).limit(10)
if not votes:
return self.notice(by, 'No matching results.')
user = User.get(User.name == account)
for vote in votes:
posit = Suffrage.select() \
.where((Suffrage.election == vote) &
(Suffrage.yea == True)).count()
negat = Suffrage.select() \
.where((Suffrage.election == vote) &
(Suffrage.yea == False)).count()
try:
yv = Suffrage.get((Suffrage.election == vote)
& (Suffrage.emitted_by == user))
if yv.yea:
you = '\00300,03YEA\003'
else:
you = '\00300,04NAY\003'
except Suffrage.DoesNotExist:
you = '\00300,01---\003'
stat = self._resolve_status(vote.status)
if vote.status == 0:
tdel = vote.close - datetime.utcnow()
ostr = self._resolve_time(tdel, _('left'))
else:
tdel = datetime.utcnow() - vote.close
ostr = self._resolve_time(tdel, _('ago'))
self.msg(
_('\002#{0} YEA: \00303{1}\003 NAY: \00305{2}\003 '
'YOU: {3} {4} {5}\002 \037{6}\037 - {7}').format(
vote.id, posit, negat, you, stat, vote.vote_type,
vote.vote_target, ostr))
elif args[0].isdigit() or args[0] in ['y', 'yes', 'n', 'no']:
if by not in self.channels[config.CHANNEL]['modes'].get(
'v', []):
if by not in self.channels[config.CHANNEL]['modes'].get(
'o', []):
return self.notice(
by, 'Failed: You are not enfranchised.')
user = User.get(User.name == account)
if args[0].isdigit():
if len(args) == 1:
return self.vote_info(by, args[0])
voteid = args[0]
positive = True if args[1] in ['y', 'yes'] else False
else:
positive = True if args[0] in ['y', 'yes'] else False
if len(args) == 1:
xe = Election.select().where(Election.status == 1)
if xe.count() == 1:
voteid = xe.get().id
else:
return self.notice(
by, 'Failed: Usage: !vote y/n '
'<vote id>')
else:
voteid = args[1].strip('#')
if not voteid.isdigit():
self.notice(by, 'Usage: !vote y/n <vote id>')
return
try:
elec = Election.get(Election.id == voteid)
except Election.DoesNotExist:
return self.notice(by, 'Failed: Vote not found')
if elec.status != 0:
return self.notice(by, 'Failed: This vote already '
'ended')
return self.vote(elec, user, by, positive,
(target != config.CHANNEL))
def clean(self):
super(ElectionForm, self).clean()
cd = self.cleaned_data
election_value = cd.get('election')
cd['universal'],cd['single_day'],cd['time_range'] = Election.get_time(election_value)
def _create_election(request):
data = request_to_dict(request)
election = Election(**data)
election.put()
return election
def generate_private_info(task):
'''
Generates the local private info for a new election
'''
input_data = task.get_data()['input_data']
election_id = input_data['id']
# 1. check this is a new election and check input data
private_data_path = app.config.get('PRIVATE_DATA_PATH', '')
election_privpath = os.path.join(private_data_path, str(election_id))
# check generic input data, similar to the data for public_api
check_election_data(input_data, False)
# check the sessions data
if not isinstance(input_data.get('sessions', None), list) or\
not len(input_data['sessions']):
raise TaskError(dict(reason="No sessions provided"))
for session in input_data['sessions']:
if not isinstance(session, dict) or 'id' not in session or\
'stub' not in session or\
not isinstance(session['stub'], str) or\
not re.match("^[a-zA-Z0-9_-]+$", session['id']):
raise TaskError(dict(reason="Invalid session data provided"))
# check that we are indeed one of the listed authorities
auth_name = None
for auth_data in input_data['authorities']:
if auth_data['orchestra_url'] == app.config.get('ROOT_URL', ''):
auth_name = auth_data['name']
if not auth_name:
raise TaskError(
dict(
reason="trying to process what SEEMS to be an external election"
))
# localProtInfo.xml should not exist for any of the sessions, as our task is
# precisely to create it. note that we only check that localProtInfo.xml
# files don't exist, because if we are the director, then the stub and
# parent directory will already exist
for session in input_data['sessions']:
session_privpath = os.path.join(election_privpath, session['id'])
protinfo_path = os.path.join(session_privpath, 'localProtInfo.xml')
if os.path.exists(protinfo_path):
raise TaskError(
dict(reason="session_id %s already created" % session['id']))
# 2. create base local data from received input in case it's needed:
# create election models, dirs and stubs if we are not the director
if certs_differ(task.get_data()['sender_ssl_cert'],
app.config.get('SSL_CERT_STRING', '')):
if os.path.exists(election_privpath):
raise TaskError(
dict(reason="Already existing election id %d" %
input_data['id']))
election = Election(
id=input_data['id'],
title=input_data['title'],
description=input_data['description'],
questions=input_data['questions'],
start_date=input_data['start_date'],
end_date=input_data['end_date'],
num_parties=input_data['num_parties'],
threshold_parties=input_data['threshold_parties'],
)
db.session.add(election)
for auth_data in input_data['authorities']:
authority = Authority(name=auth_data['name'],
ssl_cert=auth_data['ssl_cert'],
orchestra_url=auth_data['orchestra_url'],
election_id=input_data['id'])
db.session.add(authority)
# create dirs and stubs, and session model
i = 0
for session in input_data['sessions']:
session_model = Session(id=session['id'],
election_id=election_id,
status='default',
public_key='',
question_number=i)
db.session.add(session_model)
session_privpath = os.path.join(election_privpath, session['id'])
mkdir_recursive(session_privpath)
stub_path = os.path.join(session_privpath, 'stub.xml')
stub_file = codecs.open(stub_path, 'w', encoding='utf-8')
stub_content = stub_file.write(session['stub'])
stub_file.close()
i += 1
db.session.commit()
else:
# if we are the director, models, dirs and stubs have been created
# already, so we just get the election from the database
election = db.session.query(Election)\
.filter(Election.id == election_id).first()
# only create external task if we have configured autoaccept to false in
# settings:
autoaccept = app.config.get('AUTOACCEPT_REQUESTS', '')
if not autoaccept:
def str_date(date):
if date:
return date.isoformat()
else:
return ""
label = "approve_election"
info_text = {
'Title':
election.title,
'Description':
election.description,
'Voting period':
"%s - %s" %
(str_date(election.start_date), str_date(election.end_date)),
'Question data':
loads(election.questions),
'Authorities': [auth.to_dict() for auth in election.authorities]
}
approve_task = ExternalTask(label=label, data=info_text)
task.add(approve_task)
vfork_task = SimpleTask(receiver_url=app.config.get('ROOT_URL', ''),
action="generate_private_info_vfork",
queue="orchestra_performer",
data=dict())
task.add(vfork_task)
def landing_page():
"""Find all available elections"""
elections = [e.serialize() for e in Election.available_elections()]
return render_template('landing.html', elections=elections)
def main(args):
engine = create_engine(args.database)
session = sessionmaker(bind=engine)()
session.merge(ModelCode(id="HI",
description="Health insurance and voting"))
session.merge(ModelCode(id="VO", description="Voting Only"))
session.merge(ModelCode(id="PV", description="Poverty and voting"))
session.merge(ModelCode(id="IN", description="Income and voting"))
session.merge(ModelCode(id="PP", description="Population and voting"))
session.merge(ModelCode(id="GO", description="Geography and voting"))
session.merge(
ModelCode(id="GP", description="Geography, population, and voting"))
session.merge(
ModelCode(id="IH", description="Health insurance, income, and voting"))
session.merge(ModelCode(id="ALL", description="All variables"))
session.merge(
ModelCode(id="HI2020",
description="Health insurance and voting, including 2020"))
session.merge(
ModelCode(id="VO2020", description="Voting Only, including 2020"))
session.merge(
ModelCode(id="PV2020",
description="Poverty and voting, including 2020"))
session.merge(
ModelCode(id="IN2020",
description="Income and voting, including 2020"))
session.merge(
ModelCode(id="PP2020",
description="Population and voting, including 2020"))
session.merge(
ModelCode(id="GO2020",
description="Geography and voting, including 2020"))
session.merge(
ModelCode(
id="GP2020",
description="Geography, population, and voting, including 2020"))
session.merge(
ModelCode(
id="IH2020",
description="Health insurance, income, and voting, including 2020")
)
session.merge(
ModelCode(id="ALL2020", description="All variables, including 2020"))
mc_mapping = {
"HI": [
"state_population", "county_population", "people_in_poverty",
"poverty_rate", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"VO": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"PV": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"PP": [
"uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"GO": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate"
],
"GP":
["uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate"],
"HI2020": [
"state_population", "county_population", "people_in_poverty",
"poverty_rate", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"VO2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"PV2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"PP2020": [
"uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"GO2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate"
],
"GP2020":
["uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate"],
}
wc = {
"ALL": [
"year in ('2008', '2012')", "county_population is not null",
"state_population is not null", "county_area_land is not null",
"county_area_water is not null", "uninsured is not null",
"uninsured_pct is not null"
],
"HI": [
"year >= '2006'", "year < '2016'", "uninsured is not null",
"uninsured_pct is not null"
],
"PV": [
"year < 2020", "poverty_rate is not null",
"people_in_poverty is not null"
],
"GO": [
"year < 2020", "county_area_land is not null",
"county_area_water is not null"
],
"GP": [
"year < 2020", "county_area_land is not null",
"county_area_water is not null", "county_population is not null",
"state_population is not null"
],
"PP": [
"year < 2020", "county_population is not null",
"state_population is not null"
],
"ALL": [
"year in ('2008', '2012')", "county_population is not null",
"state_population is not null", "county_area_land is not null",
"county_area_water is not null", "uninsured is not null",
"uninsured_pct is not null"
],
"HI2020": [
"year >= '2006'", "year <= '2016'", "uninsured is not null",
"uninsured_pct is not null"
],
"PV2020":
["poverty_rate is not null", "people_in_poverty is not null"],
"GO2020":
["county_area_land is not null", "county_area_water is not null"],
"GP2020": [
"county_area_land is not null", "county_area_water is not null",
"county_population is not null", "state_population is not null"
],
"PP2020":
["county_population is not null", "state_population is not null"],
"ALL2020": [
"year in ('2008', '2012', '2016')",
"county_population is not null", "state_population is not null",
"county_area_land is not null", "county_area_water is not null",
"uninsured is not null", "uninsured_pct is not null"
],
}
for model_code, cols in mc_mapping.items():
for col in cols:
session.merge(ModelDropCol(model_code_id=model_code, column=col))
for model_code, wcs in wc.items():
for wc in wcs:
session.merge(ModelWhereClause(model_code_id=model_code, sql=wc))
# states and counties
states = []
counties = []
st_abbr_to_fips = dict()
with open(str(base_data / "fips.csv"), "r") as csv_file:
cols = ["fips", "name", "state"]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader) # skip header
county_names = [
"COUNTY", "PARISH", "BOROUGH", "CENSUS AREA", "MUNICIPALITY",
"CITY"
]
for row in reader:
fips = str(row.get("fips")).zfill(6)
name = str(row.get("name")).upper()
state = str(row.get("state")).upper()
# state
if not any([n in name for n in county_names]):
# DC Special case
if fips == "011000":
states.append(State(id=fips, name=name, code=state))
st_abbr_to_fips[state] = fips
elif fips == "011001":
counties.append(
County(id=fips,
name=name,
state_id=st_abbr_to_fips[state]))
else:
states.append(State(id=fips, name=name, code=state))
st_abbr_to_fips[state] = fips
# county
else:
counties.append(
County(id=fips, name=name,
state_id=st_abbr_to_fips[state]))
for state in states:
session.merge(state)
for county in counties:
session.merge(county)
session.merge(
PoliticalParty(code="D", name="Democratic Party", founding_year=1828))
session.merge(
PoliticalParty(code="R", name="Republican Party", founding_year=1854))
session.merge(PoliticalParty(code="OTH", name="Generic Other Candidate"))
session.merge(PoliticalParty(code="WI", name="Write In Candidate"))
session.merge(
PoliticalParty(code="W",
name="Whig Party",
founding_year=1833,
defunct_ind=True))
session.merge(
PoliticalParty(code="F",
name="Federalist Party",
founding_year=1791,
defunct_ind=True))
session.merge(
PoliticalParty(code="DR",
name="Democratic-Republican Party",
founding_year=1792,
defunct_ind=True))
session.merge(
PoliticalParty(code="L", name="Libertarian Party", founding_year=1971))
session.merge(
PoliticalParty(code="C", name="Constitution Party",
founding_year=1990))
session.merge(
PoliticalParty(code="RF", name="Reform Party", founding_year=1995))
session.merge(PoliticalParty(code="I", name="Independent"))
session.merge(
PoliticalParty(code="G", name="Green Party", founding_year=2001))
session.merge(
PoliticalParty(code="PSL",
name="Party for Socialism and Liberation",
founding_year=2004))
session.merge(
PoliticalParty(code="SPU",
name="Socialist Party USA",
founding_year=1973))
session.merge(
PoliticalParty(code="AM",
name="Anti-Masonic Party",
founding_year=1828,
defunct_ind=True))
session.merge(
PoliticalParty(code="DC",
name="States' Rights Democratic Party",
founding_year=1948,
defunct_ind=True))
session.merge(
PoliticalParty(code="CU",
name="Constitutional Union Party",
founding_year=1860,
defunct_ind=True))
session.merge(
PoliticalParty(code="KN",
name="Know Nothing Party",
founding_year=1844,
defunct_ind=True))
session.merge(
PoliticalParty(code="FS",
name="Free Soil Party",
founding_year=1848,
defunct_ind=True))
session.merge(
PoliticalParty(code="NU",
name="Nullifier Party",
founding_year=1828,
defunct_ind=True))
session.merge(
PoliticalParty(code="NR",
name="National Republican Party",
founding_year=1824,
defunct_ind=True))
session.merge(
PoliticalParty(code="AL", name="Alliance Party", founding_year=2018))
session.merge(
PoliticalParty(code="ADP",
name="American Delta Party",
founding_year=2016))
session.merge(
PoliticalParty(code="JU", name="Justice Party", founding_year=2011))
session.merge(
PoliticalParty(code="NL",
name="Natural Law Party",
founding_year=1992,
defunct_ind=True))
session.merge(
PoliticalParty(code="NA",
name="New Alliance Party",
founding_year=1979,
defunct_ind=True))
session.merge(
PoliticalParty(code="PO",
name="Populist Party",
founding_year=1984,
defunct_ind=True))
for t in [
"PRESIDENTIAL", "US SENATE", "US HOUSE", "GOVERNOR",
"STATE ASSEMBLY", "STATE SENATE"
]:
session.merge(ElectionType(code=t))
presidential = session.query(ElectionType).filter_by(
code="PRESIDENTIAL").first()
for year in range(1788, 2021, 4):
session.merge(Election(election_type=presidential, year=year))
with open(str(base_data / "candidates.csv"), "r") as csv_file:
cols = [
"first_name", "middle_initial", "last_name", "home_state",
"birth_date", "death_date", "nickname", "display_name"
]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader)
for r in reader:
home_state = r.get("home_state")
bd = r.get("birth_date")
dd = r.get("death_date")
if home_state:
home_state_id = session.query(
State.id).filter_by(code=home_state).scalar()
else:
home_state_id = None
if bd:
bd = datetime.datetime.strptime(bd, "%d-%b-%Y").date()
else:
bd = None
if dd:
dd = datetime.datetime.strptime(dd, "%d-%b-%Y").date()
else:
dd = None
session.merge(
Candidate(home_state_id=home_state_id,
first_name=r.get("first_name"),
last_name=r.get("last_name"),
birth_date=bd,
death_date=dd,
display_name=r.get("display_name"),
nickname=r.get("nickname"),
middle_initial=r.get("middle_initial")))
with open(str(base_data / "campaigns.csv"), "r") as csv_file:
cols = [
"p_first", "p_last", "vp_first", "vp_last", "party", "year",
"election_type"
]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader)
for row in reader:
year = int(row.get("year"))
et = session.query(ElectionType).filter_by(
code=row.get("election_type")).first()
e = session.query(Election).filter_by(
year=year, election_type_id=et.id).first()
fn = row.get("p_first")
ln = row.get("p_last")
vpfn = row.get("vp_first")
vpln = row.get("vp_last")
p = session.query(PoliticalParty).filter_by(
code=row.get("party")).first()
# bush special case
if fn == "George" and ln == "Bush":
if vpfn == "Richard":
mi = "W"
else:
mi = "HW"
cand = session.query(Candidate).filter_by(
first_name=fn, last_name=ln, middle_initial=mi).first()
else:
cand = session.query(Candidate).filter_by(
first_name=fn, last_name=ln).first()
if vpfn and vpln:
vp = session.query(Candidate).filter_by(
first_name=vpfn, last_name=vpln).first()
else:
vp = None
session.merge(
Campaign(candidate_id=cand.id,
running_mate_id=vp.id if vp else None,
election_id=e.id,
political_party_id=p.id))
session.commit()
session.close()