def getSensitiveWordByTime(self, userId, start_time, end_time):
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)}
word_sql = 'select * from sensitive_word where creator=:creator'
word_params = {'creator': userId}
ret = db.session.execute(word_sql, word_params).fetchall()
word_data = list(ret)
xAxis = []
series = []
legend = []
data_dict = dict()
if (len(word_data) == 0):
return ResponseResult.success()
all_sql = 'SELECT ss.* from ('
for t in range(len(word_data)):
item = dict(word_data[t])
word = item['word']
params['word'] = word
legend.append(word)
data_dict[word] = []
sql = "SELECT t.create_time,count(t.title) as count,'{}' as word FROM (SELECT text as title,screen_name as creator,created_at as create_time,'微博' as type from weibo \
UNION SELECT title,creator,create_time,'新闻' as type from school_news \
UNION SELECT title,creator,create_time,'贴吧' as type from tieba ) t \
where t.create_time>=:start_time and t.create_time<=:end_time and t.title like concat('%','{}','%') group by t.create_time "
all_sql = all_sql + sql.format(word, word)
if (t == len(word_data) - 1):
all_sql = all_sql + ') ss order by ss.create_time'
else:
all_sql = all_sql + ' UNION '
print(all_sql)
ret = db.session.execute(all_sql, params).fetchall()
data = list(ret)
for i in range(len(data)):
item = dict(data[i])
item['create_time'] = item['create_time'].strftime('%Y-%m-%d')
if (item['create_time'] not in xAxis):
xAxis.append(item['create_time'])
for t in range(len(legend)):
if (legend[t] == item['word']):
data_dict[legend[t]].append(item['count'])
else:
data_dict[legend[t]].append(0)
for g in range(len(legend)):
series.append({
'name': legend[g],
'type': 'line',
'data': data_dict[legend[g]]
})
result = {'xAxis': xAxis, 'series': series, 'legend': legend}
return ResponseResult.success(data=result)
def delete_user(self,id):
try:
User.query.filter_by(id=id).delete()
db.session.commit()
return ResponseResult.success()
except:
return ResponseResult.error()
def getCount(self):
sql = 'SELECT count(w.id) as weibo_number,sum(w.attitudes_count) as attitudes_count,\
sum(w.comments_count) as comments_count,sum(w.reposts_count) as reposts_count\
from weibo w'
ret = db.session.execute(sql).fetchone()
return ResponseResult.success(data=dict(ret))
def getAllCount(self, start_time, end_time):
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)}
print(params)
sql = 'SELECT count(*) as count from weibo where created_at>=:start_time and created_at<=:end_time'
ret = db.session.execute(sql, params).fetchone()
weibo_count = dict(ret)['count']
sql = 'SELECT count(*) as count from school_news where create_time>=:start_time and create_time<=:end_time'
ret = db.session.execute(sql, params).fetchone()
news_count = dict(ret)['count']
sql = 'SELECT count(*) as count from tieba where create_time>=:start_time and create_time<=:end_time'
ret = db.session.execute(sql, params).fetchone()
tieba_count = dict(ret)['count']
all_count = weibo_count + news_count + tieba_count
return ResponseResult.success({
'all_count': all_count,
'weibo_count': weibo_count,
'news_count': news_count,
'tieba_count': tieba_count
})
def getSensitiveWordList(self, userId):
sql = 'select * from sensitive_word where creator=:creator'
params = {'creator': userId}
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
word_list = []
result = dict()
query_sql = "SELECT t.* FROM \
(SELECT text as title,created_at as create_time,'微博' as type from weibo where text like concat('%',:word,'%') \
UNION SELECT title,create_time,'新闻' as type from school_news where title like concat('%',:word,'%') \
UNION SELECT title,create_time,'贴吧' as type from tieba where title like concat('%',:word,'%')) t order by t.create_time desc limit 10 "
for i in range(len(data)):
item = dict(data[i])
word = item['word']
word_list.append(word)
params = {'word': word}
info = db.session.execute(query_sql, params).fetchall()
temp = list(info)
dict_list = []
for s_temp in temp:
s_temp = dict(s_temp)
dict_list.append(s_temp['title'])
result[word] = dict_list
return ResponseResult.success({
'word_list': word_list,
'result': result
})
def getWeiboStaticByTime(self):
sql = "select DATE_FORMAT(w.created_at,'%Y-%m') as created_at,sum(1) as weibo_number,sum(w.attitudes_count) as attitudes_count\
,sum(w.comments_count) as comments_count,sum(w.reposts_count) as reposts_count from weibo w \
GROUP BY DATE_FORMAT(w.created_at,'%Y-%m') order by created_at"
ret = db.session.execute(sql).fetchall()
data = list(ret)
xAxis = []
weibo_number_list = []
attitudes_count_list = []
comments_count_list = []
reposts_count_list = []
for i in range(len(data)):
item = data[i]
xAxis.append(item.created_at)
weibo_number_list.append(item.weibo_number)
attitudes_count_list.append(item.attitudes_count)
comments_count_list.append(item.comments_count)
reposts_count_list.append(item.reposts_count)
result = {
'xAxis': xAxis,
'weibo_number_list': weibo_number_list,
'attitudes_count_list': attitudes_count_list,
'comments_count_list': comments_count_list,
'reposts_count_list': reposts_count_list
}
return ResponseResult.success(data=result)
def getTop10HotTopic(self):
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 ORDER BY a.id) t GROUP BY t.topics ORDER BY attitudes_count desc,comments_count desc,reposts_count desc limit 10"
ret = db.session.execute(sql).fetchall()
data = list(ret)
yAxis = []
attitudes_count_list = []
comments_count_list = []
reposts_count_list = []
for i in range(len(data)):
item = data[i]
yAxis.append(item.topics)
attitudes_count_list.append(item.attitudes_count)
comments_count_list.append(item.comments_count)
reposts_count_list.append(item.reposts_count)
result = {
'yAxis': list(reversed(yAxis)),
'attitudes_count_list': list(reversed(attitudes_count_list)),
'comments_count_list': list(reversed(comments_count_list)),
'reposts_count_list': list(reversed(reposts_count_list))
}
return ResponseResult.success(data=result)
def get_user(self,id):
try:
print(id)
user = User.query.filter_by(id=id).first()
print(user.user_name)
return ResponseResult.success(data=user.to_json())
except:
return ResponseResult.error()
def getLastNews(self):
query_sql = 'select title,link from school_news order by create_time desc limit 10'
ret = db.session.execute(query_sql).fetchall()
data = list(ret)
result = []
for item in data:
result.append({'title': item.title, 'link': item.link})
return ResponseResult.success(data=result)
def update_user(self,id,user_name,phone,email):
try:
User.query.filter_by(id=id).update({"user_name":user_name,"phone":phone,"email":email})
db.session.commit()
user = User.query.filter_by(id=id).first()
return ResponseResult.success(data=user.to_json())
except:
return ResponseResult.error()
def getLastTopic(self):
sql = "select text as title from weibo order by created_at desc limit 10"
ret = db.session.execute(sql).fetchall()
data = list(ret)
result = []
for i in range(len(data)):
item = dict(data[i])
result.append(item)
return ResponseResult.success(result)
def addSensitiveWord(self, userId, word):
sql = 'insert sensitive_word (word,creator,create_time) values(:word,:creator,:create_time)'
params = {
'word': word,
'creator': userId,
'create_time': datetime.datetime.now()
}
db.session.execute(sql, params)
return ResponseResult.success()
def getWeiboGender(self):
sql = "select c.gender,count(id) as count from comments c GROUP BY c.gender"
ret = db.session.execute(sql).fetchall()
data = list(ret)
result = []
for item in data:
result.append({'name': item.gender, 'value': item.count})
return ResponseResult.success(data=result)
def getNlpByTime(self, start_time, end_time):
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 DATE_FORMAT(create_time,'%Y-%m-%d') as create_time,nlp,count(id) \
as count from nlp_result where create_time is not null and create_time>=:start_time and create_time<=:end_time \
group by DATE_FORMAT(create_time,'%Y-%m-%d'),nlp order by DATE_FORMAT(create_time,'%Y-%m-%d')"
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
xAxis = []
positive_list = []
neutral_list = []
negative_list = []
positive_time = []
neutral_time = []
negative_time = []
create_time = ''
for i in range(len(data)):
item = dict(data[i])
if (item['create_time'] not in xAxis):
xAxis.append(item['create_time'])
for j in range(len(xAxis)):
time_item = xAxis[j]
positive_flag = False
neutral_flag = False
negative_flag = False
for i in range(len(data)):
item = dict(data[i])
if (time_item == item['create_time']):
if (item['nlp'] == '积极'):
positive_list.append(item['count'])
positive_flag = True
if (item['nlp'] == '中立'):
neutral_list.append(item['count'])
neutral_flag = True
if (item['nlp'] == '消极'):
negative_list.append(item['count'])
negative_flag = True
if (positive_flag == False):
positive_list.append(0)
if (neutral_flag == False):
neutral_list.append(0)
if (negative_flag == False):
negative_list.append(0)
return ResponseResult.success({
'xAxis': xAxis,
'positive_list': positive_list,
'negative_list': negative_list,
'neutral_list': neutral_list
})
def update_password(self,id,old_password,new_password):
try:
user = User.query.filter_by(id=id).first()
if(user.password != old_password):
return ResponseResult.error(ResponseCode.PASSWORD_ERROR)
user.password = new_password
db.session.commit()
return ResponseResult.success()
except:
return ResponseResult.error()
def getYuqingByTime(self, start_time, end_time):
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.create_time,t.type,count(t.title) as count 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 \
group by t.create_time,t.type order by t.create_time"
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
xAxis = []
weibo_list = []
news_list = []
tieba_list = []
type_dict = dict()
for i in range(len(data)):
item = dict(data[i])
item['create_time'] = item['create_time'].strftime('%Y-%m-%d')
if (item['create_time'] not in xAxis):
xAxis.append(item['create_time'])
if (item['type'] == '微博'):
weibo_list.append(item['count'])
type_dict[item['create_time'] + '微博'] = 1
else:
if (item['create_time'] + '微博' not in type_dict):
weibo_list.append(0)
type_dict[item['create_time'] + '微博'] = 1
if (item['type'] == '新闻'):
news_list.append(item['count'])
type_dict[item['create_time'] + '新闻'] = 1
else:
if (item['create_time'] + '新闻' not in type_dict):
news_list.append(0)
type_dict[item['create_time'] + '新闻'] = 1
if (item['type'] == '贴吧'):
tieba_list.append(item['count'])
type_dict[item['create_time'] + '贴吧'] = 1
else:
if (item['create_time'] + '贴吧' not in type_dict):
tieba_list.append(0)
type_dict[item['create_time'] + '贴吧'] = 1
result = {
'xAxis': xAxis,
'weibo_list': weibo_list,
'news_list': news_list,
'tieba_list': tieba_list
}
return ResponseResult.success(data=result)
def login(self,phone,password):
try:
print(phone)
print(password)
user = User.query.filter_by(phone=phone,password=password).first()
print(user)
if(user):
return ResponseResult.success(data=user.to_json())
else:
return ResponseResult.error(ResponseCode.LOGIN_ERROR)
except:
return ResponseResult.error()
def add_user(self,user_name, phone, password, email):
if(self.check_add_param(user_name,phone,email,password)):
return ResponseResult.error(ResponseCode.PARAM_IS_NULL)
# 创建时间
created_time = datetime.datetime.now()
user = User(user_name, phone, password, email,created_time)
try:
db.session.add(user)
db.session.commit()
return ResponseResult.success()
except:
return ResponseResult.error()
def getYuqingMap(self, start_time, end_time):
result = self.getAllCount(start_time, end_time)
weibo_count = result['data']['weibo_count']
news_count = result['data']['news_count']
tieba_count = result['data']['tieba_count']
series = [{
'name': '微博',
'value': weibo_count
}, {
'name': '新闻',
'value': news_count
}, {
'name': '贴吧',
'value': tieba_count
}]
return ResponseResult.success({'series': series})
def getTop10Topic(self):
sql = "SELECT t.topics,count(1) AS count FROM(SELECT a.id,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 ORDER BY a.id) t GROUP BY t.topics ORDER BY count desc limit 10"
ret = db.session.execute(sql).fetchall()
data = list(ret)
yAxis = []
series = []
for i in range(len(data)):
item = data[i]
yAxis.append(item.topics)
series.append(item.count)
result = {
'yAxis': list(reversed(yAxis)),
'series': list(reversed(series))
}
return ResponseResult.success(data=result)
def getNlpMap(self, start_time, end_time):
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 nlp,count(id) as count from nlp_result where create_time is not null \
and create_time>=:start_time and create_time<=:end_time \
group by nlp "
ret = db.session.execute(sql, params).fetchall()
data = list(ret)
series = []
legend = []
for i in range(len(data)):
item = dict(data[i])
series.append({'name': item['nlp'], 'value': item['count']})
legend.append(item['nlp'])
return ResponseResult.success({'legend': legend, 'series': series})
def deleteSensitiveWord(self, id):
sql = 'delete from sensitive_word where id = :id'
params = {'id': id}
db.session.execute(sql, params)
return ResponseResult.success()