create_table.py 7.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136
  1. import datetime
  2. from dateutil.relativedelta import relativedelta
  3. from service import plt_service
  4. from service.common_connect import trans
  5. base_scada_create_sql = """
  6. CREATE TABLE
  7. IF NOT EXISTS `{table_name}` (
  8. `wind_turbine_number` VARCHAR (20) DEFAULT NULL COMMENT '风机编号',
  9. `wind_turbine_name` VARCHAR(20) DEFAULT NULL COMMENT '风机原始名称',
  10. `time_stamp` datetime NOT NULL COMMENT '时间戳',
  11. `active_power` DOUBLE DEFAULT NULL COMMENT '有功功率',
  12. `rotor_speed` DOUBLE DEFAULT NULL COMMENT '风轮转速',
  13. `generator_speed` DOUBLE DEFAULT NULL COMMENT '发电机转速',
  14. `wind_velocity` DOUBLE DEFAULT NULL COMMENT '风速',
  15. `pitch_angle_blade_1` DOUBLE DEFAULT NULL COMMENT '桨距角1',
  16. `pitch_angle_blade_2` DOUBLE DEFAULT NULL COMMENT '桨距角2',
  17. `pitch_angle_blade_3` DOUBLE DEFAULT NULL COMMENT '桨距角3',
  18. `cabin_position` DOUBLE DEFAULT NULL COMMENT '机舱位置',
  19. `true_wind_direction` DOUBLE DEFAULT NULL COMMENT '绝对风向',
  20. `yaw_error1` DOUBLE DEFAULT NULL COMMENT '对风角度',
  21. `set_value_of_active_power` DOUBLE DEFAULT NULL COMMENT '有功功率设定值',
  22. `gearbox_oil_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱油温',
  23. `generatordrive_end_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '发电机驱动端轴承温度',
  24. `generatornon_drive_end_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '发电机非驱动端轴承温度',
  25. `cabin_temperature` DOUBLE DEFAULT NULL COMMENT '机舱内温度',
  26. `twisted_cable_angle` DOUBLE DEFAULT NULL COMMENT '扭缆角度',
  27. `front_back_vibration_of_the_cabin` DOUBLE DEFAULT NULL COMMENT '机舱前后振动',
  28. `side_to_side_vibration_of_the_cabin` DOUBLE DEFAULT NULL COMMENT '机舱左右振动',
  29. `actual_torque` DOUBLE DEFAULT NULL COMMENT '实际力矩',
  30. `given_torque` DOUBLE DEFAULT NULL COMMENT '给定力矩',
  31. `clockwise_yaw_count` DOUBLE DEFAULT NULL COMMENT '顺时针偏航次数',
  32. `counterclockwise_yaw_count` DOUBLE DEFAULT NULL COMMENT '逆时针偏航次数',
  33. `unusable` DOUBLE DEFAULT NULL COMMENT '不可利用',
  34. `power_curve_available` DOUBLE DEFAULT NULL COMMENT '功率曲线可用',
  35. `required_gearbox_speed` DOUBLE DEFAULT NULL COMMENT '齿轮箱转速',
  36. `inverter_speed_master_control` DOUBLE DEFAULT NULL COMMENT '变频器转速(主控)',
  37. `outside_cabin_temperature` DOUBLE DEFAULT NULL COMMENT '环境温度',
  38. `main_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '主轴承轴承温度',
  39. `gearbox_high_speed_shaft_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱高速轴轴承温度',
  40. `gearboxmedium_speed_shaftbearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱中速轴轴承温度',
  41. `gearbox_low_speed_shaft_bearing_temperature` DOUBLE DEFAULT NULL COMMENT '齿轮箱低速轴轴承温度',
  42. `generator_winding1_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组1温度',
  43. `generator_winding2_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组2温度',
  44. `generator_winding3_temperature` DOUBLE DEFAULT NULL COMMENT '发电机绕组3温度',
  45. `wind_turbine_status` DOUBLE DEFAULT NULL COMMENT '风机状态1',
  46. `wind_turbine_status2` DOUBLE DEFAULT NULL COMMENT '风机状态2',
  47. `turbulence_intensity` DOUBLE DEFAULT NULL COMMENT '湍流强度',
  48. `lab` int DEFAULT NULL COMMENT '-1:停机 0:好点 1:欠发功率点;2:超发功率点;3:额定风速以上的超发功率点 4: 限电',
  49. `year` INT (4) DEFAULT NULL COMMENT '年',
  50. `month` INT (2) DEFAULT NULL COMMENT '月',
  51. `day` INT (2) DEFAULT NULL COMMENT '日',
  52. `year_month` int(6) DEFAULT NULL COMMENT '年-月',
  53. KEY `time_stamp` (`time_stamp`),
  54. KEY `wind_turbine_number` (`wind_turbine_number`)
  55. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{wind_name}{trans_type}数据表'
  56. """
  57. base_warn_fault_sql = """
  58. CREATE TABLE
  59. IF NOT EXISTS `{table_name}` (
  60. `wind_turbine_number` varchar(20) DEFAULT NULL COMMENT '风机编号',
  61. `wind_turbine_name` varchar(20) DEFAULT NULL COMMENT '原始风机编号',
  62. `mc_version` varchar(50) DEFAULT NULL COMMENT '主控版本号',
  63. `seq_no` int default NULL comment '实时数据顺序号',
  64. `begin_time` datetime DEFAULT NULL COMMENT '开始时间',
  65. `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  66. `time_diff` int DEFAULT NULL COMMENT '处理耗时,单位秒',
  67. `fault_id` varchar(20) DEFAULT NULL COMMENT '报警或者故障ID',
  68. `fault_code` varchar(50) DEFAULT NULL COMMENT '报警或者故障CODE',
  69. `fault_detail` varchar(255) DEFAULT NULL COMMENT '错误描述',
  70. `fault_level` varchar(20) DEFAULT NULL COMMENT '报警等级',
  71. `fault_type` varchar(20) DEFAULT NULL COMMENT '报警类型',
  72. `stop_status` varchar(20) DEFAULT NULL COMMENT '刹车状态',
  73. KEY `wind_turbine_number` (`wind_turbine_number`),
  74. KEY `seq_no` (`seq_no`),
  75. KEY `begin_time` (`begin_time`),
  76. KEY `end_time` (`end_time`)
  77. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{wind_name}{trans_type}数据表'
  78. """
  79. def creat_scada_table(wind_code, wind_name):
  80. types = ['minute', 'second']
  81. for trans_type in types:
  82. table_name = f'{wind_code}_{trans_type}'
  83. create_sql = base_scada_create_sql.format_map(
  84. {'table_name': table_name, 'wind_name': wind_name, 'trans_type': trans_type})
  85. create_sql = create_sql + ' PARTITION BY RANGE COLUMNS(time_stamp) ( \n'
  86. now_month = datetime.datetime.today()
  87. for i in range(1, 3):
  88. next_month = now_month + relativedelta(months=1)
  89. create_sql = create_sql + (f" PARTITION p{str(now_month.year) + str(now_month.month).zfill(2)} "
  90. f"VALUES LESS THAN ('{str(next_month.year)}-{str(next_month.month).zfill(2)}-01'),\n")
  91. now_month = next_month
  92. create_sql = create_sql + " PARTITION pmax VALUES LESS THAN (MAXVALUE)\n );"
  93. trans.execute(create_sql)
  94. def create_warn_fault_table(wind_code, wind_name):
  95. types = ['warn', 'fault']
  96. for trans_type in types:
  97. table_name = f'{wind_code}_{trans_type}'
  98. create_sql = base_warn_fault_sql.format_map(
  99. {'table_name': table_name, 'wind_name': wind_name, 'trans_type': trans_type})
  100. create_sql = create_sql + ' PARTITION BY RANGE COLUMNS(begin_time) ( \n'
  101. now_month = datetime.datetime.today()
  102. for i in range(1, 3):
  103. next_month = now_month + relativedelta(months=1)
  104. create_sql = create_sql + (f" PARTITION p{str(now_month.year) + str(now_month.month).zfill(2)} "
  105. f"VALUES LESS THAN ('{str(next_month.year)}-{str(next_month.month).zfill(2)}-01'),\n")
  106. now_month = next_month
  107. create_sql = create_sql + " PARTITION pmax VALUES LESS THAN (MAXVALUE)\n );"
  108. trans.execute(create_sql)
  109. if __name__ == '__main__':
  110. datas = plt_service.get_all_wind()
  111. for data in datas:
  112. create_warn_fault_table(data['code_number'], data['code_name'])
  113. creat_scada_table(data['code_number'], data['code_name'])