定时发送邮件的任务
-- coding:utf-8 --
import sys
import string, os, sys
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import pymysql as mysql
import datetime
import schedule
import time
from datetime import datetime, date, timedelta
reload(sys)
sys.setdefaultencoding('utf8')
todo presto config
presto_host_test = "localhost"
presto_port = 3306
处理日期
yesterday 表示昨天，例如：2020-03-09
yesterday = date.today() + timedelta(days = -1)
year = str(yesterday)[0:4]
def get_month(name):
if str(name)[5:6] == '0':return str(name)[6:7]
else: return str(name)[5:7]
def get_date(name):
if str(name)[9:10] == '0':return str(name)[10:11]
else: return str(name)[9:11]
todo email config
todo 表头 需要修改
email_table_head1 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"昨日涨幅",u"时间" ]
email_table_head2 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]
email_table_head3 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]
email_table_head4 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]
email_table_head5 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]
todo 表title 需要修改
email_table_title1 = u"%s %s %s %s %s %s %s %s"%("一、",year,"年",get_month(yesterday),"月",
get_date(yesterday),"日","天天基金概括")
email_table_title2 = u"二、天天基金历史平均增幅最高"
email_table_title3 = u"三、过去三十天基金平均增幅最高"
email_table_title4 = u"四、过去三十天基金平均基金净值最高"
email_table_title5 = u"五、过去三十天基金平均累计净值最高"
todo 邮件名称 需要修改
email_title = u"天天基金净值概况"
todo 邮件发送列表 需要修改
email_to_list = [['flyba049@126.com']] # 接受人
def get_coums(name):
for x in email_to_list:
return
todo execute sql 需要修改
sql_1 = "select * from fund_db.flyba_yestoday_rate"
sql_2 = "select * from fund_db.flyba_history_rate"
sql_3 = "select * from fund_db.flyba_max_rate30"
sql_4 = "select * from fund_db.flyba_max_net30"
sql_5 = "select * from fund_db.flyba_max_total30"
def send_mail(mailto_list, sub, context):
# todo to_list：收件人；sub：主题；content：邮件内容
mail_host = "imap.exmail.qq.com"
mail_user = "邮箱" #选择发送邮件的邮箱
mail_pass = "密码" #选择发送邮件的邮箱
me = mail_user
msg = MIMEMultipart()
msg['Subject'] = sub
msg['From'] = me
msg['To'] = ";".join(mailto_list)
msg.attach(context)
s = smtplib.SMTP()
s.connect(mail_host)
s.login(mail_user, mail_pass)
s.sendmail(me, mailto_list, msg.as_string())
s.close()
def make_html_table(title, rows):
if title:
html_content = u"""<h3>%s</h3>""" %title
else:
html_content = u""""""
html_content += u"""<table border="1">"""
for row in rows:
html_content += u"""<tr>"""
for elem in row:
html_content += u"""<td>%s</td>""" % elem
html_content += u"""</tr>"""
html_content += u"""</table>"""
return html_content
def get_data_from_presto(host, port, sql, email_table_head):
rows = []
# todo 建立链接
cursor = mysql.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='fund_db').cursor()
# todo 执行sql
cursor.execute(sql)
# todo 一行一行取数
for line in cursor.fetchall():
if line[0] is None:
rows.append(email_table_head)
else:
rows.append(line)
cursor.close()
# todo 返回列表
return rows
if name == 'main':
# todo 查到表内容
rows_context_1 = get_data_from_presto(presto_host_test, presto_port, sql_1,
email_table_head1)
rows_context_2 = get_data_from_presto(presto_host_test, presto_port, sql_2,
email_table_head2)
rows_context_3 = get_data_from_presto(presto_host_test, presto_port, sql_3,
email_table_head3)
rows_context_4 = get_data_from_presto(presto_host_test, presto_port, sql_4,
email_table_head4)
rows_context_5 = get_data_from_presto(presto_host_test, presto_port, sql_5,
email_table_head5)
# todo 加入表头
rows_context_1.insert(0, email_table_head1)
rows_context_2.insert(0, email_table_head2)
rows_context_3.insert(0, email_table_head3)
rows_context_4.insert(0, email_table_head4)
rows_context_5.insert(0, email_table_head5)
# todo 制作表格
html_content_1 = make_html_table( email_table_title1, rows_context_1)
html_content_2 = make_html_table( email_table_title2, rows_context_2)
html_content_3 = make_html_table( email_table_title3, rows_context_3)
html_content_4 = make_html_table( email_table_title4, rows_context_4)
html_content_5 = make_html_table( email_table_title5, rows_context_5)
# todo 发送邮件
for name_list in email_to_list:
def get_name(name):
if name == ['邮箱']:return '名字'
else: return 'Jack Yangon'
email_to_total = u"\n%s %s\n %s\n" % ("\n尊敬的",get_name(name_list),"\n：")
# 表示一个空格
mail_msg = '''
<p>    您好，请查收如下天天基金数据，谢谢！</p>
'''
# todo 格式化
context = MIMEText(email_to_total+mail_msg+
html_content_1 + html_content_2 + html_content_3 + html_content_4
+ html_content_5 ,
_subtype='html', _charset='utf-8')
send_mail(name_list, email_title,context)
print("send the email success")