import datetime import json import time from MyUtils.MysqlUtils import MysqlUtils 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"] buildings = data["buildings"] 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 print("%s 等待下次执行..."%datetime_now()) while True: 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) #循环项目查询 for building in buildings: project_id = "Pj"+building["building"] groupcode = building["groupcode"] energyid = building["energyid"] rwdid = building["rwdid"] energy_total = rwdid["energy_total"] energy_light = rwdid["energy_light"] energy_ac_termial = rwdid["energy_ac_termial"] energy_cooling = rwdid["energy_cooling"] energy_heating = rwdid["energy_heating"] print("%s 开始查询%s项目数据"%(datetime_now(),project_id)) # 获取能耗数据 datas = get_query_data(yesterday, today,groupcode, project_id,energy_total,energy_light,energy_cooling,energy_heating,energy_ac_termial) if datas: # 删除昨天数据 print("%s,开始删除%s项目%s的数据..." % (datetime_now(),project_id, yesterday)) delete_sql = DELETE_SQL % (project_id, yesterday_date, today_date) MysqlUtil.update(delete_sql) objectids = [i["objectId"] for i in datas] print(objectids) sum_data_value_data = "0" energy_heating_data = "0" energy_ac_terminal_data = "0" energy_light_data = "0" energy_cooling_data = "0" for i in datas: print(i) # 总电耗 if i["objectId"] == energy_total: sum_data_value_data = i["ipValue"] # 冷 if i["objectId"] == energy_cooling: energy_cooling_data = i["ipValue"] # 热 if i["objectId"] == energy_heating: energy_heating_data = i["ipValue"] # 空调末端 if i["objectId"] == energy_ac_termial: energy_ac_terminal_data = i["ipValue"] # 照明 if i["objectId"] == energy_light: energy_light_data = i["ipValue"] energy_other = float(sum_data_value_data) - float(energy_cooling_data) - float(energy_heating_data) - float(energy_light_data) - float( energy_ac_terminal_data) sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % ( project_id, yesterday_date, energy_cooling_data, energy_heating_data, energy_ac_terminal_data, energy_light_data, energy_other, datetime_now(), datetime_now()) inser_sql = INSERT_SQL % (my_database, "energy_week_day") + sql print("%s,开始插入%s项目%s数据..." % (datetime_now(),project_id,yesterday)) MysqlUtil.update(inser_sql) else: print("%s,没有查询到%s项目%s的数据..."%(datetime_now(),project_id,yesterday)) MysqlUtil.close() print("%s 完成%s的数据插入"%(datetime_now(),yesterday)) time.sleep(2)