def getHotTopic(self, keywords, limit, page):
sql = "SELECT t.topics,sum(t.attitudes_count) as attitudes_count,sum(t.comments_count) as comments_count,\
sum(t.reposts_count) as reposts_count FROM(SELECT a.attitudes_count,a.comments_count,\
a.reposts_count,substring_index(substring_index(a.topics,\
'；',b.help_topic_id + 1),'；',- 1) topics FROM weibo a JOIN mysql.help_topic b ON ( b.help_topic_id <(\
length(a.topics) - length(REPLACE (a.topics, '；', '')) + 1) AND a.topics IS NOT NULL AND a.topics != '')\
WHERE a.topics IS NOT NULL and a.created_at >=:start_time and a.created_at <=:end_time ORDER BY a.id) t \
GROUP BY t.topics ORDER BY attitudes_count desc,comments_count desc,reposts_count desc"
start_time, end_time = get_last_week()
params = {'start_time': start_time, 'end_time': end_time}
if (keywords):
sql = "SELECT t.topics,sum(t.attitudes_count) as attitudes_count,sum(t.comments_count) as comments_count,\
sum(t.reposts_count) as reposts_count FROM(SELECT a.attitudes_count,a.comments_count,\
a.reposts_count,substring_index(substring_index(a.topics,\
'；',b.help_topic_id + 1),'；',- 1) topics FROM weibo a JOIN mysql.help_topic b ON ( b.help_topic_id <(\
length(a.topics) - length(REPLACE (a.topics, '；', '')) + 1) AND a.topics IS NOT NULL AND a.topics != '')\
WHERE a.topics IS NOT NULL and a.topics like concat('%',:keywords,'%') and a.created_at >=:start_time and a.created_at <=:end_time ORDER BY a.id) t \
GROUP BY t.topics ORDER BY attitudes_count desc,comments_count desc,reposts_count desc"
params['keywords'] = keywords
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
item = dict(data[i])
result.append(item)
return ResponseResult.lay_success(data=result, count=len(data))
def getHotWeiboList(self, orderBy, weibo_keywords, topic_keywords, limit,
page):
print(orderBy)
if (orderBy == 1):
orderBy = Weibo.comments_count.desc()
elif (orderBy == 2):
orderBy = Weibo.attitudes_count.desc()
elif (orderBy == 3):
orderBy = Weibo.reposts_count.desc()
else:
return ResponseResult.error()
if (weibo_keywords != None and weibo_keywords != ''):
if (topic_keywords != None and topic_keywords != ''):
data = Weibo.query.filter(
Weibo.text.contains(weibo_keywords),
Weibo.topics.contains(topic_keywords)).order_by(
orderBy).all()
else:
data = Weibo.query.filter(Weibo.text.contains(
weibo_keywords)).order_by(orderBy).all()
else:
if (topic_keywords != None and topic_keywords != ''):
data = Weibo.query.filter(Weibo.topics.contains(
topic_keywords)).order_by(orderBy).all()
else:
data = Weibo.query.order_by(orderBy).all()
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
result.append(data[i].to_json())
return ResponseResult.lay_success(data=result, count=len(data))
def get_users(self,limit,page):
data = User.query.all()
print(limit)
print(page)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start,end):
result.append(data[i].to_json())
return ResponseResult.lay_success(data=result,count=len(data))
def getCommentList(self, keywords, limit, page):
if (keywords != None and keywords != ''):
data = Comments.query.filter(
Comments.comment.contains(keywords)).all()
else:
data = Comments.query.all()
print(data)
print(limit)
print(page)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
result.append(data[i].to_json())
return ResponseResult.lay_success(data=result, count=len(data))
def getHotNews(self, keywords, limit, page):
sql = "select title,link,creator,create_time from school_news order by title desc"
if (keywords != None and keywords != ''):
sql = "select title,link,creator,create_time from school_news where title like concat('%',:keywords,'%') order by title desc"
params = {'keywords': keywords}
ret = db.session.execute(sql, params).fetchall()
else:
ret = db.session.execute(sql).fetchall()
data = list(ret)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
item = dict(data[i])
item['create_time'] = item['create_time'].strftime(
'%Y-%m-%d %H:%M')
result.append(item)
return ResponseResult.lay_success(data=result, count=len(data))
def getSensitiveWord(self, userId, limit, page):
sql = 'select * from sensitive_word where creator=:creator'
params = {'creator': userId}
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
user_sql = 'select * from user where id = :id'
for i in range(start, end):
item = dict(data[i])
params = {'id': item['creator']}
ret = db.session.execute(user_sql, params).fetchone()
user = dict(ret)
item['creator'] = user['user_name']
item['create_time'] = item['create_time'].strftime(
'%Y-%m-%d %H:%M')
result.append(item)
return ResponseResult.lay_success(data=result, count=len(data))
def getNlpData(self, start_time, end_time, limit, page):
start_time = datetime.datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d')
date_from = datetime.datetime(start_time.year, start_time.month,
start_time.day, 0, 0, 0)
date_to = datetime.datetime(end_time.year, end_time.month,
end_time.day, 23, 59, 59)
params = {'start_time': str(date_from), 'end_time': str(date_to)}
sql = "select * from nlp_result where create_time is not null \
and create_time>=:start_time and create_time<=:end_time "
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
item = dict(data[i])
item['create_time'] = item['create_time'].strftime(
'%Y-%m-%d %H:%M')
result.append(item)
return ResponseResult.lay_success(data=result, count=len(data))
def getLastYuqing(self, start_time, end_time, limit, page):
start_time = datetime.datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d')
date_from = datetime.datetime(start_time.year, start_time.month,
start_time.day, 0, 0, 0)
date_to = datetime.datetime(end_time.year, end_time.month,
end_time.day, 23, 59, 59)
params = {'start_time': str(date_from), 'end_time': str(date_to)}
sql = "SELECT t.* FROM (SELECT text as title,screen_name as creator,created_at as create_time,'微博' as type from weibo where created_at>=:start_time and created_at<=:end_time \
UNION SELECT title,creator,create_time,'新闻' as type from school_news where create_time>=:start_time and create_time<=:end_time \
UNION SELECT title,creator,create_time,'贴吧' as type from tieba where create_time>=:start_time and create_time<=:end_time) t order by t.create_time desc"
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
start = (page - 1) * limit
end = page * limit if len(data) > page * limit else len(data)
result = []
for i in range(start, end):
item = dict(data[i])
item['create_time'] = item['create_time'].strftime(
'%Y-%m-%d %H:%M')
result.append(item)
return ResponseResult.lay_success(data=result, count=len(data))