數(shù)據(jù)分析師肯定每天都被各種各樣的數(shù)據(jù)數(shù)據(jù)報(bào)表搞得焦頭爛額，老板的，運(yùn)營(yíng)的、產(chǎn)品的等等。而且大部分報(bào)表都是重復(fù)性的工作，這篇文章就是幫助大家如何用Python來實(shí)現(xiàn)報(bào)表的自動(dòng)發(fā)送，解放你的勞動(dòng)力，可以讓你有時(shí)間去做更有意思的事情。

首先來介紹下實(shí)現(xiàn)自動(dòng)報(bào)表要使用到的Python庫(kù)：

• pymysql 一個(gè)可以連接MySQL實(shí)例并且實(shí)現(xiàn)增刪改查功能的庫(kù)
• datetime Python標(biāo)準(zhǔn)庫(kù)中自帶的關(guān)于時(shí)間的庫(kù)
• openpyxl 一個(gè)可以讀寫07版以后的Excel文檔（.xlsx格式也支持）的庫(kù)
• smtplib SMTP即簡(jiǎn)單郵件傳輸協(xié)議，Python簡(jiǎn)單封裝成了一個(gè)庫(kù)
• email 一個(gè)用來處理郵件消息的庫(kù)

為什么使用openpyxl庫(kù)來處理Excel呢？因?yàn)樗С置總€(gè)sheet的行數(shù)為100W+，也是支持xlsx格式的文件。如果你接受xls文件，并且每個(gè)sheet的行數(shù)小于6W，也是可以使用xlwt庫(kù)，它對(duì)大文件的讀取速度要大于openpyxl。

接下來我們就進(jìn)入實(shí)戰(zhàn)部分，來正式實(shí)現(xiàn)這個(gè)過程。我把整個(gè)實(shí)現(xiàn)過程分成幾個(gè)函數(shù)的方式來實(shí)現(xiàn)，這樣看著會(huì)比較有結(jié)構(gòu)感。

一、首先導(dǎo)入所有要用到的庫(kù)

# encoding=utf-8 
import pymysql as pms 
import openpyxl 
import datetime 
from email.mime.text import MIMEText 
from email.mime.multipart import MIMEMultipart 
from email.header import Header 
import smtplib 

二、 編寫一個(gè)傳入sql就返回?cái)?shù)據(jù)的函數(shù)get_datas(sql)

def get_datas(sql): 
 # 一個(gè)傳入sql導(dǎo)出數(shù)據(jù)的函數(shù) 
 # 跟數(shù)據(jù)庫(kù)建立連接 
 conn = pms.connect(host='實(shí)例地址', user='用戶', 
 passwd='密碼', database='庫(kù)名', port=3306, charset="utf8") 
 # 使用 cursor() 方法創(chuàng)建一個(gè)游標(biāo)對(duì)象 cursor 
 cur = conn.cursor() 
 # 使用 execute() 方法執(zhí)行 SQL 
 cur.execute(sql) 
 # 獲取所需要的數(shù)據(jù) 
 datas = cur.fetchall() 
 #關(guān)閉連接 
 cur.close() 
 #返回所需的數(shù)據(jù) 
 return datas 

三、 編寫一個(gè)傳入sql就返回?cái)?shù)據(jù)的字段名稱的函數(shù)get_datas(sql)，因?yàn)橐粋€(gè)函數(shù)只能返回一個(gè)值，這邊就用2個(gè)函數(shù)來分別返回?cái)?shù)據(jù)和字段名稱（也就是excel里的表頭）

def get_fields(sql): 
 # 一個(gè)傳入sql導(dǎo)出字段的函數(shù) 
 conn = pms.connect(host='rm-rj91p2yhl9dm2xmbixo.mysql.rds.aliyuncs.com', user='bi-analyzer', 
 passwd='pcNzcKPnn', database='kikuu', port=3306, charset="utf8") 
 cur = conn.cursor() 
 cur.execute(sql) 
 # 獲取所需要的字段名稱 
 fields = cur.description 
 cur.close() 
 return fields 

四、 編寫一個(gè)傳入數(shù)據(jù)、字段名稱、存儲(chǔ)地址返回一個(gè)excel 的函數(shù)et_excel(data, field, file)

def get_excel(data, field, file): 
 # 將數(shù)據(jù)和字段名寫入excel的函數(shù) 
 #新建一個(gè)工作薄對(duì)象 
 new = openpyxl.Workbook() 
 #激活一個(gè)新的sheet 
 sheet = new.active 
 #給sheet命名 
 sheet.title = '數(shù)據(jù)展示' 
 #將字段名稱循環(huán)寫入excel***行，因?yàn)樽侄胃袷搅斜砝锇斜恚總€(gè)列表的***元素才是字段名稱 
 for col in range(len(field)): 
 #row代表行數(shù)，column代表列數(shù)，value代表單元格輸入的值，行數(shù)和列數(shù)都是從1開始，這點(diǎn)于python不同要注意 
 _ = sheet.cell(row=1, column=col+1, value=u'%s' % field[col][0]) 
 #將數(shù)據(jù)循環(huán)寫入excel的每個(gè)單元格中  
 for row in range(len(data)): 
 for col in range(len(field)): 
 #因?yàn)?**行寫了字段名稱，所以要從第二行開始寫入 
 _ = sheet.cell(row=row+2, column=col + 1, value=u'%s' % data[row][col]) 
 #將生成的excel保存，這步是必不可少的 
 newworkbook = new.save(file) 
 #返回生成的excel 
 return newworkbook 

五、 編寫一個(gè)自動(dòng)獲取昨天日期字符串格式的函數(shù)getYesterday()

def getYesterday(): 
 # 獲取昨天日期的字符串格式的函數(shù) 
 #獲取今天的日期 
 today = datetime.date.today() 
 #獲取一天的日期格式數(shù)據(jù) 
 oneday = datetime.timedelta(days=1) 
 #昨天等于今天減去一天 
 yesterday = today - oneday 
 #獲取昨天日期的格式化字符串 
 yesterdaystr = yesterday.strftime('%Y-%m-%d') 
 #返回昨天的字符串 
 return yesterdaystr 

六、編寫一個(gè)生成郵件的函數(shù)create_email(email_from, email_to, email_Subject, email_text, annex_path, annex_name)

def create_email(email_from, email_to, email_Subject, email_text, annex_path, annex_name): 
 # 輸入發(fā)件人昵稱、收件人昵稱、主題，正文，附件地址,附件名稱生成一封郵件 
 #生成一個(gè)空的帶附件的郵件實(shí)例 
 message = MIMEMultipart() 
 #將正文以text的形式插入郵件中 
 message.attach(MIMEText(email_text, 'plain', 'utf-8')) 
 #生成發(fā)件人名稱（這個(gè)跟發(fā)送的郵件沒有關(guān)系） 
 message['From'] = Header(email_from, 'utf-8') 
 #生成收件人名稱（這個(gè)跟接收的郵件也沒有關(guān)系） 
 message['To'] = Header(email_to, 'utf-8') 
 #生成郵件主題 
 message['Subject'] = Header(email_Subject, 'utf-8') 
 #讀取附件的內(nèi)容 
 att1 = MIMEText(open(annex_path, 'rb').read(), 'base64', 'utf-8') 
 att1["Content-Type"] = 'application/octet-stream' 
 #生成附件的名稱 
 att1["Content-Disposition"] = 'attachment; filename=' + annex_name 
 #將附件內(nèi)容插入郵件中 
 message.attach(att1) 
 #返回郵件 
 return message 

七、 生成一個(gè)發(fā)送郵件的函數(shù)send_email(sender, password, receiver, msg)

def send_email(sender, password, receiver, msg): 
 # 一個(gè)輸入郵箱、密碼、收件人、郵件內(nèi)容發(fā)送郵件的函數(shù) 
 try: 
 #找到你的發(fā)送郵箱的服務(wù)器地址，已加密的形式發(fā)送 
 server = smtplib.SMTP_SSL("smtp.mxhichina.com", 465) # 發(fā)件人郵箱中的SMTP服務(wù)器 
 server.ehlo() 
 #登錄你的賬號(hào) 
 server.login(sender, password) # 括號(hào)中對(duì)應(yīng)的是發(fā)件人郵箱賬號(hào)、郵箱密碼 
 #發(fā)送郵件 
 server.sendmail(sender, receiver, msg.as_string()) # 括號(hào)中對(duì)應(yīng)的是發(fā)件人郵箱賬號(hào)、收件人郵箱賬號(hào)（是一個(gè)列表）、郵件內(nèi)容 
 print("郵件發(fā)送成功") 
 server.quit() # 關(guān)閉連接 
 except Exception: 
 print(traceback.print_exc()) 
 print("郵件發(fā)送失敗") 

八、建立一個(gè)main函數(shù)，把所有的自定義內(nèi)容輸入進(jìn)去，***執(zhí)行main函數(shù)

def main(): 
 print(datetime.datetime.now()) 
 my_sql = sql = "SELECT a.id '用戶ID', 
 a.gmtCreate '用戶注冊(cè)時(shí)間', 
 af.lastLoginTime '***登錄時(shí)間', 
 af.totalBuyCount '歷史付款子單數(shù)', 
 af.paidmountUSD '歷史付款金額', 
 af.lastPayTime '用戶***支付時(shí)間' 
 FROM table a 
 LEFT JOIN tableb af ON a.id= af.accountId ;" 
 # 生成數(shù)據(jù) 
 my_data = get_datas(my_sql) 
 # 生成字段名稱 
 my_field = get_fields(my_sql) 
 # 得到昨天的日期 
 yesterdaystr = getYesterday() 
 # 文件名稱 
 my_file_name = 'user attribute' + yesterdaystr + '.xlsx' 
 # 文件路徑 
 file_path = 'D:/work/report/' + my_file_name 
 # 生成excel 
 get_excel(my_data, my_field, file_path) 
 my_email_from = 'BI部門自動(dòng)報(bào)表機(jī)器人' 
 my_email_to = '運(yùn)營(yíng)部' 
 # 郵件標(biāo)題 
 my_email_Subject = 'user' + yesterdaystr 
 # 郵件正文 
 my_email_text = "Dear all, 
    附件為每周數(shù)據(jù)，請(qǐng)查收！ 
 
BI團(tuán)隊(duì) " 
 #附件地址 
 my_annex_path = file_path 
 #附件名稱 
 my_annex_name = my_file_name 
 # 生成郵件 
 my_msg = create_email(my_email_from, my_email_to, my_email_Subject, 
 my_email_text, my_annex_path, my_annex_name) 
 my_sender = '阿里云郵箱' 
 my_password = '我的密碼' 
 my_receiver = [10001@qq.com']#接收人郵箱列表 
 # 發(fā)送郵件 
 send_email(my_sender, my_password, my_receiver, my_msg) 
 print(datetime.datetime.now()) 
if __name__ == "__main__": 
 main();