最近在工作时，需要实现在公司开发的调度系统上实现：将每天产生的游戏数据查询统计出，并发送邮件给数据分析的开发人员。
考虑到python语言的简洁易上手，在我没有任何python基础的情况下，十分轻松的完成了这个脚本功能。
下面放出代码，展示了通过python发送html邮件的模板：
1.生成数据：
1 #!/usr/bin/env python 2 # -*-coding: UTF-8 -*- 3 import MySQLdb 4 import sys 5 6 from constant import DB_PARAMS_91 7 from db_utils import new_execsqlr 8 9 10 def query_data(): 11 try: 12 sql = 13 result = new_execsqlr(sql, DB_PARAMS_91) #这里调用的是公司内部封的数据库查询模块，内部用的是MySQLdb 14 print result 15 return [x.split(" ") for x in filter(is_warning_column, result)] 16 except MySQLdb.Error, e: 17 print "Mysql Error %d: %s" % (e.args[0], e.args[1]) 18 sys.exit(1) 19 20 21 # 过滤结果中的信息 22 def is_warning_column(x): 23 return ("mysql" not in x) and ("ds" not in x) and ("Warning" not in x)
2.拼接html模板（注意html的样式最好只写在标签内，大部分的邮箱都不支持外联css样式，而且js脚本也不会起作用）：
1 #!/usr/bin/env python 2 # encoding:utf-8 3 import sys 4 5 sys.path.append("..") 6 sys.path.append("../..") 7 sys.path.append("../../..") 8 sys.path.append("../../../..") 9 from consume_data import query_data 10 11 12 # 将sql语句拼接到html中：注意样式只能嵌套在标签中，不能写在head和css中 13 def get_html_msg(): 14 result = query_data() 15 head = """<head><meta charset="utf-8"> 16 </head>""" 17 18 th = """<body>"""""" 19 <div class="container" style = "margin-left:calc(50% - 372px);"> 20 <table border="1" style = "text-align: center;border-collapse:collapse;"> 21 <tbody> 22 <tr bgcolor = "#E3E3E3"> 23 <th colspan="11" style = " padding: 2px 5px; font-size: 10px;">广告推广每日监控报表邮件</th> 24 </tr> 25 <tr bgcolor = "#E3E3E3"> 26 <th rowspan="2" style = " padding: 2px 5px; font-size: 10px;">date</th> 27 <th colspan="5" style = " padding: 2px 5px; font-size: 10px;">iOS</th> 28 <th colspan="5" style = " padding: 2px 5px; font-size: 10px;">Android</th> 29 </tr> 30 <tr bgcolor = "#E3E3E3"> 31 <th style = " padding: 2px 5px; font-size: 10px;">点击PV</th> 32 <th style = " padding: 2px 5px; font-size: 10px;">当日激活</th> 33 <th style = " padding: 2px 5px; font-size: 10px;">当日注册</th> 34 <th style = " padding: 2px 5px; font-size: 10px;">当日创角</th> 35 <th style = " padding: 2px 5px; font-size: 10px;">当日付费</th> 36 <th style = " padding: 2px 5px; font-size: 10px;">点击PV</th> 37 <th style = " padding: 2px 5px; font-size: 10px;">APP当日激活</th> 38 <th style = " padding: 2px 5px; font-size: 10px;">SDK当日激活</th> 39 <th style = " padding: 2px 5px; font-size: 10px;">当日注册</th> 40 <th style = " padding: 2px 5px; font-size: 10px;">当日付费</th> 41 </tr>""" 42 tr = '' 43 for row in result: 44 td = '' 45 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[0] + '</td>' 46 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[1] + '</td>' 47 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[2] + '</td>' 48 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[3] + '</td>' 49 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[4] + '</td>' 50 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[5] + '</td>' 51 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[6] + '</td>' 52 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[7] + '</td>' 53 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[8] + '</td>' 54 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[9] + '</td>' 55 td = td + '<td style = " padding: 2px 5px; font-size: 10px;">' + row[10] + '</td>' 56 tr = tr + '<tr>' + td + '</tr>' 57 tr = tr.encode('utf-8') 58 body = tr 59 tail = '</tbody></table></div></body></html>' 60 # 将内容拼接成完整的HTML文档 61 html = head + th + body + tail 62 return html
3.发送html邮件：
1 #!/usr/bin/env python 2 # encoding:utf-8 3 import smtplib 4 import sys 5 6 sys.path.append("..") 7 sys.path.append("../..") 8 sys.path.append("../../..") 9 sys.path.append("../../../..") 10 from email.mime.text import MIMEText 11 from email.mime.multipart import MIMEMultipart 12 13 from SMTPConstant import mail_host, sender, receivers, mail_port 14 from produce_htmltemplate import get_html_msg 15 16 17 # 邮件发送 18 def send_mail(html_msg, mail_host, mail_port, sender, receivers): # recivers定义了一个数组 19 msg = MIMEMultipart() 20 content = MIMEText(html_msg, 'html', _charset='utf8') 21 msg.attach(content) 22 msg['To'] = ";".join(receivers) 23 msg['From'] = sender 24 msg['Subject'] = "。。。。。" 25 s = smtplib.SMTP(mail_host, mail_port) 26 s.sendmail(sender, receivers, msg.as_string()) 27 s.quit() 28 print " send ok！" 29 30 31 # test 32 if __name__ == "__main__": 33 html = get_html_msg() 34 send_mail(html, mail_host, mail_port, sender, receivers)
如果使用的qq邮箱，则需要先登录自己的qq邮箱，自己的邮箱需要开通pop3服务，登录时的账号密码填写qq提供的授权码。