删除千万级mysql数据的脚本(python)

 

嗯，一个小需求所以写了一个脚本

 

 

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Python 3.9.0
# pip install pymysql 
'''
@File    :   deletedate.py
@Time    :   2021/11/18 16:05:31
@Author  :   Li Ruilong
@Version :   1.0
@Contact :   1224965096@qq.com
@Desc    :   用于删除千万级mysql数据的脚本,
'''

# here put the import lib


import pymysql.cursors
import time


# 连接MySQL数据库


# 删除的数据量
sqlselect = "select count(*)  as sum from oc_ob_task where SP_ID=999;"

# 每次删除的条数
sqldelete = "delete from   oc_ob_task where SP_ID=999 limit 10000 "

host='192.168.50.187'
port=3306
user='PT90'
password='OY******8edNY'
db='cloud'

# 通过cursor创建游标
try:
    connection = pymysql.connect(host=host, port=port, user=user, password=password,
                                 db=db, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cursor = connection.cursor()
    print("数据库连接成功============================================", time.strftime(
        "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")
except:
    print("数据库连接异常============================================", time.strftime(
        "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")


def select(sqlselect):

    cursor.execute(sqlselect)
    connection.commit()
    global result
    result = cursor.fetchone().get("sum")
    print("清理的数据条数", result)


def delete(result, sqldelete):
    print(result, "开始清理============================================", time.strftime(
        "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")
    while result > 10:
        try:
            cursor.execute(sqldelete)
            connection.commit()
            result = cursor.fetchone()
        except:
            cursor.rollback()

        cursor.execute(sqlselect)
        connection.commit()
        result = cursor.fetchone().get("sum")
        print("剩余数据量：", result, "=================================", time.strftime(
            "%Y-%m-%d %H:%M:%S", time.localtime()), "=======================")


if __name__ == '__main__':
    while True:
        print('''
        ==========================
        |    1.自定义配置清理     |
        |                        |
        |    2.默认配置清理       |
        |                        |
        |    3.数据量查询         |
        |========================|
        ''')
        i = input("请选择：\n")
        if i == '1':
            try:
                host = input("请输入host:\n ")
                port = int(input("请输入port: \n"))
                user = input("请输入db用户名user:\n ")
                password = input("请输入db用户名password:\n ")
                db = input("请输入数据库: \n")
                sqlselect = input(
                    "查询数据量SQL：例：select count(*) as sum  from oc_ob_task where SP_ID=999;\n")
                sqldelete = input(
                    "批量删除SQL：例：delete from   oc_ob_task where SP_ID=999 limit 10000\n ")
                print("清理SQL============================================", time.strftime(
                    "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")
            except:
                print("port必须为数字！") 
                continue       
            try:
                connection = pymysql.connect(host=host, port=port, user=user, password=password,
                                             db=db, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
                cursor = connection.cursor()
                print("数据库连接成功============================================", time.strftime(
                        "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")
            except:    
                print("数据库连接异常============================================", time.strftime(
                    "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================")
                time.sleep(5)    
                break
        elif i != '2' :
            if i == '3':
               select(sqlselect)
            continue  
        print("\n")
        print("连接参数host|port|user|password|db：\n",host,"\n",port,"\n",user,"\n",password,"\n",db,"\n")          
        print("查询数据量SQL：", sqlselect, "\n ")
        print("批量删除SQL：", sqldelete, "\n ")
        boo = input("确实是否清理！(y/n):\n")

        if boo == 'y':
             boo = input("确实是否清理！(y/n):\n")
             if boo == 'y':
                 boo = input("确实是否清理！(y/n):\n")
                 if boo == 'y':
                    try:
                        select(sqlselect)
                        delete(result, sqldelete)
                    except:
                        print("数据库连接异常============================================", time.strftime(
                    "%Y-%m-%d %H:%M:%S", time.localtime()), "=========================") 
                    break
                 else:
                     print("退出成功")
                     continue
             else:
                 print("退出成功")
                 continue
        else:
            print("退出成功")
            continue