python收集数据发邮件_python查数据库发邮件

有时候需要将数据库中的统计信息统计好之后，直接每天定时发邮件。

可用python脚本写好逻辑后，使用crontab每天定时执行

# -*- coding: UTF-8 -*-

import MySQLdb

import smtplib

from email.MIMEMultipart import MIMEMultipart

from email.MIMEText import MIMEText

def queryDBdata():

#连数据库

conn = MySQLdb.connect(host='****', user='***', passwd='****', db='***', port=3307, charset='utf8')

querySQL="select sum(num),date from (SELECT count(e.order_num) num, date_format(r.order_date,'%Y-%m-%d') date from orders_extension e INNER JOIN commission_order r ON e.order_num=r.order_no where e.ex_cookie like '%bd%' and e.biz_type='COMMISSION' and date_format(r.order_date,'%Y-%m')=date_format(NOW(),'%Y-%m') group by date_format(r.order_date,'%Y-%m-%d') union ALL SELECT count(e.order_num) num, date_format(r.order_date,'%Y-%m-%d') date from orders_extension e INNER JOIN order_info r ON e.order_num=r.order_num where e.ex_cookie like '%bd%' and e.biz_type='PPB' and date_format(r.order_date,'%Y-%m')=date_format(NOW(),'%Y-%m') group by date_format(r.order_date,'%Y-%m-%d') ) a group by date"

cur = conn.cursor()

cur.execute("set names 'utf8'")

cur.execute(querySQL)

#获取数据

data="每天晚上九点自动发统计邮件\n\n"+"| 总订单数 | 日期 |"+"\n"

results = cur.fetchall()

for r in results:

data=data+"| "+str(r[0])+" "+"| "+str(r[1])+" |\n"

return data

def send_mail(content):

try:

mailToList=['yebing.li@163.com',"erte@163.com"]

sender='yebing.li@163.com'

mailto=",".join(mailToList)

msg = MIMEMultipart()

msg['Subject'] = "本月****来源的订单"

msg['to'] = mailto

msg['From'] = sender

body = MIMEText(content)

msg.attach(body)

smtp = smtplib.SMTP('mail.163.com')

smtp.sendmail(sender,mailToList,msg.as_string())

smtp.quit()

except smtplib.SMTPException, e:

print "error...%d: %s" % (e.args[0], e.args[1])

if __name__=="__main__":

content=queryDBdata()

send_mail(content)

其中crontab  -e 编辑表达式