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 stop_monite():
cursor = conn.cursor()
sql = 'select uid from Figure where monitorstatus=1'
cursor.execute(sql)
results = cursor.fetchall()
uid_list = [item['uid'] for item in results]
if len(uid_list) != 0:
uids = ''
for uid in uid_list:
uids += uid + ','
day_timestamp = int(time.mktime(
datetime.date.today().timetuple())) - 86400 * 100
sql = "select uid from Information where uid in (%s) and timestamp>%s " % (
uids[:-1], day_timestamp)
cursor.execute(sql)
results = cursor.fetchall()
update_uid = list(
set(uid_list).difference(set([item['uid'] for item in results])))
if len(update_uid) != 0:
uids = ''
for uid in update_uid:
uids += uid + ','
sql = 'UPDATE Figure SET monitorstatus = 0 WHERE uid in (%s)' % uids[:
-1]
cursor.execute(sql)
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()
def save(save_dict):
val = []
for mid in save_dict:
print(mid)
for date in save_dict[mid]:
timestamp = date2ts(date)
val.append((mid + str(timestamp), mid, timestamp,
int(save_dict[mid][date]['retweet']),
int(save_dict[mid][date]['comment']),
save_dict[mid][date]['message_type'], date, 1))
sql = 'insert into Informationspread(is_id,mid,timestamp,retweet_count,comment_count,message_type,store_date,predict) values (%s,%s,%s,%s,%s,%s,%s,%s )'
cursor = conn.cursor()
try:
cursor.executemany(sql, val)
conn.commit()
except:
conn.rollback()
print('错误')