def GET(self, zipcode, name):
names = name.lower().replace('_', ' ').split(' ')
if len(names) > 1: name = names[-1]+', '+' '.join(names[:-1])
else: name = names[0]
candidates = list(db.query("""SELECT count(*) AS how_many,
sum(amount) AS how_much, p.firstname, p.lastname,
cm.name AS committee, cm.id as committee_id, occupation,
employer_stem, employer, p.id as polid ,
min(cn.sent) as from_date, max(cn.sent) as to_date
FROM contribution cn, committee cm, politician_fec_ids pfi,
politician p WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND lower(cn.name) = $name AND cn.zip = $zipcode
GROUP BY cm.id, cm.name, p.lastname, p.firstname, cn.occupation,
cn.employer_stem, cn.employer, p.id ORDER BY lower(cn.employer_stem),
lower(occupation), to_date DESC, how_much DESC""", vars=locals()))
committees = list(db.query("""SELECT count(*) AS how_many,
sum(amount) AS how_much, cm.name, cm.id, occupation,
employer_stem, employer, max(cn.sent) as to_date, min(cn.sent) as from_date
FROM contribution cn, committee cm WHERE cn.recipient_id = cm.id
AND lower(cn.name) = $name AND cn.zip = $zipcode
GROUP BY cm.id, cm.name, cn.occupation, cn.employer_stem, cn.employer
ORDER BY lower(cn.employer_stem),
lower(occupation), to_date DESC, how_much DESC""", vars=locals()))
return render.contributor(candidates, committees, zipcode, name)
def modify_userMail(userId, newUserMail):
try:
db.query("update users set mail=$um where userId=$uId", vars={"um": newUserMail, "uId": userId})
return get_user(userId)
except Exception, e:
web.debug("users.modify_userMail: failed in modify database", e)
return None
def modify_userName(userId, newUserName):
try:
db.query("update users set userName=$nun where userId=$uId", vars={"uId": userId, "nun": newUserName})
return get_user(userId)
except Exception, e:
web.debug("users.modify_userName: failed in modify database", e)
return None
def GET(self, zipcode, name):
names = name.lower().replace('_', ' ').split(' ')
if len(names) > 1: name = names[-1] + ', ' + ' '.join(names[:-1])
else: name = names[0]
candidates = list(
db.query("""SELECT count(*) AS how_many,
sum(amount) AS how_much, p.firstname, p.lastname,
cm.name AS committee, cm.id as committee_id, occupation,
employer_stem, employer, p.id as polid ,
min(cn.sent) as from_date, max(cn.sent) as to_date
FROM contribution cn, committee cm, politician_fec_ids pfi,
politician p WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND lower(cn.name) = $name AND cn.zip = $zipcode
GROUP BY cm.id, cm.name, p.lastname, p.firstname, cn.occupation,
cn.employer_stem, cn.employer, p.id ORDER BY lower(cn.employer_stem),
lower(occupation), to_date DESC, how_much DESC""",
vars=locals()))
committees = list(
db.query("""SELECT count(*) AS how_many,
sum(amount) AS how_much, cm.name, cm.id, occupation,
employer_stem, employer, max(cn.sent) as to_date, min(cn.sent) as from_date
FROM contribution cn, committee cm WHERE cn.recipient_id = cm.id
AND lower(cn.name) = $name AND cn.zip = $zipcode
GROUP BY cm.id, cm.name, cn.occupation, cn.employer_stem, cn.employer
ORDER BY lower(cn.employer_stem),
lower(occupation), to_date DESC, how_much DESC""",
vars=locals()))
return render.contributor(candidates, committees, zipcode, name)
def process_item(self, item, spider):
if item.get('song_name') is None:
# 分页完
raise DropItem('ajax page over.')
singer = db.query(
Singer.pk).filter_by(face=item['singer_face']).first()
if singer is None:
singer = Singer(name=item['singer'], face=item['singer_face'])
db.add(singer)
album_name = item.get('album_name')
if album_name is not None:
cover = item.get('album_cover')
album = db.query(Album.pk).filter_by(cover=cover).first()
if album is None:
album = Album(
name=album_name,
intro=item.get('album_intro'),
rdt=item['release_date'],
cover=cover)
db.add(album)
else:
album = Empty()
db.commit()
lrc = item.get('lrc')
song = db.query(Song).filter_by(
name=item['song_name'], singer=singer.pk).first()
if song is None:
song = Song(
name=item['song_name'],
singer=singer.pk,
album=album.pk,
lrc=lrc)
db.add(song)
db.commit()
elif None not in (lrc, song.lrc):
song.lrc = lrc
tag_objs = []
for tag in item['tags']:
t = db.query(Tag.pk).filter_by(name=tag).first()
if t is None:
t = Tag(name=tag)
db.add(t)
tag_objs.append(t)
db.commit()
for tag in tag_objs:
db.merge(SongTag(sid=song.pk, tid=tag.pk))
db.commit()
return item
def setUp(self):
db.query(self.model_class).delete()
db.commit()
for x in range(5):
p = self.model_class(
username=u'rodrigocesar.savian%s' % x,
facebook_id='100003194166055%s' % x,
name=u'Rodrigo Cesar Savian%s' % x,
gender=u'male')
db.add(p)
db.commit()
self.object_list = db.query(self.model_class).all()
self.object = self.object_list[0]
def modify_userPicKey(userId, newPicKey):
try:
results = db.query("update users set picKey=$pk where userId=$uId", vars={"pk": newPicKey, "uId": userId})
return get_user(userId)
except Exception, e:
web.debug("users.modify_userPicKey: failed in modify database", e)
return None
def get_app(self):
# first clear all
db.query(self.model_class).delete()
db.commit()
for x in range(5):
p = self.model_class(
username=u'rodrigocesar.savian%s' % x,
facebook_id='100003194166055%s' % x,
name=u'Rodrigo Cesar Savian%s' % x,
gender=u'male')
db.add(p)
db.commit()
self.object_list = db.query(self.model_class).all()
self.object = self.object_list[0]
return app.make_app_test()
def is_table_exist(cls, dbname=config.MYSQLDB['db']):
table = cls.__dict__['__table__']
query = "SELECT table_name FROM information_schema.TABLES WHERE table_name='{}' AND table_schema='{}'".format(
table, dbname)
row = conn.query(query)
return True if row else False
def query_census(location, hr_keys):
# Use DISTINCT since some hr_keys map to multiple internal_keys (but should
# have same value).
#q = db.select('census', what='SUM(DISTINCT(value))', where=web.sqlors('hr_key=', hr_keys)+' AND location='+web.sqlquote(location))
q = db.query('SELECT SUM(value) FROM (SELECT DISTINCT value, hr_key FROM census WHERE '+web.sqlors('hr_key=', hr_keys)+' AND district_id='+web.sqlquote(location)+') AS foo;')
if not q: return None
return q[0].sum
def politician_contributor_employers(polid):
return db.query("""SELECT cn.employer_stem,
sum(cn.amount) as amt FROM committee cm, politician_fec_ids pfi,
politician p, contribution cn WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND p.id = $polid AND cn.employer_stem != '' GROUP BY cn.employer_stem
ORDER BY amt DESC""", vars=locals())
def setUp(self):
db.query(self.model_class).delete()
db.commit()
self.object_list = []
for x in range(1, 5):
p = self.model_class(
id=x,
username=u"rodrigocesar.savian%s" % x,
facebook_id="100003194166055%s" % x,
name=u"Rodrigo Cesar Savian%s" % x,
gender=u"male",
)
self.object_list.append(p)
self.object = self.object_list[0]
self.serializer_object = self.serializer_class(self.object)
def interest_group_support(bill_id):
"Get the support of interest groups for a bill."
return db.query('select g.longname as longname, sum(gb.support) as support '
'from interest_group_bill_support gb , interest_group g '
'where gb.bill_id = $bill_id and g.id = gb.group_id '
'group by gb.bill_id, g.longname '
'order by sum(gb.support) desc', vars=locals()).list()
def test(formtype=None):
def getdistzipdict(zipdump):
"""returns a dict with district names as keys zipcodes falling in it as values"""
d = {}
for line in zipdump.strip().split('\n'):
zip5, zip4, dist = line.split('\t')
d[dist] = (zip5, zip4)
return d
try:
dist_zip_dict = getdistzipdict(file('zip_per_dist.tsv').read())
except:
import os, sys
path = os.path.dirname(sys.modules[__name__].__file__)
dist_zip_dict = getdistzipdict(file(path + '/zip_per_dist.tsv').read())
def getzip(dist):
return dist_zip_dict[dist]
query = "select district from wyr "
if formtype == 'wyr': query += "where contacttype='W'"
elif formtype == 'ima': query += "where contacttype='I'"
elif formtype == 'zipauth': query += "where contacttype='Z'"
elif formtype =='email': query += "where contacttype='E'"
dists = [r.district for r in db.query(query)]
for dist in dists:
print dist,
zip5, zip4 = getzip(dist)
msg_sent = writerep(dist, zipcode=zip5, zip4=zip4, prefix='Mr.',
fname='watchdog', lname ='Tester', addr1='111 av', addr2='addr extn', city='test city',
phone='001-001-001', email='*****@*****.**', msg='testing...')
print msg_sent and 'Success' or 'Failure'
def filter(cls, orderby='', rows=0, offset=10, **kwargs):
"""
复杂的查询，返回包装后的model对象列表。
:param orderby: orderby排序字典
:param rows: limit参数
:param offset: limit参数，偏移量
:param kwargs: 查询where条件
>>> users = User.filter(orderby={"password": "******"}, rows=0, offset=2, email='*****@*****.**')
"""
where, args = cls._where(**kwargs)
if orderby:
sorts = []
for field, sort in orderby.iteritems():
sorts.append("{} {} ".format(field, sort.upper()))
orderby = "ORDER BY {}".format(', '.join(sorts))
if where:
query = ("SELECT * FROM `{table}` "
"WHERE {where} "
"{orderby} "
"LIMIT {rows}, {offset}".format(table=cls.__table__, where=where, orderby=orderby,
rows=rows, offset=offset))
else:
query = ("SELECT * FROM `{table}` "
"{orderby} "
"LIMIT {rows}, {offset}".format(table=cls.__table__, where=where, orderby=orderby,
rows=rows, offset=offset))
logger.info('the SQL is {0}'.format(query))
result = conn.query(query, *args)
return [cls(**d) for d in result if result]
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 init():
db.query("CREATE VIEW census AS select * from census_meta NATURAL JOIN census_data")
db.query("CREATE VIEW curr_politician AS SELECT politician.* FROM politician, congress WHERE politician.id = politician_id AND congress_num='111' AND current_member = 't' ;")
db.query("GRANT ALL on curr_politician TO watchdog;")
try:
db.query("GRANT ALL on census TO watchdog")
except:
pass # group doesn't exist
def get(self, roomId):
userid = str(self.get_secure_cookie("userid"))
if not db.query("SELECT * FROM room WHERE id=%s", roomId):
return self.redirect("/index")
self.set_secure_cookie("roomId", roomId)
room = db.get("SELECT roomname FROM room WHERE id=%s", int(roomId))
latelyRoom = db.query(
"SELECT room.id, roomname, created_time, user.username "
"FROM room, user WHERE user.id=room.owner_id "
"AND room.id IN (SELECT roomid FROM message WHERE message.userid=%s "
"GROUP BY roomid )", userid)
owner_msg = db.query(
"SELECT user.id, user.username, msg, created_time, type FROM message, user "
"WHERE user.id=message.userid AND have_read=0 AND roomid=%s "
"ORDER BY created_time;", roomId)
room_msg = []
userid_group = []
user_group = []
for msg_item in owner_msg:
msg_group = {}
if msg_item.type == 1: continue
if msg_item.id not in userid_group:
userid_group.append(msg_item.id)
user_group.append({
"username": msg_item.username,
"userid": msg_item.id
})
msg_group["username"] = msg_item.username
msg_group["created_time"] = msg_item.created_time.strftime(
'%Y-%m-%d %H:%M:%S')
msg_group["msg"] = msg_item.msg
msg_group["type"] = str(msg_item.type)
if str(msg_item.id) == str(userid):
msg_group["belong"] = 1
else:
msg_group["belong"] = 0
room_msg.append(msg_group)
self.render("chatroom.html",
room=room,
msg=room_msg,
users=user_group,
latelyRoom=latelyRoom)
def modify_userPasswd(userId, newUserPasswd):
try:
results = db.query("update users set passwd=$pw where userId=$uId", vars={"pw": newUserPasswd, "uId": userId})
user = get_user(userId)
return user
except Exception, e:
web.debug("users.modify_userPasswd: failed in modify database", e)
return None
def get(self):
userid = self.get_current_user().id
allRoom = db.query(
"SELECT room.id, roomname, created_time, username FROM room, user "
"WHERE room.owner_id=user.id")
mineRoom = db.query(
"SELECT room.id, roomname, created_time, username FROM room, user "
"WHERE room.owner_id=%s AND user.id=%s", userid, userid)
latelyRoom = db.query(
"SELECT room.id, roomname, created_time, user.username "
"FROM room, user WHERE user.id=room.owner_id "
"AND room.id IN (SELECT roomid FROM message WHERE message.userid=%s "
"GROUP BY roomid )", userid)
self.render("index.html",
allRoom=allRoom,
mineRoom=mineRoom,
latelyRoom=latelyRoom)
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 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 findall(cls, **kwargs):
where, args = cls._where(**kwargs)
if where:
query = "SELECT * FROM `{table}` WHERE {where}".format(table=cls.__table__, where=where)
else:
query = "SELECT * FROM `{table}`".format(table=cls.__table__)
logger.info('the SQL is {0}'.format(query))
result = conn.query(query, *args)
return [cls(**d) for d in result if result]
def delete_commentOfVideo(videoId):
try:
results = db.query("delete from comments where videoId=$vId",vars={'vId':videoId})
if results==0:
web.debug('comments.delete_commentOfVideo: failed delete comments by videoId, maybe not exist')
return True
except:
web.debug('comments.delete_commentOfVideo: failed delete comments from database')
return False
def get_commentByCommentId(commentId):
try:
results = db.query("select * from comments where commentId=$cId",vars={'cId':commentId})
if len(results)==0:
return None
else:
return bigComment.BigComment(results[0])
except Exception,e:
web.debug('comments.commentByCommentId: failed in select from database')
def committees_by_occupation(occupation, limit=None):
query = """SELECT sum(amount) AS amt, cm.id, cm.name
FROM contribution cn, committee cm
WHERE cn.recipient_id = cm.id
AND lower(cn.occupation) = lower($occupation)
GROUP BY cm.id, cm.name
ORDER BY amt DESC"""
if limit: query = query + " LIMIT %d" % limit
return db.query(query, vars=locals())
def committees_by_occupation(occupation, limit=None):
query = """SELECT sum(amount) AS amt, cm.id, cm.name
FROM contribution cn, committee cm
WHERE cn.recipient_id = cm.id
AND lower(cn.occupation) = lower($occupation)
GROUP BY cm.id, cm.name
ORDER BY amt DESC"""
if limit: query = query + " LIMIT %d" % limit
return db.query(query, vars=locals())
def politician_contributor_employers(polid, limit=None):
query = """SELECT cn.employer_stem,
sum(cn.amount) as amt FROM committee cm, politician_fec_ids pfi,
politician p, contribution cn WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND p.id = $polid AND cn.employer_stem != '' GROUP BY cn.employer_stem
ORDER BY amt DESC"""
if limit: query = query + ' LIMIT %d' % limit
return db.query(query, vars=locals())
def query_census(location, hr_keys):
# Use DISTINCT since some hr_keys map to multiple internal_keys (but should
# have same value).
#q = db.select('census', what='SUM(DISTINCT(value))', where=web.sqlors('hr_key=', hr_keys)+' AND location='+web.sqlquote(location))
q = db.query(
'SELECT SUM(value) FROM (SELECT DISTINCT value, hr_key FROM census WHERE '
+ web.sqlors('hr_key=', hr_keys) + ' AND district_id=' +
web.sqlquote(location) + ') AS foo;')
if not q: return None
return q[0].sum
def set_toRead(noticeId):
try:
results = db.query("update notices set isRead=1 where noticeId=$nId", vars={'nId':noticeId})
if results==1:
return get_notice(noticeId)
else:
return None
except:
web.debug('notices.set_toRead: failed in set notice toRead in database')
return None
def getdist(zip5, zip4=None, address=None):
query = 'select distinct district_id from zip4 where zip=$zip5'
if zip4: query += ' and plus4=$zip4'
query += ' limit 2' #just to see uniqness of districts
dist = [x.district_id for x in db.query(query, vars=locals())]
if len(dist) != 1:
try:
dist = zip2dist(zip5, address and address.strip())
except Exception, details:
pass
def insert_follow(aId, bId):
try:
results = db.query("insert into follows(aId,bId) values($a,$b)",vars={'a':aId,'b':bId})
if results==1:
return get_follow(aId,bId)
else:
web.debug('follows.insert_follow: failed of insert a follow to database')
except Exception,e:
web.debug('follows.insert_follow: failed')
return None
def getdist(zip5, zip4=None, address=None):
query = 'select distinct district_id from zip4 where zip=$zip5'
if zip4: query += ' and plus4=$zip4'
query += ' limit 2' #just to see uniqness of districts
dist = [x.district_id for x in db.query(query, vars=locals())]
if len(dist) != 1:
try:
dist = zip2dist(zip5, address and address.strip())
except Exception, details:
pass
def is_aIdFollowbId(aId,bId):
try:
results = db.query("select * from follows where aId=$a and bId=$b",vars={'a':aId,'b':bId})
if len(results)==0:
return None
else:
return bigFollow.BigFollow(results[0])
except Exception,e:
web.debug('follows.get_aIdFollowbId: failed of select from database')
return None
def politician_contributors(polid, limit=None):
query = """SELECT cn.name, cn.zip,
min(cn.sent) as from_date, max(cn.sent) as to_date,
sum(cn.amount) as amt FROM committee cm, politician_fec_ids pfi,
politician p, contribution cn WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND p.id = $polid AND cn.employer_stem != '' GROUP BY cn.name, cn.zip
ORDER BY amt DESC"""
if limit: query = query + ' LIMIT %d' % limit
return db.query(query, vars=locals())
def candidates_by_occupation(occupation, limit=None):
query = """SELECT sum(amount) AS amt, p.firstname,
p.lastname, p.id as polid, p.party FROM contribution cn,
committee cm, politician_fec_ids pfi, politician p
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id
AND lower(cn.occupation) = lower($occupation)
GROUP BY polid, p.lastname, p.firstname, p.party
ORDER BY amt DESC"""
if limit: query = query + ' LIMIT %d' % limit
return db.query(query, vars=locals())
def delete_follow(aId,bId):
try:
results = db.query("delete from follows where aId=$a and bId=$b",vars={'a':aId,'b':bId})
if results==1:
return True
else:
web.debug('follows.delete_follow: failed to delete a follow')
return False
except Exception,e:
web.debug('follows.delete_follow: failed')
return False
def check_cache(start_date, end_date):
if start_date < end_date:
start_date, end_date = end_date, start_date
cache = db.query(Cache).filter(Cache.start_date == start_date,
Cache.end_date == end_date).one_or_none()
if cache:
return cache
else:
return create_cache(start_date, end_date)
def check_cache(start_date, end_date):
if start_date < end_date:
start_date, end_date = end_date, start_date
cache = db.query(Cache).filter(Cache.start_date == start_date,
Cache.end_date == end_date).one_or_none()
if cache:
return cache
else:
return create_cache(start_date, end_date)
def count(cls, **kwargs):
where, args = cls._where(**kwargs)
if where:
query = "SELECT COUNT(*) FROM `{table}` WHERE {where}".format(table=cls.__table__, where=where)
else:
query = "SELECT COUNT(*) FROM `{table}`".format(table=cls.__table__)
logger.info('The SQL is {}'.format(query))
count = conn.query(query, *args)
if count:
return count[0].get('COUNT(*)')
return 0
def candidates_by_occupation(occupation, limit=None):
query = """SELECT sum(amount) AS amt, p.firstname,
p.lastname, p.id as polid, p.party FROM contribution cn,
committee cm, politician_fec_ids pfi, politician p
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id
AND lower(cn.occupation) = lower($occupation)
GROUP BY polid, p.lastname, p.firstname, p.party
ORDER BY amt DESC"""
if limit: query = query + ' LIMIT %d' % limit
return db.query(query, vars=locals())
def modify_video(videoId, videoName, ownerId, keyValue, intro, isPublic,
recommendCount, commentCount, category, type):
if videoId is None:
return None
vars = {}
if videoName is not None:
vars['videoName'] = videoName
if ownerId is not None:
vars['ownerId'] = ownerId
if keyValue is not None:
vars['keyValue'] = keyValue
if intro is not None:
vars['intro'] = intro
if isPublic is not None:
vars['isPublic'] = isPublic
if recommendCount is not None:
vars['recommendCount'] = recommendCount
if commentCount is not None:
vars['commentCount'] = commentCount
if category is not None:
vars['category'] = category
if type is not None:
vars['type'] = type
sql = "update videos set "
length = len(vars.keys())
count = 1
for x in vars.keys():
sql = sql + x
sql = sql + "=$" + x
if count < length:
sql = sql + ","
count = count + 1
sql = sql + " where videoId = $videoId"
vars['videoId'] = videoId
try:
db.query(sql, vars=vars)
return get_video(videoId)
except Exception, e:
return None
def test(formtype=None):
def getdistzipdict(zipdump):
"""returns a dict with district names as keys zipcodes falling in it as values"""
d = {}
for line in zipdump.strip().split('\n'):
zip5, zip4, dist = line.split('\t')
d[dist] = (zip5, zip4)
return d
try:
dist_zip_dict = getdistzipdict(file('zip_per_dist.tsv').read())
except:
import os, sys
path = os.path.dirname(sys.modules[__name__].__file__)
dist_zip_dict = getdistzipdict(file(path + '/zip_per_dist.tsv').read())
def getzip(dist):
try:
return dist_zip_dict[dist]
except KeyError:
for d in dist_zip_dict.keys():
if d.startswith(dist + '-'):
return dist_zip_dict[d]
return '', ''
query = "select politician_id from pol_contacts where contacttype='%s'" % formtype[
0].upper()
pols = [r.politician_id for r in db.query(query)]
for pol in pols:
print pol,
dist = pol2dist(pol)
zip5, zip4 = getzip(dist)
print zip5, zip4,
msg = compose_msg(pol, 'testing...')
u = web.Storage(zip5=zip5,
zip4=zip4,
prefix='Mr.',
state=dist[:2],
fname='test',
lname='Tester',
addr1='111 av',
addr2='addr extn',
city='test city',
phone='0010010010',
email='*****@*****.**',
subject='general',
full_msg=msg)
msg_sent = writerep(pol, u)
print msg_sent and 'Success' or 'Failure'
def GET(self, polid, format=None):
if polid != polid.lower():
raise web.seeother('/p/' + polid.lower())
i = web.input()
idlookup = False
for k in ['votesmartid', 'bioguideid', 'opensecretsid', 'govtrackid']:
if i.get(k):
idlookup = True
ps = schema.Politician.where(**{k: i[k]})
if ps: raise web.seeother('/p/' + ps[0].id)
if idlookup:
# we were looking up by ID but nothing matched
raise web.notfound()
if polid == "" or polid == "index":
polids = tuple(x.id
for x in db.query('select id from curr_politician'))
p = schema.Politician.select(where='id in $polids',
order='district_id asc',
vars=locals())
out = apipublish.publish(p, format)
if out: return out
return render.pollist(p)
try:
p = schema.Politician.where(id=polid)[0]
except IndexError:
raise web.notfound()
#@@move into schema
p.fec_ids = [
x.fec_id for x in db.select('politician_fec_ids',
what='fec_id',
where='politician_id=$polid',
vars=locals())
]
p.related_groups = group_politician_similarity(polid)
p.contributors = politician_contributors(polid, 5)
p.contributor_employers = politician_contributor_employers(polid, 5)
p.lob_contribs = politician_lob_contributions(polid, 0, 5)
p.capitolwords = p.bioguideid and get_capitolwords(p.bioguideid)
out = apipublish.publish([p], format)
if out: return out
return render.politician(p, sparkpos)
def generate_handshakes():
print 'generating handshakes ...'
pol2corp = db.query('SELECT es.politician_id as frm, '
'e.recipient_stem as to, '
'SUM(e.final_amt) as amount, '
'2008 as year ' #all the data is of 2008 as of now
'FROM earmark e, earmark_sponsor es '
'WHERE es.earmark_id = e.id and '
'e.recipient_stem is not null and '
"e.recipient_stem != '' "
'GROUP BY es.politician_id, e.recipient_stem, year')
corp2pol = db.query('SELECT c.employer_stem as frm, '
'fec.politician_id as to, '
'SUM(c.amount) as amount, '
"date_part('year', sent) as year "
'FROM contribution c, committee pac, politician_fec_ids fec '
'WHERE c.recipient_id = pac.id and '
'pac.candidate_id = fec.fec_id and '
'c.employer_stem is not null and '
"c.employer_stem != '' "
'GROUP BY c.employer_stem, fec.politician_id, year')
pols = {}
for r in pol2corp:
pols[r.to, r.frm, r.year] = r.amount
corps = {}
for r in corp2pol:
corps[r.frm, r.to, r.year] = r.amount
for k in pols:
if k in corps:
corp, pol, year = k
yield dict(politician_id=pol, corporation=corp, year=year,
pol2corp=pols[k], corp2pol=corps[k])
def GET(self, corp_id, format=None):
if corp_id == '': raise web.notfound()
corp_id = corp_id.lower().replace('_', ' ')
contributions = db.query("""SELECT count(*) as how_many,
sum(amount) as how_much, p.firstname, p.lastname, p.id as polid
FROM contribution cn, committee cm, politician_fec_ids pfi,
politician p WHERE cn.recipient_id = cm.id
AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id
AND lower(cn.employer_stem) = $corp_id
GROUP BY p.lastname, p.firstname, p.id
ORDER BY how_much DESC""",
vars=locals())
total_num = db.select('contribution',
what='count(*)',
where='lower(employer_stem)=lower($corp_id)',
vars=locals())[0].count
return render.employer(contributions, corp_id, total_num)
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 sparkpos(table, what, id):
if table == 'district':
id_col = 'name'
id = id.upper()
elif table == 'politician':
id_col = 'id'
else:
return 0
assert table in table_map.values()
if not r_safeproperty.match(what): raise web.notfound()
item = db.query("select count(*) as position from %(table)s, \
(select * from %(table)s where %(id_col)s=$id) as a \
where %(table)s.%(what)s > a.%(what)s" % {
'table': table,
'what': what,
'id_col': id_col
},
vars={'id': id})[0]
return item.position + 1 # '#1' looks better than '#0'
def GET(self, img=None):
i = web.input()
frm, to = i.get('from', ''), i.get('to', '')
if frm and to:
contributions = db.query("""SELECT sum(amount) AS amount,
p.firstname, p.lastname, p.id as polid,
employer_stem as employer, date_part('year', sent) as year
FROM contribution cn, committee cm, politician_fec_ids pfi, politician p
WHERE cn.recipient_id = cm.id AND cm.candidate_id = pfi.fec_id
AND pfi.politician_id = p.id AND cn.employer_stem = $frm AND p.id=$to
GROUP BY year, p.lastname, p.firstname, cn.employer_stem, p.id
ORDER BY year""",
vars=locals()).list()
if img:
points = [c.amount for c in contributions]
web.header('Content-Type', 'image/png')
return simplegraphs.sparkline(points, i.get('point', 0))
return render.contributions(frm, to, contributions)
else:
raise web.notfound()
def post(self):
user = self.get_argument("user")
password = self.get_argument("password").lower()
err_code = 0
user_type = 0
if EMAIL_PAT.match(user) or PHONE_PAT.match(user):
# password=hashlib.sha256(password).hexdigest()
if not re.match("^\w+$", password):
err_code = 1003
# old user
elif db.query("select * from user where user=%s", user):
result = db.get(
"select type from user where user=%s and password=%s limit 1",
user, password)
if result:
user_type = result["type"]
my_log.info("login page : login success user:%s" % user)
else:
err_code = 1001
my_log.info("login page : password error user:%s" % user)
# new user
else:
db.execute(
"insert into user(user,password,create_time,total_flow) values (%s,%s,current_timestamp,0)",
user, password)
user_type = 1
err_code = False
my_log.info("login page : new user:%s" % user)
else:
err_code = 1000
my_log.info("login page : username error user:%s" % user)
if not err_code:
user = self.set_secure_cookie("user", self.get_argument("user"))
user_type = self.set_secure_cookie("user_type", str(user_type))
self.redirect("/")
else:
self.redirect("/?err_code=%s" % err_code)
def post(self):
roomname = self.get_argument("roomname")
password = self.get_argument("password")
if roomname:
room = db.query("SELECT * FROM room WHERE roomname=%s", roomname)
if room: return self.write(json_encode({'result': 2}))
# try:
createtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
roomId = db.execute(
"INSERT INTO room (roomname, created_time, owner_id) VALUES (%s, %s, %s)",
roomname, createtime, self.get_secure_cookie("userid"))
print roomId
room = db.get(
"SELECT room.id, roomname, created_time, username FROM room, user WHERE room.id=%s and room.owner_id=user.id",
int(roomId))
roomInfo = {}
roomInfo["roomId"] = room.id
roomInfo["roomname"] = room.roomname
roomInfo["created_time"] = room.created_time.strftime(
'%Y-%m-%d %H:%M:%S')
roomInfo["username"] = room.username
return self.write(roomInfo)
def index(self):
#/p/(.*?)/earmarks
return ('/p/%s/earmarks' % (e.politician_id) \
for e in db.query('select distinct(politician_id) from earmark_sponsor'))
def index(self):
#/p/(.*?)/lobby
return ('/p/%s/lobby' % (p.politician_id) for p in db.query(
'select distinct(politician_id) from lob_contribution'))
def index(self):
#/lob/f/?(.*?)
return ('/lob/f/%s' % (f.id)
for f in db.query('select distinct(id) from lob_filing'))
def index(self):
#/p/(.*?)/introduced
return ('/p/%s/introduced' % (p.sponsor_id) \
for p in db.query('select distinct(sponsor_id) from bill'))
def index(self):
#/p/(.*?)/groups
return ('/p/%s/groups' % (p.politician_id) \
for p in db.query('select distinct(politician_id) from group_politician_similarity'))
def create_cache(col1, col2):
cache = Cache(start_date=col1, end_date=col2)
cache.pages = []
pages = db.query(Collection).filter(Collection.date == col1).all()
parameters = [
'status_code', 'robots_txt', 'redirects', 'b_home_footer',
'description', 'b_footer_search_also', 'h2', 'h3', 'title',
'canonical', 'robots', 'b_descr_blocks_item',
'p_gsarticle_promo_aside', 'b_left', 'headers', 'b_descr_text',
'keywords', 'error', 'h1', 'load_time', 'b_similar', 'size'
]
for page1 in pages:
page2 = db.query(Collection).filter(
Collection.date == col2,
CollectionItem.url == page1.url).one_or_none()
# Main data
if page1 and page2:
for key in page1.__dict__:
if key.startswith('_'):
continue
if getattr(page1, key, None) != getattr(page2, key, None):
if getattr(cache, key, None):
cache.__dict__[key] += 1
else:
setattr(cache, key, 1)
# URLs data
keys = []
for key in parameters:
if page1 and page2:
if getattr(page1, key, None) != getattr(page2, key, None):
keys.append(key)
if page1.status_code == 404:
keys.append('404')
elif page1.robots_txt == 'Disallowed':
keys.append('rb_txt')
elif page1.robots == 'noindex, nofollow':
keys.append('rb_meta')
elif page1.redirects != '301':
keys.append('redirects')
cache_page = CachePage(url=page1.url, status_code=page1.status_code)
for k in keys:
setattr(cache_page, k, True)
cache.pages.append(cache_page)
re_redirects = re.compile(r'^301', re.IGNORECASE)
cache.errors_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.status_code == 404).count()
cache.disallowed_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.redirects == re_redirects).count()
cache.errors_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.status_code == 404).count()
cache.disallowed_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.redirects == re_redirects).count()
db.add(cache)
db.commit()
return cache
def handshakes(self):
return db.query('select * from handshakes '
'where politician_id=$self.id '
'order by year desc, pol2corp+corp2pol desc',
vars=locals()).list()
def init():
db.query("CREATE VIEW census AS select * from census_meta NATURAL JOIN census_data")
db.query("CREATE INDEX contribution_recipient_id_idx ON contribution (recipient_id)")
db.query("CREATE INDEX contribution_zip_idx ON contribution (zip)")
db.query("CREATE INDEX contribution_empl_stem_idx ON contribution (LOWER(employer_stem))")
db.query("CREATE INDEX contribution_occupation_idx ON contribution (LOWER(occupation));")
db.query("CREATE INDEX contribution_lower_name_zip_idx ON contribution (LOWER(name), zip);")
db.query("ANALYZE contribution;")
db.query("CREATE VIEW curr_politician AS SELECT politician.* FROM politician, congress WHERE politician.id = politician_id AND congress_num='111' AND current_member = 't' ;")
db.query("GRANT ALL on curr_politician TO watchdog;")
try:
db.query("GRANT ALL on census TO watchdog")
except:
pass # group doesn't exist
