def findById(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select j.id, j.title,j.content,j.view_count,j.created_at,j.category_id,c.title ctitle from %s j left join category c on j.category_id=c.category_id where j.id = %s" %(tbname,v))
return n.fetchAll()
def list(self,page, limit, cid,menu):
n = MySQL()
currRow = (int(page)-1)*int(limit)
n.selectDb('joke')
tbname = 'joke'
print "currRow:"+str(currRow)
conditions = (tbname,)
sqlCount = "SELECT FOUND_ROWS() c"
sql = "select j.id, j.title,j.view_count,j.created_at,c.title ctitle from %s j left join category c on j.category_id=c.category_id where 1=1 and j.content!='' "
if(cid):
print "cid:"+cid
sql += " AND j.category_id = %s"
conditions = conditions + (cid ,)
print "conditions:"+str(conditions)
conditions = conditions+(currRow,limit)
if(menu=="latest"):
sql += " order by j.created_at desc,j.id desc "
elif(menu=="hot"):
sql += " order by j.view_count desc,j.id desc "
sql += " limit %s,%s"
print "conditions:"+str(conditions)
sql = sql % conditions
print sql
n.query(sql)
ret = n.fetchAll()
n.query(sqlCount)
# totalCount = n.fetchAll()
totalCount = self.findCount(cid)
print "totalCount:"
print totalCount
print len(ret)
return ret,totalCount
def findByTitleDate(self,title,created_at):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select id from %s where title = '%s' and created_at= '%s'" %(tbname,title,created_at))
return n.fetchAll()
def findByTitle(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select name from %s where title = %s" %(tbname,v))
return n.fetchAll()
def findAll(self):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images")
return n.fetchAll()
def findAll(self):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
# n.query("select name from %s where updated_at <'2016-06-27 00:00:00'" %(tbname) )
# n.query("select name from %s where is_face!=1 " %(tbname) )
n.query("select * from %s " %(tbname) )
return n.fetchAll()
def findCount(sefl,cid):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
conditions = (tbname,)
sqlCount = "SELECT count(id) c from %s where content!=''"
if(cid):
sqlCount += " AND category_id = %s"
conditions = conditions+(cid ,)
sqlCount = sqlCount % conditions
n.query(sqlCount)
totalCount = n.fetchAll()
return totalCount[0]['c']
#本地主机
#myhost = "107.191.118.80"
myhost = "127.0.0.1"
myuser = "******"
mypw = ""
myconn=MySQL(myhost,myuser,mypw)
myconn.selectDb('twitter')
#获取popularity相关数据
#retweet数目和favorite数目
sql_get_stauts="select distinct * from statusUpdate2 order by 'updatetime' desc"
results_1=conn.query(sql_get_stauts)
print u"查询条数："+str(results_1)
res_status_1 = conn.fetchAll()
for row in res_status_1:
status_id_1=row['status_id']
retweet_1=row['retweet_count']
favorite_1=row['favorite_count']
print "status:"+status_id_1
print "retweet:"+retweet_1
print "favorite:"+favorite_1
sql_check_1="select * from popularity where status_id='%s'"%(status_id_1)
check_1=myconn.query(sql_check_1)
res_1=myconn.fetchAll()
for i in res_1:
myretweet_1=i['retweet']
myfavorite_1=i['favorite']
if check_1>0:
if int(retweet_1) > int(myretweet_1) or int(myfavorite_1)>int(favorite_1) :