使用python解决烦人的每周邮件汇总！

　　最近开始接手BI工作，其中又一个繁琐又不得不做的事，就是每周五都得汇总上个财务周的数据给运营人员！

　　作为一个懒人，只能把这件事交由电脑去处理了。

　　初步的idea：周五11点前mac自动执行汇总程序－>读取数据库数据－>写入模版汇总excel->以邮件的形式发送给相关运营人员。

　　为了实现此idea并不考虑性能的情况下，选择了实用python这种简单且代码量少的方式。

　　首先先实现读取数据库数据部分

　　1.python_constant.py（常量部分，肯定要屏蔽具体信息啦，不然被公司投诉了！）

 1 # -*- coding: utf-8 -*- 
 2 
 3 
 4 config_b2b = {
 5     'user':'sqlbidev',
 6     'password':'xxx',
 7     'host':'xxx',
 8     'database':'ODS_B2B'
 9 }
10 
11 config_b2c = {
12     'user':'sqlbidev',
13     'password':'xxx',
14     'host':'xxx',
15     'database':'ODS_B2C'
16 }
17 
18 config_o2o = {
19     'user':'sqlbidev',
20     'password':'xxx',
21     'host':'xxx',
22     'database':'ODS_O2O'
23 }
24 
25 data_b2c = {
26     '东北区域':'C6',
27     '海南区域':'C7',
28     '华北区域':'C8',
29     '华东区域':'C9',
30     '华南区域':'C10',
31     '山东区域':'C11',
32     '西北区域':'C12',
33     '中南区域':'C13'
34 }
35 
36 data_o2o = {
37     '东北区域':'D6',
38     '海南区域':'D7',
39     '华北区域':'D8',
40     '华东区域':'D9',
41     '华南区域':'D10',
42     '山东区域':'D11',
43     '西北区域':'D12',
44     '中南区域':'D13'
45 }
46 
47 data_b2b = {
48     '东北区域':'E6',
49     '海南区域':'E7',
50     '华北区域':'E8',
51     '华东区域':'E9',
52     '华南区域':'E10',
53     '山东区域':'E11',
54     '西北区域':'E12',
55     '中南区域':'E13'
56 }

　　2. python_query.py（查询数据，由于查三个库的数据，所以特定三个方法，简单实用！）

 1 # -*- coding: utf-8 -*- 
 2 import pymssql
 3 import python_constant as constant
 4 
 5 #读取b2c的会员数据
 6 def query_b2c(start_time, end_time):
 7     print('开始查询b2c数据')
 8     conn = pymssql.connect(**constant.config_b2c)
 9     cursor = conn.cursor(as_dict=True)
10 
11     sql = "select ds.FirstLevelRegion, COUNT(distinct consignee_mobile) ActiveMem from ( 
12          select * from openquery(B2C,'SELECT ord.* FROM tbl_order ord, tbl_seller ts 
13         WHERE ord.seller_no = ts.id AND ord.basic_state <> 404 AND DATE_FORMAT(ord.create_time, ''%Y%m%d'')  between ''{0}'' and ''{1}'' ') ) ord 
14         inner join Dim_Store ds on ord.seller_no = ds.StoreId group by ds.FirstLevelRegion".format(start_time, end_time)
15     cursor.execute(sql)
16     result = [row for row in cursor]
17     conn.close()
18     print('结束查询b2c数据')
19     return result
20 
21 #读取o2o的会员数据
22 def query_o2o(start_time, end_time):
23     print('开始查询o2o数据')
24     conn = pymssql.connect(**constant.config_o2o)
25     cursor = conn.cursor(as_dict=True)
26 
27     sql = "select ds.FirstLevelRegion, COUNT(distinct buyer_phone) ActiveMem from ( 
28          select * from openquery(O2O, 'SELECT tod.* FROM tbl_order tod,tbl_outlet_basic_info tobi WHERE 1 = 1 AND tod.outlet_id = tobi.outlet_id 
29         AND DATE_FORMAT(tod.create_time, ''%Y%m%d'')  between ''{0}'' and ''{1}'' ') ) ord  
30         inner join Dim_Store ds on ord.outlet_id = ds.StoreId group by ds.FirstLevelRegion".format(start_time, end_time)
31     cursor.execute(sql)
32     result = [row for row in cursor]
33     conn.close()
34     print('结束查询o2o数据')
35     return result
36 
37 #读取b2b的会员数据
38 def query_b2b(start_time, end_time):
39     print('开始查询b2b数据')
40 
41     conn = pymssql.connect(**constant.config_b2b)
42     cursor = conn.cursor(as_dict=True)
43 
44     sql = "select ds.FirstLevelRegion, COUNT(distinct contact_mobile) ActiveMem from ( 
45          select * from openquery(B2B, 'SELECT * FROM order_info tod
46          where DATE_FORMAT(tod.create_time, ''%Y%m%d'')  between ''{0}'' and ''{1}'' ') ) ord  
47         inner join Dim_Store ds on ord.supply_user_code = ds.StoreId 
48         group by ds.FirstLevelRegion".format(start_time, end_time)
49     cursor.execute(sql)
50     result = [row for row in cursor]
51     conn.close()
52     print('结束查询b2b数据')
53     return result

　　3. 实现发送email邮件

 1 #!/usr/bin/env python3  
 2 #coding: utf-8  
 3 import smtplib
 4 from email.mime.multipart import MIMEMultipart 
 5 from email.mime.text import MIMEText  
 6 from email.header import Header  
 7   
 8 sender = 'll-chen6@hnair.com'  
 9 receiver = 'chenqianyun@ccoop.com.cn'  
10 subject = '每周会员统计数'  
11 smtpserver = 'smtp.hnair.com'  
12 username = 'xxxx'  
13 password = 'xxxx'  
14 
15 def send(path):
16     msg = MIMEText('<html><h1>倩云，你好！汇总数据请查收附件</h1></html>','html','utf-8')  
17 
18     msgRoot = MIMEMultipart('related')
19     msgRoot['Subject'] = Header(subject, 'utf-8')
20 
21     title = '每周会员统计数.xlsx'.decode('utf-8')
22 
23     #构造附件  
24     att = MIMEText(open(path, 'rb').read(), 'base64', 'utf-8')  
25     att["Content-Type"] = 'application/octet-stream'  
26     att["Content-Disposition"] = 'attachment; filename=%s' %title.encode('gb2312')
27 
28     msgRoot.attach(msg)
29     msgRoot.attach(att) 
30       
31     smtp = smtplib.SMTP()  
32     smtp.connect(smtpserver)  
33     smtp.login(username, password)  
34     smtp.sendmail(sender, receiver, msgRoot.as_string())
35     smtp.quit()

　　5. 主体调用

 1 # -*- coding: utf-8 -*- 
 2 from openpyxl import Workbook
 3 from openpyxl import load_workbook
 4 import python_query as query
 5 import python_email as email
 6 import time
 7 import datetime    
 8 import python_constant as constant
 9 
10 
11 #date_time = datetime.datetime.now()
12 date_time = datetime.datetime(2017, 2, 28)
13 dayOfWeek = date_time.weekday()
14 #假如是星期五则执行查询
15 if dayOfWeek == 4:
16     start_date = date_time + datetime.timedelta(-27)
17     end_time = date_time.strftime("%Y%m%d")
18     start_time = start_date.strftime("%Y%m%d")
19     print "开始时间：{0}, 结束时间：{1}".format(start_time, end_time)
20     #获取当前月份
21     month = date_time.month
22     #获取统计周
23     query_day = start_date.strftime("%m.%d") + '-' + date_time.strftime("%m.%d")
24     wb = load_workbook('/Users/chenlili/Desktop/BI立项/汇报数据模版/第3周活跃会员数.xlsx')
25     sheet = wb.get_sheet_by_name(u'Sheet1')
26     sheet['B2'] = str(month) + '月'
27     sheet['B4'] = query_day
28     
29     #填充b2c数据
30     b2c_result = query.query_b2c(start_time, end_time)
31     if b2c_result:
32         for data in b2c_result:
33             first_level_region = data['FirstLevelRegion'].encode('utf-8')
34             row = constant.data_b2c[first_level_region]
35             print first_level_region + ', ' + str(data['ActiveMem'])
36             if row:
37                 sheet[row] = data['ActiveMem']
38     #填充o2o数据
39     o2o_result = query.query_o2o(start_time, end_time)
40     if o2o_result:
41         for data in o2o_result:
42             first_level_region = data['FirstLevelRegion'].encode('utf-8')
43             row = constant.data_o2o[first_level_region]
44             print first_level_region + ', ' + str(data['ActiveMem'])
45             if row:
46                 sheet[row] = data['ActiveMem']
47 
48     #填充b2b数据
49     try:
50         b2b_result = query.query_b2b(start_time, end_time)
51         if b2b_result:
52             for data in b2b_result:
53                 first_level_region = data['FirstLevelRegion'].encode('utf-8')
54                 row = constant.data_b2b[first_level_region]
55                 print first_level_region + ', ' + str(data['ActiveMem'])
56                 if row:
57                     sheet[row] = data['ActiveMem']
58     except Exception, e:
59         print '异常错误: ' + str(e)
60     else:
61         pass
62     finally:
63         print '解除异常继续执行'
64 
65     path = '/Users/chenlili/Desktop/BI立项/汇报数据模版/第3周活跃会员数副本.xlsx'
66     wb.save(path)
67 
68     #发送邮件
69     email.send(path)
70 else:
71     print "未到星期五！"

　　完结，提供github上的代码：friday_task，暂时未实现自动执行脚本的shell，还在想怎么实现。

　　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/cl2Blogs/p/6484209.html