[ python ] 查询数据库生成Excel并发送邮件
开发要求：
由于管理后台导出数据非常缓慢，找程序员解决无果后，自己动手写了一个脚本，每天定时将报表发送给业务部门。
1. 通过条件查询MySQL获取数据
2. 将获取的数据写入到Excel中，对应字段名
3. 将Excel作为附件内容，将邮件发送至相关人员
程序：
1. README
# 作者：hkey # 博客地址：hukey.cnblogs.com # http://www.cnblogs.com/hukey/p/8983831.html # 目录结构： PS: 功能比较单一，为了方便管理写入了一个文件中，有如下两个类： CreateExcel: 查询数据库并生成Excel SendMail: 将Excel作为附件内容，将邮件发送至相关人员 # 使用说明： 数据库信息和邮件信息内容都需要手动填写，确保无误; 可实现发送至多人;
2. 数据库脚本：
-- 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
3. 程序代码
# -*- 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', '0') 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': '123456', '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()
运行结果：
