def get_uid_list(table_name, field_name="*"):
cursor = pi_cur()
uid_list = set()
uids = sql_select(cursor, table_name, field_name)
for uid_dict in uids:
uid_list.update(list(uid_dict.values()))
return list(uid_list)
def update_count_max(eid, max_num):
cursor = pi_cur()
sql = "UPDATE Event SET count_max = %s WHERE e_id = %s"
params = [(max_num, eid)]
n = cursor.executemany(sql, params)
conn.commit()
def update_cal_status(eid_dict, cal_status):
cursor = pi_cur()
sql = "UPDATE Event SET cal_status = %s WHERE e_id = %s"
params = [(cal_status, item['e_id']) for item in eid_dict]
n = cursor.executemany(sql, params)
conn.commit()
def update_monitor_status(eid, status):
cursor = pi_cur()
sql = "UPDATE Event SET monitor_status = %s WHERE e_id = %s"
params = [(status, eid)]
n = cursor.executemany(sql, params)
conn.commit()
def get_mid_dic_caled():
cursor = pi_cur()
sql = 'select * from Information where cal_status = 2 and monitor_status = 1 and add_manully = 0'
cursor.execute(sql)
result = cursor.fetchall()
return result
def check_monitor_status(mid_dic_batch):
midlist = [item["mid"] for item in mid_dic_batch]
midlist_format = ""
for mid in midlist:
midlist_format += "'{}',".format(mid)
midlist_format = midlist_format[:-1]
cursor = pi_cur()
sql = "SELECT e.monitor_status, i.i_id FROM `Event` e JOIN Event_information ei ON e.e_id = ei.event_id JOIN Information i ON i.i_id = ei.information_id WHERE i.mid IN ({})".format(
midlist_format)
cursor.execute(sql)
result = cursor.fetchall()
check_dic = {}
for item in result:
i_id = item["i_id"]
monitor_status = item["monitor_status"]
if i_id in check_dic:
check_dic[i_id].append(monitor_status)
else:
check_dic[i_id] = [monitor_status]
params = []
for i_id in check_dic:
if sum(check_dic[i_id]) == 0:
params.append((0, i_id))
sql = "UPDATE Information SET monitor_status = %s WHERE i_id = %s"
n = cursor.executemany(sql, params)
conn.commit()
def sql_insert_many(table_name, primary_key, data_dict):
cursor = pi_cur()
columns = []
params = []
columns.append(primary_key)
for item_id in data_dict:
item = data_dict[item_id]
param_one = []
param_one.append(item_id)
for k, v in item.items():
if k not in columns:
columns.append(k)
param_one.append(v)
params.append(tuple(param_one))
columns_sql = ",".join(columns)
values = []
for i in range(len(columns)):
values.append("%s")
values_sql = ",".join(values)
sql = 'replace into %s (%s) values (%s)' % (table_name, columns_sql,
values_sql)
if len(params):
n = cursor.executemany(sql, params)
m = len(params)
print("insert {} success, {} failed".format(m, m - n))
conn.commit()
else:
print('empty data')
def update_cal_status(uid_list, computestatus):
cursor = pi_cur()
sql = "UPDATE Figure SET computestatus = %s WHERE uid = %s"
params = [(computestatus, item) for item in uid_list]
n = cursor.executemany(sql, params)
conn.commit()
def update_process_status(eid, cal_status):
cursor = pi_cur()
sql = "UPDATE EventPositive SET process_status = %s WHERE e_id = %s and store_type = 2"
params = [(cal_status, eid)]
cursor.executemany(sql, params)
conn.commit()
def update_cal_status(mid_dic, cal_status):
cursor = pi_cur()
sql = "UPDATE Information SET cal_status = %s WHERE i_id = %s"
params = [(cal_status, item["i_id"]) for item in mid_dic]
n = cursor.executemany(sql, params)
conn.commit()
def insert_uid(uid_list, e_id):
uids = ''
if len(uid_list) == 0:
return 0
for uid in uid_list:
uids += uid + ','
cusor = pi_cur()
sql = "select figure_id from Event_figure where figure_id in (%s) and event_id='%s'" % (
uids[:-1], e_id)
cusor.execute(sql)
results = cusor.fetchall()
# print(results)
id_list = [item['figure_id'] for item in results]
# print(id_list)
insert = list(set(uid_list).difference(set(id_list)))
# print(insert)
val = []
for uid in insert:
val.append((uid, e_id))
insert_sql = 'insert into Event_figure(figure_id,event_id) values (%s,%s)'
try:
# print(val)
cusor.executemany(insert_sql, val)
# 获取所有记录列表
conn.commit()
except:
print('错误')
conn.rollback()
def update_mysql_hazard_index():
cur = conn.cursor()
sql = "SELECT * FROM Information, Event_information WHERE Event_information.event_id = 'event_ostudent' and Event_information.information_id = Information.i_id"
cur.execute(sql)
data = cur.fetchall()
cursor = pi_cur()
for index, item in enumerate(data):
mid = item["mid"]
i_id = item["i_id"]
sql = "SELECT * FROM Informationspread WHERE mid = '{}' and predict = 0 order by store_date desc".format(
mid)
cursor.execute(sql)
try:
hazard_index = cursor.fetchone()["hazard_index"]
update_sql = "UPDATE Information SET hazard_index={} WHERE i_id='{}'".format(
hazard_index, i_id)
print(index)
except:
update_sql = "UPDATE Information SET cal_status=0 WHERE i_id='{}'".format(
i_id)
print(index, "没找到。。")
cursor.execute(update_sql)
conn.commit()
def get_event_info(e_id):
sql = "select keywords_dict,begin_date,end_date,es_index_name from Event where e_id = '{}'".format(
e_id)
cursor = pi_cur()
cursor.execute(sql)
results = cursor.fetchall()[0]
return results['keywords_dict'], results['begin_date'], results[
'end_date'], results['es_index_name']
def get_sensitive_word(e_id, bias, file):
cusor = pi_cur()
sql = "select prototype from SensitiveWord where e_id = '{}' and perspective_bias = '{}'".format(
e_id, bias)
cusor.execute(sql)
results = cusor.fetchall()
with open(file, 'w', encoding='utf-8') as wf:
for result in results:
wf.write(result['prototype'] + '\n')
def get_event(uid_list):
uids = ''
for uid in uid_list:
uids += uid + ','
cusor = pi_cur()
sql = 'select figure_id,event_id from Event_figure where figure_id in (%s)' %uids[:-1]
cusor.execute(sql)
results = cusor.fetchall()
return results
def update_cal_status(uid_list, computestatus):
uids = ''
for uid in uid_list:
uids += uid + ','
cursor = pi_cur()
sql = "UPDATE Figure SET computestatus = %s WHERE uid in (%s)"%(computestatus,uids[:-1])
cursor.execute(sql)
# params = [(computestatus, item) for item in uid_list]
# n = cursor.executemany(sql, params)
conn.commit()
def get_uid_list(n):
cursor = pi_cur()
if n == 0:
sql = 'select %s from %s where computestatus=0' % ("uid", "Figure")
else:
sql = 'select %s from %s' % ("uid", "Figure")
cursor.execute(sql)
result = cursor.fetchall()
uidlist = [item["uid"] for item in result]
return uidlist
def get_info(uid_list):
if len(uid_list) == 0:
return []
uids = ''
for uid in uid_list:
uids += uid + ','
cusor = pi_cur()
sql = 'select uid,mid from Information where uid in (%s)' % uids[:-1]
cusor.execute(sql)
results = cusor.fetchall()
return results
def get_pos(POS_NUM):
cursor = pi_cur()
sql = 'select i_id, text from Event_information, Information where information_id = i_id group by information_id order by Count(*) DESC, hazard_index DESC'
cursor.execute(sql)
try:
result = cursor.fetchall()[:POS_NUM]
except:
result = cursor.fetchall()
mid = [i['i_id'] for i in result]
texts = [i['text'] for i in result]
return mid, texts
def get_add_num(e_id):
"""
获取人工添加的文本数
:param e_id: 该事件e_id
:return: add_num
"""
sql = "select id from EventPositive where e_id = '{}' and process_status = 0".format(e_id)
cursor = pi_cur()
cursor.execute(sql)
add_num = len(cursor.fetchall())
return add_num
def save_figure(save_dict):
cusor = pi_cur()
val = []
for uid in save_dict:
val.append((uid,uid,save_dict[uid]))
sql = "INSERT INTO Figure(f_id,uid,identitystatus,computestatus,monitorstatus) VALUE(%s,%s,%s,0,1) ON DUPLICATE KEY UPDATE " \
"uid=values(uid),f_id=values(f_id),identitystatus=values(identitystatus)"
# try:
cusor.executemany(sql,val)
# 获取所有记录列表
conn.commit()
def get_pos_quick(e_id):
cursor = pi_cur()
sql = 'select * from EventPositive where e_id="%s"' %e_id
cursor.execute(sql)
result = cursor.fetchall()
pos_num = len(result)
texts = [item['text'] for item in result]
vec = [np.frombuffer(item['vector'],dtype=np.float32) for item in result]
# print(text)
# print(vec)
return pos_num, texts, vec
def store_pos(e_id, pos_data):
cursor = pi_cur()
t = nowts()
sql = 'insert into EventPositive set e_id=%s,text=%s,vector=%s,store_timestamp=%s,store_type=0,process_status=1'
val = []
for index, row in pos_data.iterrows():
val.append((e_id, row['text'], row['vec'].tostring(), t))
# 执行sql语句
n = cursor.executemany(sql, val)
print("事件相关正类入库成功 %d 条" % n)
conn.commit()
def get_mid_info(e_id):
"""
获取事件已有敏感信息的mid
:param e_id: 该事件e_id
:return: mid
"""
sql = "select information_id from Event_information where event_id = '{}'".format(e_id)
cursor = pi_cur()
cursor.execute(sql)
results = cursor.fetchall()
mid = [i['information_id'][2:] for i in results]
return mid
def get_mid_extend(e_id):
"""
获取事件已在扩线新增库里的mid
:param e_id: 该事件e_id
:return: mid
"""
sql = "select mid from ExtendReview where e_id = '{}'".format(e_id)
cursor = pi_cur()
cursor.execute(sql)
results = cursor.fetchall()
mid = [i['mid'] for i in results]
return mid
def get_data(midlist, start_time, end_time):
cursor = pi_cur()
mids = ""
for m in midlist:
mids += m + ","
sql = "select * from Informationspread where mid in (%s) and predict = 0 and timestamp>%s and timestamp<%s order by timestamp ASC" % (
','.join(['%s'] * len(midlist)), start_time, end_time)
# sql = "select * from Informationspread where mid in ('%s') order by timestamp ASC "% mids[:-1]
cursor.execute(sql, midlist)
results = cursor.fetchall()
# print(results)
return results
def event_sensitivity(e_id, data_dict):
if len(data_dict) == 0:
print('no data')
else:
cursor = pi_cur()
sql = 'insert into Event_information set event_id=%s,information_id=%s'
val = []
for i, j in data_dict.items():
val.append((e_id, j.get('source', None) + i))
# 执行sql语句
n = cursor.executemany(sql, val)
print("入库成功 %d 条" % n)
conn.commit()
def store_event_para(e_id, p_name):
cursor = pi_cur()
sql = 'replace into EventParameter values (%s, %s, %s, %s, %s)'
parameters = {'sentiment_neg':(e_id+'_sentiment_neg','sentiment_neg',0.2,e_id,'信息情感极性负面阈值'),
'sentiment_pos':(e_id+'_sentiment_pos','sentiment_pos',0.7,e_id,'信息情感极性正面阈值'),
'pos_num':(e_id+'_pos_num','pos_num',1000,e_id,'敏感计算时正类数量'),
'neg_num':(e_id+'_neg_num','neg_num',15000,e_id,'敏感计算时负类数量'),
'weibo_num':(e_id+'_weibo_num','weibo_num',100000,e_id,'每日LDA聚类时采样的微博总数'),
'stop_percent':(e_id+'_stop_percent','stop_percent',0.05,e_id,'停止监测的信息数量阈值（峰值的百分比）'),
'pos_neg':(e_id+'_pos_neg','pos_neg',15,e_id,'敏感计算时负类比正类比例'),
'extend_scale':(e_id+'_extend_scale','extend_scale',10,e_id,'扩线比例阈值')}
cursor.executemany(sql, [parameters[p_name]])
conn.commit()
def store_event_para(e_id, p_name):
cursor = pi_cur()
sql = 'replace into EventParameter values (%s, %s, %s, %s, %s)'
parameters = {
'sentiment_neg':
(e_id + '_sentiment_neg', 'sentiment_neg', 0.2, e_id, '信息情感极性负面阈值'),
'sentiment_pos':
(e_id + '_sentiment_pos', 'sentiment_pos', 0.7, e_id, '信息情感极性正面阈值'),
'pos_num': (e_id + '_pos_num', 'pos_num', 1000, e_id, '敏感计算时正类数量'),
'neg_num': (e_id + '_neg_num', 'neg_num', 15000, e_id, '敏感计算时负类数量'),
'weibo_num':
(e_id + '_weibo_num', 'weibo_num', 100000, e_id, '每日LDA聚类时采样的微博总数')
}
cursor.executemany(sql, [parameters[p_name]])
conn.commit()
def update_mysql_geo():
cur = conn.cursor()
sql = "SELECT * from Information, Event_information WHERE Event_information.event_id = 'xianggangshijian_1581919160' and Information.i_id = Event_information.information_id"
cur.execute(sql)
data = cur.fetchall()
cur.close()
cursor = pi_cur()
for index, item in enumerate(data):
geo = item["geo"]
i_id = item["i_id"]
geo_new = geo_transfer(geo)
update_sql = "UPDATE Information SET geo='{}' WHERE i_id='{}'".format(
geo_new, i_id)
cursor.execute(update_sql)
print(index)
conn.commit()
