import pymysql
import time

master_db_config = dict(
    host="rds.amazonaws.com", port=3306, user="user",
    passwd="pwd", cursorclass=pymysql.cursors.DictCursor)

def get_mysql_connection(mysql_config) -> pymysql.connect:
    try:
        conn = pymysql.connect(**mysql_config, autocommit=True)
    except Exception as e:
        print(str(e))
        print(f"db conn err: {mysql_config}")
    else:
        return conn

con = get_mysql_connection(master_db_config)
con.select_db('db_name')
cursor = con.cursor()

sql = """DELETE FROM `table_records` WHERE `id` < 28374149 limit 50000; """

cursor.execute(sql)

num_rows_deleted = cursor.rowcount
while num_rows_deleted > 0:
    cursor.execute(sql)
    num_rows_deleted = cursor.rowcount
    if num_rows_deleted > 0:
        print(f"成功删除了 {num_rows_deleted} 行。")
    else:
        print("没有符合条件的行被删除。")
    time.sleep(4)

import pymysql
import re
import datetime
import time
import sys

def log(*args, **kwargs):
    current_time = time.time()
    formatted_time = datetime.datetime.fromtimestamp(current_time).strftime('%Y-%m-%d %H:%M:%S')
    millis = int((current_time - int(current_time)) * 1000)
    print(f"当前时间（毫秒级）: {formatted_time}.{str(millis).zfill(3)}")
    print(*args, **kwargs)


# 定义数据库连接参数
host = "rds.amazonaws.com"
username = "user"
password = "password"
database = "db_name"
port = 3306
# 创建数据库连接
db = pymysql.connect(host=host, user=username, password=password, db=database, port=port)
# 创建一个游标对象
cursor = db.cursor()

# 定义变量
table_name = 'table_records'
tmp_table_name = 'tmp_old'
num_records = 100000

# 检查 tmp_table_name 是否存在
cursor.execute(f"SHOW TABLES LIKE '{tmp_table_name}';")
result = cursor.fetchone()
if result:
    log(f"{tmp_table_name} 已存在，请检查")
    sys.exit()

log("开始重命名")
# 第一步：重命名 table_name 为 tmp_table_name
cursor.execute(f"RENAME TABLE {table_name} TO {tmp_table_name};")
log("重命名结束")

# 第二步：建立一个新表名为 table_name，表结构与 tmp_table_name 一致，并且自增主键 id 为 tmp_table_name 的最大自增id
# 执行 SHOW CREATE TABLE 获取旧表的创建语句
cursor.execute(f"SHOW CREATE TABLE {tmp_table_name};")
result = cursor.fetchone()
create_table_statement = result[1]

# 使用正则表达式将旧表名替换为新表名
create_table_statement = re.sub(f'CREATE TABLE `{tmp_table_name}`', f'CREATE TABLE `{table_name}`', create_table_statement)

# 执行创建新表的 SQL 语句
cursor.execute(create_table_statement)
log("新表创建完毕")

# 第三步：把 tmp_table_name 表中最新的 num_records 条数据插入到新建的 table_name 中
cursor.execute(f"INSERT INTO {table_name} SELECT * FROM {tmp_table_name} ORDER BY id DESC LIMIT {num_records};")

log("数据库初始化完毕")

# 提交到数据库执行
db.commit()

# 关闭数据库连接
db.close()