編程狗在線
自由的編程學(xué)習(xí)平臺(tái)
數(shù)據(jù)分析師肯定每天都被各種各樣的數(shù)據(jù)數(shù)據(jù)報(bào)表搞得焦頭爛額，老板的，運(yùn)營(yíng)的、產(chǎn)品的等等。而且大部分報(bào)表都是重復(fù)性的工作，這篇文章就是幫助大家如何用Python來(lái)實(shí)現(xiàn)報(bào)表的自動(dòng)發(fā)送，解放你的勞動(dòng)力，可以讓你有時(shí)間去做更有意思的事情。
首先來(lái)介紹下實(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è)可以讀寫(xiě)07版以后的Excel文檔（.xlsx格式也支持）的庫(kù)
smtplib SMTP即簡(jiǎn)單郵件傳輸協(xié)議，Python簡(jiǎn)單封裝成了一個(gè)庫(kù)
email 一個(gè)用來(lái)處理郵件消息的庫(kù)
為什么使用openpyxl庫(kù)來(lái)處理Excel呢？因?yàn)樗С置總€(gè)sheet的行數(shù)為100W+，也是支持xlsx格式的文件。如果你接受xls文件，并且每個(gè)sheet的行數(shù)小于6W，也是可以使用xlwt庫(kù)，它對(duì)大文件的讀取速度要大于openpyxl。
接下來(lái)我們就進(jìn)入實(shí)戰(zhàn)部分，來(lái)正式實(shí)現(xiàn)這個(gè)過(guò)程。我把整個(gè)實(shí)現(xiàn)過(guò)程分成幾個(gè)函數(shù)的方式來(lái)實(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
二、 編寫(xiě)一個(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='用戶(hù)',
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
三、 編寫(xiě)一個(gè)傳入sql就返回?cái)?shù)據(jù)的字段名稱(chēng)的函數(shù)get_datas(sql)，因?yàn)橐粋€(gè)函數(shù)只能返回一個(gè)值，這邊就用2個(gè)函數(shù)來(lái)分別返回?cái)?shù)據(jù)和字段名稱(chēng)（也就是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)
# 獲取所需要的字段名稱(chēng)
fields = cur.description
cur.close()
return fields
四、 編寫(xiě)一個(gè)傳入數(shù)據(jù)、字段名稱(chēng)、存儲(chǔ)地址返回一個(gè)excel 的函數(shù)et_excel(data, field, file)
def get_excel(data, field, file):
# 將數(shù)據(jù)和字段名寫(xiě)入excel的函數(shù)
#新建一個(gè)工作薄對(duì)象
new = openpyxl.Workbook()
#激活一個(gè)新的sheet
sheet = new.active
#給sheet命名
sheet.title = '數(shù)據(jù)展示'
#將字段名稱(chēng)循環(huán)寫(xiě)入excel第一行，因?yàn)樽侄胃袷搅斜砝锇斜恚總€(gè)列表的第一元素才是字段名稱(chēng)
for col in range(len(field)):
#row代表行數(shù)，column代表列數(shù)，value代表單元格輸入的值，行數(shù)和列數(shù)都是從1開(kāi)始，這點(diǎn)于python不同要注意
_ = sheet.cell(row=1, column=col+1, value=u'%s' % field[col][0])
#將數(shù)據(jù)循環(huán)寫(xiě)入excel的每個(gè)單元格中
for row in range(len(data)):
for col in range(len(field)):
#因?yàn)榈谝恍袑?xiě)了字段名稱(chēng)，所以要從第二行開(kāi)始寫(xiě)入
_ = sheet.cell(row=row+2, column=col + 1, value=u'%s' % data[row][col])
#將生成的excel保存，這步是必不可少的
newworkbook = new.save(file)
#返回生成的excel
return newworkbook
五、 編寫(xiě)一個(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
六、編寫(xiě)一個(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ā)件人昵稱(chēng)、收件人昵稱(chēng)、主題，正文，附件地址,附件名稱(chēng)生成一封郵件
#生成一個(gè)空的帶附件的郵件實(shí)例
message = MIMEMultipart()
#將正文以text的形式插入郵件中
message.attach(MIMEText(email_text, 'plain', 'utf-8'))
#生成發(fā)件人名稱(chēng)（這個(gè)跟發(fā)送的郵件沒(méi)有關(guān)系）
message['From'] = Header(email_from, 'utf-8')
#生成收件人名稱(chēng)（這個(gè)跟接收的郵件也沒(méi)有關(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'
#生成附件的名稱(chēng)
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 '用戶(hù)ID',\
a.gmtCreate '用戶(hù)注冊(cè)時(shí)間',\
af.lastLoginTime '最后登錄時(shí)間',\
af.totalBuyCount '歷史付款子單數(shù)',\
af.paidmountUSD '歷史付款金額',\
af.lastPayTime '用戶(hù)最后支付時(shí)間'\
FROM table a\
LEFT JOIN tableb af ON a.id= af.accountId ;'
# 生成數(shù)據(jù)
my_data = get_datas(my_sql)
# 生成字段名稱(chēng)
my_field = get_fields(my_sql)
# 得到昨天的日期
yesterdaystr = getYesterday()
# 文件名稱(chēng)
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部門(mén)自動(dòng)報(bào)表機(jī)器人'
my_email_to = '運(yùn)營(yíng)部'
# 郵件標(biāo)題
my_email_Subject = 'user' + yesterdaystr
# 郵件正文
my_email_text = 'Dear all,\n\t附件為每周數(shù)據(jù)，請(qǐng)查收！\n\nBI團(tuán)隊(duì) '
#附件地址
my_annex_path = file_path
#附件名稱(chēng)
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();作者：王向君，年輕的夢(mèng)想家。一個(gè)正在學(xué)習(xí)的數(shù)據(jù)分析師。
聯(lián)系客服
