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'])