CGN_taiyuan_104/tmp/generate_minute_sql_sigle_sql.py

import datetime

import pandas as pd
from sqlalchemy import create_engine


def get_engine():
    username = 'envision'
    password = 'envision'
    host = '172.21.6.37'
    port = 3306
    dbname = 'envision'
    return create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}')


def generate_sql(df: pd.DataFrame, wind_name: str):
    print(f"开始执行{wind_name}")
    wind_nos = df['风机号'].unique()

    all_sqls = list()

    begin_time_str = '2025-01-01 00:00:00'
    end_time_str = '2025-03-01 00:00:00'
    for wind_no in wind_nos:

        wind_df = df[df['风机号'] == wind_no]
        for wind_factory, table, col, en_name in zip(wind_df['风场'], wind_df['历史采样表名'], wind_df['历史采样域名'],
                                                     wind_df['en_name']):
            all_sqls.append((wind_factory,
                             f"select '{wind_factory}' as wind_factory,{wind_no} as wind_no, occur_time, {col} as {en_name}"
                             f" from {table} where occur_time >= '{begin_time_str}' and occur_time <'{end_time_str}'"))

    return all_sqls


def show_sqls(datas):
    for index, data in enumerate(datas):
        print('--', data[0])
        print(data[1])
    print()


def save_to_csv(datas):
    dfs = list()
    for index, data in enumerate(datas):
        begin = datetime.datetime.now()
        print('开始', begin)

        dfs.append(pd.read_sql(data[1], get_engine()))
        df.to_csv(data[0] + str(index) + ".csv", encoding='utf8', index=False)
        print('结束', datetime.datetime.now(), '耗时:', datetime.datetime.now() - begin)


if __name__ == '__main__':
    df = pd.read_csv(r"C:\Users\wzl\Desktop\中广核104测点\min_tables.csv")

    datas = generate_sql(df[df['风场'] == '右玉'], '右玉')

    show_sqls(datas)