start.py 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128
  1. import json
  2. from MyUtils.Dingtalk import send_message
  3. from MyUtils.Dingtalk import send_message_markdown
  4. import datetime
  5. import time
  6. import pymysql
  7. import pytz
  8. SELECT_BUILDING_SQL = "SELECT zj_building_id,sg_building_id FROM sagacloud_setup.zj_sync_config_building"
  9. SELECT_FC_SQL = "SELECT building_id,room_name from zj_fc.t_building_rooms"
  10. SELECT_SPACE_SQL = "SELECT building_id,local_name FROM sagacloud_object.object_data_space"
  11. TRUNCATE_BUILDING_ROOMS_SQL = "truncate table sagacloud_setup.zj_sync_building_rooms"
  12. 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"
  13. 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'"
  14. def datetime_now():
  15. # datetime_now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
  16. #容器时间
  17. # tz = pytz.timezone('Asia/Shanghai') # 东八区
  18. datetime_now = datetime.datetime.fromtimestamp(int(time.time()),
  19. pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
  20. return datetime_now
  21. # 读取配置文件信息
  22. with open("config.json", "r") as f:
  23. data = json.load(f)
  24. mysql = data["mysql"]
  25. database = mysql["database"]
  26. dingding = data["dingding"]
  27. # mysql插入数据
  28. def insert_mysql(sqls):
  29. print("开始往mysql插入数据...")
  30. for i in range(0, len(sqls), 1000):
  31. sqlranges = sqls[i:i + 1000]
  32. sqlranges = INSERT_BUILDING_ROOMS_SQL + ",".join(sqlranges)
  33. mysql_cur.execute(sqlranges)
  34. conn.commit()
  35. print("mysql数据插入成功,合计%s条..." % (len(sqls)))
  36. #钉钉推送消息
  37. messages = []
  38. #存储zj_fc表的空间名称
  39. FC_NAMELIST_DICT = {}
  40. #存储space表的空间名称
  41. SPACE_NAMELIST_DICT={}
  42. # 连接mysql
  43. conn = pymysql.connect(**mysql)
  44. mysql_cur = conn.cursor()
  45. # 获取建筑列表
  46. mysql_cur.execute(SELECT_BUILDING_SQL)
  47. buildinglist = mysql_cur.fetchall()
  48. # 获取zj_fc空间名称
  49. mysql_cur.execute(SELECT_FC_SQL)
  50. fc_namelist = mysql_cur.fetchall()
  51. for building in buildinglist:
  52. if building[0] and building[1] != None:
  53. for fc_name in fc_namelist:
  54. if fc_name[0] == building[0]:
  55. FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = building[0]+"_"+fc_name[1]
  56. # FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = 1
  57. else:
  58. message = (" - **存在建筑对应关系为空:** %s(%s) \n") % (building[0],building[1])
  59. print(message)
  60. messages.append(message)
  61. messages.append("___")
  62. # 获取space空间名称
  63. mysql_cur.execute(SELECT_SPACE_SQL)
  64. space_namelist = mysql_cur.fetchall()
  65. for space in space_namelist:
  66. SPACE_NAMELIST_DICT[space[0]+"_"+space[1]] = 1
  67. # print(FC_NAMELIST_DICT)
  68. # print(SPACE_NAMELIST_DICT)
  69. #对比空间名称,取出差异数据生成sql语句
  70. sqls = []
  71. diff_namelist = set(FC_NAMELIST_DICT) - set(SPACE_NAMELIST_DICT)
  72. if diff_namelist != set():
  73. for diff_name in diff_namelist:
  74. diff_building_rooms = FC_NAMELIST_DICT[diff_name]
  75. diff_building_id = str(diff_building_rooms).split("_")[0]
  76. diff_room_name = str(diff_building_rooms).split("_")[1]
  77. mysql_cur.execute(SELECT_DIFF_FC_SQL%(diff_building_id,diff_room_name))
  78. diff_data = mysql_cur.fetchall()[0]
  79. building_name = diff_data[0]
  80. floor_id = diff_data[1]
  81. floor_name = diff_data[2]
  82. state = diff_data[3]
  83. type = diff_data[4]
  84. print(building_name,diff_building_id,diff_room_name,floor_id,floor_name,state,type)
  85. 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())
  86. sqls.append(sqlline)
  87. # 获取space空间名称
  88. print("清空MySQL表数据...")
  89. mysql_cur.execute(TRUNCATE_BUILDING_ROOMS_SQL)
  90. conn.commit()
  91. #往MySQL插入数据
  92. insert_mysql(sqls)
  93. # if messages:
  94. # messages_info = "\n".join(messages)
  95. # # 如果有报警,发送钉钉
  96. # title = "## 【之江实验室】空间名称报警 "
  97. # messages_info = title + "\n" + messages_info
  98. # print(messages_info)
  99. # send_message_markdown(title, messages_info, dingding)
  100. mysql_cur.close()
  101. conn.close()