connect mysql.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266
  1. import numpy as np
  2. from scipy.signal import hilbert
  3. from scipy.fft import ifft
  4. import plotly.graph_objs as go
  5. import pandas as pd
  6. import pymysql
  7. from sqlalchemy import create_engine, text
  8. import math
  9. # # 与志亮的数据库连接
  10. # #1、定义从235数据库中通过“windcode”风场编号和'ids'id号来查找波形数据记录
  11. # def _get_by_id(windcode, ids):
  12. # df_res = []
  13. # engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
  14. # for id in ids:
  15. # table_name = windcode + '_wave'
  16. # lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
  17. # # print(lastday_df_sql)
  18. # df = pd.read_sql(lastday_df_sql, engine)
  19. # df_res.append(df)
  20. # return df_res
  21. # #2、找到波形记录提取特定的列值
  22. # datas = _get_by_id('WOF091200030', [115041])
  23. # datas = [df[['mesure_data','time_stamp','sampling_frequency', 'wind_turbine_number', 'rotational_speed', 'mesure_point_name']] for df in datas]
  24. # data_filter = datas[0]
  25. # rpm_Gen = data_filter['rotational_speed'].iloc[0]
  26. # wind_code = data_filter['wind_turbine_number'].iloc[0]
  27. # mesure_point_name = data_filter['mesure_point_name'].iloc[0]
  28. # fs = int(data_filter['sampling_frequency'].iloc[0])
  29. # print(wind_code)
  30. # print(fs)
  31. # print(rpm_Gen)
  32. # #3、从测点名称中提取部件名称(计算特征频率的部件)
  33. # str1 = mesure_point_name
  34. # str2 = ["main_bearing", "front_main_bearing", "rear_main_bearing", "generator_non_drive_end"]
  35. # for str in str2:
  36. # if str in str1:
  37. # parts = str
  38. # if parts == "front_main_bearing":
  39. # parts = "front_bearing"
  40. # elif parts == "rear_main_bearing":
  41. # parts = "rear_bearing"
  42. # print(parts)
  43. # #4、连接233的数据库'energy_show',从表'wind_engine_group'查找风机编号'engine_code'对应的机型编号'mill_type_code'
  44. # engine_code = wind_code
  45. # Engine2 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  46. # df_sql2 = f"SELECT * FROM {'wind_engine_group'} where engine_code = {'engine_code'} "
  47. # df2 = pd.read_sql(df_sql2, Engine2)
  48. # mill_type_code = df2['mill_type_code'].iloc[0]
  49. # print(mill_type_code)
  50. # #5、从表'unit_bearings'中通过机型编号'mill_type_code'查找部件'brand'、'model'的参数信息
  51. # Engine3 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  52. # df_sql3 = f"SELECT * FROM {'unit_bearings'} where mill_type_code = {'mill_type_code'} "
  53. # df3 = pd.read_sql(df_sql3, Engine3)
  54. # brand = parts+'_brand' # parts代替'front_bearing'
  55. # model = parts +'_model' # parts代替'front_bearing'
  56. # print(brand)
  57. # _brand = df3[brand].iloc[0]
  58. # _model = df3[model].iloc[0]
  59. # print(_brand)
  60. # print(_model)
  61. # #6、从表'unit_dict_brand_model'中通过'_brand'、'_model'查找部件的参数信息
  62. # Engine4 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  63. # df_sql4 = f"SELECT * FROM unit_dict_brand_model where manufacture = %s AND model_number = %s"
  64. # params = [(_brand, _model)]
  65. # df4 = pd.read_sql(df_sql4, Engine4, params=params)
  66. # if 'bearing' in parts:
  67. # n_rolls = df4['rolls_number'].iloc[0]
  68. # d_rolls = df4['rolls_diameter'].iloc[0]
  69. # D_diameter = df4['circle_diameter'].iloc[0]
  70. # theta_deg = df4['theta_deg'].iloc[0]
  71. # print(n_rolls)
  72. # print(d_rolls)
  73. # print(D_diameter)
  74. # print(theta_deg)
  75. # 与志亮的数据库连接
  76. #1、定义从235数据库中通过“windcode”风场编号和'ids'id号来查找波形数据记录
  77. def _get_by_id(windcode, ids):
  78. df_res = []
  79. engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
  80. for id in ids:
  81. table_name = windcode + '_wave'
  82. lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
  83. # print(lastday_df_sql)
  84. df = pd.read_sql(lastday_df_sql, engine)
  85. df_res.append(df)
  86. return df_res
  87. #2、找到波形记录提取特定的列值
  88. datas = _get_by_id('WOF091200030', [115041])
  89. datas = [df[['mesure_data','time_stamp','sampling_frequency', 'wind_turbine_number', 'rotational_speed', 'mesure_point_name']] for df in datas]
  90. data_filter = datas[0]
  91. rpm_Gen = data_filter['rotational_speed'].iloc[0]
  92. wind_code = data_filter['wind_turbine_number'].iloc[0]
  93. mesure_point_name = data_filter['mesure_point_name'].iloc[0]
  94. fs = int(data_filter['sampling_frequency'].iloc[0])
  95. print(wind_code)
  96. print(fs)
  97. print(rpm_Gen)
  98. #3、从测点名称中提取部件名称(计算特征频率的部件)
  99. str1 = mesure_point_name
  100. str2 = ["main_bearing", "front_main_bearing", "rear_main_bearing", "generator","stator","gearbox"]
  101. for str in str2:
  102. if str in str1:
  103. parts = str
  104. # if parts == "front_main_bearing":
  105. # parts = "front_bearing"
  106. # elif parts == "rear_main_bearing":
  107. # parts = "rear_bearing"
  108. print(parts)
  109. #4、连接233的数据库'energy_show',从表'wind_engine_group'查找风机编号'engine_code'对应的机型编号'mill_type_code'
  110. engine_code = wind_code
  111. Engine2 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  112. df_sql2 = f"SELECT * FROM {'wind_engine_group'} where engine_code = {'engine_code'} "
  113. df2 = pd.read_sql(df_sql2, Engine2)
  114. mill_type_code = df2['mill_type_code'].iloc[0]
  115. print(mill_type_code)
  116. #5、从表'unit_bearings'中通过机型编号'mill_type_code'查找部件'brand'、'model'的参数信息
  117. Engine3 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  118. df_sql3 = f"SELECT * FROM {'unit_dynamo'} where mill_type_code = {'mill_type_code'} "
  119. df3 = pd.read_sql(df_sql3, Engine3)
  120. brand = 'end_bearing'+'_brand' # parts代替'front_bearing'
  121. model = 'end_bearing' +'_model' # parts代替'front_bearing'
  122. print(brand)
  123. _brand = df3[brand].iloc[0]
  124. _model = df3[model].iloc[0]
  125. print(_brand)
  126. print(_model)
  127. #6、从表'unit_dict_brand_model'中通过'_brand'、'_model'查找部件的参数信息
  128. Engine4 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
  129. df_sql4 = f"SELECT * FROM unit_dict_brand_model where manufacture = %s AND model_number = %s"
  130. params = [(_brand, _model)]
  131. df4 = pd.read_sql(df_sql4, Engine4, params=params)
  132. n_rolls = df4['rolls_number'].iloc[0]
  133. d_rolls = df4['rolls_diameter'].iloc[0]
  134. D_diameter = df4['circle_diameter'].iloc[0]
  135. theta_deg = df4['theta_deg'].iloc[0]
  136. print(n_rolls)
  137. print(d_rolls)
  138. print(D_diameter)
  139. print(theta_deg)
  140. def calculate_bearing_frequencies(n, d, D, theta_deg, rpm):
  141. """
  142. 计算轴承各部件特征频率
  143. 参数:
  144. n (int): 滚动体数量
  145. d (float): 滚动体直径(单位:mm)
  146. D (float): 轴承节圆直径(滚动体中心圆直径,单位:mm)
  147. theta_deg (float): 接触角(单位:度)
  148. rpm (float): 转速(转/分钟)
  149. 返回:
  150. dict: 包含各特征频率的字典(单位:Hz)
  151. """
  152. # 转换角度为弧度
  153. theta = math.radians(theta_deg)
  154. # 转换直径单位为米(保持单位一致性,实际计算中比值抵消单位影响)
  155. # 注意:由于公式中使用的是比值,单位可以保持mm不需要转换
  156. ratio = d / D
  157. # 基础频率计算(转/秒)
  158. f_r = rpm / 60.0
  159. # 计算各特征频率
  160. BPFI = n / 2 * (1 + ratio * math.cos(theta)) * f_r # 内圈故障频率
  161. BPFO = n / 2 * (1 - ratio * math.cos(theta)) * f_r # 外圈故障频率
  162. BSF = (D / (2 * d)) * (1 - (ratio ** 2) * (math.cos(theta) ** 2)) * f_r # 滚动体故障频率
  163. FTF = 0.5 * (1 - ratio * math.cos(theta)) * f_r # 保持架故障频率
  164. return {
  165. "BPFI": BPFI,
  166. "BPFO": BPFO,
  167. "BSF": BSF,
  168. "FTF": FTF
  169. }
  170. # 示例使用(6208深沟球轴承参数)
  171. if __name__ == "__main__":
  172. # 轴承参数
  173. n = n_rolls # 滚动体数量
  174. d = d_rolls # 滚动体直径(mm)
  175. D = D_diameter # 节圆直径(mm)
  176. theta_deg = theta_deg # 接触角(度)
  177. rpm = rpm_Gen # 转速
  178. frequencies = calculate_bearing_frequencies(n, d, D, theta_deg, rpm)
  179. print("轴承特征频率(Hz):")
  180. print(f"内圈故障频率 (BPFI): {frequencies['BPFI']:.2f} Hz")
  181. print(f"外圈故障频率 (BPFO): {frequencies['BPFO']:.2f} Hz")
  182. print(f"滚动体故障频率 (BSF): {frequencies['BSF']:.2f} Hz")
  183. print(f"保持架故障频率 (FTF): {frequencies['FTF']:.2f} Hz")
  184. """
  185. # 建立数据库连接
  186. connection = pymysql.connect(
  187. host='192.168.50.233', # 数据库地址
  188. user='admin', # 用户名
  189. password='admin123456', # 密码
  190. database='energy_show', # 数据库名
  191. port=3306, # 端口号(默认3306)
  192. charset='utf8mb4' # 字符编码
  193. )
  194. try:
  195. with connection.cursor() as cursor:
  196. # 编写 SQL 查询
  197. sql = "select\
  198. ub.mill_type_code,\
  199. ub.front_bearing_brand,\
  200. ub.front_bearing_model,\
  201. udbm.rolls_number,\
  202. udbm.rolls_diameter,\
  203. udbm.circle_diameter,\
  204. udbm.theta_deg,\
  205. rub.rear_bearing_brand,\
  206. rub.rear_bearing_model,\
  207. rub.rrollsNumber,\
  208. rub.rrollsDiameter,\
  209. rub.rcircleDiameter,\
  210. rub.thetaDeg\
  211. from\
  212. unit_bearings ub\
  213. inner join unit_dict_brand_model udbm\
  214. on ub.front_bearing_brand= udbm.manufacture and ub.front_bearing_model= udbm.model_number\
  215. inner join\
  216. (select\
  217. ub.mill_type_code,\
  218. ub.rear_bearing_brand,\
  219. ub.rear_bearing_model,\
  220. udbm.rolls_number as rrollsNumber,\
  221. udbm.rolls_diameter as rrollsDiameter,\
  222. udbm.circle_diameter as rcircleDiameter,\
  223. udbm.theta_deg as thetaDeg\
  224. from\
  225. unit_bearings ub\
  226. inner join unit_dict_brand_model udbm\
  227. on ub.rear_bearing_brand = udbm.manufacture and ub.rear_bearing_model= udbm.model_number\
  228. where\
  229. ub.mill_type_code = 'WEM00001') rub\
  230. on ub.mill_type_code = rub.mill_type_code\
  231. where\
  232. ub.mill_type_code = 'WEM00001'"
  233. cursor.execute(sql) # 参数化查询防止 SQL 注入
  234. # 读取数据
  235. # result = cursor.fetchone() # 获取单条数据
  236. result = cursor.fetchall() # 获取所有数据
  237. print(result)
  238. finally:
  239. connection.close() # 关闭数据库连接
  240. """