【MySQL】删除数据备份数据脚本

背景

最近需要删除数据，删除数据的要求比较奇葩，需要从文件中拿出来要删除的ID，
然后要一条条删除，并且在删除之前生成反向的备份SQL。所以这里整理了一下自己简单
搞的脚本。

PS:其实删除数据这种需求我一般是两种建议方式：

1.使用pt-archive归档删除并且备份数据到归档库。
2.代码去删除，控制速率，每秒操作1k条，每秒操作四次，即250ms操作一次，
大事务完全转化为小事务。

脚本如下

# -- coding:UTF-8 --

import os, time, re, sys, io, random
import pymysql

AccountFile = '/data1/DataClean/th/account_th_3.del'
db_host = 'localhost'
db_port = 6606
db_database = ''
db_user = ''
db_pass = ""
DelTable = ''


def get_conn():
    try:
        # 连接数据库
        db = pymysql.connect(
            host=db_host,
            user=db_user,
            password=db_pass,
            port=db_port,
            database=db_database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor)
        #cursor = db.cursor()
        return db
    except Exception as e:
        return e


def mysqldb(sql, m='r'):
    try:
        # 连接数据库
        db = pymysql.connect(
            host=db_host,
            user=db_user,
            password=db_pass,
            port=db_port,
            database=db_database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor)
        # 创建游标
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            if m == 'r':
                data = cursor.fetchall()
            elif m == 'w':
                db.commit()
                data = cursor.rowcount
        except Exception as e:
            data = False
            # print("[MySQL] MySQL {} {} 读写失败，报错为: {}".format(config["mysql"]["host"], config["mysql"]["dbname"], e))
            db.rollback()
        db.close()
        return data
    except Exception as e:
        return e
    # print("[MySQL] 连接MySQL {} {} 失败, 报错为: {}".format(config["mysql"]["host"], config["mysql"]["dbname"], e))


AccountBakRe = AccountFile.replace('del', 'bak')
FDel = open(AccountFile, 'r')
FBak = open(AccountBakRe, 'a')
conn  = get_conn()
cursor = conn.cursor()
for line in FDel.readlines():
    if not len(line) or line.startswith('#'):
        continue
    account_id = int(line)
    BakSQL = "select * from %s where id=%d;" % (DelTable, account_id)
    cursor.execute(BakSQL)
    BakRow = cursor.fetchall()
    insert = "insert into %s" % (DelTable)
    if len(BakRow) > 0:
        key_list = []
        for key in BakRow[0].keys():
            key_list.append(key)
        insert = 'insert into %s (%s) values ' % (DelTable, ','.join(key_list))
        # print(insert)
        data_list = []
        for i in BakRow:
            j_list = []
            for j in i.keys():
                if type(i[j]) == int:
                    j_list.append(str(i[j]))
                else:
                    j_list.append('"' + str(i[j]) + '"')
            now_data = '(%s)' % (','.join(j_list))
            data_list.append(now_data)
        insert = insert + ','.join(data_list) + ';'
        print(insert)
        FBak.write(str(insert) + '\n')
        DelSQL = "delete from %s where id=%d;" % (DelTable, account_id)
        print (DelSQL)
        cursor.execute(DelSQL)
conn.commit()
conn.close()
cursor.close()