python练手_邮件定时发送

#加载模块
import pymysql
import time
import smtplib #发送邮件模块
from email.mime.text import MIMEText #定义邮件内容
from email.mime.multipart import MIMEMultipart #用于传送附件
from multiprocessing import Pool #进程模块

import pyttsx3 #文字语音提示模块

#数据库函数
def mysql_execute(in_sql, leixing):
# 登录数据库
conn = pymysql.connect(host=‘127.0.0.1’, port=3306, user=‘szc’, password=‘szcNSP850219’, database=‘szc_sql’,charset=‘utf8’)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 数据库执行导入的语句
if leixing == ‘数量’:
# 反馈数量
count = cursor.execute(in_sql)
elif leixing == ‘单条’:
# 反馈单条
cursor.execute(in_sql)
count = cursor.fetchone()[0]
elif leixing == ‘多条’:
# 反馈多条
cursor.execute(in_sql)
count = cursor.fetchall()
elif leixing == ‘编辑’:
count = cursor.execute(in_sql)
conn.commit()
# 关闭光标对象
cursor.close()
# 关闭数据库连接
conn.close()
# 反馈
return count

#昨天时间计算
def get_yesterday_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time()-246060
local_time = time.localtime(ct)
data_head = time.strftime("%Y%m%d%H%M%S", local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’

return time_stamp

#今天时间计算
def get_today_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time()
local_time = time.localtime(ct)
data_head = time.strftime("%Y%m%d%H%M%S", local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’

return time_stamp

#邮件推送
def email_out(user,password,smtpserver,sender,receives,title,content,send_file,input_date):
‘’’
:param user: 发送邮箱用户名
:param password:发送邮箱密码
:param smtpserver:发送服务器
:param sender:发送方邮箱
:param receives:接收方邮箱
:param title:邮件标题
:param content:邮件正文
:param send_file:邮件附件
:param input_date:发送时间，8位长度的yyyymmdd格式
:return:
‘’’

in_time = get_yesterday_time(input_date)
# 发送邮件主题和内容
subject = title + '[%s]' % in_time[0:8]
content = content
# 构造附件内容：定义附件，构造附件内容
send_file_path = r".\send_file\%s" % send_file
send_file = open(send_file_path, 'rb').read()  # 'rb'表示r读取，b表示二进制方式读取
att = MIMEText(send_file, 'base64', 'utf-8')  # 调用传送附件模块，传送附件
att["Content-Type"] = 'application/octet-stream'
att["Content-Disposition"] = 'attachment;filename="appendix.xlsx"'  # 附件描述外层要用单引号
# 构建发送与接收信息
msgRoot = MIMEMultipart()  # 发送附件的方法定义为一个变量
msgRoot.attach(MIMEText(content, 'html', 'utf-8'))  # 发送附件的方法中嵌套发送正文的方法
msgRoot['subject'] = subject
msgRoot['From'] = sender
#msgRoot['From'] ='13605330822@139.com' #可以设置接收者看到的发送人邮箱
msgRoot['To'] = ','.join(receives)
msgRoot.attach(att)  # 添加附件到正文中
# SSL协议端口号要使用465
smtp = smtplib.SMTP_SSL(smtpserver, 465)
# HELO 向服务器标识用户身份
smtp.helo(smtpserver)
# 服务器返回结果确认
smtp.ehlo(smtpserver)
# 登录邮箱服务器用户名和密码
smtp.login(user, password)
#print("Start send email...")
smtp.sendmail(sender, receives, msgRoot.as_string())
smtp.quit()
return "发送成功"

#获取数据库内邮箱信息
def email_user():
sql = ‘SELECT userid,password,email_server,email_post,smtpserver FROM email_user_type where status=“1”’
num = mysql_execute(sql, ‘多条’)
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
return {‘user’:num_cf[0],‘password’:num_cf[1],‘email_server’:num_cf[2],‘email_post’:num_cf[3],‘smtpserver’:num_cf[4]}

#获取邮件任务数据
def email_order():
num_zidian=[]
sql = ‘SELECT id,email_name,email_note,order_time,fujian_name FROM email_order_biaoti where status=“1”’
num_list = mysql_execute(sql, ‘多条’)
for num in num_list:
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
num_zidian.append({‘id’:num_cf[0],‘email_name’:num_cf[1],‘email_note’:num_cf[2],‘order_time’:num_cf[3],‘fujian_name’:num_cf[4]})
return num_zidian

#获取邮件当天是否已发送完成
def email_confirm(id,in_time):
sql = ‘SELECT count(id) FROM email_order_confirm where status=“1” and id="%s" and in_time="%s"’ % (id,in_time)
num = mysql_execute(sql, ‘单条’)
return num

#y邮件发送失败记录
def email_faillog(id,in_time,op_time,status,notes):
sql = “insert into email_order_faillog (id,in_time,createdate,status,notes) values(%d,’%s’,’%s’,’%s’,’%s’)” % (
int(id), in_time, op_time, status,notes)
mysql_execute(sql, ‘编辑’)
return ‘记录完成’

#邮件发送增加收件人
#user,password,smtpserver,sender,receives,title,content,send_file,input_date

def email_sending(id,user,password,smtpserver,sender,title,content,send_file,input_date):
# 当前系统时间
in_time = get_today_time()
# 语音提示时间
yy_time = in_time[0:4] + ‘年’ + in_time[4:6] + ‘月’ + in_time[6:8] + ‘日’ + in_time[8:10] + ‘点’ + in_time[10:12] + ‘分’
num_lists = []
sql = ‘SELECT email_id FROM email_order_chengyuan where status=“1” and id="%s"’ % id
num_list = mysql_execute(sql, ‘多条’)
print(num_list)
for num in num_list:
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
num_cf.pop()
num_lists = num_lists + num_cf
#如果收件人邮箱大于1才会触发下面数据
if len(num_lists) >=1:
try:
email_out_status = email_out(user,password,smtpserver,sender,num_lists,title,content,send_file,input_date)
if email_out_status == ‘发送成功’:
#语音提示
pyttsx3_yy(‘编号%s：%s。邮件发送成功，时间：%s 播报完毕’ % (id,title,yy_time))
print(id,input_date,in_time[0:12],‘1’)
sql = “insert into email_order_confirm (id,in_time,createdate,status) values(%d,’%s’,’%s’,’%s’)” % (
int(id),input_date,in_time[0:12],‘1’)
mysql_execute(sql, ‘编辑’)
print(‘id:’,id,‘发送完成’)
else:
faillog = email_faillog(id,input_date,in_time[0:12],‘1’,‘发送失败请核实’)
print(‘id:’, id, ‘发送失败请核实’,faillog)
except:
faillog = email_faillog(id, input_date, in_time[0:12], ‘1’, ‘发送失败请核实附件是否正常’)
print(‘id:’, id, ‘发送失败请核实附件是否正常’,faillog)
else:
faillog = email_faillog(id, input_date, in_time[0:12], ‘1’, ‘无接收人请核实’)
print(‘id:’, id, ‘无接收人请核实’,faillog)

#语音提示
def pyttsx3_yy(msg):
engine = pyttsx3.init()
engine.setProperty(‘rate’, 150) #语音速度
text = str(msg) #文字内容
engine.say(text) #朗读内容
engine.runAndWait()
return ‘语音提示完成’

if name == ‘main’:

#程序运行起5个进程
p = Pool(5)
# p.apply_async(task,args=(1,)) 其中 task 是调用的函数名

# 循环执行
while True:
    #第一步获取正常登陆的邮箱名称等信息
    email_user_zd = email_user()
    print(email_user_zd)
    user = email_user_zd['user']
    password = email_user_zd['password']
    email_server = email_user_zd['email_server']
    email_post = email_user_zd['email_post']
    smtpserver = email_user_zd['smtpserver']

    #当前系统时间
    in_time = get_today_time()
    yesterday_time = get_yesterday_time()


    #邮件工单触发列表
    email_trigger=[]
    #邮件工单任务遍历
    email_order_list = email_order()
    for email_order_id in email_order_list:
        #print('工单id:',email_order_id['id'],'email_name:',email_order_id['email_name'],'email_note:',email_order_id['email_note'],'order_time:',email_order_id['order_time'],'fujian_name:',email_order_id['fujian_name'])
        #判断邮件ID当天是否发送过，没有发送过的进入 邮件工单触发列表
        email_ok = email_confirm(email_order_id['id'],yesterday_time[0:8])
        #如果当天没有发送记录，并且发送时间在定制时间就发送
        if email_ok==0 and email_order_id['order_time']<=in_time[8:12]:
            print('工单未处理','工单id:',email_order_id['id'])
            email_trigger.append(email_order_id)
            p.apply_async(email_sending, args=(email_order_id['id'],user,password,smtpserver,user,email_order_id['email_name'],email_order_id['email_note'],email_order_id['fujian_name'],yesterday_time[0:8]))
    #邮件等待发送记录
    if len(email_trigger)==0:
        print('当前时间 %s ,无等待发送邮件任务' % in_time[0:12])

    time.sleep(60)
p.close()
p.join()