energy-data-trans/service/trans_service.py

# -*- coding: utf-8 -*-
# @Time    : 2024/6/7
# @Author  : 魏志亮
import os

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine

from conf.db import mysql_config
from utils.db.ConnectMysqlPool import ConnectMysqlPool
from utils.file.trans_methods import read_file_to_df
from utils.log.trans_log import trans_print

trans = ConnectMysqlPool("trans")


def get_trans_conf(wind_name, trans_type) -> dict:
    query_sql = "SELECT * FROM trans_conf where wind_name = %s and type = %s"
    res = trans.execute(query_sql, (wind_name, trans_type))
    print(res)
    if type(res) == tuple:
        return None
    return res[0]


def save_to_trans_conf(data_dict=dict()):
    trans.save_dict(data_dict)


def creat_table_and_add_partition(table_name, count, read_type):
    create_sql = f"""
    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 '湍流强度',
        `year` INT (4) DEFAULT NULL COMMENT '年',
        `month` INT (2) DEFAULT NULL COMMENT '月',
        `day` INT (2) DEFAULT NULL COMMENT '日',
        `param1` DOUBLE DEFAULT NULL COMMENT '预留1',
        `param2` DOUBLE DEFAULT NULL COMMENT '预留2',
        `param3` DOUBLE DEFAULT NULL COMMENT '预留3',
        `param4` DOUBLE DEFAULT NULL COMMENT '预留4',
        `param5` DOUBLE DEFAULT NULL COMMENT '预留5',
        `param6` DOUBLE DEFAULT NULL COMMENT '预留6',
        `param7` DOUBLE DEFAULT NULL COMMENT '预留7',
        `param8` DOUBLE DEFAULT NULL COMMENT '预留8',
        `param9` DOUBLE DEFAULT NULL COMMENT '预留9',
        `param10` DOUBLE DEFAULT NULL COMMENT '预留10',
         KEY `time_stamp` (`time_stamp`),
         KEY `wind_turbine_number` (`wind_turbine_number`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4
    """

    if read_type == 'second':
        create_sql = create_sql + f" PARTITION BY KEY (`wind_turbine_number`) PARTITIONS {count}"

    trans.execute(create_sql)


def rename_table(table_name, renamed_table_name, save_db=True):
    if save_db:
        rename_sql = f"RENAME TABLE {table_name} TO {renamed_table_name}"
        try:
            trans.execute(rename_sql)
        except Exception as e:
            trans_print(e)


def drop_table(table_name, save_db=True):
    if save_db:
        rename_sql = f"drop TABLE `{table_name}` "
        try:
            trans.execute(rename_sql)
        except Exception as e:
            trans_print(e)


def save_file_to_db(table_name: str, file: str, batch_count=200000):
    # trans.df_batch_save(table_name, df, batch_count)
    env = os.environ['env']

    config = mysql_config['trans_' + env]
    username = config['user']
    password = config['password']
    host = config['host']
    port = config['port']
    dbname = config['database']
    engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}')
    for i, df in enumerate(pd.read_csv(file, chunksize=batch_count)):
        df.to_sql(table_name, engine, if_exists='append', index=False)
        count = (i + 1) * batch_count
        trans_print(f"Chunk {count} written to MySQL.")


if __name__ == '__main__':
    path_prix = r"/data/download/collection_data/2完成/招远风电场-山东-大唐/清理数据/WOF063100040-WOB00013/second"
    files = ["WOG00030.csv", "WOG00034.csv"]
    for path in files:
        save_file_to_db("WOF063100040-WOB00013_second", path_prix + os.sep + path, batch_count=100000)