# 作者：hkey

# 博客地址：hukey.cnblogs.com

# http://www.cnblogs.com/hukey/p/8983831.html

# 目录结构：

    PS: 功能比较单一，为了方便管理写入了一个文件中，有如下两个类：

        CreateExcel: 查询数据库并生成Excel

        SendMail: 将Excel作为附件内容，将邮件发送至相关人员

# 使用说明：

    数据库信息和邮件信息内容都需要手动填写，确保无误;

    可实现发送至多人;

-- MySQL dump 10.14  Distrib 5.5.56-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: user_info

-- ------------------------------------------------------

-- Server version    5.5.56-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `user`

--

DROP TABLE IF EXISTS `user`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `user` (

  `id` int(16) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `city` varchar(255) DEFAULT NULL,

  `class` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `user`

--

LOCK TABLES `user` WRITE;

/*!40000 ALTER TABLE `user` DISABLE KEYS */;

INSERT INTO `user` VALUES (1,'小飞','北京','三年二班'),(2,'小四','上海','三年一班'),(3,'小K','西安','三年三班');

/*!40000 ALTER TABLE `user` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-05-03  9:45:06

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

# Author: hkey

from email.header import Header

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

# from email.utils import parseaddr, formataddr

from email import encoders

from email.mime.base import MIMEBase

import smtplib, os

import pymysql, xlwt

class CreateExcel(object):

    '''查询数据库并生成Excel文档'''

    def __init__(self, mysql_info):

        self.mysql_info = mysql_info

        self.conn = pymysql.connect(host = self.mysql_info['host'], port = self.mysql_info['port'],

                               user = self.mysql_info['user'], passwd = self.mysql_info['passwd'],

                               db = self.mysql_info['db'], charset='utf8')

        self.cursor = self.conn.cursor()

    def getUserData(self, sql):

        # 查询数据库

        self.cursor.execute(sql)

        table_desc = self.cursor.description

        result = self.cursor.fetchall()

        if not result:

            print('没数据。')

            # 返回查询数据、表字段

        print('数据库查询完毕'.center(30, '#'))

        return result, table_desc

    def writeToExcel(self, data, filename):

        # 生成Excel文档

        # 注意：生成Excel是一列一列写入的。

        result, fileds = data

        wbk = xlwt.Workbook(encoding='utf-8')

        # 创建一个表格

        sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True)

        for filed in range(len(fileds)):

            # Excel插入第一行字段信息

            sheet1.write(0, filed, fileds[filed][0]) # (行，列，数据)

        for row in range(1, len(result)+1):

            # 将数据从第二行开始写入

            for col in range(0, len(fileds)):

                sheet1.write(row, col, result[row-1][col]) #(行, 列, 数据第一行的第一列)

        wbk.save(filename)

    def close(self):

        # 关闭游标和数据库连接

        self.cursor.close()

        self.conn.close()

        print('关闭数据库连接'.center(30, '#'))

class SendMail(object):

    '''将Excel作为附件发送邮件'''

    def __init__(self, email_info):

        self.email_info = email_info

        # 使用SMTP_SSL连接端口为465

        self.smtp = smtplib.SMTP_SSL(self.email_info['server'], self.email_info['port'])

        # 创建两个变量

        self._attachements = []

        self._from = ''

    def login(self):

        # 通过邮箱名和smtp授权码登录到邮箱

        self._from = self.email_info['user']

        self.smtp.login(self.email_info['user'], self.email_info['password'])

    # def _format_addr(self, s):

    #     name, addr = parseaddr(s)

    #     return formataddr((Header(name, 'utf-8').encode(), addr))

    def add_attachment(self):

        # 添加附件内容

        # 注意：添加附件内容是通过读取文件的方式加入

        file_path = self.email_info['file_path']

        with open(file_path, 'rb') as file:

            filename = os.path.split(file_path)[1]

            mime = MIMEBase('application', 'octet-stream', filename=filename)

            mime.add_header('Content-Disposition', 'attachment', filename=('gbk', '', filename))

            mime.add_header('Content-ID', '<0>')

            mime.add_header('X-Attachment-Id', '')

            mime.set_payload(file.read())

            encoders.encode_base64(mime)

            # 添加到列表，可以有多个附件内容

            self._attachements.append(mime)

    def sendMail(self):

        # 发送邮件，可以实现群发

        msg = MIMEMultipart()

        contents = MIMEText(self.email_info['content'], 'plain', 'utf-8')

        msg['From'] = self.email_info['user']

        msg['To'] = self.email_info['to']

        msg['Subject'] = self.email_info['subject']

        for att in self._attachements:

            # 从列表中提交附件，附件可以有多个

            msg.attach(att)

        msg.attach(contents)

        try:

            self.smtp.sendmail(self._from, self.email_info['to'].split(','), msg.as_string())

            print('邮件发送成功，请注意查收'.center(30, '#'))

        except Exception as e:

            print('Error:', e)

    def close(self):

        # 退出smtp服务

        self.smtp.quit()

        print('logout'.center(30, '#'))

if __name__ == '__main__':

    # 数据库连接信息

    mysql_dict = {

        'host': '192.168.118.13',

        'port': 3306,

        'user': 'root',

        'passwd': '',

        'db': 'user_info'

    }

    # 邮件登录及内容信息

    email_dict = {

    # 手动填写，确保信息无误

        "user": "xxx@126.com",

        "to": "xxx@qq.com, xxx@qq.com", # 多个邮箱以','隔开；

        "server": "smtp.126.com",

        'port': 465,    # values值必须int类型

        "username": "xxx@126.com",

        "password": "xxxx",

        "subject": "user测试表",

        "content": '用户测试数据',

        'file_path': 'example.xls'

    }

    sql = 'select * from user'

    # filename = 'example.xls'

    create_excel = CreateExcel(mysql_dict)

    sql_res = create_excel.getUserData(sql)

    create_excel.writeToExcel(sql_res,email_dict['file_path'])

    create_excel.close()

    sendmail = SendMail(email_dict)

    sendmail.login()

    sendmail.add_attachment()

    sendmail.sendMail()

    sendmail.close()