import json from MyUtils.Dingtalk import send_message from MyUtils.Dingtalk import send_message_markdown import datetime import time import pymysql import pytz SELECT_BUILDING_SQL = "SELECT zj_building_id,sg_building_id FROM sagacloud_setup.zj_sync_config_building" SELECT_FC_SQL = "SELECT building_id,room_name from zj_fc.t_building_rooms" SELECT_SPACE_SQL = "SELECT building_id,local_name FROM sagacloud_object.object_data_space" TRUNCATE_BUILDING_ROOMS_SQL = "truncate table sagacloud_setup.zj_sync_building_rooms" INSERT_BUILDING_ROOMS_SQL = "INSERT into sagacloud_setup.zj_sync_building_rooms(building_id,building_name,room_name,floor_id,floor_name,`state`,`type`,create_time) values" SELECT_DIFF_FC_SQL="SELECT building_name,floor_id,floor_name,`state`,`type` from zj_fc.t_building_rooms WHERE building_id='%s' and room_name='%s'" def datetime_now(): # datetime_now = datetime.datetime.now().strftime("%Y%m%d%H%M%S") #容器时间 # tz = pytz.timezone('Asia/Shanghai') # 东八区 datetime_now = datetime.datetime.fromtimestamp(int(time.time()), pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S') return datetime_now # 读取配置文件信息 with open("config.json", "r") as f: data = json.load(f) mysql = data["mysql"] database = mysql["database"] dingding = data["dingding"] # mysql插入数据 def insert_mysql(sqls): print("开始往mysql插入数据...") for i in range(0, len(sqls), 1000): sqlranges = sqls[i:i + 1000] sqlranges = INSERT_BUILDING_ROOMS_SQL + ",".join(sqlranges) mysql_cur.execute(sqlranges) conn.commit() print("mysql数据插入成功,合计%s条..." % (len(sqls))) #钉钉推送消息 messages = [] #存储zj_fc表的空间名称 FC_NAMELIST_DICT = {} #存储space表的空间名称 SPACE_NAMELIST_DICT={} # 连接mysql conn = pymysql.connect(**mysql) mysql_cur = conn.cursor() # 获取建筑列表 mysql_cur.execute(SELECT_BUILDING_SQL) buildinglist = mysql_cur.fetchall() # 获取zj_fc空间名称 mysql_cur.execute(SELECT_FC_SQL) fc_namelist = mysql_cur.fetchall() for building in buildinglist: if building[0] and building[1] != None: for fc_name in fc_namelist: if fc_name[0] == building[0]: FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = building[0]+"_"+fc_name[1] # FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = 1 else: message = (" - **存在建筑对应关系为空:** %s(%s) \n") % (building[0],building[1]) print(message) messages.append(message) messages.append("___") # 获取space空间名称 mysql_cur.execute(SELECT_SPACE_SQL) space_namelist = mysql_cur.fetchall() for space in space_namelist: SPACE_NAMELIST_DICT[space[0]+"_"+space[1]] = 1 # print(FC_NAMELIST_DICT) # print(SPACE_NAMELIST_DICT) #对比空间名称,取出差异数据生成sql语句 sqls = [] diff_namelist = set(FC_NAMELIST_DICT) - set(SPACE_NAMELIST_DICT) if diff_namelist != set(): for diff_name in diff_namelist: diff_building_rooms = FC_NAMELIST_DICT[diff_name] diff_building_id = str(diff_building_rooms).split("_")[0] diff_room_name = str(diff_building_rooms).split("_")[1] mysql_cur.execute(SELECT_DIFF_FC_SQL%(diff_building_id,diff_room_name)) diff_data = mysql_cur.fetchall()[0] building_name = diff_data[0] floor_id = diff_data[1] floor_name = diff_data[2] state = diff_data[3] type = diff_data[4] print(building_name,diff_building_id,diff_room_name,floor_id,floor_name,state,type) sqlline = "('%s','%s','%s','%s','%s','%s','%s','%s')"%(diff_building_id,building_name,diff_room_name,floor_id,floor_name,state,type,datetime_now()) sqls.append(sqlline) # 获取space空间名称 print("清空MySQL表数据...") mysql_cur.execute(TRUNCATE_BUILDING_ROOMS_SQL) conn.commit() #往MySQL插入数据 insert_mysql(sqls) # if messages: # messages_info = "\n".join(messages) # # 如果有报警,发送钉钉 # title = "## 【之江实验室】空间名称报警 " # messages_info = title + "\n" + messages_info # print(messages_info) # send_message_markdown(title, messages_info, dingding) mysql_cur.close() conn.close()