读取数据生成excel
from utils.db_config import engine_web_logs
import pandas as pd
class ExcelUser:
engine_web_logs = engine_web_logs()
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()
发送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"))
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"))
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['Subject'] = Header('用户访问行为', 'utf-8')
message.attach(MIMEText('用户访问行为:详情请看附件', 'plain', 'utf-8'))
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))
完整代码
'''
生成用户行为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()
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"))
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"))
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['Subject'] = Header('用户访问行为', 'utf-8')
message.attach(MIMEText('用户访问行为:详情请看附件', 'plain', 'utf-8'))
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()