读取数据生成excel

from utils.db_config import engine_web_logs
import pandas as pd
class ExcelUser:
    engine_web_logs = engine_web_logs()#创建数据实例连接
    # 生成excel
    def exceluser(self):
		sql_e = """SELECT 表字段 FROM 表名"""
		df = pd.read_sql(sql_e, self.engine_web_logs)
		df.sort_values(by=['num'], inplace=True, ascending=False) #按字段num进行倒叙排序
        df.reset_index(drop=True, inplace=True)#删除原来的索引，从0开始
        writer = pd.ExcelWriter(
            '/mydata/logs/user/nameuser{}.xlsx'.format(dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d"))) #excel的存储路径
        df.to_excel(writer, float_format='%.5f')#数据整体写入excel文件
        writer.save(）#保存excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14

发送excel文件

    def email_u(self):
        from_mail = '1234@tenxun.com' #自己腾讯企业邮箱
        from_name1 = '小明' #名字
        from_name = '用户访问行为_{}.xlsx'.format(dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d")) #excel的名字
        to_mails = ['123@tengxun.com', '456@tengxun.com'] #要发送人的腾讯企业邮箱
        to_names = ['小百', '小蓝'] #与邮箱对应人的名字
        file_path = '/mydata/logs/user/nameuser{}.xlsx'.format(
            dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d"))#excel的存储路径
        print('开始发邮件')
        # 创建一个带附件的实例
        message = MIMEMultipart()

        message['From'] = self._format_addr(from_name1 + ' <%s>' % from_mail)
        message['To'] = self._format_addr(to_names[0] + ' <%s>' % to_mails[0])
        # message['To'] = self._format_addr(to_names[1] + ' <%s>' % to_mails[1])
        message['Subject'] = Header('用户访问行为', 'utf-8')

        # 邮件正文
        message.attach(MIMEText('用户访问行为:详情请看附件', 'plain', 'utf-8'))

        # 构造附件1，传送当前目录下的filepath文件
        att1 = MIMEText(open(file_path, 'rb').read(), 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        att1.add_header('Content-Disposition', 'attachment', filename=from_name)
        message.attach(att1)

        try:
            print('连接smtp')
            smtp_obj = smtplib.SMTP_SSL()
            smtp_obj.connect('smtp.exmail.qq.com', 465)#
            smtp_obj.login(from_mail, '客户端登录的密码')
            smtp_obj.sendmail(from_mail, to_mails, message.as_string())
            print('邮件发送成功')
        except smtplib.SMTPException:
            print('无法发送邮件')

    def _format_addr(self, s):
        name, addr = parseaddr(s)
        return formataddr((Header(name, 'utf-8').encode(), addr))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

完整代码

# coding=utf-8
'''
生成用户行为excel 并发送邮件
'''

import sys
from importlib import reload

reload(sys)

sys.path.append('/mydata/logs/DataETL')

from utils.db_config import engine_web_logs
import pandas as pd
import datetime as dt
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import parseaddr, formataddr


class ExcelUser:
    engine_web_logs = engine_web_logs()

    # 生成excel
    def exceluser(self):
        sql_e = """SELECT 表字段 FROM 表名"""
        df = pd.read_sql(sql_e, self.engine_web_logs)
        df.sort_values(by=['num'], inplace=True, ascending=False)
        df.reset_index(drop=True, inplace=True)
        writer = pd.ExcelWriter(
            '/mydata/logs/user/nameuser{}.xlsx'.format(dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d")))
        df.to_excel(writer, float_format='%.5f')
        writer.save()
    # 发送邮件
    def email_u(self):
        from_mail = '1234@tenxun.com' #自己腾讯企业邮箱
        from_name1 = '小明' #名字
        from_name = '用户访问行为_{}.xlsx'.format(dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d")) #excel的名字
        to_mails = ['123@tengxun.com', '456@tengxun.com'] #要发送人的腾讯企业邮箱
        to_names = ['小百', '小蓝'] #与邮箱对应人的名字
        file_path = '/mydata/logs/user/nameuser{}.xlsx'.format(
            dt.datetime.strftime(dt.datetime.today().date(), "%Y%m%d"))#excel的存储路径
        print('开始发邮件')
        # 创建一个带附件的实例
        message = MIMEMultipart()

        message['From'] = self._format_addr(from_name1 + ' <%s>' % from_mail)
        message['To'] = self._format_addr(to_names[0] + ' <%s>' % to_mails[0])
        # message['To'] = self._format_addr(to_names[1] + ' <%s>' % to_mails[1])
        message['Subject'] = Header('用户访问行为', 'utf-8')

        # 邮件正文
        message.attach(MIMEText('用户访问行为:详情请看附件', 'plain', 'utf-8'))

        # 构造附件1，传送当前目录下的filepath文件
        att1 = MIMEText(open(file_path, 'rb').read(), 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        att1.add_header('Content-Disposition', 'attachment', filename=from_name)
        message.attach(att1)

        try:
            print('连接smtp')
            smtp_obj = smtplib.SMTP_SSL()
            smtp_obj.connect('smtp.exmail.qq.com', 465)#
            smtp_obj.login(from_mail, '客户端登录的密码')
            smtp_obj.sendmail(from_mail, to_mails, message.as_string())
            print('邮件发送成功')
        except smtplib.SMTPException:
            print('无法发送邮件')

    def _format_addr(self, s):
        name, addr = parseaddr(s)
        return formataddr((Header(name, 'utf-8').encode(), addr))


if __name__ == '__main__':
    EU = ExcelUser()
    EU.exceluser()
    EU.email_u()
    # EU.email_u()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83