前面的文章我们写到了利用Python实现钉钉和邮件的数据推送，在数据处理这一块实现了对mysql和odps的数据获取和处理，可以满足常规业务大部分数据场景需求，在一家初创公司数据基础建设还不完善的时候，可以利用Python快速搭建一个简便的数据推送系统，提高数据的获取使用效率。这篇文章会系统的把这一块儿的内容完善（附上完整的代码），同时会完善数据报表直接写入邮件正文的代码（之前是直接写入Excel附件）。如果大家有更好的方法欢迎讨论交流。
主要目录如下：
一、主要流程
二、钉钉推送
1、添加机器人
2、数据准备
1）处理mysql数据
2）处理odps数据
三、邮件推送
1、发送邮件函数
2、数据处理
1）处理mysql数据
2）处理odps数据
3、数据结果写在邮件正文
4、mian()方法
四、报警机制
五、odps使用注意事项
1、连接odps
2、全表扫描配置
六、通用配置
七、常用Git指令
一、主要流程
本地.py文件Git上传至stash仓库，服务器端可以通过Git把.py文件下载至服务器，然后通过crontab设置定时任务执行对应的.py文件进行脚本推送
二、钉钉推送
1、添加机器人
需要安全设置，一般选择关键词，基本上都是推送中包含的特定关键词
生成的钉钉链接不要泄露
https://oapi.dingtalk.com/robot/send?access_token=*********
具体可查看官方文档：https://ding-doc.dingtalk.com/doc#/serverapi2/krgddi
2、数据准备
使用Python来编写脚本
#导入第三方包
from dingtalkchatbot.chatbot import DingtalkChatbot
#构建推送消息函数
def send_message(message):
# WebHook地址
webhook = 'https://oapi.dingtalk.com/robot/send?access_token=*******'
# 初始化机器人
xiaoding = DingtalkChatbot(webhook)
# Text消息@所有人
xiaoding.send_text(msg=message, is_at_all=False)
剩下就是处理数据然后传到message里面进行推送即可
1）处理mysql数据
首先连接和查询数据库还是依赖pymysql包，网上很多文章讲解，这里不多说，Python操作mysql得到的数据结果是一个元组
result : ((a,b),(c,d),(e,f),)
如果我们需要获取数据则根据索引取出对应的数据即可，
比如GMV：result([0][0])，得到结果a，其他数据以此类推
但是这里需要注意的是我们仅仅获取到了数据结果，但是这个数据是什么内容还没有得到，就是SQL结果的列名，这里需要我们手动拼一下。举一个日常的例子，销售相关的数据
【销售数据】
mes_gmv = 'GMV:' + result([0][0])
mes_order = '订单量:' + result([0][1])
这里我们拼了一个数据内容名称“销售数据”，然后拼了一个字段名称：GMV、订单量，这样我们推的数据表达什么意思就很清楚。
这个是一行一条数据的展示，实际工作中还会有数据的展示效果跟Excel一致，感兴趣的可以试试，对于这种规律可以用for循环遍历，大致效果如下：
日期 | GMV | 订单量 | 下单人数 | 销售件数
10.01 | 1000 | 10000 | 100000 | 20000
10.02 | 1000 | 10000 | 100000 | 20000
以上的拼接都要注意换行，要不然数据就全部挤在一行了
2）处理odps数据
相关文档：
PyODPS: ODPS Python SDK and data analysis frameworkpyodps.readthedocs.io Python SDK方法说明_Python SDK_SDK参考_MaxCompute-阿里云help.aliyun.com
#导入相关包
from odps import ODPS
from collections import defaultdict
#构建处理odps_SQL的函数
def sql_odps(sql):
odps = ODPS(access_id = odps_setting['access_id'],
secret_access_key = odps_setting['secret_access_key'],
project = odps_setting['project'],
endpoint = odps_setting['endpoint'])
with odps.execute_sql(sql).open_reader() as reader:
print("odps_sql查询成功")
for record in reader:
return record #这里返回的结果是一个字典格式，获取数据key-value获取即可
三、邮件推送
1、发送邮件函数
#导入需要的包
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
#生成邮件
def create_mail(mail_from, mail_to, subject, content, annex_path, annex_name): # 生成邮件
# 输入发件人昵称、收件人昵称、主题，正文，附件地址,附件名称生成一封邮件
# 生成一个空的带附件的邮件实例
message = MIMEMultipart()
# 将正文以text的形式插入邮件中
message.attach(MIMEText(content, 'plain', 'utf-8'))
# 生成发件人名称（这个跟发送的邮件没有关系）
message['From'] = mail_from
# 生成收件人名称（这个跟接收的邮件也没有关系）
message['To'] = mail_to
# 生成邮件主题
message['Subject'] = Header(subject, 'utf-8')
# 读取附件的内容
att1 = MIMEText(open(annex_path, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream;name="%s"' % make_header([(annex_name, 'UTF-8')]).encode('UTF-8')
# 生成附件的名称
att1["Content-Disposition"] = 'attachment; filename="%s"' % make_header([(annex_name, 'UTF-8')]).encode('UTF-8')
# 将附件内容插入邮件中
message.attach(att1)
# 返回邮件
return message
#发送邮件
def send_mail(user, password, sender, receiver, meg): # 发送邮件
try:
smtpObj = smtplib.SMTP_SSL("smtp.163.com")
smtpObj.connect("smtp.163.com", 465)
smtpObj.login(user, password)
print("登录成功")
smtpObj.sendmail(sender, receiver, meg.as_string())
print("邮件发送成功")
smtpObj.quit() # 用于结束SMTP会话。
except smtplib.SMTPException as e:
print("Error: 邮件发送失败")
print("error", e)
2、数据处理
1）处理mysql数据
#导入需要的包
import pymysql
import openpyxl
#这种遍历每个单元格写入数据的时候会出现因为包含特殊字符串而报错，具体可查看sheet2的用法
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
#获取数据
def sql_data(host, port, user, password, db, sql_content, yesterday_str = None, today_str = None, yesterday_str_1 = None, today_str_1 = None): # 生成SQL数据
conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db, use_unicode=True, charset="utf8")
cursor = conn.cursor()
if yesterday_str_1:
cursor.execute(sql_content, (yesterday_str, today_str, yesterday_str_1, today_str_1))
elif yesterday_str:
cursor.execute(sql_content, (yesterday_str, today_str))
else :
cursor.execute(sql_content)
datas = cursor.fetchall()
print("sql查询成功")
# print(datas)
cursor.close()
return datas
#获取字段名
def sql_title(host, port, user, password, db, sql_content, yesterday_str = None, today_str = None, yesterday_str_1 = None, today_str_1 = None): # 生成表头字段
conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db, use_unicode=True, charset="utf8")
cursor = conn.cursor()
if yesterday_str_1:
cursor.execute(sql_content, (yesterday_str, today_str, yesterday_str_1, today_str_1))
elif yesterday_str:
cursor.execute(sql_content, (yesterday_str, today_str))
else:
cursor.execute(sql_content)
title = cursor.description
cursor.close()
return title
#写入Excel
def get_excel(data_1, field_1, data_2, field_2, data_3, field_3, data_4, field_4,file):
# 将数据和字段名写入excel的函数
# 新建一个工作薄对象
new = openpyxl.Workbook()
# 激活一个新的sheet
sheet1 = new.active
# 给sheet命名
sheet1.title = '数据汇总'
# 将字段名称循环写入excel第一行，因为字段格式列表里包含列表，每个列表的第一元素才是字段名称
for col in range(len(field_1)):
# row代表行数，column代表列数，value代表单元格输入的值，行数和列数都是从1开始，这点于python不同要注意
_ = sheet1.cell(row=1, column=col + 1, value=u'%s' % field_1[col][0])
# 将数据循环写入excel的每个单元格中
for row in range(len(data_1)):
for col in range(len(field_1)):
# 因为第一行写了字段名称，所以要从第二行开始写入
_ = sheet1.cell(row=row + 2, column=col + 1, value=u'%s' % data_1[row][col])
# 激活一个新的sheet
sheet2 = new.create_sheet('sheet2')
# 给sheet命名
sheet2.title = '数据明细'
# 将字段名称循环写入excel第一行，因为字段格式列表里包含列表，每个列表的第一元素才是字段名称
for col in range(len(field_2)):
# row代表行数，column代表列数，value代表单元格输入的值，行数和列数都是从1开始，这点于python不同要注意
_ = sheet2.cell(row=1, column=col + 1, value=u'%s' % field_2[col][0])
# 将数据循环写入excel的每个单元格中
for row in range(len(data_2)):
for col in range(len(field_2)):
# 因为第一行写了字段名称，所以要从第二行开始写入，这个包主要是对数据中一些非法字符串过滤
_ = sheet2.cell(row=row + 2, column=col + 1, value=u'%s' % ILLEGAL_CHARACTERS_RE.sub(r'', str(data_2[row][col])))
# 将生成的excel保存，这步是必不可少的
newworkbook = new.save(file)
# 返回生成的excel
return newworkbook
上面这种方法是获取数据，然后分成每一个单元格遍历写入，这种遍历每个单元格写入数据的时候会出现因为包含特殊字符而报错，具体可查看sheet2的写法解决。另外还可以直接使用pandas来读取SQL然后获取df格式的数据，直接用pandas写入Excel，这样会更方便
#导入需要的包
import pandas as pd
import pymysql
#读取SQL，获取数据
def execute_sql(sql,yesterday_str = None, today_str = None, yesterday_str_1 = None, today_str_1 = None):
db = pymysql.connect(host=mysql_setting['host'], port=mysql_setting['port'], user=mysql_setting['user'],
password=mysql_setting['password'], db='trade', use_unicode=mysql_setting['use_unicode'],
charset=mysql_setting['charset'])
if yesterday_str_1:
datas = pd.read_sql(sql = sql, con = db, params = (yesterday_str, today_str, yesterday_str_1, today_str_1))
elif yesterday_str:
datas = pd.read_sql(sql = sql, con = db, params = (yesterday_str, today_str))
else :
datas = pd.read_sql(sql = sql, con = db)
print("sql查询成功")
return datas
def main():
#处理SQL
data_summary_df = execute_sql(sql_summary,yesterday_str, today_str, yesterday_str_1,today_str_1)
data_detail_df = execute_sql(sql_detail,yesterday_str, today_str)
#写入Excel
excel = pd.ExcelWriter('/Users/excel/' + yesterday_str + '数据' + '.xlsx')
data_summary_df.to_excel(excel, '汇总', index=False)
data_detail_df.to_excel(excel, '明细', index=False)
excel.save()
# 发送邮件
user = mail_setting['user']
password = mail_setting['password']
sender = mail_setting['sender']
receiver = ['python@163.com'] # 可以增加收件人
send_mail(user, password, sender, receiver, mail_message)
2）处理odps数据
#主要是将字典形式的数据转换成需要的df格式
def sql_odps(sql):
odps = ODPS(access_id=odps_setting['access_id'],
secret_access_key=odps_setting['secret_access_key'],
project=odps_setting['project'],
endpoint=odps_setting['endpoint'])
with odps.execute_sql(sql).open_reader() as reader:
print("odps_sql查询成功")
d = defaultdict(list) # collection默认一个dict
# record一行数据，res一个元组
for record in reader:
for res in record:
d[res[0]].append(res[1]) # 解析record中的每一个元组，存储方式为(k,v)，以k作为key，存储每一列的内容；
data = pd.DataFrame.from_dict(d, orient='index').T # 转换为数据框，并转置，不转置的话是横条数据
return data
3、数据结果写在邮件正文
主要是DataFrame的to_html函数，不过需要注意的是在构建邮件的方法中要把正文的格式由"plain"改为"html"，处理完之后优化CSS样式，得到想要的图表格式，然后再格式化数据结果就可以了
def data_to_html(df, title, width=1200):
html_head = '''
<!doctype html>
<html><head>
<meta charset="utf-8">
<style>
* { padding:0; margin:0; border:0;
font-style:normal;
list-style:none;
font-family:arial,"微软雅黑";
font-size:13px;}
.tbb { background:#e8e8e8;}
.tbb td,
.tbb th { background:#fff; padding:5px 3px; text-align:center;}
body th.bgfa { background:#fafafa;}
p{font-size:16px;}
</style>
</head>
'''
html_config = '<p>{title}</p><body><table width="{width}" border="0" cellspacing="1" cellpadding="0" class="tbb">'
# 加入标题和宽度参数
html_config = html_config.format(title=title, width=width)
html_after = '</body><br/><br/><!doctype html>n'
# 将dataframe转为html的table
table = df.to_html(index=False)
# 去除dataframe生成的table参数，以便使用自定义参数
table_body = re.sub(u'<table.*?>', u'', table)
html = html_head + html_config + table_body + html_after
return html
def format_data(data_list):
content = ''
for data in data_list:
# 宽度参数默认为1200 可以不配置
if 'width' in data:
width = data['width']
else:
width = 1200
html = data_to_html(data['df'], data['title'], width=width) # 可根据实际表格大小需求调整width实际值
content = content + html
return content
4、mian()方法
SQL在这里处理，原始SQL——>处理SQL——>写入Excel
def main():
sql_data = """"""
data = sql_odps(sql_data)
writer = pd.ExcelWriter('/home/test/测试数据' + '.xlsx')
writer.save()
# 文件路径
file_path = '/home/test/测试数据' + '.xlsx'
# 邮件主要信息
bi_mail_from = "python@163.com" # 发件人名称
bi_mail_to = "需求方" # 收件人名称
bi_mail_subject = '测试数据' # 邮件标题
bi_mail_content = "Dear all,nnt附件为测试数据，请注意查收！nnBI团队 " # 邮件正文
# 附件地址
my_annex_path = file_path
# 附件名称
my_annex_name = yesterday_str + '测试数据.xlsx'
# 生成邮件
mail_message = create_mail(bi_mail_from, bi_mail_to, bi_mail_subject, bi_mail_content, my_annex_path, my_annex_name)
# 发送邮件
user = mail_setting['user']
password = mail_setting['password']
sender = mail_setting['sender']
receiver = ['python@163.com'] # 可以增加收件人
send_mail(user, password, sender, receiver, mail_message)
四、报警机制
主要是创建一个邮件发送失败的函数，然后在数据推送的邮件执行失败的时候执行该函数即可，同时把错误信息在邮件正文生成。相较于发送失败邮箱，做失败的钉钉推送会更好，手机查看更方便，提示可便捷，感兴趣的可以自己尝试一下
import traceback
def create_fail_mail(mail_from, mail_to, subject, content): # 生成邮件
# 输入发件人昵称、收件人昵称、主题，正文生成一封邮件
# 生成一个空的带附件的邮件实例
message = MIMEMultipart()
# 将正文以text的形式插入邮件中
message.attach(MIMEText(content, 'plain', 'utf-8'))
# 生成发件人名称（这个跟发送的邮件没有关系）
message['From'] = mail_from
# 生成收件人名称（这个跟接收的邮件也没有关系）
message['To'] = mail_to
# 生成邮件主题
message['Subject'] = Header(subject, 'utf-8')
# 返回邮件
return message
def main_fail(bi_mail_content_fail):
# 邮件主要信息
bi_mail_from = "@" # 发件人名称
bi_mail_to = "需求方" # 收件人名称
bi_mail_subject = "数据发送失败" # 邮件标题
bi_mail_content_fail = "数据发送失败" + "n" + bi_mail_content_fail # 失败邮件正文
# 生成邮件
mail_message_fail = create_fail_mail(bi_mail_from, bi_mail_to, bi_mail_subject, bi_mail_content_fail)
# 发送邮件
user = mail_setting['user']
password = mail_setting['password']
sender = mail_setting['sender']
receiver = ['@'] # 可以增加收件人
send_mail(user, password, sender, receiver, mail_message_fail)
if __name__ == '__main__':
try:
main()
except:
main_fail(traceback.format_exc())
五、odps使用注意事项
1、连接odps
"access_id":"access_id ",
"secret_access_key":"secret_access_key",
"project":"自己的项目空间名称",
"endpoint":"https://service.odps.aliyun.com/api"
2、全表扫描配置
from odps import options
options.sql.settings = {"odps.sql.submit.mode": "script"}
六、通用配置
由于我们的脚本会很多，如果每次写脚本的时候都要把mysql配置、odps配置、邮箱配置写在脚本里面会比较重复，同时如果出现链接或者账号密码变更，修改起来非常不方便，这个时候把这些公共的模块单独配置在一个.py文件中就比较方便，即使修改也只修改配置文件就可以了。可以在一个配置文件，也可以每项内容一个配置文件，这里简便处理就放在一起展示。
# -*- coding:utf-8 -*-
#发邮件本地配置
mail_setting = {
"user" : "python@163.com",
"password" : "password",
"sender" : "python@163.com",
}
# -*- coding:utf-8 -*-
# mysql本地配置
mysql_setting = {
"host": "host",
"user": "user",
"password": "password",
"port": 3306,
"charset": "utf8",
"db": "db",
"use_unicode": True
}
# -*- coding:utf-8 -*-
# ODPS连接配置
odps_setting = {
"access_id":"access_id",
"secret_access_key":"secret_access_key",
"project":"project",
"endpoint":"endpoint"
}
在脚本中引用
#有的环境下不能主动识别配置文件路径，这时需要我们制定路径（即config所在路径）
import sys
sys.path.append("/home/pyton/")
#从配置文件夹中的文件导入我们想要的模块
from config.odpsSetting import odps_setting
def sql_odps(sql):
odps = ODPS(access_id=odps_setting['access_id'],
secret_access_key=odps_setting['secret_access_key'],
project=odps_setting['project'],
endpoint=odps_setting['endpoint'])
#这里只需要填对应的参数即可
七、常用Git指令
正常在stash创建Repositories会有完成的教学指导你如何将本地代码Git到仓库托管，这里说一下常用的指令
$ git add . 将改动点提交到暂存区（必须）
$ git status 查看修改文件状态（非必须）
$ git commit -m "备注信息" 用来正式提交变动，提交至 .git
$ git push 提交代码
$ git pull 从远程把代码拉到本地
$ git reset --hard HEAD^2 回滚版本，数字表示往前回滚几个版本
详细使用可以查看这篇文章：
从只会git add .的菜鸟到掌握git基本功能juejin.im
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
