second_data.py 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166
  1. import pandas as pd
  2. from sqlalchemy import create_engine
  3. import datetime
  4. engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/appoint')
  5. mesurepoint_conf = {
  6. 0: '网侧L1相功率因数',
  7. 1: '网侧L1相电流',
  8. 2: '网侧L2相电流',
  9. 3: '网侧L3相电流',
  10. 4: '变频器水冷入口压力',
  11. 5: '变频器水冷出口压力',
  12. 6: '变频器水冷入口温度',
  13. 7: '变频器水冷出口温度',
  14. 8: '电网频率',
  15. 9: '网侧L1相电压',
  16. 10: '网侧L2相电压',
  17. 11: '网侧L3相电压',
  18. 12: '外测有功功率',
  19. 13: '发电机进风口前部温度',
  20. 14: '发电机压差传感器',
  21. 15: '发电机进风口后部温度',
  22. 16: '发电机出风口温度',
  23. 17: '风机有功功率',
  24. 18: '发电机水冷入口压力',
  25. 19: '发电机水冷出口压力',
  26. 20: '发电机无功功率',
  27. 21: '发电机转速',
  28. 22: '发电机水冷入口温度',
  29. 23: '发电机水冷出口温度',
  30. 24: '发电机驱动端轴承温度',
  31. 25: '发电机非驱动端轴承温度',
  32. 26: '发电机滑环室温度',
  33. 27: '发电机定子U相线圈温度',
  34. 28: '发电机定子V相线圈温度',
  35. 29: '发电机定子W相线圈温度',
  36. 30: '机舱水冷齿轮箱冷却液温度1min',
  37. 31: '机舱水冷发电机冷却液温度1min',
  38. 32: '机舱冷却系统水泵入口压力',
  39. 33: '机舱水冷泵出口压力1min',
  40. 34: '塔底水冷变频器入口水温',
  41. 35: '塔底水冷变频器出口水温',
  42. 36: '塔底湿度',
  43. 37: '塔底水冷三通阀设置实际值',
  44. 38: '塔底水冷三通阀反馈值',
  45. 39: '塔外湿度',
  46. 40: '塔底水冷泵入口压力',
  47. 41: '塔底水冷泵出口压力',
  48. 42: '舱内温度',
  49. 43: '机舱控制柜温度',
  50. 44: '舱外温度',
  51. 45: '塔底温度',
  52. 46: '塔底控制柜温度',
  53. 47: '风向',
  54. 48: '风速',
  55. 49: '机舱中轴线与风向夹角',
  56. 50: '变桨电池1电压',
  57. 51: '变桨电池2电压',
  58. 52: '变桨电池3电压',
  59. 53: '变桨电机1电流',
  60. 54: '变桨电机2电流',
  61. 55: '变桨电机3电流',
  62. 56: '1#桨电机温度',
  63. 57: '2#桨电机温度',
  64. 58: '3#桨电机温度',
  65. 59: '变桨驱动器1温度',
  66. 60: '变桨驱动器2温度',
  67. 61: '变桨驱动器3温度',
  68. 62: '轮毂内温度',
  69. 63: '变桨电池柜1温度',
  70. 64: '变桨电池柜2温度',
  71. 65: '变桨电池柜3温度',
  72. 66: '动力电缆温度监控1',
  73. 67: '动力电缆温度监控2',
  74. 68: '辅变绕组温度',
  75. 69: '主变绕组温度',
  76. 70: '齿轮箱入口油压',
  77. 71: '齿轮箱油路滤网前油压',
  78. 72: '液压站预充压力',
  79. 73: '主轴转速',
  80. 74: '齿轮箱油路入口温度',
  81. 75: '齿轮箱散热器出口温度',
  82. 76: '齿轮箱高速轴HSS_GSGS端轴承温度',
  83. 77: '齿轮箱高速轴HSS_GSRS端轴承温度',
  84. 78: '齿轮箱中速轴HSS_RS端轴承温度',
  85. 79: '齿轮箱中速轴IMS_GSRS端轴承温度',
  86. 80: '齿轮箱高速轴驱动端轴承温度',
  87. 81: '齿轮箱高速轴非驱动端轴承温度',
  88. 82: '齿轮箱油池温度',
  89. 83: '齿轮箱中速轴驱动端轴承温度',
  90. 84: '齿轮箱中速轴非驱动端轴承温度',
  91. 85: '主轴承温度',
  92. 86: '主轴承内圈温度',
  93. 87: '主轴承外圈温度',
  94. 88: '主控有功设置值',
  95. 89: '主控无功设置值',
  96. 90: '主控次要SC',
  97. 91: '主控有功限值上限',
  98. 92: '对外AI型状态1',
  99. 93: '机舱侧向振动(已滤波)',
  100. 94: '机舱轴向振动(已滤波)',
  101. 95: '后方摩擦片距离',
  102. 96: '前方摩擦片距离',
  103. 97: '机舱角度',
  104. 98: '机舱电流',
  105. 99: '扭揽角度',
  106. 100: '偏航电机转速1',
  107. 101: '偏航电机转速2',
  108. 102: '偏航角度'
  109. }
  110. common_address_conf = {
  111. 1: '风场1',
  112. 2: '风场2',
  113. 3: '风场3',
  114. 4: '风场4',
  115. 5: '风场5',
  116. 6: '风场6',
  117. 7: '风场7',
  118. 8: '风场8',
  119. 9: '风场9',
  120. 10: '风场10',
  121. 11: '风场11',
  122. 12: '风场12'
  123. }
  124. def pretty_print(message):
  125. print(datetime.datetime.now(), ":", message)
  126. def exists_table(table_name):
  127. sql = f"SELECT * FROM information_schema.tables WHERE table_schema = 'appoint' AND table_name = '{table_name}'"
  128. pretty_print(sql)
  129. table_df = pd.read_sql_query(sql, engine)
  130. if table_df.empty:
  131. return False
  132. return True
  133. if __name__ == '__main__':
  134. begin = datetime.datetime.now()
  135. pretty_print("开始执行")
  136. today_year_month = datetime.datetime.now().strftime('%Y%m')
  137. today = datetime.datetime.now().strftime('%d')
  138. yestoday = (datetime.datetime.now() - datetime.timedelta(days=1)).strftime('%Y%m%d')
  139. lastday = (datetime.datetime.now() - datetime.timedelta(days=2)).strftime('%Y%m%d')
  140. lastday_table = f'{lastday}_measurement'
  141. lastday_df = pd.DataFrame()
  142. if not exists_table(lastday_table):
  143. pretty_print(f"{lastday_table}表不存在")
  144. else:
  145. lastday_df_sql = f"SELECT * FROM `{lastday_table}` order by id desc limit 10"
  146. pretty_print(lastday_df_sql)
  147. lastday_df = pd.read_sql_query(lastday_df_sql, engine)
  148. yestoday_table = f'{yestoday}_measurement'
  149. if not exists_table(yestoday_table):
  150. pretty_print(f"{yestoday_table}表不存在")
  151. raise Exception(f"{yestoday_table}表不存在")
  152. else:
  153. yestoday_df_sql = f"SELECT * FROM `{yestoday_table}`"
  154. pretty_print(yestoday_df_sql)
  155. yestoday_df = pd.read_sql_query(yestoday_df_sql, engine)