python读取mysql数据生成excel和html并发送指定邮箱

需求

由于业务需要，需读取mysql数据生成excel，并以附件的形式发送到指定邮箱,正文内容也以表格的形式进行数据展示

分析

采用pymysql进行mysql数据的读取，通过openpyxl处理读取的数据并写入excel,之后通过smtplib将相关数据文件发送到指定邮箱

实现

1.pymysql读取mysql表数据返回result

import pymysql

class MysqlCon():
    def __init__(self,host,user,password,database,charset):
        '''
        :param host: 主机名
        :param user: 用户名
        :param password: 密码
        :param database: 数据库名
        :param charset: 编码
        '''
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self.cursor = None
        self.connection = None

    def __enter__(self):
        self.connection = pymysql.connect(host=self.host, user=self.user
                                          ,password=self.password, database=self.database, charset=self.charset)
        self.cursor = self.connection.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()


if __name__ == '__main__':
    host = 'mysql-test-test.test.local'
    username = 'test'
    password = 'test@test'
    database = 'test'
    charset = 'utf8'
    sql = 'select f1,f2,f3 from test'
    with MysqlCon(host,username,password,database,charset) as mycursor:
        mycursor.execute(sql)
        result = mycursor.fetchall()

2.openpyxl将读取的result转存excel

import os
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import time
import logging

def create_xlsx_file(file_path,file_name):
    '''
    用于创建xlsx文件，存在则备份建新的，反之直接创建
    :param file_path: 文件所在文件夹
    :param file_name: 文件名称
    :return:
    '''
    dir_exist = os.path.exists(file_path)
    if not dir_exist:
        os.mkdir(file_path)

    file_comp_path = os.path.join(file_path,file_name)
    file_comp_path_copy =file_comp_path +  "." + str(time.time())
    file_exist = os.path.exists(file_comp_path)
    if file_exist:
        os.rename(file_comp_path,file_comp_path_copy)

    wb = Workbook()
    wb.save(file_comp_path)
    wb.close()

def result_to_excel(file_path, file_name, result, excel_head, sheet_name):
    '''
    用于将mysql中读取的result生成合适的excel
    :param file_path: 文件路径
    :param file_name: 文件名称
    :param result: 结果集
    :param excel_head: excel表头
    :param sheet_name: excel sheet名称
    :return:
    '''
    file_comp_path = os.path.join(file_path, file_name)

    wb = load_workbook(file_comp_path)
    ws = wb.create_sheet(sheet_name, 0)
    ws.append(excel_head)

    # 写入数据
    ser_no = 0 #增加一个序号
    for row in result:
        row_data = []
        ser_no = ser_no + 1
        row_data.append(str(ser_no))
        for i in range(len(excel_head) - 1):
            f = row[i]
            row_data.append(f)
        ws.append(row_data)

    # 调整格式
    alignment = Alignment('center', 'center')
    for col_idx in range(1, ws.max_column + 1):
        column = get_column_letter(col_idx)
        max_length = 0
        for row in ws.iter_rows(min_row=1, values_only=False):
            try:
                cell_position = row[col_idx - 1]
                cell_position.alignment = alignment
                cell_value = str(cell_position.value)
                if cell_value != 'None':
                    cell_length = len(cell_value)
                    if cell_length <= 5:
                        cell_length = cell_length * 4
                    else:
                        cell_length = cell_length * 2
                    if cell_length > max_length:
                        max_length = cell_length
            except Exception as e:
                logging.error('excel表格样式处理出错了。。。')
        adjusted_width = max_length
        ws.column_dimensions[column].width = adjusted_width

    wb.save(file_comp_path)
    wb.close()

if __name__ == '__main__':
    file_path = './20230413'
    file_name = 'test.xlsx'
    result = None # mysql中查询的结果
    excel_head = ['f1','f2']
    sheet_name = 'test'
    
    create_xlsx_file(file_path,file_name)
    result_to_excel(file_path, file_name, result, excel_head, sheet_name)

3.将读取的result转html

def convert_result_to_html(heads,result):
    '''
    mysql查询result转html的table
    :param heads: 表头
    :param result: 结果集
    :return:
    '''
    t_head = ''
    t_head_sample = '<th>{0}</th>'
    for head in heads:
        t_head += t_head_sample.format(head)
    t_head = '<thead><tr>' + t_head + '</tr></thead>\n'

    t_body = ''
    t_body_all = ''
    t_body_sample = '<td>{0}</td>'
    for row in result:
        for i in range(len(row)):
            f = row[i]
            t_body += t_body_sample.format(f)
        t_body_all = t_body_all + '<tr>' + t_body + '</tr>\n'
    t_body = '<tbody>' + t_body_all + '</tbody>'
    table = '<table>\n' + t_head + t_body + '\n</table>'
    return table

def combine_part_to_all_html(tables):
    '''
    用于将多个表table合并至一个html中
    :param tables: table 列表
    :return: 
    '''
    table = ''
    for table in tables:
        table += table
    html = None
    thead = '''
        <head>
            <style type="text/css">
                p{
                    color: red;
                    font-size: 1.1em;
                    margin : 0.2em
                }
                table{
                    width: 100%;
                    border-collapse: collapse;
                }

                table caption{
                    font-weight: bold;
                    margin: 0.5em 0;
                }

                th,td{
                    font-size: 0.8em;
                    border: 1px solid #999;
                    text-align: center;
                    padding: 10px 0;
                }

                table thead tr{
                    background-color: #008c8c;
                    color: #fff;
                }
            </style>
        </head>'''
    html = '<html>' + thead + '<body>' + table + '</body>' + '</html>'
    return html

if __name__ == '__main__':
    # 表1
    heads1 = ['f1','f2','f3','f4'] # 表头
    result1 = None # mysql查询结果
    table1 = convert_result_to_html(heads1,result1)

    # 表2
    heads2 = ['f1','f2','f3','f4'] # 表头
    result2 = None # mysql查询结果
    table2 = convert_result_to_html(heads2,result2)

    tables = [table1,table2]
    html = combine_part_to_all_html(tables)

4.采用smtp将生成的excel与html发送到指定邮箱

import os
import smtplib
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
import logging

def mail(html_msg,my_user,my_cc,file_path,file_name):
    '''
    用于将excel附件 与 html正文发送到指定邮箱
    :param html_msg: 正文主体
    :param my_user: 收件人
    :param my_cc: 抄送人
    :param file_path: 文件路径
    :param file_name: 文件名称
    :return: 
    '''
    ret = True
    if my_user is not None and html_msg is not None:
        # 发件人邮箱账号
        my_sender = '321@123.com.cn'
        # user登录邮箱的用户名，password登录邮箱的密码（授权码，即客户端密码，非网页版登录密码），但用腾讯邮箱的登录密码也能登录成功
        my_pass = '123321'
        try:
            msg = MIMEMultipart()
            # 邮件内容
            content = MIMEText(html_msg, 'html', 'utf-8')
            msg.attach(content)
            # 括号里的对应发件人邮箱昵称、发件人邮箱账号
            msg['From'] = my_sender
            # 括号里的对应收件人邮箱昵称、收件人邮箱账号
            msg['To'] = ",".join(my_user)
            # 邮件的主题
            msg['Subject'] = "测试一下"
            # 抄送人
            if my_cc is not None:
                msg['Cc'] = ",".join(my_cc)
                all_receivers = my_user + my_cc
            else:
                all_receivers = my_user

            hidden = ['123.qq.com'] #隐藏发送人邮箱
            all_receivers = hidden + all_receivers
            xlsx = MIMEApplication(open(os.path.join(file_path,file_name), 'rb').read())  # 打开Excel,读取Excel文件
            xlsx["Content-Type"] = 'application/octet-stream'  # 设置内容类型
            xlsx.add_header('Content-Disposition', 'attachment', filename=file_name)  # ？
            msg.attach(xlsx)

            # SMTP服务器，腾讯企业邮箱端口是465，腾讯邮箱支持SSL(不强制)， 不支持TLS
            # qq邮箱smtp服务器地址:smtp.qq.com,端口号：456
            # 163邮箱smtp服务器地址：smtp.163.com，端口号：25
            server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)
            # 登录服务器，括号中对应的是发件人邮箱账号、邮箱密码
            server.login(my_sender, my_pass)
            # 发送邮件，括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
            server.sendmail(my_sender, all_receivers, msg.as_string())
            # 关闭连接
            server.quit()
            # 如果 try 中的语句没有执行，则会执行下面的 ret=False
        except Exception:
            logging.error("---------邮件发送出错-------")
            ret = False
    else:
            logging.error("---------收件人 和 发送消息体 都不能为空-------")
            ret = False
    return ret

5.汇总

将以上4个部分组合起来，便完成了需求的实现