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(): 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 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 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(): for p, v in positions.iteritems(): db.insert('position', seqname=False, bill_id=bill_id, politician_id=p, vote=v)
def load_new_pols(): with db.transaction(): db.update('congress', where="current_member='t'", current_member=False) for polid, pol in new_pols.items(): district = pol['district_id'] create_or_update(polid, district) update_congress(polid, district)
def POST_delete(self, pid): with db.transaction(): title = db.select('petition', what='title', where='id=$pid', vars=locals())[0].title db.delete('signatory', where='petition_id=$pid', vars=locals()) db.delete('petition', where='id=$pid', vars=locals()) helpers.set_msg('Petition "%s" deleted' % (title)) raise web.seeother('/')
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(): outdb = {} done = set() with db.transaction(): db.delete('earmark_sponsor', '1=1') db.delete('earmark', '1=1') for e in earmarks.parse_file(earmarks.EARMARK_FILE): de = dict(e) de['id'] = web.intget(de['id']) if not de['id'] or de['id'] in done: continue # missing the ID? come on! if isinstance(de['house_request'], basestring): continue # CLASSIFIED for k in de: de[k] = cleanrow(de[k]) for x in ['house_member', 'house_state', 'house_party', 'senate_member', 'senate_state', 'senate_party', 'district']: de.pop(x) de['recipient_stem'] = tools.stemcorpname(de['intended_recipient']) try: db.insert('earmark', seqname=False, **de) except: pprint(de) raise done.add(de['id']) reps_not_found = set() for e in earmarks.parse_file(earmarks.EARMARK_FILE): for rawRequest, chamber in zip([e.house_request, e.senate_request],[e.house_member, e.senate_member]): for rep in chamber: if rep.lower() not in lastname2rep: #@@ should work on improving quality reps_not_found.add(rep) else: rep = lastname2rep[rep.lower()] if e.id in done: try: db.insert('earmark_sponsor', seqname=False, earmark_id=e.id, politician_id=rep) except: print "Couldn't add %s as sponsor to earmark %d" %(rep, e.id) outdb.setdefault(rep, { 'amt_earmark_requested': 0, 'n_earmark_requested': 0, 'n_earmark_received': 0, 'amt_earmark_received': 0 }) outdb[rep]['n_earmark_requested'] += 1 requested = rawRequest or e.final_amt if not isinstance(requested, float): requested = e.final_amt if requested: outdb[rep]['amt_earmark_requested'] += requested if isinstance(e.final_amt, float) and e.final_amt: outdb[rep]['n_earmark_received'] += 1 outdb[rep]['amt_earmark_received'] += e.final_amt print "Did not find",len(reps_not_found),"reps:", pformat(reps_not_found) for rep, d in outdb.iteritems(): db.update('politician', where='id=$rep', vars=locals(), **d)
def load_phones(phones): with db.transaction(): db.delete('pol_phones', '1=1') for k, v in phones.items(): polid, city = k db.insert('pol_phones', seqname=False, politician_id=polid, city=city, **v)
def save_petition(p): p.pid = p.pid.replace(' ', '_') with db.transaction(): try: owner = db.select('users', where='email=$p.email', vars=locals())[0] except: owner_id = db.insert('users', email=p.email, verified=True) else: if not owner.verified: db.update('users', where='email=$p.email', verified=True, vars=locals()) owner_id = owner.id db.insert('petition', seqname=False, id=p.pid, title=p.ptitle, description=p.pdescription, owner_id=owner_id) #make the owner of the petition sign for it (??) db.insert('signatory', seqname=False, user_id=owner_id, share_with='E', petition_id=p.pid)
def main(): watchdog_map = combine() with db.transaction(): # db.delete('congress', where='1=1') # db.delete('politician', where='1=1') for polid, pol in watchdog_map.items(): roles = pol.pop("roles") # Load the Politician table if db.select("politician", where="id=$polid", vars=locals()): # pol.get('current_member'): if roles and pol.current_member: pol.officeurl = roles[-1].get("url") pol.party = roles[-1].get("party") db.update( "politician", where="id=$polid", vars=locals(), **unidecode(filter_dict(schema.Politician.columns.keys(), pol)) ) else: db.insert( "politician", seqname=False, id=polid, **unidecode(filter_dict(schema.Politician.columns.keys(), pol)) ) # Load the Congress table done = set() for r in roles: repr = r.state if r.type == "rep" and int(r.district) >= 0: repr = "%s-%02d" % (r.state, int(r.district)) for term in cong_term_lookup(r.startdate, r.enddate): # if not db.select('congress', where='politician_id=$polid AND congress_num=$term AND district_id=$repr', vars=locals()): if (polid, repr, term) not in done: db.insert( "congress", seqname=False, party=r.party, congress_num=term, politician_id=polid, current_member=pol.current_member, district_id=repr, ) done.add((polid, repr, term))
def insert_notice(senderId, receiverId, type, text, link): t = db.transaction() try: results = db.insert('notices',senderId=senderId,receiverId=receiverId,type=type,text=text, link=link) if results==0: web.debug('notices.insert_notice: failed insert notice to database') return None else: results = db.query("select * from notices where noticeId in (select max(noticeId) from notices)") if len(results)>0: t.commit() return bigNotice.BigNotice(results[0]) else: t.rollback() web.debug('notices.insert_notice: failed in getting notice with max(noticeId)') except Exception, e: t.rollback() web.debug('notices.insert_notice: failed', e)
def main(): watchdog_map = combine() with db.transaction(): #db.delete('congress', where='1=1') #db.delete('politician', where='1=1') for polid, pol in watchdog_map.items(): roles = pol.pop('roles') # Load the Politician table if db.select('politician', where='id=$polid', vars=locals()): #pol.get('current_member'): if roles and pol.current_member: pol.officeurl = roles[-1].get('url') pol.party = roles[-1].get('party') db.update('politician', where='id=$polid', vars=locals(), **unidecode( filter_dict(schema.Politician.columns.keys(), pol))) else: db.insert('politician', seqname=False, id=polid, **unidecode( filter_dict(schema.Politician.columns.keys(), pol))) # Load the Congress table done = set() for r in roles: repr = r.state if r.type == 'rep' and int(r.district) >= 0: repr = '%s-%02d' % (r.state, int(r.district)) for term in cong_term_lookup(r.startdate, r.enddate): #if not db.select('congress', where='politician_id=$polid AND congress_num=$term AND district_id=$repr', vars=locals()): if (polid, repr, term) not in done: db.insert('congress', seqname=False, party=r.party, congress_num=term, politician_id=polid, current_member=pol.current_member, district_id=repr) done.add((polid, repr, term))
def load_wyr(): with db.transaction(): db.delete('pol_contacts', where='1=1') for pol, data in wyr.iteritems(): if data['contacttype'] not in types.keys(): continue if data['contacttype'] == 'wyr': contact = 'https://writerep.house.gov/writerep/welcome.shtml' else: contact = data['contact'] d = {'politician_id':pol, 'contact':contact, 'contacttype': types[data['contacttype']], 'captcha': data['captcha'] } try: db.insert('pol_contacts', seqname=False, **d) except: continue
def load_wyr(): with db.transaction(): db.delete('pol_contacts', where='1=1') for pol, data in wyr.iteritems(): if data['contacttype'] not in types.keys(): continue if data['contacttype'] == 'wyr': contact = 'https://writerep.house.gov/writerep/welcome.shtml' else: contact = data['contact'] d = { 'politician_id': pol, 'contact': contact, 'contacttype': types[data['contacttype']], 'captcha': data['captcha'] } try: db.insert('pol_contacts', seqname=False, **d) except: continue
def insert_comment(videoId, commenterId, replyToId, text): # if video is not exit, return False, do not insert comment oneVideo = videos.get_video(videoId) if oneVideo is None: web.debug('comments.insert_comment: failed insert comment because video is not exist') return None t = db.transaction() try: results = db.insert('comments', videoId=videoId, commenterId=commenterId, replyToId=replyToId, text=text) if results==0: web.debug('comments.insert_comment: failed insert comment to database') return None else: results = db.query("select * from comments where commentId in (select max(commentId) from comments)") if len(results)>0: t.commit() return bigComment.BigComment(results[0]) else: t.rollback() web.debug('comments.insert_commnet: failed in getting notice with max(commentId)') except Exception,e: t.rollback() web.debug('comments.insert_comment: failed')
def load_into_db(pname, distname, electionresults, recent_election_year): #load the details of the winner in recent election results into `politician` table #and all the details of elections in district `distname` into the `past_elections` table with db.transaction(): for r in electionresults: candidate_id = r.candidate.split('(')[0].strip().lower() if r.year == recent_election_year and r.type == 'Gen' and pname in candidate_id: polid = db.update('politician', where="district_id=$distname AND %s" % (web.sqlors('lastname ilike ', pname.split(' '))), n_vote_received=r.votes, pct_vote_received=r.vote_pct, last_elected_year=r.year, vars=locals()) candidate_id = candidate_id.replace(' ', '_') if not db.select('past_elections', where='politician_id=$candidate_id and district_id=$distname ' 'and year=$r.year and type=$r.type', vars=locals()): db.insert('past_elections', seqname=False, politician_id=candidate_id, district_id=distname, votes_received=r.votes, pct_votes_received=r.vote_pct, type=r.type, year=r.year, expenditure=r.expenditure)
else: insert_pac(x['pacs']) # lob_contribution table def insert_contribution(con): c = {} for z, val in con.items(): if z == 'amount': val = int(float(val)) if z in lob_contribution: c[lob_contribution[z]] = val if z == 'recipientName': c['politician_id']=findPol(val) db.insert('lob_contribution', seqname=False, filing_id=x['file_id'], **c) if 'contributions' in x: if isinstance(x['contributions'], list): for con in x['contributions']: insert_contribution(con) else: insert_contribution(x['contributions']) if __name__ == "__main__": with db.transaction(): print "Deleting old data from lob_* tables." db.delete('lob_pac_filings',where='1=1') db.delete('lob_contribution',where='1=1') db.delete('lob_filing',where='1=1') db.delete('lob_pac',where='1=1') db.delete('lob_person',where='1=1') db.delete('lob_organization',where='1=1') load_house_lobbyists()
def load_handshakes(handshakes): with db.transaction(): db.delete('handshakes', '1=1') for h in handshakes: db.insert('handshakes', seqname=False, **h)
# lob_contribution table def insert_contribution(con): c = {} for z, val in con.items(): if z == 'amount': val = int(float(val)) if z in lob_contribution: c[lob_contribution[z]] = val if z == 'recipientName': c['politician_id'] = findPol(val) db.insert('lob_contribution', seqname=False, filing_id=x['file_id'], **c) if 'contributions' in x: if isinstance(x['contributions'], list): for con in x['contributions']: insert_contribution(con) else: insert_contribution(x['contributions']) if __name__ == "__main__": with db.transaction(): print "Deleting old data from lob_* tables." db.delete('lob_pac_filings', where='1=1') db.delete('lob_contribution', where='1=1') db.delete('lob_filing', where='1=1') db.delete('lob_pac', where='1=1') db.delete('lob_person', where='1=1') db.delete('lob_organization', where='1=1') load_house_lobbyists()
def load(): outdb = {} done = set() with db.transaction(): db.delete('earmark_sponsor', '1=1') db.delete('earmark', '1=1') for e in earmarks.parse_file(earmarks.EARMARK_FILE): de = dict(e) de['id'] = web.intget(de['id']) if not de['id'] or de['id'] in done: continue # missing the ID? come on! if isinstance(de['house_request'], basestring): continue # CLASSIFIED for k in de: de[k] = cleanrow(de[k]) for x in [ 'house_member', 'house_state', 'house_party', 'senate_member', 'senate_state', 'senate_party', 'district' ]: de.pop(x) de['recipient_stem'] = tools.stemcorpname(de['intended_recipient']) try: db.insert('earmark', seqname=False, **de) except: pprint(de) raise done.add(de['id']) reps_not_found = set() for e in earmarks.parse_file(earmarks.EARMARK_FILE): for rawRequest, chamber in zip([e.house_request, e.senate_request], [e.house_member, e.senate_member]): for rep in chamber: if rep.lower() not in lastname2rep: #@@ should work on improving quality reps_not_found.add(rep) else: rep = lastname2rep[rep.lower()] if e.id in done: try: db.insert('earmark_sponsor', seqname=False, earmark_id=e.id, politician_id=rep) except: print "Couldn't add %s as sponsor to earmark %d" % ( rep, e.id) outdb.setdefault( rep, { 'amt_earmark_requested': 0, 'n_earmark_requested': 0, 'n_earmark_received': 0, 'amt_earmark_received': 0 }) outdb[rep]['n_earmark_requested'] += 1 requested = rawRequest or e.final_amt if not isinstance(requested, float): requested = e.final_amt if requested: outdb[rep]['amt_earmark_requested'] += requested if isinstance(e.final_amt, float) and e.final_amt: outdb[rep]['n_earmark_received'] += 1 outdb[rep]['amt_earmark_received'] += e.final_amt print "Did not find", len(reps_not_found), "reps:", pformat(reps_not_found) for rep, d in outdb.iteritems(): db.update('politician', where='id=$rep', vars=locals(), **d)
def main(): with db.transaction(): db.update('politician', votesmartid=None, where='1=1') db.delete('congress', where="congress_num='-1'") load_votesmart()