【Python3】实现数据库数据提取并发送邮件，添加附件

最新推荐文章于 2023-02-08 15:18:58 发布

沐浴清风z

最新推荐文章于 2023-02-08 15:18:58 发布

阅读量1.2k

点赞数 3

分类专栏： Python

本文链接：https://blog.csdn.net/daliangliangliangge/article/details/102919782

版权

Python 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

前言
准备
代码

前言

作为以数据作为自己工作的人而言，每天需要有大量的报表需要通过邮件发送，当然，有很多属于日常工作，即固定格式，或许我们可以程序来完成这一工作，设定每天某一时间自动执行填充数据，即可。

准备

邮件实际内容当中需要添加Excel附件，而且Excel当中是需要有背景色等一定格式的，查找了常用xlrd，xlwt，openpyxl等操作Excel的库，发现实现这一功能都比较复杂，苦苦挣扎中，我发现了一个名为xlwings 的库，可以基于VBA的语法完成对于Excel的操作，比较好的是可以快速的基于List完成对于数据的填充。

xlwings

xlwings 是一个开源免费，且现在一直在更新的库，相比于其他操作Excel的库，他有一些比较独特的地方

xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改
可以和matplotlib以及pandas无缝连接
可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。

xlwings对于数据的批量数据的填充可以采用列表的方式比如

sht.range('A4').options(expand='table').value = [[1, 2], [3, 4]]

结果如下
在这里插入图片描述
参考
xlwings模块简单使用
 xlwings的官方文档
 xlwings : 从此可以 VBA 调用 Python 代码啦

smtplib

SMTP（Simple Mail Transfer Protocol）即简单邮件传输协议,它是一组用于由源地址到目的地址传送邮件的规则，由它来控制信件的中转方式。Python的smtplib提供了一种很方便的途径发送电子邮件。它对smtp协议进行了简单的封装。

Python SMTP发送邮件

简单来说，Python对SMTP支持有smtplib和email两个模块，email负责构造邮件，smtplib负责发送邮件。
比如

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import smtplib
from email.mime.text import MIMEText
from email.header import Header
# 第三方 SMTP 服务
mail_host=""  #设置服务器
mail_user=""    #用户名
mail_pass=""   #口令 
sender = ''
receivers = ['']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
message = MIMEText('Python 邮件发送测试...', 'plain', 'utf-8')
message['From'] = Header("菜鸟教程", 'utf-8')
message['To'] =  Header("测试", 'utf-8')
subject = 'Python SMTP 邮件测试'
message['Subject'] = Header(subject, 'utf-8')
try:
    smtpObj = smtplib.SMTP() 
    smtpObj.connect(mail_host, 25)    # 25 为 SMTP 端口号
    smtpObj.login(mail_user,mail_pass)  
    smtpObj.sendmail(sender, receivers, message.as_string())
    print "邮件发送成功"
except smtplib.SMTPException:
    print "Error: 无法发送邮件"

我自己使用的是腾讯的企业邮箱，所以host设置为 ‘imap.exmail.qq.com’。而且邮件的内容可以通过HTML构造，由于本次的需求是实现带背景色表格的发送，所以通过发送HTML的邮件会更合适,所以又涉及到一个问题，如何使用HTML构造一个表格。

html代码

邮件涉及到的html代码比较简单，主要是基于css样式实现表格的展现，代码如下
css基础文档
 HTML表格基础

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <style>
        html,
        body {
            width: 100%;
            height: 100%;
            background: #fff;
            color: #000;
            font-size: 14px;
            font-family: Microsoft YaHei;
        }

        .main {
            width: 80%;
            margin: 0 auto;
        }

        p,
        table {
            width: 100%;
            margin: 10px 0;
            padding: 0;
        }

        .title {
            font-size: 16px;
        }

        .content {
            text-indent: 2em;
        }

        .content .red {
            color: #ff0000;
        }

        table {
            border-right: 1px solid #000;
            border-bottom: 1px solid #000;
            text-align: center;
        }

        table td,
        table th {
            border-left: 1px solid #000;
            border-top: 1px solid #000;
            padding: 3px;
        }

        table thead tr th {
            font-weight: normal;
        }

        table thead tr:first-child th {
            font-size: 18px;
        }

        table thead tr:last-child th,
        table tfoot tr td {
            color: #fff;
        }

        .one-table thead tr:last-child th {
            background: #5b9bd5;
        }

        .one-table tfoot tr td {
            background: #ffc000;
        }

        .two-table thead tr:last-child th {
            background: #0070c0;
        }

        .two-table tfoot tr td {
            background: #ed7d31;
        }

        .two-table {
            margin-top: 30px;
        }
    </style>
</head>

<body>
    <div class="main">
        <p class="title">各位领导及同事，你们好：</p>
        <p class="content">附件为<span class="red">2019-10-16</span>的<span class="red">电销坐席外呼日报</span>，请查收，谢谢。</p>
        <table class="one-table" border="0" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <th colspan="5">
                        电销一部2019-10-16各坐席通话时长
                    </th>
                </tr>
                <tr>
                    <th>坐席</th>
                    <th>总通话时长（分钟）</th>
                    <th>外呼次数</th>
                    <th>外呼接通次数</th>
                    <th>外呼接通率</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>8001</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8002</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8003</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8004</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8005</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
            </tbody>
            <tfoot>
                <tr>
                    <td>汇总</td>
                    <td>75.52</td>
                    <td>40</td>
                    <td>23</td>
                    <td>57.50%</td>
                </tr>
            </tfoot>
        </table>
        <table class="two-table" border="0" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <th colspan="5">
                        电销二部2019-10-16各坐席通话时长
                    </th>
                </tr>
                <tr>
                    <th>坐席</th>
                    <th>总通话时长（分钟）</th>
                    <th>外呼次数</th>
                    <th>外呼接通次数</th>
                    <th>外呼接通率</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>8006</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8007</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8008</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8009</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8010</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8011</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8012</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8013</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8014</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
                <tr>
                    <td>8015</td>
                    <td>3.78</td>
                    <td>3</td>
                    <td>2</td>
                    <td>66.67%</td>
                </tr>
            </tbody>
            <tfoot>
                <tr>
                    <td>汇总</td>
                    <td>75.52</td>
                    <td>40</td>
                    <td>23</td>
                    <td>57.50%</td>
                </tr>
            </tfoot>
        </table>
    </div>
</body>

</html>

渲染结果
在这里插入图片描述
可以看到正文部分的红字和表格里的内容是需要随着时间变化而变化的，这是我们需要在Python中填充的数据。

代码

说一下自己在代码中的逻辑，

将邮件附件中的excel保存在电脑中，并将单元格格式和样式等弄好，需要填充的数据为空，公式等一并编辑好，Python中调用xlwings的目的是为了将SQL执行的数据填充进去，所以数据区域要留有足够的空行，然后去除空行。
使用Format函数填充HTML代码作为邮件的正文内容，，注意因为HTML中是有" { “,” } “符号的，这里我是先提前将” { “,” } “替换成"QQQ"和"qqq”,读取之后在Python中进行替换操作，将SQL执行的数据放入HTML中，完成最终的展示。

 <table class="one-table" border="0" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <th colspan="5">
                        {}
                    </th>
                </tr>
                <tr>
                    <th>坐席</th>
                    <th>总通话时长（分钟）</th>
                    <th>外呼次数</th>
                    <th>外呼接通次数</th>
                    <th>外呼接通率</th>
                </tr>
            </thead>
            <tbody>
               {}
            </tbody>
            <tfoot>
               {}
            </tfoot>
        </table>

最终的代码实现如下，完成附件上传，添加HTML作为正文，发送邮件，添加抄送人

#!/usr/bin/python3

import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
import pymysql
import xlwings as xw
from email import encoders
import  datetime
'''
function:从数据库取数，添加至附件，使用html的格式发送邮件，附件的excel采用xlwings方式操作，格式事先在excel中设置好
author:shikailiang
'''
#定义数据库连接
conn = pymysql.connect(host="", user="", password="", database="", charset="utf8")
cursor = conn.cursor()
# 第三方 SMTP 服务
mail_host = "imap.exmail.qq.com"  # 设置服务器
mail_user = ""  # 自己的邮箱
mail_pass = ""  # 密码
# sender为发件人，receivers为收件人，acc为抄送人，多个人可以用列表
sender = ''
receivers = ['']  
acc=[''] 
# 定义附件文件储存的位置
last_path=r'C:\Users\Administrator\Desktop\学习\Python'
data_path = last_path + r"\data\电销坐席通话时长"
# 读取html的内容
with open(data_path+'\\'+'test.txt','r',encoding='utf-8') as file:
    # msg_html = re.sub('\s+','',file.read())
    msg_html = file.read()

message = MIMEMultipart()
# sql_1和sql_2分别为1部和2部执行的SQL
sql_1='''
'''
sql_2='''
'''
cursor.execute(sql_1)
# pj_html_1为拼接的一部的html代码，pj_html_2为拼接的二部的html代码
pj_html_1 = ""
pj_html_2 = ""
# 拼接表格正文内容，数字5是5列的意思
pj_html_0 ="<tr>"+("<td>{}</td>")*5+"</tr>"
# 拼接表格汇总内容，数字4是4列，第一列汇总
hz_html = "<tr><td>汇总</td>"+("<td>{}</td>")*4+"</tr>"
result_list_1=[]
# hz=[0,0,0]
# 计算汇总内容，hz_time为总通话时长，hz_count为总外呼次数，hz_jietong为总外呼接通次数，按行循环SQL的内容计算
hz_time=0
hz_count=0
hz_jietong=0
for i in cursor.fetchall():
    hz_time=hz_time + i[1]
    hz_count=hz_count + i[2]
    hz_jietong = hz_jietong + i[3]
    list0=[str(j) for j in i][:-1]
    # 将最后一个数据转成百分比形式，添加至列表
    list0.append(str("%.2f%%" % (i[-1] * 100)))
    result_list_1.append(list0)
    # 汇总部分html的定义
hz_html_1= hz_html.format(str(hz_time),str(hz_count),str(hz_jietong),str("%.2f%%" % ( hz_jietong/hz_count * 100)))
cursor.execute(sql_2)
result_list_2 = []
hz_time=0
hz_count=0
hz_jietong=0
for i in cursor.fetchall():
    hz_time = hz_time + i[1]
    hz_count = hz_count + i[2]
    hz_jietong = hz_jietong + i[3]
    list0=[str(j) for j in i][:-1]
    list0.append(str("%.2f%%" % (i[-1] * 100)))
    result_list_2.append(list0)
hz_html_2= hz_html.format(str(hz_time),str(hz_count),str(hz_jietong),str("%.2f%%" % ( hz_jietong/hz_count * 100)))
# 将数据添加至html的表的正文中
for i in result_list_1:
    pj_html_1 = pj_html_1 + pj_html_0.format(*i)
for i in result_list_2:
    pj_html_2 = pj_html_2 + pj_html_0.format(*i)
# 定义excel文件
app = xw.App(visible=False, add_book=False)
wb=app.books.open(data_path +'\\' + "电销坐席通话时长.xlsx")
sheet1=wb.sheets["Sheet1"]
# sheet1.range('A1:e1').api.merge()  #记得带上()
# sheet1.range('A20:e20').api.merge()
sheet1.range('A3').options(expand='table').value=result_list_1
time_str=datetime.date.today()- datetime.timedelta(1)
# 附件中表头的定义
header_name_1 = "电销一部"+str(time_str)+"各坐席通话时长"
header_name_2 = "电销二部"+str(time_str)+"各坐席通话时长"
# 昨天的日期
time_str=datetime.date.today()- datetime.timedelta(1)
# 将数据写入excel中
sheet1.range('A1').value = header_name_1
sheet1.range('A20').value = header_name_2
sheet1.range('A22').options(expand='table').value=result_list_2
aa = sheet1.range('A9').value
temp_del = 0
# 循环删除空行
for i in range(100):
    if sheet1.range('A'+str(i+1-temp_del)).value == None:
        sheet1.range('A'+str(i+1-temp_del)).api.EntireRow.Delete()
        temp_del = temp_del +1
file_name1="电销坐席通话时长_"+str(time_str) +".xlsx"
wb.save(data_path +'\\' + file_name1)
wb.close()
app.quit()
# 将所有数据写入html中
msg_html=msg_html.format(time_str,header_name_1,pj_html_1, hz_html_1,header_name_2,pj_html_2, hz_html_2)
# 替换字符为{或}，html当中存在多个{和}，会干扰format函数的正常运行，所以事先将{替换成QQQ，}替换成qqq，这里需要替换回去
msg_html=msg_html.replace('QQQ','{').replace('qqq','}')
message.attach(MIMEText(msg_html, 'html', 'utf-8'))
message['From'] = Header("", 'utf-8')
message['To'] = Header("", 'utf-8')
message['Cc'] = Header("", 'utf-8')
subject = 'Python SMTP 邮件测试'
message['Subject'] = Header(subject, 'utf-8')

with open(data_path +'\\' + file_name1, 'rb') as f:
    # 设置附件的MIME和文件名，这里是png类型:
    mime = MIMEBase('text', 'xlsx', filename=file_name1)
    # 加上必要的头信息:
    mime.add_header('Content-Disposition', 'attachment', filename=file_name1)
    mime.add_header('Content-ID', '<0>')
    mime.add_header('X-Attachment-Id', '0')
    # 把附件的内容读进来:
    mime.set_payload(f.read())
    # 用Base64编码:
    encoders.encode_base64(mime)
message.attach(mime)
try:
    smtpObj = smtplib.SMTP()
    smtpObj.connect(mail_host,25)  # 25 为 SMTP 端口号
    smtpObj.login(mail_user, mail_pass)
    smtpObj.sendmail(sender, receivers + acc, message.as_string())
    print("邮件发送成功")
except smtplib.SMTPException:
    print("Error: 无法发送邮件")

smtpObj.quit()

代码实现了邮件正文表格的长度随着SQL计算结果而自动变化，结果

在这里插入图片描述

沐浴清风z

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
【Python3】实现数据库数据提取并发送邮件，添加附件

#!/usr/bin/python3import smtplibfrom email.mime.text import MIMETextfrom email.header import Headerfrom email.mime.base import MIMEBasefrom email.mime.multipart import MIMEMultipartimport pymys...
复制链接

扫一扫