CGN_taiyuan_104/create_table.py

import datetime

from dateutil.relativedelta import relativedelta

from service import plt_service
from service.common_connect import trans

base_scada_create_sql = """
        CREATE TABLE
        IF NOT EXISTS `{table_name}` (
            `wind_turbine_number` VARCHAR (20) DEFAULT NULL COMMENT '风机编号',
            `wind_turbine_name` VARCHAR(20) DEFAULT NULL COMMENT '风机原始名称',
            `time_stamp` datetime NOT NULL COMMENT '时间戳',
            `active_power` DOUBLE DEFAULT NULL COMMENT '有功功率',
            `rotor_speed` DOUBLE DEFAULT NULL COMMENT '风轮转速',
            `generator_speed` DOUBLE DEFAULT NULL COMMENT '发电机转速',
            `wind_velocity` DOUBLE DEFAULT NULL COMMENT '风速',
            `pitch_angle_blade_1` DOUBLE DEFAULT NULL COMMENT '桨距角1',
            `pitch_angle_blade_2` DOUBLE DEFAULT NULL COMMENT '桨距角2',
            `pitch_angle_blade_3` DOUBLE DEFAULT NULL COMMENT '桨距角3',
            `cabin_position` DOUBLE DEFAULT NULL COMMENT '机舱位置',
            `true_wind_direction` DOUBLE DEFAULT NULL COMMENT '绝对风向',
            `yaw_error1` DOUBLE DEFAULT NULL COMMENT '对风角度',
            `set_value_of_active_power` DOUBLE DEFAULT NULL COMMENT '有功功率设定值',
            `gearbox_oil_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱油温',
            `generatordrive_end_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '发电机驱动端轴承温度',
            `generatornon_drive_end_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '发电机非驱动端轴承温度',
            `cabin_temperature` DOUBLE DEFAULT NULL COMMENT '机舱内温度',
            `twisted_cable_angle` DOUBLE DEFAULT NULL COMMENT '扭缆角度',
            `front_back_vibration_of_the_cabin` DOUBLE DEFAULT NULL COMMENT '机舱前后振动',
            `side_to_side_vibration_of_the_cabin` DOUBLE DEFAULT NULL COMMENT '机舱左右振动',
            `actual_torque` DOUBLE DEFAULT NULL COMMENT '实际力矩',
            `given_torque` DOUBLE DEFAULT NULL COMMENT '给定力矩',
            `clockwise_yaw_count` DOUBLE DEFAULT NULL COMMENT '顺时针偏航次数',
            `counterclockwise_yaw_count` DOUBLE DEFAULT NULL COMMENT '逆时针偏航次数',
            `unusable` DOUBLE DEFAULT NULL COMMENT '不可利用',
            `power_curve_available` DOUBLE DEFAULT NULL COMMENT '功率曲线可用',
            `required_gearbox_speed` DOUBLE DEFAULT NULL COMMENT '齿轮箱转速',
            `inverter_speed_master_control` DOUBLE DEFAULT NULL COMMENT '变频器转速(主控)',
            `outside_cabin_temperature` DOUBLE DEFAULT NULL COMMENT '环境温度',
            `main_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '主轴承轴承温度',
            `gearbox_high_speed_shaft_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱高速轴轴承温度',
            `gearboxmedium_speed_shaftbearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱中速轴轴承温度',
            `gearbox_low_speed_shaft_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱低速轴轴承温度',
            `generator_winding1_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组1温度',
            `generator_winding2_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组2温度',
            `generator_winding3_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组3温度',
            `wind_turbine_status` DOUBLE DEFAULT NULL COMMENT '风机状态1',
            `wind_turbine_status2` DOUBLE DEFAULT NULL COMMENT '风机状态2',
            `turbulence_intensity` DOUBLE DEFAULT NULL COMMENT '湍流强度',
            `lab` int DEFAULT NULL COMMENT '-1:停机 0:好点  1:欠发功率点；2:超发功率点；3:额定风速以上的超发功率点 4: 限电',
            `year` INT (4) DEFAULT NULL COMMENT '年',
            `month` INT (2) DEFAULT NULL COMMENT '月',
            `day` INT (2) DEFAULT NULL COMMENT '日',
            `year_month` int(6) DEFAULT NULL COMMENT '年-月',
             KEY `time_stamp` (`time_stamp`),
             KEY `wind_turbine_number` (`wind_turbine_number`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{wind_name}{trans_type}数据表'
        """

base_warn_fault_sql = """
    CREATE TABLE
    IF NOT EXISTS `{table_name}` (
  `wind_turbine_number` varchar(20) DEFAULT NULL COMMENT '风机编号',
  `wind_turbine_name` varchar(20) DEFAULT NULL COMMENT '原始风机编号',
  `mc_version` varchar(50)  DEFAULT NULL COMMENT '主控版本号',
  `seq_no` int default NULL comment '实时数据顺序号',
  `begin_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `time_diff` int DEFAULT NULL COMMENT '处理耗时,单位秒',
  `fault_id` varchar(20) DEFAULT NULL COMMENT '报警或者故障ID',
  `fault_code` varchar(50) DEFAULT NULL COMMENT '报警或者故障CODE',
  `fault_detail` varchar(255) DEFAULT NULL COMMENT '错误描述',
  `fault_level` varchar(20) DEFAULT NULL COMMENT '报警等级',
  `fault_type` varchar(20) DEFAULT NULL COMMENT '报警类型',
  `stop_status` varchar(20) DEFAULT NULL COMMENT '刹车状态',
  KEY `wind_turbine_number` (`wind_turbine_number`),
  KEY  `seq_no` (`seq_no`),
  KEY `begin_time` (`begin_time`),
  KEY `end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{wind_name}{trans_type}数据表'

"""


def creat_scada_table(wind_code, wind_name):
    types = ['minute', 'second']
    for trans_type in types:
        table_name = f'{wind_code}_{trans_type}'

        create_sql = base_scada_create_sql.format_map(
            {'table_name': table_name, 'wind_name': wind_name, 'trans_type': trans_type})

        create_sql = create_sql + ' PARTITION BY RANGE COLUMNS(time_stamp) ( \n'

        now_month = datetime.datetime.today()

        for i in range(1, 3):
            next_month = now_month + relativedelta(months=1)
            create_sql = create_sql + (f" PARTITION p{str(now_month.year) + str(now_month.month).zfill(2)} "
                                       f"VALUES LESS THAN ('{str(next_month.year)}-{str(next_month.month).zfill(2)}-01'),\n")
            now_month = next_month

        create_sql = create_sql + " PARTITION pmax VALUES LESS THAN (MAXVALUE)\n );"

        trans.execute(create_sql)


def create_warn_fault_table(wind_code, wind_name):
    types = ['warn', 'fault']
    for trans_type in types:
        table_name = f'{wind_code}_{trans_type}'
        create_sql = base_warn_fault_sql.format_map(
            {'table_name': table_name, 'wind_name': wind_name, 'trans_type': trans_type})

        create_sql = create_sql + ' PARTITION BY RANGE COLUMNS(begin_time) ( \n'

        now_month = datetime.datetime.today()

        for i in range(1, 3):
            next_month = now_month + relativedelta(months=1)
            create_sql = create_sql + (f" PARTITION p{str(now_month.year) + str(now_month.month).zfill(2)} "
                                       f"VALUES LESS THAN ('{str(next_month.year)}-{str(next_month.month).zfill(2)}-01'),\n")
            now_month = next_month

        create_sql = create_sql + " PARTITION pmax VALUES LESS THAN (MAXVALUE)\n );"

        trans.execute(create_sql)


if __name__ == '__main__':
    datas = plt_service.get_all_wind()

    for data in datas:
        create_warn_fault_table(data['code_number'], data['code_name'])
        creat_scada_table(data['code_number'], data['code_name'])