【案例-自动化报表】mysql数据+python报表和发送邮件

一、思路

1、mysql创建表wy_user，包含用户信息和用户邮箱；
2、mysql创建表wy_user_data，包含用户用电信息；
3、python读取mysql的两个表，形成自动化报表（文字+图）；
4、python将形成的报表发送至用户邮箱。

二、执行

1、mysql的user表

CREATE SCHEMA `mysql_python` ;
use mysql_python;
CREATE TABLE `mysql_python`.`wy_user` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `email_address` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));
INSERT INTO `mysql_python`.`wy_user` (`id`, `name`, `email_address`) VALUES ('1', '张三', 'xxxxxxx@qq.com');
INSERT INTO `mysql_python`.`wy_user` (`id`, `name`, `email_address`) VALUES ('2', '李四', 'xxxxxxx@qq.com');

结果：

image.png

2、mysql的data表

CREATE TABLE `mysql_python`.`wy_user_data` (
  `id` INT NOT NULL,
  `Q1` INT NULL,  `Q2` INT NULL,  `Q3` INT NULL,
  `Q4` INT NULL,  `Q5` INT NULL,  `Q6` INT NULL,
  `Q7` INT NULL,  `Q8` INT NULL,  `Q9` INT NULL,
  `Q10` INT NULL,  `Q11` INT NULL,  `Q12` INT NULL,
  `Q13` INT NULL,  `Q14` INT NULL,  `Q15` INT NULL,
  `Q16` INT NULL,  `Q17` INT NULL, `Q18` INT NULL,
  `Q19` INT NULL, `Q20` INT NULL,  `Q21` INT NULL,
  `Q22` INT NULL,  `Q23` INT NULL, `Q24` INT NULL,
  PRIMARY KEY (`id`));
INSERT INTO `mysql_python`.`wy_user_data` (`id`, `Q1`, `Q2`, `Q3`, `Q4`, `Q5`, `Q6`, `Q7`, `Q8`, `Q9`, `Q10`, `Q11`, `Q12`, `Q13`, `Q14`, `Q15`, `Q16`, `Q17`, `Q18`, `Q19`, `Q20`, `Q21`, `Q22`, `Q23`, `Q24`) VALUES ('1', '200', '322', '450', '511', '645', '111', '234', '564', '1234', '1234', '452', '1234', '1234', '102', '046', '1023', '1054', '503', '432', '346', '275', '345', '456', '880');
INSERT INTO `mysql_python`.`wy_user_data` (`id`, `Q1`, `Q2`, `Q3`, `Q4`, `Q5`, `Q6`, `Q7`, `Q8`, `Q9`, `Q10`, `Q11`, `Q12`, `Q13`, `Q14`, `Q15`, `Q16`, `Q17`, `Q18`, `Q19`, `Q20`, `Q21`, `Q22`, `Q23`, `Q24`) VALUES ('2', '30', '246', '304', '241', '0054', '204', '213', '459', '42', '1234', '105', '2014', '243', '895', '940', '1054', '234', '1247', '243', '220', '189', '204', '36', '55');

结果：

image.png

3、python形成报表

# -*- coding:utf-8 -*-
import pymysql
import pandas as pd
from docx import Document
from docx.shared import Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH, WD_TAB_ALIGNMENT, WD_TAB_LEADER
import time
import seaborn as sns
import matplotlib.pyplot as plt

# 取表（定义函数一直有点问题，所以就不定义了）
# 连接数据库
db = pymysql.connect("xxx.mysql.rds.aliyuncs.com", "xxx", "xxx", "mysql_python")
cursor = db.cursor()
sql1 = 'select * from wy_user'
sql2 = 'select * from wy_user_data'
cursor.execute(sql1)
user = cursor.fetchall()
columnDes = cursor.description  # 获取描述信息
columnNames = [columnDes[i][0] for i in range(len(columnDes))]
user_df = pd.DataFrame([list(i) for i in user], columns=columnNames)
user_df.set_index('id', inplace=True)

cursor.execute(sql2)
user_data = cursor.fetchall()
columnDes = cursor.description  # 获取描述信息
columnNames = [columnDes[i][0] for i in range(len(columnDes))]
user_data_df = pd.DataFrame([list(i) for i in user_data], columns=columnNames)
user_data_df.set_index('id', inplace=True)

# python报表

plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

for id in range(2):
    #数据准备
    name = user_df.iloc[id]['name']
    all_value = user_data_df.iloc[id].sum()
    high_value = user_data_df.iloc[id][7:11].sum() + user_data_df.iloc[id][19:23].sum()
    flat_value = user_data_df.iloc[id][11:19].sum()
    valley_value = user_data_df.iloc[id].sum() - high_value - flat_value
    #图片准备
    fig, ax = plt.subplots(figsize=(10, 5))
    plt.title("Electricity-Quantity Distribution", fontsize=15)
    plt.xticks(rotation=90)
    plt.xlabel('Time Scale', fontsize=12)
    plt.ylabel('Volumn(kWh)', fontsize=12)
    sns.barplot(x=list(user_data_df.columns), y=list(user_data_df.iloc[id]))
    plt.savefig('/Users/ranmo/Desktop/数据分析案例/自动化报表/%d.jpg' % (id + 1))

    # 创建文档
    document = Document()
    # 标题
    year = int(time.strftime('%Y', time.localtime()))
    month = int(time.strftime('%m', time.localtime())) - 1

    title = ('%d年%d月客户用电分析报告' % (year, month))
    document.add_heading(title, 0)

    # 开头
    head = document.add_heading('尊敬的  ', level=1)
    head.add_run(name).italic = True
    head.add_run('  客户:')

    # 正文

    p = document.add_paragraph('    您好！')
    p.add_run('您%d年%d月的总用电量为%d千瓦时，其中峰、平、谷段的电量分别为：%d千瓦时、%d千瓦时以及%d千瓦时。具体用电情况如下图所示：' % (
    year, month, all_value, high_value, flat_value, valley_value))
    document.add_picture('/Users/ranmo/Desktop/数据分析案例/自动化报表/%d.jpg' % (id + 1), width=Inches(6))
    document.add_paragraph('')
    document.add_paragraph('')
    document.add_paragraph('')

    a = document.add_paragraph()
    a.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT  # 段落文字居右设置
    a.add_run('wy公司')

    b = document.add_paragraph()
    b.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT  # 段落文字居右设置
    b.add_run('%d年%d月01日' % (year, (month+1)))

    document.save('/Users/ranmo/Desktop/数据分析案例/自动化报表/%d_%s_报告.docx'%(id+1,name))

python生成word：
https://python-docx.readthedocs.io/en/latest/
https://www.cnblogs.com/xiao987334176/p/9995976.html

(本来还想把word转成pdf再发送的，但是api好像都是win环境的)

4、定时发送用户邮箱
a.python代码发送用户邮箱：

# -*- coding:utf-8 -*-

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header

import pymysql
import pandas as pd

db = pymysql.connect("xxx.mysql.rds.aliyuncs.com", "xxx", "xxx", "mysql_python")
cursor = db.cursor()
sql1 = 'select * from wy_user'
cursor.execute(sql1)
user = cursor.fetchall()
columnDes = cursor.description  # 获取描述信息
columnNames = [columnDes[i][0] for i in range(len(columnDes))]
user_df = pd.DataFrame([list(i) for i in user], columns=columnNames)
user_df.set_index('id', inplace=True)
cursor.close()


for id in range(2):
    my_sender = 'xxx'
    my_pass = 'xxx'
    my_user = user_df.iloc[id]['email_address']  # 接收邮件
    name = user_df.iloc[id]['name']

    #创建一个带附件的实例
    message = MIMEMultipart()
    message['From'] = Header("Arthur", 'utf-8')
    message['To'] =  Header(name, 'utf-8')
    subject = '客户用电分析报告'
    message['Subject'] = Header(subject, 'utf-8')

    #邮件正文内容
    message.attach(MIMEText('您好！这是您上月的用电分析报告，请查收！', 'plain', 'utf-8'))

    # 构造附件1，传送当前目录下的 test.txt 文件
    att1 = MIMEText(open('/Users/ranmo/Desktop/数据分析案例/自动化报表/%d_%s_报告.docx'%(id+1,name), 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
    att1.add_header("Content-Disposition", "attachment", filename=("utf-8", "", "%d_%s_报告.docx"%(id+1,name)))
    message.attach(att1)



    try:
        server=smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件人邮箱中的SMTP服务器，端口是465
        server.login(my_sender, my_pass)  # 括号中对应的是发件人邮箱账号、邮箱密码
        server.sendmail(my_sender,my_user,message.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
        server.quit()  # 关闭连接
        print ("邮件发送成功")
    except smtplib.SMTPException:
        print ("Error: 无法发送邮件")

python发送邮件：
https://www.runoob.com/python3/python3-smtp.html

b.编写定时执行脚本

#!/bin/bash
# -*- coding: utf-8 -*-
cd /Users/ranmo/Desktop/数据分析案例/自动化报表
python3 auto-word.py
python3 Email-Sending.py

* * * * *  /Users/ranmo/Desktop/数据分析案例/自动化报表/auto-word-sending.sh

crontab定时器：
https://blog.csdn.net/ty_hf/article/details/72354230
http://www.shanhuxueyuan.com/news/detail/118.html

ps:写crontab脚本的时候，遇到环境变量配置问题，最后发展成了“拯救环境变量三连”：
1、export PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
2、open -e ~/.bash_profile
3、source ~/.bash_profile
结论就是，有的路径不能往配置里面写。。不然会导致崩溃。
https://blog.csdn.net/haishen111/article/details/88867968

我写好了脚本，可以直接拖到终端执行，但是crontab提示“python3: command not found”，查询了半天，可能是因为crontab里面调用py，必须指定解释器的路径
https://www.cnblogs.com/zhhiyp/p/10160754.html
原执行脚本修改为:

#!/bin/bash
# -*- coding: utf-8 -*-
cd /Users/ranmo/Desktop/数据分析案例/自动化报表
/Users/ranmo/anaconda3/bin/python3 auto-word.py
/Users/ranmo/anaconda3/bin/python3 Email-Sending.py

搞定！

三、结果
1、收到邮件

image.png

2、邮件附件

image.png