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()
07-24
1130
1130
03-15
2285
2285
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
