背景: 自动发送邮件可以将常规化得需求自动化,解放双手做更多有意义的事情
以下即为代码:
# -*- coding: utf-8 -*-
"""
Created on Wed Jul 3 12:22:58 2019
@author: wuxian
"""
import smtplib
import email.mime.multipart
import email.mime.text
import email.mime.base
import os.path
import xlwt
import time
import schedule
import sys
import os
with open(r'.\Desktop\PID.txt','a') as file:
file.write(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())+'\t')
file.write(str(sys.argv[0]).split('\\')[-1]+'\t')
file.write('PID:'+str(os.getpid())+'\t')
file.write('PPID:'+str(os.getppid())+'\n')
#open-'a':写入打开,如果文件存在则添加到尾部
# =============================================================================
#连接数据库
# =============================================================================
import pymysql
def dbreadsql(v_sql):
conn = pymysql.connect(host=None, port=3306,
user='root', passwd='wx123456', db= 'testwuxian',
charset='utf8')
cur = conn.cursor()
cur.execute(v_sql)
rows = cur.fetchall()
cur.close()
conn.close()
return rows
sql0 = "select * from testwuxian.register_info"
#sql1 = "select * from testwuxian.register_detail"
sql2 = "select * from testwuxian.search_info"
sql3 = "select * from testwuxian.search_detail"
rows0 = dbreadsql(sql0)
#rows1 = dbreadsql(sql1)
rows2 = dbreadsql(sql2)
rows3 = dbreadsql(sql3)
# 生成excel文件名
def mailsend(rows0,rows2,rows3):
v_file_name = 'COCOK生产库数据.xls'
# 生成excel文件
book = xlwt.Workbook()#
sheet1 = book.add_sheet('注册人数', cell_overwrite_ok=True)
# sheet2 = book.add_sheet('注册明细', cell_overwrite_ok=True)
sheet3 = book.add_sheet('关键字搜索次数', cell_overwrite_ok=True)
sheet4 = book.add_sheet('关键字搜索明细', cell_overwrite_ok=True)
#表头标题
sheet1.write(0, 0, '注册日期')
sheet1.write(0, 1, '注册人数')
# sheet2.write(0, 0, '注册日期')
# sheet2.write(0, 1, '手机号')
# sheet2.write(0, 2, '设备型号')
# sheet2.write(0, 3, '神策标识码')
sheet3.write(0, 0, '搜索日期')
sheet3.write(0, 1, '搜索总次数')
sheet4.write(0, 0, '搜索日期')
sheet4.write(0, 1, '搜索关键字')
sheet4.write(0, 2, '搜索次数')
# 每一列写入excel文件,不然数据会全在一个单元格中
for i in range(len(rows0)):
for j in range(2):
# print (rows[i][j])-
# print ("--------")
sheet1.write(i + 1, j, rows0[i][j])
# for i in range(len(rows1)):
# for j in range(4):
# # print (rows[i][j])-
# # print ("--------")
# sheet2.write(i + 1, j, rows1[i][j])
for i in range(len(rows2)):
for j in range(2):
# print (rows[i][j])-
# print ("--------")
sheet3.write(i + 1, j, rows2[i][j])
for i in range(len(rows3)):
for j in range(3):
# print (rows[i][j])-
# print ("--------")
sheet4.write(i + 1, j, rows3[i][j])
book.save("C:/Users/wuxian/Desktop/" + v_file_name)
# 邮件信息
From = "发送者邮箱"
To = "收件人邮箱"
Cc = "抄送人邮箱"
file_name = v_file_name
server = smtplib.SMTP_SSL("smtp.aliyun.com",465)
server.login("邮箱", "密码") # 仅smtp服务器需要验证时
# 构造MIMEMultipart对象做为根容器
main_msg = email.mime.multipart.MIMEMultipart()
# 构造MIMEText对象做为邮件显示内容并附加到根容器
text_msg = email.mime.text.MIMEText("Hi All,\n \n 正文部分内容,请查收,谢谢。\n \n Best Wishes!")
main_msg.attach(text_msg)
# 构造MIMEBase对象做为文件附件内容并附加到根容器
maintype,subtype = 'ershoufanglianjia','.xls'
# 读入文件内容并格式化
data = open("C:/Users/wuxian/Desktop/" + file_name, 'rb')
file_msg = email.mime.base.MIMEBase(maintype, subtype)
file_msg.set_payload(data.read())
data.close()
email.encoders.encode_base64(file_msg)
# 设置附件头
basename = os.path.basename("./Desktop/" + file_name)
file_msg.add_header('Content-Disposition',
# 附件如果有中文会出现乱码问题,加入gbk
'attachment', filename=('gbk', '', basename))
main_msg.attach(file_msg)
# 设置根容器属性
main_msg['From'] = From
main_msg['To'] = To
main_msg['Cc'] = Cc
main_msg['Subject'] = "邮件主题"
main_msg['Date'] = email.utils.formatdate()
# 得到格式化后的完整文本
fullText = main_msg.as_string()
# 用smtp发送邮件
try:
server.sendmail(From, To.split(',') + Cc.split(','), fullText)
print("发送成功")
except Exception as e:
print("发送失败")
print(str(e))
finally:
server.quit()
mailsend(rows0,rows2,rows3)
#若是需要定时发送则使用以下代码:
#schedule.every().day.at('8:00').do(mailsend)
#while True:
# schedule.run_pending()
# time.sleep(60)