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