python定时导出已发送文件_python定时发送数据库备份文件到邮箱

下面的代码能实现基本的功能。有一些参数需要改成自己的，比如数据库连接参数和邮箱相关的参数。

#定时相关

import datetime

import schedule #定时器

import threading#多线程

#数据库相关

import time

import csv

import codecs

import pymysql#mysql

#邮件相关

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

import smtplib

import os,sys

Last_backup_date=datetime.datetime.now()+datetime.timedelta(days=-1);#最后备份日期(初始值为当前时间减去1天)

#time_array=[13,20]#每天的备份时间(24小时制，例：下午1点30分备份【time_array=[13,30]】)

backups=True#可以备份

tim=100#循环的时间

sql='select * from ?'

mail_from='**********@qq.com' #发送方邮箱

mail_pass='**********' #填入发送方邮箱的授权码

to_list='**********@qq.com' #收件人邮箱

def sendmail_backups(mail_from,to_list,subject,content,filename):#发件人，收件人，主题，正文，附件

msg = MIMEMultipart('related') ##采用related定义内嵌资源的邮件体

msgtext = MIMEText(content,_subtype='plain',_charset='utf-8') ##_subtype有plain,html等格式，避免使用错误

msg.attach(msgtext)

attach = MIMEText(open(filename,'rb').read(), 'base64', 'utf-8')

attach["Content-Type"] = 'application/octet-stream'

attach.add_header("Content-Disposition", "attachment", filename=("gbk", "", filename))

msg.attach(attach)

msg['Subject'] = subject

msg['From'] = mail_from

msg['To'] = to_list

return msg

def sendMail(msg):

try:

server = smtplib.SMTP_SSL("smtp.qq.com",465)

print ('连接邮箱服务器成功')

server.login(mail_from, mail_pass)

print ('登陆成功')

print ('发送邮件中')

server.sendmail(mail_from, msg['To'], msg.as_string())

server.quit() ##断开smtp连接

print ("邮件发送成功")

except Exception.e:

print ("失败"+str(e))

def get_conn():

conn = pymysql.connect(host='数据库主机',port=端口,user='用户名',password='密码',db='数据库',charset='utf8')

return conn

def query_all(cur,sql,args):#执行sql

cur.execute(sql,args)

return cur.fetchall()

def read_mysql_to_csv(filename):#获取备份CSV文件

print(filename)

with codecs.open(filename=filename,mode='w',encoding='utf-8') as f:

write=csv.writer(f,dialect='excel')

conn=get_conn()

cur=conn.cursor()

results=query_all(cur = cur, sql=sql, args=None)

for result in results:

#print(result)

write.writerow(result)

msg=sendmail_backups(mail_from,to_list,str(datetime.datetime.now().date())+'【备份邮件】','这是'+sql+'语句的数据库备份文件',filename)

sendMail(msg)

def job():#备份任务

global backups

global Last_backup_date

backups=False#锁定备份

sname=str(datetime.datetime.now().date())+'.csv'

read_mysql_to_csv(sname)

print(str(datetime.datetime.now())+' '+sname+' 备份成功！')

Last_backup_date=datetime.datetime.now()

backups=True#解锁备份

def job_task():

if Last_backup_date.date()!=datetime.datetime.now().date():#最后备份日期不是今天

if backups:

threading.Thread(target=job).start()

else:

print('正在执行备份，备份任务锁定中！')

else:

print('今天已备份，无需再次备份！')

def run():

job_task()

schedule.every(tim).seconds.do(job_task)

while True:

schedule.run_pending()

if __name__ == '__main__':

print('程序运行时间:'+str(datetime.datetime.now()))

run()

效果图

更新：刚刚发现上面写的有问题，会大量占用cpu资源，导致系统卡顿。修改成下面的，上面的代码我也就不删了，供其它人参考。

def run():

while True:

job_task()

time.sleep(tim)