def generate_similarities():
"""
Generate similarity information for each (interest group, politician) pair and store in DB
"""
result = db.query(
'select igbp.group_id, position.politician_id, igbp.support, position.vote'
' from interest_group_bill_support igbp, position'
' where igbp.bill_id = position.bill_id')
sim = {}
total = {}
for r in result:
k = (r.group_id, r.politician_id)
if r.support == r.vote and r.support != 0:
sim[k] = sim.get(k, 0) + 1
total[k] = total.get(k, 0) + 1
with db.transaction():
db.delete('group_politician_similarity', '1=1')
for k, agreed in sim.items():
group_id, politician_id = k
db.insert('group_politician_similarity',
seqname=False,
group_id=group_id,
politician_id=politician_id,
agreed=agreed,
total=total[k])
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_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 aggregate_similarities():
"""
Aggregate the similarity info in group_politician_similarity table, category wise.
"""
result = db.query("select sum(sim.agreed) as agreed, sum(sim.total) as total,"
" sim.politician_id, cat.name as category_name"
" from group_politician_similarity sim, interest_group grp, category cat"
" where sim.group_id=grp.id and grp.category_id != '' and cat.id = grp.category_id"
" group by sim.politician_id, cat.name")
#for r in result:
# print r.politician_id, r.category_name, r['agreed']*100.0/r['total'], r['agreed'], r['total']
return result
def aggregate_similarities():
"""
Aggregate the similarity info in group_politician_similarity table, category wise.
"""
result = db.query(
"select sum(sim.agreed) as agreed, sum(sim.total) as total,"
" sim.politician_id, cat.name as category_name"
" from group_politician_similarity sim, interest_group grp, category cat"
" where sim.group_id=grp.id and grp.category_id != '' and cat.id = grp.category_id"
" group by sim.politician_id, cat.name")
#for r in result:
# print r.politician_id, r.category_name, r['agreed']*100.0/r['total'], r['agreed'], r['total']
return result
def generate_similarities():
"""
Generate similarity information for each (interest group, politician) pair and store in DB
"""
result = db.query('select igbp.group_id, position.politician_id, igbp.support, position.vote'
' from interest_group_bill_support igbp, position'
' where igbp.bill_id = position.bill_id')
sim = {}
total = {}
for r in result:
k = (r.group_id, r.politician_id)
if r.support == r.vote and r.support != 0:
sim[k] = sim.get(k, 0) + 1
total[k] = total.get(k, 0) + 1
with db.transaction():
db.delete('group_politician_similarity', '1=1')
for k, agreed in sim.items():
group_id, politician_id = k
db.insert('group_politician_similarity', seqname=False,
group_id=group_id, politician_id=politician_id, agreed=agreed, total=total[k])
