def GET(self, table, what): try: table = table_map[table] except KeyError: raise web.notfound if not r_safeproperty.match(what): raise web.notfound #if `what` is not there in the `table` (provide available options rather than 404???) try: maxnum = float(db.select(table, what='max(%s) as m' % what, vars=locals())[0].m) except: raise web.notfound items = db.select(table, what="*, 100*(%s/$maxnum) as pct" % what, order='%s desc' % what, where='%s is not null' % what, vars=locals()).list() for item in items: if table == 'district': item.id = 'd' + item.name item.path = '/us/' + item.name.lower() elif table == 'politician': item.name = '%s %s (%s-%s)' % (item.firstname, item.lastname, item.party[0], item.district.split('-')[0]) item.path = '/p/' + item.id return render.dproperty(items, what)
def zip2dist(zip5, scale_column='population'): ## ARRRG, The census provides the congressional districts down to the tract # level, but not to the block level. The ZCTA are provided at the block # level, but NOT at the tract level. # This would be ok if tracts didn't overlap ZCTAs, but they do. Not sure # how to solve this problem. if scale_column=='zip4': return zip2dist_by_zip4(zip5) pop_zip = db.select('census_population', what='sum('+scale_column+')', where="sumlev='ZCTA' and zip_id=$zip5", vars=locals()).list() if pop_zip and len(pop_zip)==1: pop_zip = pop_zip[0].sum else: print "oops"; return None # Limit our search to known intersecting districts dists = db.select('zip4', what='district_id', where="zip=$zip5", group='district_id', vars=locals()) intersect_pops = db.query("select a.district_id, b.state_id, SUM(b."+scale_column+") from (SELECT * FROM census_population WHERE sumlev='TRACT' AND district_id != '') as a INNER JOIN (SELECT * FROM census_population WHERE sumlev='BLOCK' AND zip_id=$zip5) as b ON (a.state_id=b.state_id AND a.county_id=b.county_id AND a.tract_id=b.tract_id) group by a.district_id, b.state_id", vars=locals()).list() # NOTE: This is not the correct behavior, but for now just adjust this to # give us something that sums to 1.0. pop_zip2 = sum(map(lambda x: x.sum if x.sum else 0.0, intersect_pops)) print >>sys.stderr, "Pop Zip:",pop_zip, pop_zip2 pop_zip = pop_zip2 ret = {} for ip in intersect_pops: print >>sys.stderr, ip.sum, pop_zip ret['%s-%s' % (ip.state_id, ip.district_id)] = Decimal(ip.sum) / pop_zip if pop_zip else 0.0 return ret
def GET(self, format=None): i = web.input(address=None) pzip5 = re.compile(r'\d{5}') pzip4 = re.compile(r'\d{5}-\d{4}') pdist = re.compile(r'[a-zA-Z]{2}\-\d{2}') dists = None if not i.get('q'): i.q = i.get('zip') if i.q: if pzip4.match(i.q): zip, plus4 = i.q.split('-') dists = [x.district_id for x in db.select('zip4', where='zip=$zip and plus4=$plus4', vars=locals())] elif pzip5.match(i.q): try: dists = zip2rep.zip2dist(i.q, i.address) except zip2rep.BadAddress: return render.find_badaddr(i.q, i.address) if dists: d_dists = list(schema.District.select(where=web.sqlors('name=', dists))) out = apipublish.publish(d_dists, format) if out: return out if len(dists) == 1: raise web.seeother('/us/%s' % dists[0].lower()) elif len(dists) == 0: return render.find_none(i.q) else: return render.find_multi(i.q, d_dists) if pdist.match(i.q): raise web.seeother('/us/%s' % i.q) results = se.query(i.q) reps = schema.Politician.select(where=web.sqlors('id=', results)) if len(reps) > 1: return render.find_multi_reps(reps, congress_ranges) else: try: rep = reps[0] web.seeother('/p/%s' % rep.id) except IndexError: raise web.notfound() else: index = list(schema.District.select(order='name asc')) for i in index: i.politician = list(db.select('curr_politician', where='district_id = $i.name', vars=locals())) out = apipublish.publish(index, format) if out: return out return render.districtlist(index)
def GET_signatories(self, pid): user_email = helpers.get_loggedin_email() ptitle = db.select('petition', what='title', where='id=$pid', vars=locals())[0].title signs = db.select(['signatory', 'users'], what='users.name, users.email, ' 'signatory.share_with, signatory.comment', where='petition_id=$pid AND user_id=users.id', order='signtime desc', vars=locals()).list() return render.signature_list(pid, ptitle, signs, is_author(user_email, pid))
def is_author(email, pid): if not email: return False try: user_id = db.select('users', where='email=$email', what='id', vars=locals())[0].id owner_id = db.select('petition', where='id=$pid', what='owner_id', vars=locals())[0].owner_id except: return False else: return user_id == owner_id
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 check_user(userName, passwd): myvar = dict(uName=userName, upwd=passwd) results = db.select("users", myvar, where="userName = $uName and passwd = $upwd") if results is None or len(results) == 0: results = db.select("users", myvar, where="mail = $uName and passwd = $upwd") if len(results) > 0: temp = results[0] videoList = videos.get_videoList(temp["userId"]) return bigUser.BigUser(temp, videoList) else: return None
def zip2dist_by_zip4(zip5): if db.select('state', where='code=$zip5',vars=locals()): return {zip5:1.0} dists = db.select('zip4', what='COUNT(plus4), district_id', where='zip=$zip5', group='district_id', vars=locals()).list() all_zip4 = sum(map(lambda d: d.count, dists)) ret = {} for d in dists: ret[d.district_id] = float(d.count) / float(all_zip4) return ret
def GET(self, polid, format=None): limit = 50 page = int(web.input(page=0).page) #c = schema.lob_contribution.select(where='politician_id=$polid', limit=limit, offset=page*limit, order='amount desc', vars=locals()) a = db.select(['lob_filing', 'lob_contribution'], what='SUM(amount)', where="politician_id = $polid AND lob_filing.id = filing_id", vars=locals())[0].sum c = db.select(['lob_organization', 'lob_filing', 'lob_contribution', 'lob_person'], where="politician_id = $polid and lob_filing.id = filing_id and lob_organization.id = org_id and lob_person.id = lobbyist_id", order='amount desc', limit=limit, offset=page*limit, vars=locals()) return render.politician_lobby(c, a, limit)
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_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 GET(self, n): res = db.select('pastes', where='id=$n', vars=locals()) db.update('pastes', where='id=$n',vars=locals(), lastview=now_time()) if res: return render.view(res[0],n) else: return web.notfound()
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 sendmail_to_signatory(user, pid): p = db.select('petition', where='id=$pid', vars=locals())[0] p.url = 'http//watchdog.net/c/%s' % (pid) token = auth.get_secret_token(user.email) msg = render_plain.signatory_mailer(user, p, token) #@@@ shouldn't this web.utf8 stuff taken care by in web.py? web.sendmail(web.utf8(config.from_address), web.utf8(user.email), web.utf8(msg.subject.strip()), web.utf8(msg))
def get_contacts(user, by='id'): if by == 'email': where = 'uemail=$user' else: where = 'user_id=$user' contacts = db.select('contacts', what='cname as name, cemail as email, provider', where=where, vars=locals()).list() if by == 'id': #remove repeated emails due to multiple providers; prefer the one which has name cdict = {} for c in contacts: if c.email not in cdict.keys(): cdict[c.email] = c elif c.name: cdict[c.email] = c contacts = cdict.values() for c in contacts: c.name = c.name or c.email.split('@')[0] contacts.sort(key=lambda x: x.name.lower()) return contacts
def GET(self, district, format=None): try: district = district.upper() d = db.select(['district', 'state', 'politician'], what=('district.*, ' 'state.name as state_name, ' 'politician.firstname as pol_firstname, ' 'politician.lastname as pol_lastname, ' 'politician.id as pol_id, ' 'politician.photo_path as pol_photo_path'), where=('district.name = $district AND ' 'district.state = state.code AND ' 'politician.district = district.name'), vars=locals())[0] except IndexError: raise web.notfound out = apipublish.publish({ 'uri': 'http://watchdog.net/us/' + district.lower(), 'type': 'District', 'state': apipublish.URI('http://watchdog.net/us/' + d.state.lower()), 'wikipedia almanac': apipublish.URI, 'name voting area_sqmi cook_index poverty_pct median_income ' 'est_population est_population_year outline center_lat ' 'center_lng zoom_level': apipublish.identity, }, [d], format) if out is not False: return out if d.district == 0: d.districtth = 'at-large' else: d.districtth = web.nthstr(d.district) return render.district(d)
def getcontact(pol): r = db.select('pol_contacts', what='contact, contacttype', where='politician_id=$pol', vars=locals()) if r: r = r[0] return r.contact, r.contacttype else: return None, None
def pol2dist(pol): try: return db.select("curr_politician", what="district_id", where="curr_politician.id=$pol", vars=locals())[ 0 ].district_id except KeyError: return
def load_election_results(d, distname): (year, votes, vote_pct) = (0,'0','0') if 'name' not in d: print "No name for the congress person for: ",distname return pname = d['name'].lower() if 'electionresults' not in d: print "No election results for %s repsenting %s." % (d['name'],distname) return for e in d['electionresults']: if 'candidate' in e and 'primary' not in e['election'] and \ pname.replace(' ','') in e['candidate'].lower().replace(' ',''): if int(e['election'][0:4]) > year: (year,votes) = (int(e['election'][0:4]), e['totalvotes']) if 'percent' in e: vote_pct = e['percent'] #print year, votes, vote_pct, d['name'], distname if year: pol=db.select('politician', what='id', where="district_id='"+distname+"' AND "+web.sqlors('lastname ilike ',pname.split(' ')), vars=locals()).list() if pol and len(pol)==1: polid=pol[0].id db.update('politician', where='id=$polid', n_vote_received=votes.replace(',','').replace('Unopposed','0'), pct_vote_received=vote_pct.replace('%',''), last_elected_year=year, vars=locals()); else: print "Couldn't find an id for %s representing %s." % (d['name'], distname) else: print "Didn't find a recent election for %s representing %s." %(d['name'], distname) #, pformat(d['electionresults'])
def interest_group_ratings(polid): "Returns the interest group ratings for a politician." return list(db.select(['interest_group_rating', 'interest_group'], what='year, interest_group.groupname, rating, longname', where=('politician_id = $polid ' 'AND interest_group.id = interest_group_rating.group_id'), vars=locals()))
def polname_by_id(pol_id): try: p = db.select('politician', what='firstname, middlename, lastname', where='id=$pol_id', vars=locals())[0] except: return None else: return "%s %s %s" %(p.firstname or '', p.middlename or '', p.lastname or '')
def dist2pols(dist): if not dist: return [] where = 'curr_politician.district_id=$dist or curr_politician.district_id=$dist[:2]' try: return [p.id for p in db.select('curr_politician', what='id', where=where, vars=locals())] except KeyError: return []
def fill_user_details(form, fillings=["email", "name", "contact"]): details = {} email = helpers.get_loggedin_email() or helpers.get_unverified_email() if email: if "email" in fillings: details["email"] = email user = db.select("users", where="email=$email", vars=locals()) if user: user = user[0] if "name" in fillings: details["userid"] = user.id details["prefix"] = user.prefix details["fname"] = user.fname details["lname"] = user.lname if "contact" in fillings: details["addr1"] = user.addr1 details["addr2"] = user.addr2 details["city"] = user.city details["zipcode"] = user.zip5 details["zip4"] = user.zip4 details["phone"] = user.phone details["state"] = user.state form.fill(**details)
def findPol(raw_name): name = cleanName(raw_name).replace(',','').split(' ') name = map(string.lower,filter(lambda x: x, name)) p = db.select('politician', where=web.sqlors('LOWER(lastname)=',name) + ' AND (' + web.sqlors('LOWER(firstname)=',name)+' OR '+web.sqlors('LOWER(nickname)=',name)+')').list() #print raw_name, "-->", name if p and len(p) == 1: return p[0].id
def past_votes(self): return db.select( "past_elections", where="district_id=$self.district_id and politician_id = $self.id", order="year desc", vars=locals(), ).list()
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 get_video(videoId): if videoId is None: return None myvar = dict(videoId = videoId) results = db.select('videos', myvar, where="videoId = $videoId") if len(results)==0: return None return bigVideo.BigVideo(results[0])
def index(self): def format(name): names = name.lower().split(', ') if len(names) > 1: return '_'.join(names[1].split() + [names[0]]) return urllib.quote(name) return ('/contrib/%s/%s' % (c.zip, format(c.name)) \ for c in db.select('contribution', what='zip, name'))
def compose_msg(polid, msg): #@@ compose msg here p = db.select('politician', where='id=$polid', what='firstname, middlename, lastname, district_id', vars=locals())[0] pol_name = "%s %s %s" % (p.firstname or '', p.middlename or '', p.lastname or '') rep_or_sen = 'Sen.' if len(p.district_id) == 2 else 'Rep.' full_msg = 'Dear %s %s,\n%s' % (rep_or_sen, pol_name, msg) return full_msg
def calculate_per_capita(): """ """ print "Pre getting all populations per district..." pop_dist = {} for d in schema.District.select(where='est_population is not null'): pop_dist[d.name] = d.est_population print "Calculate the per-capita impact of each earmark..." pc = {} for e in db.select('earmark', what='final_amt, id', order='id asc'): done_states = set() amount = float(e.final_amt or 0) pop = 0 sponsors = db.query("select district_id, state_id, id from politician, district, earmark_sponsor where politician.district_id = district.name and earmark_sponsor.politician_id = politician.id and earmark_id=$e.id",vars=locals()).list() if not sponsors: continue # Get the population for each district sponsoring for p in sponsors: if p.district_id != p.state_id: done_states.add(p.state_id) pop += pop_dist.get(p.district_id, 0) # Get the population for state sponsoring unless a district has from # within state also sponsors. for p in sponsors: if p.district_id == p.state_id: if p.state_id in done_states: continue done_states.add(p.state_id) pop += pop_dist.get(p.district_id, 0) if not pop: pc[e.id] = 0.0 else: pc[e.id] = amount / pop #print e.id, pc[e.id], amount, pop print "Aggregating per-capita impact to districts..." for d in schema.District.select(): if d.name == d.state_id: continue # Don't set for states. congress_people = set() senators = db.select('curr_politician', where='district_id = $d.state.code', vars=locals()) if senators: congress_people.update(p.id for p in senators) politician = db.select('curr_politician', where='district_id = $d.name', vars=locals()) if politician: congress_people.update(p.id for p in politician) ems = db.select('earmark_sponsor', what='distinct(earmark_id)', where=web.sqlors('politician_id=',congress_people)) empc = sum(map(lambda x: pc.get(x, 0.0), set(e.earmark_id for e in ems))) #print d.name, empc db.update('district',where='name=$d.name',earmark_per_capita=empc, vars=locals())
def getcontact(pol): p = db.select('pol_contacts', where='politician_id=$pol', vars=locals()) return p[0] if p else {}
def get_user_by_email(email): try: return db.select('users', where='email=$email', vars=locals())[0] except IndexError: return
def _fill(): for pol in db.select('politician', what='id, govtrackid, opensecretsid'): _govtrackcache[pol.govtrackid] = str(pol.id) _opensecretscache[pol.opensecretsid] = str(pol.id)
def is_verified(email): verified = db.select( 'users', where='email=$email and (verified=True or password is not null)', vars=locals()) return bool(verified)
def get_user_by_id(uid): try: return db.select('users', where='id=$uid', vars=locals())[0] except IndexError: return