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('错误')