需求就是統(tǒng)計每天客戶端開機數(shù)據(jù)捶朵，將開機率低于30%的數(shù)據(jù)導(dǎo)出室谚，每天發(fā)送郵件給相關(guān)業(yè)務(wù)人員。
- 查詢數(shù)據(jù)
- 整理excel
- 發(fā)送郵件
一闺骚、數(shù)據(jù)查詢方法（此項可根據(jù)實際業(yè)務(wù)需求更改）
def getUpRate(project_id):
val = str(1)
sql = "select rateNum from tablename where project_id=" + str(project_id)
cursor.execute(sql)
rateNum = cursor.fetchone()
if type(rateNum) != tuple :
insert_sql = "insert into tablename (project_id,rateNum) values('"+ str(project_id) + "','" + val + "')"
cursor.execute(insert_sql)
db.commit()
return val
else:
i = rateNum[0]+1
update_sql = "update tablename set rateNum="+str(i)+" where project_id=" + str(project_id)
cursor.execute(update_sql)
db.commit()
return i
二梳猪、數(shù)據(jù)生成excel
#create excel
s = 0;
wb = xlwt.Workbook(encoding = 'utf-8')
sh = wb.add_sheet('hotel')
sh.write(0,0,'project_id')
sh.write(0,1,'項目名稱')
sh.write(0,2,'設(shè)備總數(shù)')
sh.write(0,3,'開機數(shù)')
sh.write(0,4,'異常累計天數(shù)')
for project in res:
s=s+1
err_num = getUpRate(project[1])
sh.write(s,0,project[1])
sh.write(s,1,project[2])
sh.write(s,2,project[3])
sh.write(s,3,project[4])
sh.write(s,4,err_num)
wb.save("example-"+ Yesterday + ".xls")
sendMail() #發(fā)送郵件方法麻削，下方詳情
三、發(fā)送郵件方法介紹
def sendMail():
host_server = 'smtp.qq.com' #根據(jù)實際情況寫
sender_qq = '123@qq.com' #根據(jù)實際情況寫
pwd = '123@123' #根據(jù)實際情況寫
#發(fā)件人的郵箱
sender_qq_mail = '123@qq.com' #根據(jù)實際情況寫
#收件人郵箱
receiver = '456@qq.com'#根據(jù)實際情況寫
#郵件的正文內(nèi)容
mail_content = "你好春弥，<p>每日統(tǒng)計項目數(shù)據(jù)呛哟，請查收附件</p>"
#郵件標(biāo)題
mail_title = 'xxx數(shù)據(jù)統(tǒng)計'
#郵件正文內(nèi)容
msg = MIMEMultipart()
#msg = MIMEText(mail_content, "plain", 'utf-8')
msg["Subject"] = Header(mail_title, 'utf-8')
msg["From"] = sender_qq_mail
msg["To"] = Header("運維通知郵件組", 'utf-8') ## 接收者的別名
#郵件正文內(nèi)容
msg.attach(MIMEText(mail_content, 'html', 'utf-8'))
# 構(gòu)造附件1，傳送當(dāng)前目錄下的 test.txt 文件
att1 = MIMEText(open("example-"+Yesterday+".xls", 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
# 這里的filename可以任意寫惕稻，寫什么名字竖共，郵件中顯示什么名字
att1["Content-Disposition"] = 'attachment; filename="example.xls"'
msg.attach(att1)
#ssl登錄
smtp = SMTP_SSL(host_server)
#set_debuglevel()是用來調(diào)試的。參數(shù)值為1表示開啟調(diào)試模式俺祠，參數(shù)值為0關(guān)閉調(diào)試模式
smtp.set_debuglevel(1)
smtp.ehlo(host_server)
smtp.login(sender_qq, pwd)
smtp.sendmail(sender_qq_mail, receiver, msg.as_string())
smtp.quit()
導(dǎo)出excel如遇到下圖報錯：
1.排查編碼 #coding:utf-8 sys.setdefaultencoding('utf8') 等
2.寫入第一行數(shù)據(jù)的中文字符公给，或者字符串需要有引號借帘，忽略會報錯。
Traceback (most recent call last):
File "excel.py", line 19, in <module>
wb.save('example.xls')
File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 643, in save
doc.save(filename, self.get_biff_data())
File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 618, in get_biff_data
shared_str_table = self.__sst_rec()
File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 580, in __sst_rec
return self.__sst.get_biff_record()
File "/usr/lib/python2.7/site-packages/xlwt/BIFFRecords.py", line 77, in get_biff_record
self._add_to_sst(s)
File "/usr/lib/python2.7/site-packages/xlwt/BIFFRecords.py", line 92, in _add_to_sst
u_str = upack2(s, self.encoding)
File "/usr/lib/python2.7/site-packages/xlwt/UnicodeUtils.py", line 50, in upack2
us = unicode(s, encoding)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 in position 0: ordinal not in range(128)
