zjsys_export_spaceequipment/main.py

import json
from MyUtils.MysqlUtils import MysqlUtils
import datetime
import time
import pymysql
import pytz

SELECT_BUILDINGNAME_SQL = "SELECT local_name from object_data_building where id='%s' "
SELECT_FLOORNAME_SQL = "SELECT local_name from object_data_floor where id='%s'"
SELECT_SPACENAME_SQL = "SELECT local_name,floor_id,building_id from object_data_space where id = '%s'"
SELECT_EQUIPMENTID_SQL = "SELECT from_id,to_id FROM object_data_relation where graph_id ='GtEquipforSpace001'"
SELECT_EQUIPMENTNAME_SQL = "SELECT id,category,infos from object_data_equipment_infos where id = '%s'"
SELECT_EQUIPNAME_SQL = "SELECT equip_name from object_data_category where equip_code = '%s'"
DELETE_SQL = "delete from sagacloud_test.export_space_equipment"
INSERT_SQL = "replace into sagacloud_test.export_space_equipment(buildingname,floor_name,space_name,equip_code,equip_name,equipment_id,local_name,equipment_point,meter,funcid) VALUES "


equipment_points = {"FanGearNum","RunStatus","WaterValveSwitchStatus","ReturnAirTemp","WorkMode","FanGearSet","EquipSwitchSet","infoPointCode","WorkModeSet","ctm-info004","ctm-info003","AirSpeedGear","ModeSet","TempSet","AirSpeedGearSet","CoolCoilAirTemp","SupplyAirTempSet","ReturnAirTempSet","SupplyAirTemp","ctm-SeasonModeSe","FreshAirTemp","ctm-SeasonModeSet","LampGearSet","isPassengerPass"}

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"]

EquipforSpace_DICK = {}
# #连接mysql
MysqlUtil = MysqlUtils(**mysql)

equipment_id_list= MysqlUtil.query(SELECT_EQUIPMENTID_SQL)
for equipment_id in equipment_id_list:
    equip_id,space_id = equipment_id[0],equipment_id[1]
    EquipforSpace_DICK[equip_id] = space_id

sqls = []
for equip_id in EquipforSpace_DICK:
    print(equip_id)
    if len(MysqlUtil.query(SELECT_SPACENAME_SQL % (EquipforSpace_DICK[equip_id]))) == 1:
        space_name,floor_id,building_id = MysqlUtil.query(SELECT_SPACENAME_SQL % (EquipforSpace_DICK[equip_id]))[0]
        print(space_name,floor_id,building_id)
        building_name = MysqlUtil.query(SELECT_BUILDINGNAME_SQL % (building_id))[0][0]
        if floor_id is not None:
            floor_name = MysqlUtil.query(SELECT_FLOORNAME_SQL % (floor_id))[0][0]
            # SPACE_DICK[id] = [building_name,floor_name,space_name]
        else:
            floor_name = "NULL"
            # SPACE_DICK[id] = [building_name, floor_name, space_name]

        equipmentinfos = MysqlUtil.query(SELECT_EQUIPMENTNAME_SQL % (equip_id))
        if equipmentinfos:
            equipm_id,category,equipment_infos = equipmentinfos[0]
            equipment_name = MysqlUtil.query(SELECT_EQUIPNAME_SQL % (category))[0][0]
            equipment_infos = json.loads(equipment_infos)
            for equipment_point in equipment_infos:
                if "EquipLocalName" in equipment_infos:
                    local_name = equipment_infos["EquipLocalName"]
                else:
                    local_name = "NUll"
                if equipment_point in equipment_points:
                    print(equipm_id,equipment_point,equipment_infos[equipment_point])
                    infos = str(equipment_infos[equipment_point])
                    if "-" in infos:
                        meter = infos.split("-")[0]
                        funcid = infos.split("-")[1]
                    else:
                        meter = infos
                        funcid = "NUll"
                    print(building_name,floor_name,space_name,category,equipment_name,equipm_id,local_name,equipment_point,meter,funcid)
                    sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(building_name,floor_name,space_name,category,equipment_name,equipm_id,local_name,equipment_point,meter,funcid)
                    sqls.append(sql)

        else:
            category = "NUll"
            equipment_name = "NUll"
            equipm_id = "NUll"
            local_name = "NUll"
            equipment_point = "NUll"
            meter = "NUll"
            funcid = "NUll"
            print(building_name, floor_name, space_name, category, equipment_name, equipm_id, local_name, equipment_point,
                  meter, funcid)
            sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
            building_name, floor_name, space_name, category, equipment_name, equipm_id, local_name, equipment_point,
            meter, funcid)
            sqls.append(sql)


    else:
        with open("log.log","w") as f:
            f.write(equip_id)


if sqls:
    print("%s 清空数据库表"%datetime_now())
    MysqlUtil.update(DELETE_SQL)
    print("%s 数据库表清空完成"%datetime_now())
    print("%s 开始往数据库插入数据,共计%s条"%(datetime_now(),len(sqls)))
    for i in range(0,len(sqls),1000):
        sqlranges = sqls[i:i + 1000]
        sqlranges = INSERT_SQL + ",".join(sqlranges)
        MysqlUtil.update(sqlranges)
    print("%s 数据库插入成功"%datetime_now())
MysqlUtil.close()