删除Mysql历史数据（Python）

Python实现删除mysql历史月表数据，以及删除N天前历史数据并释放磁盘空间，delete数据不会释放表空间，手动optimize整理释放（！会锁表！）

import pymysql
import datetime
import sys

# 指定 Mysql 连接参数
HOST = ''
PORT = ''
USER = ''
PASSWORD = ''
# 保留近n天的数据
DAYS = 365

# 数据库配置
DS_0 = 'vn_wl_0'
DS_1 = 'vn_wl_1'
DS_ADAS = 'vn_wl_adas'
# 直接drop的表，有分月表的
DROP_TABLES = {
    'all_alarm_info': [DS_1, DS_ADAS],
    'can': [DS_1],
    'gps': [DS_1],
    'gps_alarm': [DS_1],
    'gps_run': [DS_1],
    'veh_arrive_station_info': [DS_1],
    'vehiclestatu_his': [DS_1],
    'wechat_push_alarm_record': [DS_1]
}
# delete部分数据的表(根据生产环境统计的表大小)
DELETE_TABLES = {
    # [vn_wl_0]
    'device_register_or_off': DS_0,
    'check_post_record': DS_0,
    'sim_gprs': DS_0,
    'driver_punch_card_record': DS_0,
    # [vn_wl_1]
    'veh_login_log': DS_1,
    'veh_online_stat': DS_1,
    'with_superior_platform_connection_detail': DS_1,
    # [vn_wl_adas]
    'mobileye_driver_safe_event': DS_ADAS,
    'mobileye_driver_score': DS_ADAS,
    'adas_media_info': DS_ADAS,
}


# 获取连接对象
def connection(DB):
    return pymysql.connect(host=HOST, port=int(PORT), user=USER, passwd=PASSWORD, db=DB)


def dropTable(table, time):
    for db in DROP_TABLES[table]:
        try:
            
            conn = connection(db)
            cur = conn.cursor()
            for num in range(0,23):
                yearMouth = time + datetime.timedelta(days=-num*31)
                sql = 'DROP TABLE IF EXISTS ' + table + "_" + yearMouth.strftime("%Y%m")
                print(sql)
                cur.execute(sql)
            cur.close()
            conn.close()
        except Exception as e:
            print("drop pre year table error [" + table + "]:" + time.strftime("%Y%m"))
            print(repr(e))


def deleteTable(db, table, yearMouthDay):
    try:
        if "device_register_or_off" == table:
            dateField = "register_or_off_time"
        elif "check_post_record" == table:
            dateField = "check_time"
        else:
            dateField = "ct"
        sql = 'delete from ' + table + ' where ' + dateField + ' < '' + yearMouthDay + '''
        print(sql)
        conn = connection(db)
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()
        cur.close()
        conn.close()
    except Exception as e:
        print("delete table error [" + table + "]:")
        print(repr(e))


# 优化表空间，delete删除后整理磁盘碎片删除数据。
# ！会锁表！
def optimizeTable(db, table):
    try:
        sql = 'optimize table ' + table
        print(sql)
        conn = connection(db)
        cur = conn.cursor()
        cur.execute(sql)
        cur.close()
        conn.close()
    except Exception as e:
        print("optimize table error [" + table + "]:")
        print(repr(e))


def job():
    day = -int(DAYS)
    time = datetime.datetime.now() + datetime.timedelta(days=day)
    yearMouthDay = time.strftime("%Y-%m-%d")
    for table in DROP_TABLES:
        dropTable(table, time)

    for table in DELETE_TABLES:
        db = DELETE_TABLES[table]
        deleteTable(db, table, yearMouthDay)
        optimizeTable(db, table)


if __name__ == "__main__":

    if len(sys.argv) != 6:
        print("Parameter error, use eg:")
        print("python3 cleanHistoryData.py [mysql-host] [mysql-port] [mysql-user] [mysql-passwd] [delete-days]")
        print("python3 cleanHistoryData.py 127.0.0.1 3306 root yx_123 365")
    else:
        HOST = sys.argv[1]
        PORT = sys.argv[2]
        USER = sys.argv[3]
        PASSWORD = sys.argv[4]
        DAYS = sys.argv[5]
        job()