CMS/connect mysql.py

import numpy as np
from scipy.signal import hilbert
from scipy.fft import ifft
import plotly.graph_objs as go
import pandas as pd
import pymysql
from sqlalchemy import create_engine, text
import math

# # 与志亮的数据库连接

# #1、定义从235数据库中通过“windcode”风场编号和'ids'id号来查找波形数据记录
# def _get_by_id(windcode, ids):
#     df_res = []
#     engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
#     for id in ids:
#         table_name = windcode + '_wave'
#         lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
#         # print(lastday_df_sql)
#         df = pd.read_sql(lastday_df_sql, engine)
#         df_res.append(df)
#     return df_res

# #2、找到波形记录提取特定的列值
# datas = _get_by_id('WOF091200030', [115041])
# datas = [df[['mesure_data','time_stamp','sampling_frequency', 'wind_turbine_number', 'rotational_speed', 'mesure_point_name']] for df in datas]
# data_filter = datas[0]
# rpm_Gen = data_filter['rotational_speed'].iloc[0]
# wind_code = data_filter['wind_turbine_number'].iloc[0]
# mesure_point_name = data_filter['mesure_point_name'].iloc[0]
# fs = int(data_filter['sampling_frequency'].iloc[0])
# print(wind_code)
# print(fs)
# print(rpm_Gen)

# #3、从测点名称中提取部件名称（计算特征频率的部件）
# str1 = mesure_point_name
# str2 = ["main_bearing", "front_main_bearing", "rear_main_bearing", "generator_non_drive_end"]
# for str in str2:
#     if str in str1:
#         parts = str
#         if parts == "front_main_bearing":
#             parts = "front_bearing"
#         elif parts == "rear_main_bearing":
#             parts = "rear_bearing"
# print(parts)

# #4、连接233的数据库'energy_show'，从表'wind_engine_group'查找风机编号'engine_code'对应的机型编号'mill_type_code'
# engine_code = wind_code
# Engine2 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
# df_sql2 = f"SELECT * FROM {'wind_engine_group'} where engine_code = {'engine_code'} "
# df2 = pd.read_sql(df_sql2, Engine2)
# mill_type_code = df2['mill_type_code'].iloc[0]
# print(mill_type_code)

# #5、从表'unit_bearings'中通过机型编号'mill_type_code'查找部件'brand'、'model'的参数信息
# Engine3 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
# df_sql3 = f"SELECT * FROM {'unit_bearings'} where mill_type_code = {'mill_type_code'} "
# df3 = pd.read_sql(df_sql3, Engine3)
# brand = parts+'_brand'            # parts代替'front_bearing'
# model = parts +'_model'           # parts代替'front_bearing'
# print(brand)
# _brand = df3[brand].iloc[0]
# _model = df3[model].iloc[0]
# print(_brand)
# print(_model)

# #6、从表'unit_dict_brand_model'中通过'_brand'、'_model'查找部件的参数信息
# Engine4 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
# df_sql4 = f"SELECT * FROM unit_dict_brand_model where manufacture = %s AND model_number = %s"
# params = [(_brand,  _model)]
# df4 = pd.read_sql(df_sql4, Engine4, params=params)
# if 'bearing' in parts:
#     n_rolls = df4['rolls_number'].iloc[0]
#     d_rolls = df4['rolls_diameter'].iloc[0]
#     D_diameter = df4['circle_diameter'].iloc[0]
#     theta_deg = df4['theta_deg'].iloc[0]
# print(n_rolls)
# print(d_rolls)
# print(D_diameter)
# print(theta_deg)
# 与志亮的数据库连接

#1、定义从235数据库中通过“windcode”风场编号和'ids'id号来查找波形数据记录
def _get_by_id(windcode, ids):
    df_res = []
    engine = create_engine('mysql+pymysql://root:admin123456@106.120.102.238:10336/energy_data_prod')
    for id in ids:
        table_name = windcode + '_wave'
        lastday_df_sql = f"SELECT * FROM {table_name} where id = {id} "
        # print(lastday_df_sql)
        df = pd.read_sql(lastday_df_sql, engine)
        df_res.append(df)
    return df_res

#2、找到波形记录提取特定的列值
datas = _get_by_id('WOF091200030', [115041])
datas = [df[['mesure_data','time_stamp','sampling_frequency', 'wind_turbine_number', 'rotational_speed', 'mesure_point_name']] for df in datas]
data_filter = datas[0]
rpm_Gen = data_filter['rotational_speed'].iloc[0]
wind_code = data_filter['wind_turbine_number'].iloc[0]
mesure_point_name = data_filter['mesure_point_name'].iloc[0]
fs = int(data_filter['sampling_frequency'].iloc[0])
print(wind_code)
print(fs)
print(rpm_Gen)

#3、从测点名称中提取部件名称（计算特征频率的部件）
str1 = mesure_point_name
str2 = ["main_bearing", "front_main_bearing", "rear_main_bearing", "generator","stator","gearbox"]
for str in str2:
    if str in str1:
        parts = str
        # if parts == "front_main_bearing":
        #     parts = "front_bearing"
        # elif parts == "rear_main_bearing":
        #     parts = "rear_bearing"
print(parts)

#4、连接233的数据库'energy_show'，从表'wind_engine_group'查找风机编号'engine_code'对应的机型编号'mill_type_code'
engine_code = wind_code
Engine2 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
df_sql2 = f"SELECT * FROM {'wind_engine_group'} where engine_code = {'engine_code'} "
df2 = pd.read_sql(df_sql2, Engine2)
mill_type_code = df2['mill_type_code'].iloc[0]
print(mill_type_code)

#5、从表'unit_bearings'中通过机型编号'mill_type_code'查找部件'brand'、'model'的参数信息
Engine3 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
df_sql3 = f"SELECT * FROM {'unit_dynamo'} where mill_type_code = {'mill_type_code'} "
df3 = pd.read_sql(df_sql3, Engine3)
brand = 'end_bearing'+'_brand'            # parts代替'front_bearing'
model = 'end_bearing' +'_model'           # parts代替'front_bearing'
print(brand)
_brand = df3[brand].iloc[0]
_model = df3[model].iloc[0]
print(_brand)
print(_model)

#6、从表'unit_dict_brand_model'中通过'_brand'、'_model'查找部件的参数信息
Engine4 = create_engine('mysql+pymysql://admin:admin123456@106.120.102.238:16306/energy_show')
df_sql4 = f"SELECT * FROM unit_dict_brand_model where manufacture = %s AND model_number = %s"
params = [(_brand,  _model)]
df4 = pd.read_sql(df_sql4, Engine4, params=params)
n_rolls = df4['rolls_number'].iloc[0]
d_rolls = df4['rolls_diameter'].iloc[0]
D_diameter = df4['circle_diameter'].iloc[0]
theta_deg = df4['theta_deg'].iloc[0]
print(n_rolls)
print(d_rolls)
print(D_diameter)
print(theta_deg)
def calculate_bearing_frequencies(n, d, D, theta_deg, rpm):
    """
    计算轴承各部件特征频率

    参数：
    n (int): 滚动体数量
    d (float): 滚动体直径（单位：mm）
    D (float): 轴承节圆直径（滚动体中心圆直径，单位：mm）
    theta_deg (float): 接触角（单位：度）
    rpm (float): 转速（转/分钟）

    返回：
    dict: 包含各特征频率的字典（单位：Hz）
    """
    # 转换角度为弧度
    theta = math.radians(theta_deg)

    # 转换直径单位为米（保持单位一致性，实际计算中比值抵消单位影响）
    # 注意：由于公式中使用的是比值，单位可以保持mm不需要转换
    ratio = d / D

    # 基础频率计算（转/秒）
    f_r = rpm / 60.0

    # 计算各特征频率
    BPFI = n / 2 * (1 + ratio * math.cos(theta)) * f_r  # 内圈故障频率
    BPFO = n / 2 * (1 - ratio * math.cos(theta)) * f_r  # 外圈故障频率
    BSF = (D / (2 * d)) * (1 - (ratio ** 2) * (math.cos(theta) ** 2)) * f_r  # 滚动体故障频率
    FTF = 0.5 * (1 - ratio * math.cos(theta)) * f_r  # 保持架故障频率

    return {
        "BPFI": BPFI,
        "BPFO": BPFO,
        "BSF": BSF,
        "FTF": FTF
    }


# 示例使用（6208深沟球轴承参数）
if __name__ == "__main__":
    # 轴承参数
    n = n_rolls  # 滚动体数量
    d = d_rolls  # 滚动体直径（mm）
    D = D_diameter  # 节圆直径（mm）
    theta_deg = theta_deg  # 接触角（度）
    rpm = rpm_Gen  # 转速

    frequencies = calculate_bearing_frequencies(n, d, D, theta_deg, rpm)

    print("轴承特征频率（Hz）：")
    print(f"内圈故障频率 (BPFI): {frequencies['BPFI']:.2f} Hz")
    print(f"外圈故障频率 (BPFO): {frequencies['BPFO']:.2f} Hz")
    print(f"滚动体故障频率 (BSF): {frequencies['BSF']:.2f} Hz")
    print(f"保持架故障频率 (FTF): {frequencies['FTF']:.2f} Hz")
"""
# 建立数据库连接
connection = pymysql.connect(
    host='192.168.50.233',      # 数据库地址
    user='admin',           # 用户名
    password='admin123456',  # 密码
    database='energy_show',  # 数据库名
    port=3306,             # 端口号（默认3306）
    charset='utf8mb4'      # 字符编码
)
try:
    with connection.cursor() as cursor:
        # 编写 SQL 查询
        sql = "select\
            ub.mill_type_code,\
            ub.front_bearing_brand,\
            ub.front_bearing_model,\
            udbm.rolls_number,\
            udbm.rolls_diameter,\
            udbm.circle_diameter,\
            udbm.theta_deg,\
            rub.rear_bearing_brand,\
            rub.rear_bearing_model,\
            rub.rrollsNumber,\
            rub.rrollsDiameter,\
            rub.rcircleDiameter,\
            rub.thetaDeg\
            from\
            unit_bearings ub\
            inner join unit_dict_brand_model udbm\
            on ub.front_bearing_brand= udbm.manufacture and ub.front_bearing_model= udbm.model_number\
            inner join\
            (select\
            ub.mill_type_code,\
            ub.rear_bearing_brand,\
            ub.rear_bearing_model,\
            udbm.rolls_number as rrollsNumber,\
            udbm.rolls_diameter as rrollsDiameter,\
            udbm.circle_diameter as rcircleDiameter,\
            udbm.theta_deg as thetaDeg\
            from\
            unit_bearings ub\
            inner join unit_dict_brand_model udbm\
            on ub.rear_bearing_brand = udbm.manufacture and ub.rear_bearing_model= udbm.model_number\
            where\
            ub.mill_type_code = 'WEM00001') rub\
            on ub.mill_type_code = rub.mill_type_code\
            where\
            ub.mill_type_code = 'WEM00001'"
        cursor.execute(sql)  # 参数化查询防止 SQL 注入

        # 读取数据
        # result = cursor.fetchone()  # 获取单条数据
        result = cursor.fetchall()  # 获取所有数据

        print(result)

finally:
    connection.close()  # 关闭数据库连接
"""