spider_work/cgc_spider/task_remove.py

# -*- coding: utf-8 -*-
# Author : Charley
# Python : 3.10.8
# Date   : 2025/9/28 11:32
from loguru import logger

from mysql_pool import MySQLConnectionPool

sql_pool = MySQLConnectionPool(log=logger)


def remove_unwanted_tasks_batch():
    """
    分批删除cgc_task表中cert_id末尾三位数不在1-300范围内的记录
    """
    try:
        deleted_total = 0
        batch_size = 10000  # 每批删除10000条记录

        while True:
            # 使用原生SQL执行分批删除
            delete_query = """
                DELETE FROM cgc_task 
                WHERE CAST(RIGHT(cert_id, 3) AS UNSIGNED) NOT BETWEEN 1 AND 300 
                LIMIT %s
            """

            # 执行删除操作
            cursor = sql_pool._execute(delete_query, (batch_size,), commit=True)
            affected_rows = cursor.rowcount

            deleted_total += affected_rows
            logger.info(f"已删除 {affected_rows} 条记录，总计: {deleted_total}")

            # 如果本次删除少于批次大小，说明已处理完
            if affected_rows < batch_size:
                break

        logger.success(f"删除完成，总共删除 {deleted_total} 条记录")

    except Exception as e:
        logger.error(f"批量删除失败: {e}")


# 执行清理任务
if __name__ == "__main__":
    remove_unwanted_tasks_batch()