mysql +python定时删除脚本

import datetime
import time
import traceback
from log import Log
import pymysql
from multiprocessing import Process

# 1.设置删除周期end_time，用datatime的库设置
# 2.因为需要存储一定量的数据，因此，再第一个删除周期不对数据进行删除，从第二个删除周期开始按照进1退1的方法删除
# 3.进1退1实现通过设置一个记录时间，记录时间在程序运行时的基准时间上往上加，每次到达记录时间后就执行删除


class Delete(Process):
    def __init__(self):
        super(Delete, self).__init__()


    def run(self):
        self.logger = Log('MySqlDeleteLog').getlog()
        try:
            self.logger.error('--------------start-----------------')
            # 记录时间表
            record_time_list = []
            # 记录时间
            count = 0
            # 删除次数
            del_times = 0
            # 首轮标记
            FLAG_ = True
            while True:
                # 1获得每轮删除后的时间，也就是下一轮的基准时间
                actor_time = datetime.datetime.strptime(str(datetime.datetime.now().date()) + ' 00:00:00',
                                                      "%Y-%m-%d %H:%M:%S")
                # 按分钟测试
                # actor_time = datetime.datetime.strptime(str(datetime.datetime.now())[:19], "%Y-%m-%d %H:%M:%S")
                print('actor_time', actor_time)
                # FLAG为记录时间标记，为1时是刚开始，从基准时间上+指定时间
                # 为0时是循环回来的数据，从上一轮的nowtime当做基准时间再加基准时间
                FLAG = True
                while True:
                    # 2 获得时间
                    # (1) 获得删除时间
                    end_time = datetime.datetime.strptime(
                        (str((datetime.datetime.now() + datetime.timedelta(days=7)).date())
                         + ' 00:00:00'), "%Y-%m-%d %H:%M:%S")
                    # 按分钟测试
                    # end_time = datetime.datetime.strptime((str((actor_time + datetime.timedelta(minutes=7)))), "%Y-%m-%d %H:%M:%S")
                    # (2) 获得记录时间
                    # record_time = datetime.datetime.strptime((str((datetime.datetime.now()+datetime.timedelta(days=1))[:-7])),
                    #                                          "%Y-%m-%d %H:%M:%S")
                    # 按分钟测试
                    if FLAG:
                        # 更新记录时间record_time
                        # record_time = datetime.datetime.strptime((str((actor_time + datetime.timedelta(minutes=1)))), "%Y-%m-%d %H:%M:%S")
                        record_time = datetime.datetime.strptime((str((actor_time + datetime.timedelta(days=1)))),
                                                                 "%Y-%m-%d %H:%M:%S")
                        FLAG = False
                    else:
                        record_time = datetime.datetime.strptime((str((now_time + datetime.timedelta(days=1)))),
                                                                 "%Y-%m-%d %H:%M:%S")
                    # 退出控制条件为达到终止时间
                    if end_time == datetime.datetime.strptime(str(datetime.datetime.now())[:19], "%Y-%m-%d %H:%M:%S"):
                        break
                    while True:
                        # 3 判断是否在时间范围内
                        # (1) 获得当前时间
                        now_time = datetime.datetime.strptime(
                            (str((datetime.datetime.now().date())
                             + ' 00:00:00'), "%Y-%m-%d %H:%M:%S")
                        # print('now_time', now_time)
                        # (2)如果当前时间和记录时间相等
                        if now_time == record_time:
                            if count == 7:
                                count = 1
                                # 7次之后首轮结束，首轮标记置0
                                FLAG_ = False
                            else:
                                count += 1
                            try:
                                db = pymysql.connections.Connection(
                                    host='127.0.0.1',  # 要连接的主机地址
                                    user='root',  # 用于登录的数据库用户
                                    password='root',  # 
                                    database='test',  # 要连接的数据库
                                    port=3306,  # 端口，一般为 3306
                                )
                                # 得到最大id，最大id作为下一轮的删除点
                                cursor = db.cursor()
                                Sql_max = "select MAX(id) as id from detest"
                                cursor.execute(Sql_max)
                                max = cursor.fetchall()[0][0]
                                record_time_list.append(max)
                                print('save id times for all %s / 7 times foot index is %s' % (count, max))
                                # 这里等1s，因为时间相同会持续执行
                                time.sleep(1)
                                cursor.close()
                                db.close()
                                break
                            except:
                                self.logger.error('check your connection for times counting in sql delete function')
                                self.logger.error(str(traceback.format_exc()))
                                try:
                                    self.AutoMail()
                                except:
                                    self.logger.error('mail can not send check your connection')
                                # 关闭该连接对应的游标以及连接
                                try:
                                    cursor.close()
                                except:
                                    pass
                                try:
                                    db.close()
                                except:
                                    pass
                                continue
                    # 首轮不删除
                    if FLAG_:
                        pass
                    else:
                        try:
                            # 连接数据库
                            db = pymysql.connections.Connection(
                                host='127.0.0.1',  # 要连接的主机地址
                                user='root',  # 用于登录的数据库用户
                                password='root',  # 
                                database='test',  # 要连接的数据库
                                port=3306,  # 端口，一般为 3306
                            )
                            cursor_ = db.cursor()
                            # (3) 找到删除的上限
                            max_lim = str(record_time_list[0])
                            # (4) 删除之间的数据
                            Sql_acc = "delete from detest where Id < '" + max_lim + "'"
                            cursor_.execute(Sql_acc)
                            db.commit()
                            del_times += 1
                            print('脚标', max_lim)
                            print('第%s次删除,当前时间%s, record长度%s' % (del_times, now_time, len(record_time_list)))
                            # (5) 更新record
                            record_time_list.pop(0)
                            # (6) 跳出循环
                            FLAG_ = False
                            cursor_.close()
                            db.close()
                            break
                        except:
                            # 写入log
                            self.logger.error('check your connection for delete data in sql delete function')
                            self.logger.error(str(traceback.format_exc()))
                            # 发送邮件给我
                            try:
                                self.AutoMail()
                            except:
                                self.logger.error('mail can not send check your connection')
                            # 关闭该连接对应的游标以及连接
                            try:
                                cursor_.close()
                            except:
                                pass
                            try:
                                db.close()
                            except:
                                pass
                            continue

        except:
            try:
                self.logger.error(str(traceback.format_exc()))
            except:
                self.logger.error('error in sql delete error raise')
                try:
                    self.AutoMail()
                except:
                    self.logger.error('mail can not send check your connection')

    def AutoMail(self):
        import smtplib
        from email.mime.text import MIMEText
        import time
        # 重连计数
        count = 0
        while count < 5:
            try:
                count += 1
                send_mail = '发送邮箱地址@163.com'
                receive_mail = '接收邮箱地址8@qq.com'
                # 构造邮件
                msg = MIMEText('MySql删除程序报错，请检查！\n\n这是系统自动发出邮件，请不要回复。', 'plain', 'utf-8')
                # 发送账号
                msg['From'] = send_mail
                # 接收账号
                msg['To'] = receive_mail
                # 邮件主题
                msg['Subject'] = '您有新的程序报错！'
                # 密码是授权码，授权码一直拿不到垃圾腾讯网易邮箱就可以
                password = '自己的授权码'
                smtp_server = 'smtp.163.com'
                server = smtplib.SMTP_SSL(smtp_server)
                # 显示发送过程
                server.set_debuglevel(0)
                # 登陆验证
                server.login(send_mail, password)
                # 发送邮件
                server.sendmail(send_mail, [receive_mail], msg.as_string())
                # 退出
                server.quit()
                time.sleep(1)
                break
            except:
                print('第%s次邮件重发' % (count))
                time.sleep(1)
        # 重发失败直接报错
        if count == 5:
            raise


class Insert(Process):
    def __init__(self):
        super(Insert, self).__init__()
    def run(self):
        count = 0
        while True:
            # time.sleep(0.1)
            # 连接数据库
            db = pymysql.connections.Connection(
                host='127.0.0.1',  # 要连接的主机地址
                user='root',  # 用于登录的数据库用户
                password='root',  # 
                database='test',  # 要连接的数据库
                port=3306,  # 端口，一般为 3306
            )
            cursor = db.cursor()
            count += 1
            Sql_acc = "insert into detest(Account) values ('%s')" % ('cty')
            cursor.execute(Sql_acc)
            db.commit()
            # 游标未关闭或是造成下述错误的原因
            # pymysql.err.OperationalError: (1135, 'create a new thread (errno 1);
            # if you are not out of available memory, you can consult the manual for a possible OS-dependent bug')
            cursor.close()
            db.close()


if __name__ == '__main__':
    processes = []
    for i in range(0, 40, 1):
        processes.append(Insert())
    [process.start() for process in processes]  # 开启子进程
    print('insert process ok')
    de = Delete()
    de.start()