def loadbill(fn, maplightid=None):
bill = xmltramp.load(fn)
d = bill2dict(bill)
d.maplightid = maplightid
try:
bill_id = d.id
db.insert('bill', seqname=False, **d)
except IntegrityError:
bill_id = d.pop('id')
db.update('bill', where="id=" + web.sqlquote(bill_id), **d)
positions = {}
for vote in bill.actions['vote':]:
if not vote().get('roll'): continue
rolldoc = '/us/%s/rolls/%s%s-%s.xml' % (
d.session, vote('where'), vote('datetime')[:4], vote('roll'))
roll = xmltramp.load(GOVTRACK_CRAWL + rolldoc)
for voter in roll['voter':]:
positions[govtrackp(voter('id'))] = fixvote(voter('vote'))
if None in positions: del positions[None]
with db.transaction():
db.delete('position', where='bill_id=$bill_id', vars=locals())
for p, v in positions.iteritems():
db.insert('position', seqname=False,
bill_id=bill_id, politician_id=p, vote=v)
def load_data():
c = csv.reader(file('../data/crawl/maplight/uniq_map_export_bill_research.csv'))
supportdict = {'0': -1, '1': 1, '2': 0 } #0: oppose ; 1: support; 2: not known (from README)
with db.transaction():
db.delete('interest_group_bill_support', '1=1')
for line in c:
if not line[0].startswith('#'):
category_id, longname, maplightid, session, measure, support = line
support = supportdict[support]
if support == 0: continue
typenumber = measure.lower().replace(' ', '')
r = db.select('interest_group', what="id", where="longname=$longname", vars=locals())
if r:
groupid = r[0].id
else:
groupid = db.insert('interest_group', longname=longname, category_id=category_id)
bill_id = 'us/%s/%s' % (session, typenumber)
r = db.select('bill', where="id=$bill_id", vars=locals())
if not r:
filename = "../data/crawl/govtrack/us/%s/bills/%s.xml" % (session, typenumber)
bills.loadbill(filename, maplightid=maplightid)
else:
db.update('bill', maplightid=maplightid, where="id=$bill_id", vars=locals())
try:
#print '\r', bill_id,
db.insert('interest_group_bill_support', seqname=False, bill_id=bill_id, group_id=groupid, support=support)
except:
print '\n Duplicate row with billid %s groupid %s support %s longname %s' % (bill_id, groupid, support, longname)
raise
def load_data():
c = csv.reader(
file('../data/crawl/maplight/uniq_map_export_bill_research.csv'))
supportdict = {
'0': -1,
'1': 1,
'2': 0
} #0: oppose ; 1: support; 2: not known (from README)
with db.transaction():
db.delete('interest_group_bill_support', '1=1')
for line in c:
if not line[0].startswith('#'):
category_id, longname, maplightid, session, measure, support = line
support = supportdict[support]
if support == 0: continue
typenumber = measure.lower().replace(' ', '')
r = db.select('interest_group',
what="id",
where="longname=$longname",
vars=locals())
if r:
groupid = r[0].id
else:
groupid = db.insert('interest_group',
longname=longname,
category_id=category_id)
bill_id = 'us/%s/%s' % (session, typenumber)
r = db.select('bill', where="id=$bill_id", vars=locals())
if not r:
filename = "../data/crawl/govtrack/us/%s/bills/%s.xml" % (
session, typenumber)
bills.loadbill(filename, maplightid=maplightid)
else:
db.update('bill',
maplightid=maplightid,
where="id=$bill_id",
vars=locals())
try:
#print '\r', bill_id,
db.insert('interest_group_bill_support',
seqname=False,
bill_id=bill_id,
group_id=groupid,
support=support)
except:
print '\n Duplicate row with billid %s groupid %s support %s longname %s' % (
bill_id, groupid, support, longname)
raise
def loadroll(fn):
roll = web.storage()
roll.id = fn.split('/')[-1].split('.')[0]
vote = xmltramp.load(fn)
if vote['bill':]:
b = vote.bill
roll.bill_id = 'us/%s/%s%s' % (b('session'), b('type'), b('number'))
else:
roll.bill_id = None
roll.type = str(vote.type)
roll.question = str(vote.question)
roll.required = str(vote.required)
roll.result = str(vote.result)
try:
db.insert('roll', seqname=False, **roll)
except IntegrityError:
if not db.update('roll', where="id=" + web.sqlquote(roll.id), bill_id=roll.bill_id):
print "\nMissing bill:", roll.bill_id
raise NotDone
with db.transaction():
db.delete('vote', where="roll_id=$roll.id", vars=locals())
for voter in vote['voter':]:
rep = govtrackp(voter('id'))
if rep:
db.insert('vote', seqname=False,
politician_id=rep, roll_id=roll.id, vote=fixvote(voter('vote')))
else:
pass #@@!--check again after load_everyone
def load_fec_cans():
for can in fec.parse_candidates():
if can.candidate_id in fec2pol:
pol_id = fec2pol[can.candidate_id]
total = float(can.total_receipts)
if total == 0.0:
print "Oops:", pol_id, total, can.total_receipts, can.total_disbursements, can.contrib_from_candidate, can.total_indiv_contrib, can.contrib_from_other_pc
continue
db.update('politician', where='id = $pol_id', vars=locals(),
money_raised = can.total_receipts,
pct_spent = can.total_disbursements/total,
pct_self = can.contrib_from_candidate/total,
pct_indiv = can.total_indiv_contrib/total,
pct_pac = can.contrib_from_other_pc/total
)
def load_cans_fec_data():
"""Calculate percentage from business versus labor PACs
Calculate percentage money within-state
Calculate percentage money from small donors """
for p in db.query("""SELECT id FROM politician"""):
polid = p.id
num = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi,
politician p, contribution cn WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND p.id = $polid""",
vars=locals())[0].count
if num:
num_labor = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p, contribution cn
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND cm.type = 'L'
AND p.id = $polid""",
vars=locals())[0].count
labor_pct = num_labor / float(num)
num_instate = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p,
contribution cn, district d, state s
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND p.district_id = d.name
AND d.state_id = s.code AND lower(cn.state) = lower(s.code)
AND p.id = $polid""",
vars=locals())[0].count
instate_pct = num_instate / float(num)
num_smalldonor = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p, contribution cn
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND p.id = $polid
AND cn.amount < 250""",
vars=locals())[0].count
smalldonor_pct = num_smalldonor / float(num)
else:
labor_pct = 0
instate_pct = 0
smalldonor_pct = 0
db.update('politician',
where='id = $polid',
vars=locals(),
pct_labor=labor_pct,
pct_instate=instate_pct,
pct_smalldonor=smalldonor_pct)
print polid, labor_pct, instate_pct, smalldonor_pct
def load_fec_cans():
for can in fec_cobol.parse_cansum():
can = web.storage(can)
if can.candidate_id in fec2pol:
pol_id = fec2pol[can.candidate_id]
total = float(can.total_receipts)
if total == 0.0:
print "Oops:", pol_id, total, can.total_receipts, can.total_disbursements, can.contrib_from_candidate, can.total_indiv_contrib, can.contrib_from_other_pc
continue
db.update('politician',
where='id = $pol_id',
vars=locals(),
money_raised=can.total_receipts,
pct_spent=can.total_disbursements / total,
pct_self=can.contrib_from_candidate / total,
pct_indiv=can.total_indiv_contrib / total,
pct_pac=can.contrib_from_other_pc / total)
def update_asset(request):
data = request.POST.get('data')
info = json.loads(data)
mess = db.update(info)
if mess:
data = db.hostlist()
return render(request, "hostlist.html", {"hlist": data})
else:
mess = "Failure to modify"
return HttpResponse(mess)
def loadbill(fn, maplightid=None):
bill = xmltramp.load(fn)
d = bill2dict(bill)
if maplightid:
d["maplightid"] = maplightid
db.insert("bill", seqname=False, **d)
print "\r %-25s" % d["id"],
sys.stdout.flush()
done = []
for vote in bill.actions["vote":]:
if not vote().get("roll"):
continue
if vote("where") in done:
continue # don't count veto overrides
done.append(vote("where"))
votedoc = "%s/rolls/%s%s-%s.xml" % (d["session"], vote("where"), vote("datetime")[:4], vote("roll"))
vote = xmltramp.load("../data/crawl/govtrack/us/" + votedoc)
yeas = 0
neas = 0
for voter in vote["voter":]:
if fixvote(voter("vote")) == 1:
yeas += 1
elif fixvote(voter("vote")) == -1:
neas += 1
rep = govtrackp(voter("id"))
if rep:
# UGLY HACK: if a politician (bob_menendez for instance) voted
# for the same bill in both chambers of congress the insert
# fails.
if not db.select("vote", where="bill_id=$d['id'] AND politician_id=$rep", vars=locals()):
db.insert("vote", seqname=False, politician_id=rep, bill_id=d["id"], vote=fixvote(voter("vote")))
else:
print
print "Updating:", votedoc, rep, d["id"], fixvote(voter("vote"))
db.update(
"vote",
where="bill_id=$d['id'] AND politician_id=$rep",
vote=fixvote(voter("vote")),
vars=locals(),
)
db.update("bill", where="id = $d['id']", yeas=yeas, neas=neas, vars=locals())
def load_cans_fec_data():
"""Calculate percentage from business versus labor PACs
Calculate percentage money within-state
Calculate percentage money from small donors """
for p in db.query("""SELECT id FROM politician"""):
polid = p.id
num = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi,
politician p, contribution cn WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND p.id = $polid""", vars=locals())[0].count
if num:
num_labor = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p, contribution cn
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND cm.type = 'L'
AND p.id = $polid""", vars=locals())[0].count
labor_pct = num_labor/float(num)
num_instate = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p,
contribution cn, district d, state s
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND p.district_id = d.name
AND d.state_id = s.code AND lower(cn.state) = lower(s.code)
AND p.id = $polid""", vars=locals())[0].count
instate_pct = num_instate/float(num)
num_smalldonor = db.query("""SELECT count(*)
FROM committee cm, politician_fec_ids pfi, politician p, contribution cn
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND p.id = $polid
AND cn.amount < 250""", vars=locals())[0].count
smalldonor_pct = num_smalldonor/float(num)
else:
labor_pct = 0
instate_pct = 0
smalldonor_pct = 0
db.update('politician', where='id = $polid', vars=locals(),
pct_labor = labor_pct,
pct_instate = instate_pct,
pct_smalldonor = smalldonor_pct
)
print polid, labor_pct, instate_pct, smalldonor_pct
def set_server(oid, dic):
"""
set server values in mongodb
"""
server = db.server
update(oid, server, dic)
def update_admin(oid, user, pw, real):
passwd = gen_code(pw)
dic = {'username': user,
'password': passwd,
'real_name':real}
update(oid, db.user, dic)
def update_admin(oid, user, pw, real):
passwd = gen_code(pw)
dic = {'username': user, 'password': passwd, 'real_name': real}
update(oid, db.user, dic)
def set_server(oid, dic):
"""
set server values in mongodb
"""
server = db.server
update(oid, server, dic)