123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081 |
- 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_sqls = []
- index = 0
- stander_cols = list()
- stander_cols.append('wind_factory')
- stander_cols.append('wind_no')
- stander_cols.append('occur_time')
- 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']):
- stander_cols.append(en_name)
- if index == 0:
- wind_sqls.append(
- f"select * from (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}') a{index}")
- index = index + 1
- else:
- wind_sqls.append(
- f"\n inner join "
- 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}') a{index} "
- f"on a{index - 1}.wind_factory = a{index}.wind_factory and a{index - 1}.wind_no = a{index}.wind_no"
- f" and a{index - 1}.occur_time = a{index}.occur_time")
- index = index + 1
- result_sql = "".join(wind_sqls) + ";"
- stander_cols_str = "'" + "',".join(stander_cols) + "'"
- all_sqls.append((result_sql, stander_cols_str))
- return all_sqls
- def show_sqls(stander_cols_str, datas):
- for index, data in enumerate(datas):
- print(f'String [] cols = {"".join(stander_cols_str[index])}')
- print()
- print(data)
- print()
- def save_to_csv(datas, name):
- dfs = list()
- for data in datas:
- dfs.append(pd.read_sql(data, get_engine()))
- df = pd.concat(dfs, ignore_index=True)
- df.to_csv(name + ".csv", encoding='utf8', index=False)
- if __name__ == '__main__':
- df = pd.read_csv(r"C:\Users\wzl\Desktop\中广核104测点\min_tables.csv")
- datas = generate_sql(df[df['风场'] == '右玉'], '右玉')
- show_sqls(datas[1], datas[0])
- # save_to_csv(datas[0], '右玉')
|