逻辑比较简单 ，直接上代码
定时发送直接使用了win服务器的定时任务来定时执行脚本
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
#coding:utf-8from __future__ import divisionimport pymssql,sys,datetime,xlwt import smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.header import Header reload(sys)sys.setdefaultencoding("utf-8") class MSSQL: def __init__(self,host,user,pwd,db): self.host = host self.user = user self.pwd = pwd self.db = db def __GetConnect(self): if not self.db: raise(NameError,"") self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8") cur = self.conn.cursor() if not cur: raise(NameError,"") else: return cur def ExecQuery(self,sql): cur = self.__GetConnect() cur.execute(sql) resList = cur.fetchall() # self.conn.close() return resList def ExecNonQuery(self,sql): cur = self.__GetConnect() cur.execute(sql) self.conn.commit() self.conn.close() def write_data_to_excel(self,name,sql): # 将sql作为参数传递调用get_data并将结果赋值给result,(result为一个嵌套元组) result = self.ExecQuery(sql) # 实例化一个Workbook()对象(即excel文件) wbk = xlwt.Workbook() # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。 sheet = wbk.add_sheet('Sheet1',cell_overwrite_ok=True) # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000) today = datetime.date.today() yesterday = today - datetime.timedelta(days=1) # 将获取到的datetime对象仅取日期如：2016-8-9 yesterdaytime = yesterday.strftime("%Y-%m-%d") # 遍历result中的没个元素。 for i in xrange(len(result)): #对result的每个子元素作遍历， for j in xrange(len(result[i])): #将每一行的每个元素按行号i,列号j,写入到excel中。 sheet.write(i,j,result[i][j]) # 以传递的name+当前日期作为excel名称保存。 filename = name+str(yesterdaytime)+'.xls' wbk.save(filename) return filename ms = MSSQL(host="122.229.*.*",user="root",pwd="root",db="test") today = datetime.date.today()yesterday = today - datetime.timedelta(days=1)yesterdayStart = yesterday.strftime("%Y-%m-%d") + ' 00:00:00'yesterdayEnd = yesterday.strftime("%Y-%m-%d") + ' 23:59:59'print yesterdayStartpreCheckCountSuccesSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";preCheckUseridSuccesSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";preCheckCountErrorSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";preCheckUseridErrorSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; orderSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";orderErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";unsubscribeSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";unsubscribeErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; orderKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";unsubscribeKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";preCherkKeyList =['CRM预校验成功单子数量：','CRM预校验成功账号数量：','CRM预校验失败单子数量：','CRM预校验失败账号数量：','订购的订单数 成功:','订购的订单数 失败:','订购卡单数:','退订的订单数 成功:','退订的订单数 失败:','退订卡单数:']preCherkL = {'CRM预校验成功单子数量：' :preCheckCountSuccesSql ,'CRM预校验成功账号数量：' :preCheckUseridSuccesSql ,'CRM预校验失败单子数量：' :preCheckCountErrorSql ,'CRM预校验失败账号数量：' :preCheckUseridErrorSql}preCherkL['订购的订单数 成功:'] = orderSucessCountSqlpreCherkL['订购的订单数 失败:'] = orderErrorCountSqlpreCherkL['订购卡单数:'] = orderKadanSqlpreCherkL['退订的订单数 成功:'] = unsubscribeSucessCountSqlpreCherkL['退订的订单数 失败:'] = unsubscribeErrorCountSqlpreCherkL['退订卡单数:'] = unsubscribeKadanSql mailMessageText ='' for key in preCherkKeyList: reslist = ms.ExecQuery(preCherkL[key]) for i in reslist: for n in i: mailMessageText = mailMessageText + key + bytes(n) + '\n' crmOrderHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";crmunsubscribeHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; crmOrderHandle = ms.ExecQuery(crmOrderHandleTimeSql)orderCount = len(crmOrderHandle)if orderCount != 0: totleTime = 0 for temp in crmOrderHandle: addtime = temp[0] notifytime = temp[1] # adddate = datetime.datetime.strptime(addtime,"%Y-%m-%d %H:%M:%S")# notifydate =datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S") chazhi = (notifytime - addtime).seconds / 60 totleTime = float(totleTime) + float(chazhi) mailMessageText = mailMessageText + '订购平均处理时长:' + bytes(float(totleTime)/orderCount) + '分' + '\n' crmunsubscribeHandle = ms.ExecQuery(crmunsubscribeHandleTimeSql)subscribeCount = len(crmunsubscribeHandle)if subscribeCount != 0: subscribetotleTime = 0 for temp in crmunsubscribeHandle: addtime = temp[0] notifytime = temp[1]# adddate = datetime.datetime.strptime(addtime, "%Y-%m-%d %H:%M:%S")# notifydate = datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S") chazhi = (notifytime - addtime).seconds / 60 subscribetotleTime = float(subscribetotleTime) + float(chazhi) mailMessageText = mailMessageText + '退订平均处理时长:' + bytes(float(subscribetotleTime)/subscribeCount) + '分' + '\n'mailMessageText = mailMessageText + '附件为 ：预校验失败订单，订购/退订失败订单，卡单订单' + '\n' print mailMessageText #生成excel文件 preCheckErrorname = 'preCheckError'preCerroeFile = ms.write_data_to_excel(preCheckErrorname, "select ordercode,userid,productid,action,msg FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'") orderErrorname = 'orderFalse'ordererroeFile = ms.write_data_to_excel(orderErrorname, "select ordercode,userid,productid,action,sg,notifyresult,notifymsg FROM tb_crmorders WHERE type =2 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'") kadanname = 'noSynchMsg'kadanFile = ms.write_data_to_excel(kadanname, "select ordercode,userid,productid,sg,action FROM tb_crmorders WHERE type =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'")# 第三方 SMTP 服务mail_host="###@163.com" #设置服务器mail_user=##" #用户名mail_pass="##" #口令 sender = '###@163.com'receivers = ['##@qq.com'] # 接收邮件，可设置为你的QQ邮箱或者其他邮箱 #创建一个带附件的实例message = MIMEMultipart() message['From'] = Header("测试", 'utf-8')message['To'] = Header(" , ".join(receivers), 'utf-8') subject = 'CRM订单日数据' + yesterday.strftime('%Y-%m-%d')message['Subject'] = Header(subject, 'utf-8') #邮件正文内容message.attach(MIMEText(mailMessageText, 'plain', 'utf-8'))#设置邮件名片（html格式）# html = file('qianming.html').read().decode("utf-8")# message.attach(MIMEText(html, 'html', 'utf-8')) # 构造附件1，传送当前目录下的preCerroeFile 文件att1 = MIMEText(open(preCerroeFile, 'rb').read(), 'base64', 'utf-8')att1["Content-Type"] = 'application/octet-stream'# 这里的filename可以任意写，写什么名字，邮件中显示什么名字att1["Content-Disposition"] = 'attachment; filename=' + preCerroeFilemessage.attach(att1) att2 = MIMEText(open(ordererroeFile, 'rb').read(), 'base64', 'utf-8')att2["Content-Type"] = 'application/octet-stream'att2["Content-Disposition"] = 'attachment; filename='+ordererroeFilemessage.attach(att2) att3 = MIMEText(open(kadanFile, 'rb').read(), 'base64', 'utf-8')att3["Content-Type"] = 'application/octet-stream'att3["Content-Disposition"] = 'attachment; filename='+kadanFilemessage.attach(att3) try: smtpObj = smtplib.SMTP() smtpObj.connect(mail_host, 25) # 25 为 SMTP 端口号 smtpObj.login(mail_user,mail_pass) smtpObj.sendmail(sender, receivers, message.as_string()) print "邮件发送成功"except smtplib.SMTPException,e: print "Error: 无法发送邮件" + repr(e) |
以上这篇python生成每日报表数据(Excel)并邮件发送的实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/ymlkl/article/details/75126374
