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文件，存在