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_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 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_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 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()
def main():
with db.transaction():
db.update('politician', votesmartid=None, where='1=1')
db.delete('congress', where="congress_num='-1'")
load_votesmart()
