energy-data-trans/wind_farm/中广核/purge_history_data.py

import datetime
import logging
import os
import sys

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine('mysql+pymysql://root:admin123456@192.168.50.235:30306/appoint')

base_dir = r'/data/logs/104'
log_dir = base_dir + os.sep + 'logs' + os.sep + 'delete'


def create_dir(save_dir, is_file=False):
    if is_file:
        save_dir = os.path.dirname(save_dir)
    os.makedirs(save_dir, exist_ok=True)


def init_log():
    logger = logging.getLogger("104data")
    logger.setLevel(logging.INFO)
    stout_handle = logging.StreamHandler(sys.stdout)
    stout_handle.setFormatter(
        logging.Formatter("%(asctime)s: %(message)s"))
    stout_handle.setLevel(logging.INFO)
    logger.addHandler(stout_handle)
    create_dir(log_dir)
    file_name = log_dir + os.sep + datetime.datetime.now().strftime('%Y%m') + '-info.log'
    file_handler = logging.FileHandler(file_name, encoding='utf-8')
    file_handler.setFormatter(
        logging.Formatter("%(asctime)s: %(message)s"))
    file_handler.setLevel(logging.INFO)
    logger.addHandler(file_handler)

    file_name = log_dir + os.sep + datetime.datetime.now().strftime('%Y%m') + '-error.log'
    file_handler = logging.FileHandler(file_name, encoding='utf-8')
    file_handler.setFormatter(
        logging.Formatter("%(asctime)s: %(message)s"))
    file_handler.setLevel(logging.ERROR)
    logger.addHandler(file_handler)

    return logger


logger = init_log()


def info_print(*kwargs):
    message = " ".join([str(i) for i in kwargs])
    logger.info(message)


def error_print(*kwargs):
    message = " ".join([str(i) for i in kwargs])
    logger.error(message)


def drop_table(lastdays):
    # 构建查询语句
    query = text(
        f"SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='appoint' AND TABLE_NAME like '{lastdays}%'")
    table_df = pd.read_sql(query, engine)

    info_print('查询到表', table_df['TABLE_NAME'].values)
    for table_name in table_df['TABLE_NAME'].values:
        # 构建删除表的SQL语句
        drop_query = text(f"DROP TABLE {table_name}")
        # 执行删除操作
        with engine.connect() as connection:
            connection.execute(drop_query)

        info_print(f"Table {table_name} deleted")


if __name__ == '__main__':
    info_print("开始执行")
    begin = datetime.datetime.now()
    lastdays = (datetime.datetime.now() - datetime.timedelta(days=8)).strftime('%Y%m%d')
    print(lastdays)
    drop_table(lastdays)
    info_print("执行结束，总耗时:", datetime.datetime.now() - begin)