start.py 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167
  1. import json
  2. from MyUtils.Dingtalk import send_message_markdown
  3. import datetime
  4. import time
  5. import pymysql
  6. import pytz
  7. import os
  8. SELECT_BUILDING_SQL = "SELECT zj_building_id,sg_building_id FROM sagacloud_setup.zj_sync_config_building"
  9. SELECT_STATE_SQL = "SELECT room_id from sagacloud_setup.zj_t_building_rooms where `type` = 1" #只对比类型为1的点位
  10. SELECT_FC_SQL = "SELECT building_id,room_name,room_id from zj_fc.t_building_rooms"
  11. SELECT_SPACE_SQL = "SELECT building_id,local_name FROM sagacloud_object.object_data_space"
  12. TRUNCATE_BUILDING_ROOMS_SQL = "truncate table sagacloud_setup.zj_sync_building_rooms"
  13. 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"
  14. 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'"
  15. def datetime_now():
  16. # datetime_now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
  17. #容器时间
  18. # tz = pytz.timezone('Asia/Shanghai') # 东八区
  19. datetime_now = datetime.datetime.fromtimestamp(int(time.time()),
  20. pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
  21. return datetime_now
  22. # mysql插入数据
  23. def insert_mysql(sqls):
  24. print("%s 开始往mysql插入数据..." % datetime_now())
  25. for i in range(0, len(sqls), 1000):
  26. sqlranges = sqls[i:i + 1000]
  27. sqlranges = INSERT_BUILDING_ROOMS_SQL + ",".join(sqlranges)
  28. mysql_cur.execute(sqlranges)
  29. conn.commit()
  30. print("%s mysql数据插入成功,合计%s条..." % (datetime_now(), len(sqls)))
  31. # # 读取配置文件信息
  32. # with open("config.json", "r") as f:
  33. # data = json.load(f)
  34. # mysql = data["mysql"]
  35. # database = mysql["database"]
  36. # dingding = data["dingding"]
  37. mysql = {
  38. "host": os.getenv("host"),
  39. "port": os.getenv("port"),
  40. "user": os.getenv("user"),
  41. "passwd": os.getenv("passwd"),
  42. "database": os.getenv("database")
  43. }
  44. database = os.getenv("database")
  45. dingding = os.getenv("dingding")
  46. while True:
  47. print("%s 等待开始检查..."%datetime_now())
  48. time_now = datetime.datetime.fromtimestamp(int(time.time()),
  49. pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
  50. # time_now = time.strftime("%H:%M:%S", time.localtime()) # 刷新
  51. am_set_time = "05:00:00"
  52. if time_now == am_set_time: # 此处设置每天定时的时间
  53. #钉钉推送消息
  54. messages = []
  55. #存储zj_fc表的空间名称
  56. FC_NAMELIST_DICT = {}
  57. #存储space表的空间名称
  58. SPACE_NAMELIST_DICT={}
  59. # 连接mysql
  60. conn = pymysql.connect(**mysql)
  61. mysql_cur = conn.cursor()
  62. # 获取建筑列表
  63. mysql_cur.execute(SELECT_BUILDING_SQL)
  64. buildinglist = mysql_cur.fetchall()
  65. #获取需要对应的点位列表
  66. mysql_cur.execute(SELECT_STATE_SQL)
  67. roomid_list = mysql_cur.fetchall()
  68. roomid_list = [i[0] for i in roomid_list]
  69. # 获取zj_fc空间名称
  70. print("%s 获取zj_fc空间名称..."%datetime_now())
  71. mysql_cur.execute(SELECT_FC_SQL)
  72. fc_namelist = mysql_cur.fetchall()
  73. for building in buildinglist:
  74. if building[0] and building[1] != None:
  75. for fc_name in fc_namelist:
  76. #判断空间是否需要对比
  77. if fc_name[2] in roomid_list:
  78. if fc_name[0] == building[0]:
  79. FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = building[0]+"_"+fc_name[1]
  80. # FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = 1
  81. else:
  82. message = (" - **类型:** 建筑关系未对应 \n" + " - **建筑ID:** <font color=#FF0000>%s(%s)</font> \n")% (building[0],building[1])
  83. print(message)
  84. messages.append(message)
  85. messages.append("___")
  86. # 获取space空间名称
  87. print("%s 获取space空间名称..."%datetime_now())
  88. mysql_cur.execute(SELECT_SPACE_SQL)
  89. space_namelist = mysql_cur.fetchall()
  90. for space in space_namelist:
  91. SPACE_NAMELIST_DICT[space[0]+"_"+space[1]] = 1
  92. # print(FC_NAMELIST_DICT)
  93. # print(SPACE_NAMELIST_DICT)
  94. #对比空间名称,取出差异数据生成sql语句
  95. sqls = []
  96. print("%s 开始对比差异数据..."%datetime_now())
  97. diff_namelist = set(FC_NAMELIST_DICT) - set(SPACE_NAMELIST_DICT)
  98. if diff_namelist != set():
  99. for diff_name in diff_namelist:
  100. diff_building_rooms = FC_NAMELIST_DICT[diff_name]
  101. diff_building_id = str(diff_building_rooms).split("_")[0]
  102. diff_room_name = str(diff_building_rooms).split("_")[1]
  103. mysql_cur.execute(SELECT_DIFF_FC_SQL%(diff_building_id,diff_room_name))
  104. diff_data = mysql_cur.fetchall()[0]
  105. building_name = diff_data[0]
  106. floor_id = diff_data[1]
  107. floor_name = diff_data[2]
  108. state = diff_data[3]
  109. type = diff_data[4]
  110. 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())
  111. sqls.append(sqlline)
  112. # message = (" - **building_id:** %s \n" + " - **building_name:** %s \n" + " - **room_name:** %s \n" + " - **type:** %s \n")%(diff_building_id, building_name,diff_room_name,type)
  113. # messages.append(message)
  114. # messages.append("___")
  115. print("%s 差异数据共计%s条..."%(datetime_now(),len(diff_namelist)))
  116. message = (" - **类型:** 空间名称未对应 \n" + " - **数量:** <font color=#FF0000>%s</font> \n") % (len(diff_namelist))
  117. messages.append(message)
  118. messages.append("___")
  119. # 获取space空间名称
  120. print("%s 清空MySQL表数据..."%datetime_now())
  121. mysql_cur.execute(TRUNCATE_BUILDING_ROOMS_SQL)
  122. conn.commit()
  123. #往MySQL插入数据
  124. insert_mysql(sqls)
  125. #关闭数据库
  126. mysql_cur.close()
  127. conn.close()
  128. if messages:
  129. print("%s 发送钉钉报警"%datetime_now())
  130. messages.append("<font color=#0080FF>详情查看MySQL数据库sagacloud_setup里的zj_sync_building_rooms表</font>")
  131. messages_info = "\n".join(messages)
  132. # 如果有报警,发送钉钉
  133. title = "## **【之江实验室】空间名称报警** "
  134. messages_info = title + "\n" +"___"+"\n"+ messages_info
  135. send_message_markdown(title, messages_info, dingding)