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