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 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 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 create_petition(i, email):
tocongress = i.get("tocongress", "off") == "on"
i.pid = i.pid.replace(" ", "-")
u = helpers.get_user_by_email(email)
is_draft = "save" in i
published = None if is_draft else datetime.now()
try:
db.insert(
"petition",
seqname=False,
id=i.pid,
title=i.ptitle,
created=datetime.now(),
published=published,
description=i.msg,
owner_id=u.id,
to_congress=tocongress,
)
except:
return
if is_draft:
msg = """Petition saved for publishing later."""
helpers.set_msg(msg)
else:
create_first_signature(i, u.email)
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)
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 update_congress(polid, dist):
db.insert('congress',
seqname=False,
politician_id=polid,
district_id=dist,
congress_num=111,
current_member='t')
def index():
'''
:desc:重构索引
'''
logger.info('重构索引...')
start_time = time()
resource_count = db.get('select count(*) as count from resource')['count']
logger.info('收录数据 {} 条'.format(resource_count))
sql = 'select * from resource limit 10000 offset %s'
counter = 0
writer = IndexWriter(INDEX_DIR, ANALYZER, True,
IndexWriter.MaxFieldLength.UNLIMITED)
for offset in range(0, resource_count, 10000):
items = db.query(sql, offset)
for item in items:
doc = Document()
doc.add(
Field('title', item['title'], Field.Store.YES,
Field.Index.ANALYZED))
doc.add(
Field('url', item['url'], Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('feed_time', str(item['feed_time']), Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('feed_username', item['feed_username'], Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('feed_user_uk', str(item['feed_user_uk']),
Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(
Field('origin', item['origin'], Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('size', str(item['size']), Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('v_cnt', str(item['v_cnt']), Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('d_cnt', str(item['d_cnt']), Field.Store.YES,
Field.Index.NOT_ANALYZED))
doc.add(
Field('t_cnt', str(item['t_cnt']), Field.Store.YES,
Field.Index.NOT_ANALYZED))
writer.addDocument(doc)
counter += 1
if counter % 10000 == 0:
logger.info('计数 {} / {}'.format(counter, resource_count))
writer.close()
cost_time = int(time() - start_time)
logger.info('重构索引完毕，耗时 {}'.format(cost_time, ))
index_time = int(time())
db.insert(
'insert into index_log (index_time, count, cost_time) values (%s, %s, %s)',
index_time, resource_count, cost_time)
def save_user(i):
pin = web.numify(i.pin)
volunteer = 'volunteer' in i
if 'donate' in i:
donate_amt = int(i.donate_amt or '0')
else:
donate_amt = 0
donate = 'donate' in i and int(i.donate_amt, 0)
db.insert('users', name=i.name, email=i.email, phone=i.phone, pin=pin, want2volunteer=volunteer, want2donate=donate_amt)
def load_votesmart():
# Candidates from votesmart
for district, cands in votesmart.candidates():
district = tools.fix_district_name(district)
for pol in cands:
vs_id = pol['candidateId']
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_cand = filter_dict(cand_mapping, pol)
if not db.select('politician', where='id=$polid', vars=locals()):
db.insert('politician',
seqname=False,
id=polid,
**unidecode(
filter_dict(schema.Politician.columns.keys(),
pol_cand)))
else:
# @@ Should probably check that we really want to do this, but
# it apears as though the data has two entries for current
# members (the second having more info filled out).
db.update('politician',
where='id=$polid',
vars=locals(),
**unidecode(
filter_dict(schema.Politician.columns.keys(),
pol_cand)))
if not db.select(
'congress',
where="politician_id=$polid AND congress_num='-1'",
vars=locals()):
db.insert('congress',
seqname=False,
congress_num='-1',
politician_id=polid,
district_id=district,
party=pol_cand['party'])
# Bios from votesmart
for vs_id, p in votesmart.bios():
pol = p['candidate']
if pol['gender']:
pol['gender'] = pol['gender'][0]
if pol['education']:
pol['education'] = pol['education'].replace('\r\n', '\n')
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_people = filter_dict(schema.Politician.columns.keys(),
filter_dict(bios_mapping, pol))
if db.select('politician', where='votesmartid=$vs_id', vars=locals()):
db.update('politician',
where='votesmartid=$vs_id',
vars=locals(),
**unidecode(pol_people))
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 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)
def create_or_update(polid, dist):
if not db.select('district', where='name=$dist', vars=locals()):
db.insert('district', seqname=False, name=dist, state_id=dist[:2])
if db.select('politician', where='id=$polid', vars=locals()):
db.update('politician', where='id=$polid', district_id=dist, last_elected_year='2008', vars=locals())
else:
first, last = id.split('_', 1)
first, last = first.title(), last.title()
db.insert('politician', seqname=False, id=polid, firstname=first, lastname=last, last_elected_year='2008', district_id=dist)
def insert_video(keyValue, ownerId):
try:
db.insert("videos", keyValue=keyValue, ownerId=ownerId)
myvar = dict(keyName=keyValue)
results = db.select("videos", myvar, where="keyValue = $keyName")
if len(results)>0:
return bigVideo.BigVideo(results[0])
else:
return None
except Exception, e:
return None
def insert_user(userName, pwd, mail, picKey, isVip, videoCount=None, publicVideoCount=None, interest=None):
try:
db.insert("users", userName=userName, passwd=pwd, mail=mail, picKey=picKey, isVip=isVip, interest=interest)
myvar = dict(uName=userName)
results = db.select("users", myvar, where="userName=$uName")
if len(results) > 0:
return bigUser.BigUser(results[0], [])
else:
return None
except:
return None
def process(msg):
#store the msg in db and send followup msg to the signatory
wyr_id = getid(msg)
if not signid: return
msgbody = get_msg_body(msg)
received = format_date(msg.get('Date')) #msg.get_date() doesn't work!!
try:
db.insert('wyr_responses', wyr_id=wyr_id, response=msgbody, received=received)
except Exception, details:
print 'Error:', details
def insert_video(keyValue, ownerId):
try:
db.insert("videos", keyValue=keyValue, ownerId=ownerId)
myvar = dict(keyName=keyValue)
results = db.select("videos", myvar, where="keyValue = $keyName")
print len(results)
if len(results) > 0:
return bigVideo.BigVideo(results[0])
else:
return None
except Exception, e:
return None
def insert_pac(pac):
pac_id[0] += 1
pa = {'id':pac_id[0]} #@@ stable ids
for z, val in pac.items():
if z in lob_pac: pa[lob_pac[z]] = val
db_pac = db.select('lob_pac', where='LOWER(name)='+web.sqlquote(pa['name'].lower()))
if not db_pac:
db_pac = db.select('lob_pac', where='name ilike '+web.sqlquote('%'+cleanPacName(pa['name'])+'%') )
if not db_pac:
db.insert('lob_pac', seqname=False, **pa)
else:
pa = db_pac[0]
db.insert('lob_pac_filings',seqname=False, pac_id=pa['id'], filing_id=fil['id'])
def load_all():
for code, state in items('states/index'):
if 'aka' in state: state.pop('aka')
db.insert('state', seqname=False, code=code, **unidecode(state))
for name, district in items('districts/index'):
db.insert('district', seqname=False, name=name, **unidecode(district))
for fn in ['almanac', 'shapes', 'centers']:
for name, district in items('districts/' + fn):
if 'interest_group_rating' in district:
district.pop('interest_group_rating')
db.update('district',
where='name = $name',
vars=locals(),
**unidecode(district))
district_to_pol = {}
for polid, pol in items('politicians/index'):
db.insert('politician', seqname=False, id=polid, **unidecode(pol))
district_to_pol[pol['district']] = polid
for fn in ['govtrack', 'voteview', 'earmarks']:
for polid, pol in items('politicians/' + fn):
db.update('politician',
where='id = $polid',
vars=locals(),
**unidecode(pol))
groupname_to_id = {}
for groupname, longname in items('interest_groups'):
gid = db.insert('interest_group', groupname=groupname, longname=longname)
groupname_to_id[groupname] = gid
for name, district in items('districts/almanac'):
if name not in district_to_pol: continue #@@ desynchronized data!
if 'interest_group_rating' in district:
for year, groups in district['interest_group_rating'].items():
for groupname, rating in groups.items():
try:
group_id = db.select('interest_group', what='id', where='groupname=$groupname', vars=locals())[0].id
except:
group_id = db.insert('interest_group', groupname=groupname)
db.insert('interest_group_rating',
politician_id=district_to_pol[name],
year=year,
group_id=groupname_to_id[groupname],
rating=rating)
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 insert_user(userName, pwd, mail, picKey, isVip, videoCount, publicVideoCount, interest):
try:
db.insert('users',userName=userName, passwd=pwd, mail=mail, picKey=picKey, isVip=isVip, videoCount=videoCount, publicVideoCount=publicVideoCount, interest=interest)
myvar = dict(uName=userName)
results = db.select('users', myvar, where="userName=$uName")
if len(results)>0:
return bigUser.BigUser(results[0],[])
else:
return None
except:
return None
def load_votesmart():
# Candidates from votesmart
for district, cands in votesmart.candidates():
district=tools.fix_district_name(district)
for pol in cands:
vs_id=pol['candidateId']
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_cand = filter_dict(cand_mapping, pol)
if not db.select('politician',
where='id=$polid', vars=locals()):
db.insert('politician',
seqname=False,
id=polid,
**unidecode(filter_dict(schema.Politician.columns.keys(),
pol_cand)))
else:
# @@ Should probably check that we really want to do this, but
# it apears as though the data has two entries for current
# members (the second having more info filled out).
db.update('politician', where='id=$polid', vars=locals(),
**unidecode(filter_dict(schema.Politician.columns.keys(),
pol_cand)))
if not db.select('congress',
where="politician_id=$polid AND congress_num='-1'",
vars=locals()):
db.insert('congress', seqname=False, congress_num='-1',
politician_id=polid, district_id=district,
party=pol_cand['party'])
# Bios from votesmart
for vs_id, p in votesmart.bios():
pol = p['candidate']
if pol['gender']:
pol['gender']=pol['gender'][0]
if pol['education']:
pol['education'] = pol['education'].replace('\r\n', '\n')
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_people = filter_dict(schema.Politician.columns.keys(),
filter_dict(bios_mapping, pol))
if db.select('politician', where='votesmartid=$vs_id',vars=locals()):
db.update('politician', where='votesmartid=$vs_id',
vars=locals(), **unidecode(pol_people))
def save_signature(i, pid, uid):
where = 'petition_id=$pid AND user_id=$uid'
signed = db.select('signatory', where=where, vars=locals())
share_with = (i.get('share_with', 'off') == 'on' and 'N') or 'A'
update_user_details(i)
if not signed:
referrer = get_referrer(pid, uid)
signid = db.insert('signatory',
user_id=uid,
share_with=share_with,
petition_id=pid,
comment=i.get('comment'),
referrer=referrer)
helpers.set_msg(
"Thanks for your signing! Why don't you tell your friends about it now?"
)
return signid
else:
db.update('signatory',
where='user_id=$uid and petition_id=$pid',
comment=i.get('comment'),
deleted=None,
vars=locals())
helpers.set_msg(
"Your signature has been changed. Why don't you tell your friends about it now?"
)
return 'old_%s' % signed[0].id
def save_msg(frm, to, subj, msg, source_id=None, status=None):
"""saves the given msg and return back msg id.
It's assumed that the msg is already sent.
msg is always sent from a user to a politician.
Optionally the source of the message can be specified by `source_id`.
"""
return db.insert("messages", from_id=frm, to_id=to, subject=subj, body=msg, source_id=source_id, sent=status)
def save_contacts(email, contacts, provider):
#Even if the user is not logged-in, but has an account with us, let him import contacts
user_id = helpers.get_loggedin_userid()
if not user_id:
user = db.select('users',
what='id',
where='email=$email',
vars=locals())
if user: user_id = user[0].id
if user_id:
for c in contacts:
cname, cemail = c['name'], c['email']
vars = dict(user_id=user_id,
uemail=email,
cemail=cemail,
cname=cname,
provider=provider)
e = db.select(
'contacts',
where='user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
if not e: n = db.insert('contacts', seqname=False, **vars)
elif cname:
db.update(
'contacts',
cname=cname,
where=
'user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
def save_signature(i, pid, uid):
where = "petition_id=$pid AND user_id=$uid"
signed = db.select("signatory", where=where, vars=locals())
share_with = (i.get("share_with", "off") == "on" and "N") or "A"
update_user_details(i)
if not signed:
referrer = get_referrer(pid, uid)
signid = db.insert(
"signatory",
user_id=uid,
share_with=share_with,
petition_id=pid,
comment=i.get("comment"),
referrer=referrer,
)
helpers.set_msg("Thanks for your signing! Why don't you tell your friends about it now?")
return signid
else:
db.update(
"signatory",
where="user_id=$uid and petition_id=$pid",
comment=i.get("comment"),
deleted=None,
vars=locals(),
)
helpers.set_msg("Your signature has been changed. Why don't you tell your friends about it now?")
return "old_%s" % signed[0].id
def save_response(msgid, response, timestamp):
"""saves the `response` to msg with `msgid` and returns the response id
"""
try:
return db.insert("responses", msg_id=msgid, response=response, received=timestamp)
except:
pass
def load_wyr():
for distname, data in wyr_manual.iteritems():
if data['contacttype'] not in types.keys():
continue
if data['contacttype'] == 'wyr':
contact = 'https://forms.house.gov/wyr/welcome.shtml'
else:
contact = data['contact']
d = {'district':distname,
'contact':contact,
'contacttype': types[data['contacttype']],
'captcha': data['captcha']
}
db.insert('wyr', seqname=False, **d)
def create_petition(i, email, wyrform):
tocongress = i.get('tocongress', 'off') == 'on'
i.pid = i.pid.replace(' ', '_')
u = helpers.get_user_by_email(email)
try:
db.insert('petition', seqname=False, id=i.pid, title=i.ptitle, description=i.msg,
owner_id=u.id, to_congress=tocongress)
except:
return
signid = save_signature(i, i.pid, u.id, tocongress)
if tocongress and captcha_to_be_filled(i): wyrform.fill(signid=signid)
if tocongress: send_to_congress(i, wyrform, signid)
msg = """Congratulations, you've created your petition.
Now sign and share it with all your friends."""
helpers.set_msg(msg)
def new_user(email, password):
password = encrypt_password(password)
exists = db.select('users', where='email=$email', vars=locals())
if exists:
return None
user_id = db.insert('users', email=email, password=password, verified=True)
user = web.storage(id=user_id, email=email, password=password, verified=True)
return user
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 save_response(msgid, response, timestamp):
"""saves the `response` to msg with `msgid` and returns the response id
"""
try:
return db.insert('responses',
msg_id=msgid,
response=response,
received=timestamp)
except:
pass
def save_signature(forminput, pid):
try:
user = db.select('users', where='email=$forminput.email', vars=locals())[0]
except:
user_id = db.insert('users', name=forminput.name, email=forminput.email)
else:
user_id = user.id
if user.name != forminput.name:
db.update('users', where='id=$user_id', name=forminput.name, vars=locals())
user = web.storage(id=user_id, name=forminput.name, email=forminput.email)
signed = db.select('signatory', where='petition_id=$pid AND user_id=$user.id', vars=locals())
if not signed:
signature = dict(petition_id=pid, user_id=user_id,
share_with=forminput.share_with, comment=forminput.comment)
db.insert('signatory', seqname=False, **signature)
helpers.set_msg('Your signature has been taken for this petition.')
helpers.unverified_login(user.email)
return user
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 create_or_update(polid, dist):
if not db.select('district', where='name=$dist', vars=locals()):
db.insert('district', seqname=False, name=dist, state_id=dist[:2])
if db.select('politician', where='id=$polid', vars=locals()):
db.update('politician',
where='id=$polid',
district_id=dist,
last_elected_year='2008',
vars=locals())
else:
first, last = id.split('_', 1)
first, last = first.title(), last.title()
db.insert('politician',
seqname=False,
id=polid,
firstname=first,
lastname=last,
last_elected_year='2008',
district_id=dist)
def insert_pac(pac):
pac_id[0] += 1
pa = {'id': pac_id[0]} #@@ stable ids
for z, val in pac.items():
if z in lob_pac: pa[lob_pac[z]] = val
db_pac = db.select('lob_pac',
where='LOWER(name)=' +
web.sqlquote(pa['name'].lower()))
if not db_pac:
db_pac = db.select(
'lob_pac',
where='name ilike ' +
web.sqlquote('%' + cleanPacName(pa['name']) + '%'))
if not db_pac:
db.insert('lob_pac', seqname=False, **pa)
else:
pa = db_pac[0]
db.insert('lob_pac_filings',
seqname=False,
pac_id=pa['id'],
filing_id=fil['id'])
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 new_user(email, password):
password = encrypt_password(password)
exists = db.select('users', where='email=$email', vars=locals())
if exists:
return None
user_id = db.insert('users', email=email, password=password, verified=True)
user = web.storage(id=user_id,
email=email,
password=password,
verified=True)
return user
def load_all():
for code, state in items('states'):
if 'aka' in state: state.pop('aka')
db.insert('state', seqname=False, code=code, **unidecode(state))
for name, district in items('districts'):
db.insert('district', seqname=False, name=name, **unidecode(district))
db.update('congress', where="current_member='t'", current_member=False)
for polid, pol in items('politicians'):
db.insert('politician', seqname=False, id=polid, **unidecode(pol))
db.insert('congress', seqname=False, politician_id=polid,
congress_num=current_session, district_id=pol['district_id'],
current_member=True)
def save_contacts(email, contacts, provider):
user_id = helpers.get_loggedin_userid()
for c in contacts:
cname, cemail = c['name'], c['email']
vars = dict(user_id=user_id, uemail=email, cemail=cemail,
cname=cname, provider=provider)
e = db.select('contacts',
where='user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
if not e: n = db.insert('contacts', seqname=False, **vars)
else: db.update('contacts', cname=cname,
where='user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
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 save_msg(frm, to, subj, msg, source_id=None, status=None):
"""saves the given msg and return back msg id.
It's assumed that the msg is already sent.
msg is always sent from a user to a politician.
Optionally the source of the message can be specified by `source_id`.
"""
return db.insert('messages',
from_id=frm,
to_id=to,
subject=subj,
body=msg,
source_id=source_id,
sent=status)
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)
def POST(self):
i = web.input()
#store values in session as "remember me"
session.name = i.name
session.title = i.title
session.name = i.name
syntax = i.syntax.lower()
if not syntax: syntax = 'text'
html = pygmentize(i.source, syntax)
n = db.insert('pastes', source=i.source, title=i.title,
name=i.name, syntax=i.syntax, html=html, created=now_time(),
ipaddr=web.ctx.env['REMOTE_ADDR'])
return web.seeother('/%s' % (n,))
def create_petition(i, email):
tocongress = i.get('tocongress', 'off') == 'on'
i.pid = i.pid.replace(' ', '-')
u = helpers.get_user_by_email(email)
is_draft = 'save' in i
published = None if is_draft else datetime.now()
try:
db.insert('petition',
seqname=False,
id=i.pid,
title=i.ptitle,
created=datetime.now(),
published=published,
description=i.msg,
owner_id=u.id,
to_congress=tocongress)
except:
return
if is_draft:
msg = """Petition saved for publishing later."""
helpers.set_msg(msg)
else:
create_first_signature(i, u.email)
def load_all():
for code, state in items('states'):
if 'aka' in state: state.pop('aka')
db.insert('state', seqname=False, code=code, **unidecode(state))
for name, district in items('districts'):
db.insert('district', seqname=False, name=name, **unidecode(district))
db.update('congress', where="current_member='t'", current_member=False)
for polid, pol in items('politicians'):
db.insert('politician', seqname=False, id=polid, **unidecode(pol))
db.insert('congress',
seqname=False,
politician_id=polid,
congress_num=current_session,
district_id=pol['district_id'],
current_member=True)
def index():
'''
:desc:重构索引
'''
logger.info('重构索引...')
start_time = time()
resource_count = db.get('select count(*) as count from resource')['count']
logger.info('收录数据 {} 条'.format(resource_count))
sql = 'select * from resource limit 10000 offset %s'
counter = 0
writer = IndexWriter(INDEX_DIR, ANALYZER, True, IndexWriter.MaxFieldLength.UNLIMITED)
for offset in range(0, resource_count, 10000):
items = db.query(sql, offset)
for item in items:
doc = Document()
doc.add(Field('title', item['title'], Field.Store.YES, Field.Index.ANALYZED))
doc.add(Field('url', item['url'], Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('feed_time', str(item['feed_time']), Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('feed_username', item['feed_username'], Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('feed_user_uk', str(item['feed_user_uk']), Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('origin', item['origin'], Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('size', str(item['size']), Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('v_cnt', str(item['v_cnt']), Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('d_cnt', str(item['d_cnt']), Field.Store.YES, Field.Index.NOT_ANALYZED))
doc.add(Field('t_cnt', str(item['t_cnt']), Field.Store.YES, Field.Index.NOT_ANALYZED))
writer.addDocument(doc)
counter += 1
if counter % 10000 == 0:
logger.info('计数 {} / {}'.format(counter, resource_count))
writer.close()
cost_time = int(time() - start_time)
logger.info('重构索引完毕，耗时 {}'.format(cost_time,))
index_time = int(time())
db.insert('insert into index_log (index_time, count, cost_time) values (%s, %s, %s)',
index_time, resource_count, cost_time)
def load_all():
for code, state in items('states'):
if 'aka' in state: state.pop('aka')
db.insert('state', seqname=False, code=code, **unidecode(state))
for name, district in items('districts'):
db.insert('district', seqname=False, name=name, **unidecode(district))
for polid, pol in items('politicians'):
db.insert('politician', seqname=False, id=polid, **unidecode(pol))
def save_contacts(email, contacts, provider):
#Even if the user is not logged-in, but has an account with us, let him import contacts
user_id = helpers.get_loggedin_userid()
if not user_id:
user = db.select('users', what='id', where='email=$email', vars=locals())
if user: user_id = user[0].id
if user_id:
for c in contacts:
cname, cemail = c['name'], c['email']
vars = dict(user_id=user_id, uemail=email, cemail=cemail,
cname=cname, provider=provider)
e = db.select('contacts',
where='user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
if not e: n = db.insert('contacts', seqname=False, **vars)
elif cname: db.update('contacts', cname=cname,
where='user_id=$user_id and uemail=$uemail and cemail=$cemail',
vars=vars)
def unverified_login(email, fname, lname):
setcookie('wd_email', email)
user = get_user_by_email(email)
if user:
return user.id
return db.insert('users', fname=fname, lname=lname, email=email)
def load_handshakes(handshakes):
with db.transaction():
db.delete('handshakes', '1=1')
for h in handshakes:
db.insert('handshakes', seqname=False, **h)
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_house_lobbyists():
print "Loading new lobbyist data."
pac_id = [1]
for i, x in enumerate(lobbyists.parse_house_lobbyists()):
(per, org, fil) = ({}, {}, {})
#pprint(x)
for z, val in x.items():
if z in lob_person: per[lob_person[z]] = val
if z in lob_organization: org[lob_organization[z]] = val
if z in lob_filing: fil[lob_filing[z]] = val
# lob_person table
if per:
person = db.select('lob_person',
where=web.db.sqlwhere(per, ' AND '))
per['id'] = i #@@ stable ids
if not person:
db.insert('lob_person', seqname=False, **per)
else:
per = person[0]
# lob_organization table
organization = db.select('lob_organization',
where=web.db.sqlwhere(org, ' AND '))
org['id'] = i #@@ stable ids
if not organization:
db.insert('lob_organization', seqname=False, **org)
else:
org = organization[0]
# lob_filing table
fil['lobbyist_id'] = per['id'] if 'id' in per else None
fil['org_id'] = org['id']
fil['id'] = x['file_id']
db.insert('lob_filing', seqname=False, **fil)
# lob_pac table
def insert_pac(pac):
pac_id[0] += 1
pa = {'id': pac_id[0]} #@@ stable ids
for z, val in pac.items():
if z in lob_pac: pa[lob_pac[z]] = val
db_pac = db.select('lob_pac',
where='LOWER(name)=' +
web.sqlquote(pa['name'].lower()))
if not db_pac:
db_pac = db.select(
'lob_pac',
where='name ilike ' +
web.sqlquote('%' + cleanPacName(pa['name']) + '%'))
if not db_pac:
db.insert('lob_pac', seqname=False, **pa)
else:
pa = db_pac[0]
db.insert('lob_pac_filings',
seqname=False,
pac_id=pa['id'],
filing_id=fil['id'])
if 'pacs' in x:
if isinstance(x['pacs'], list):
for pac in x['pacs']:
insert_pac(pac)
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'])