import datetime import json,pymysql import os import time from MyUtils.MysqlUtils import MysqlUtils from MyUtils.Dingtalk import send_message from MyUtils.DmprwdUtil import get_query_data import pytz INSERT_SQL = "replace into %s.%s(project_id,date,energy_cooling,energy_heating,energy_ac_terminal,energy_light,energy_others,create_time,update_time) values " DELETE_SQL = "DELETE FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s' " SELETE_COUNTLASTDATA_SQL = "SELECT count(*) FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s'" SELETE_SUMLASTDATA_SQL = "SELECT SUM(energy_ac_terminal)+SUM(energy_heating)+SUM(energy_cooling)+SUM(energy_light)+sum(energy_others) as last_data FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s'" with open("config.json", "r") as f: data = json.load(f) mysql = data["mysql"] my_database = mysql["database"] dingding = data["dingding"] at_mobiles = data["at_mobiles"] 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 building = "1101080259" def transfer_energy(): print("同步%s项目数据" % (building)) while True: project_id = "Pj" + building time_now = datetime.datetime.fromtimestamp(int(time.time()), pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S') am_set_time = "08:00:00" pm_set_time = "22:00:00" if time_now == am_set_time or time_now ==pm_set_time:#此处设置每天定时的时间 today = datetime.date.today().strftime("%Y%m%d")+"000000" yesterday = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d")+"000000" today_date = datetime.date.today().strftime("%Y%m%d") yesterday_date = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d") # #连接mysql MysqlUtil = MysqlUtils(**mysql) #获取能耗数据 datas = get_query_data(yesterday,today) if datas: # 删除昨天数据 print("%s,开始删除%s的数据..." % (datetime_now(), yesterday)) delete_sql = DELETE_SQL % (project_id, yesterday_date, today_date) MysqlUtil.update(delete_sql) objectids = [i["objectId"] for i in datas] print(objectids) energy_cooling = "0" energy_heating = "0" energy_ac_terminal = "0" energy_light = "0" sum_data_value = "0" for i in datas: print(i) #总电耗 if i["objectId"] == "Vo1101080259f2481ca3604644399f1dacb84e20adae": sum_data_value = i["ipValue"] #冷热源 if i["objectId"] == "Vo1101080259e6dcf338d9be4bbf826f659b0b5a9ab2": energy_cooling = i["ipValue"] #空调末端 if i["objectId"] == "Vo11010802590eaef68d3289452d86d89fbee721e6df": energy_ac_terminal = i["ipValue"] #照明 if i["objectId"] == "Vo110108025953894df8d4ae4dbeb7c81ced7df3f83e": energy_light = i["ipValue"] energy_other = float(sum_data_value) - float(energy_cooling) - float(energy_heating)- float(energy_light) - float(energy_ac_terminal) sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % ( project_id, yesterday_date, energy_cooling, energy_heating, energy_ac_terminal, energy_light, energy_other, datetime_now(), datetime_now()) inser_sql = INSERT_SQL % (my_database, "energy_week_day") + sql print("%s,开始插入数据..." % datetime_now()) MysqlUtil.update(inser_sql) else: print("%s,没有查询到数据...") MysqlUtil.close() if __name__ == '__main__': transfer_energy()