Python db.select函数代码示例

 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 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 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 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()