1.需求：产品经常需要我们部门导出数据，以供他查看。
2.实际：只是一些重复性的操作，写一个脚本导出批量发送即可。
3.记录状态，并监控。
4.优化如果数据库查出无数据，则不创建附件，仅发送邮件通知即可。
5.优化某些列的值为用python取出是datetime类型，但写入excel后变成了float类型，思路：把datetime写入前转换成str字符串类型。
1):python:代码
# -*- coding: utf-8 -*-
# Author: jmmei201912
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
import time, datetime
#pip install email xlwt os
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, '#'))
print("%s:success"%nowdate)
except Exception as e:
print('Error:', e)
print("%s:error"%nowdate)
def close(self):
# 退出smtp服务
self.smtp.quit()
print('logout'.center(30, '#'))
if __name__ == '__main__':
#time
nowdate=datetime.date.today()
oneday=datetime.timedelta(days=1)
yesterday=nowdate-oneday
# 数据库连接信息
mysql_dict = {
'host': '192.168.1.0',
'port': 3306,
'user': 'sb',
'passwd': 'dsb',
'db': 'wjj-physique'
}
# 邮件登录及内容信息
email_dict = {
# 手动填写，确保信息无误
"user": "system.jk@126.com",
"to": "ying.ding@sb.com,kai.zhu@sb.com,jxu@sb.com,phzhang@sb.com,jylin@sb.com, nzhou@sb.com,bshu@sb.com;",
# 多个邮箱以','隔开；
"server": "smtp.126.com",
'port': 465, # values值必须int类型
"username": "system.jk@126.com",
"password": "sb123456",
"subject": "业务订单信息-昨天的%s"%yesterday,
"content": '数据见附件--导出DevOps小组',
'file_path': '%s-example.xls'%nowdate
}
sql = "select t.orders_no '业务单号', t.name '收件人姓名', t.phone_no '收件人手机', concat_ws(t.province_name,t.city_name,t.district_name,t.town_name,t.address) '收件人地址', t.goods_name '品名', t.goods_num '数量', t.remark '备注', t.weight '重量', t.bulk '体积' from free_orders t where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1"
# 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()
#增加无数据，仅发送邮件通知
# -*- coding: utf-8 -*-
# Author: jmmei
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.mime.base import MIMEBase
import smtplib, os
import pymysql, xlwt
import time, datetime
#pip install xlwt os
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, '#'))
print("%s:success"%nowdate)
except Exception as e:
print('Error:', e)
print("%s:error"%nowdate)
def close(self):
# 退出smtp服务
self.smtp.quit()
print('logout'.center(30, '#'))
if __name__ == '__main__':
#time
nowdate=datetime.date.today()
oneday=datetime.timedelta(days=1)
yesterday=nowdate-oneday
# 数据库连接信息
mysql_dict = {
'host': '192.168.1.1',
'port': 3306,
'user': 'read',
'passwd': 'read',
'db': 'wjj-physique'
}
# 邮件登录及内容信息
email_dict = {
# 手动填写，确保信息无误
"user": "system.jk@sb.com.cn",
"to": "ying.ding@sb.com,kai.zhu@sb.com", # 多个邮箱以','隔开；
"server": "mail.sb.com.cn",
'port': 465, # values值必须int类型
"username": "system.jk@sb.com.cn",
"password": "sb123456",
"subject": "舌诊领取体质饮品业务订单信息-昨天的%s"%yesterday,
"content": '数据见附件--运维小组',
'file_path': '%s-example.xls'%nowdate
}
not_email_dict = {
# 手动填写，确保信息无误
"user": "system.jk@sb.com.cn",
"to": "ying.ding@sb.com,kai.zhu@sb.com,jxu@sb.com.cn",
"server": "mail.sb.com.cn",
'port': 465, # values值必须int类型
"username": "system.jk@sb.com.cn",
"password": "sb123456",
"subject": "舌诊领取体质饮品业务订单信息-昨天的%s"%yesterday,
"content": '昨天%s暂无数据导出---运维小组'%yesterday,
}
sql = "select t.orders_no '业务单号', t.name '收件人姓名', t.phone_no '收件人手机', concat_ws(t.province_name,t.city_name,t.district_name,t.town_name,t.address) '收件人地址', t.goods_name '品名', t.goods_num '数量', t.remark '备注', t.weight '重量', t.bulk '体积' from free_orders t where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1"
# filename = 'example.xls'
create_excel = CreateExcel(mysql_dict)
sql_res = create_excel.getUserData(sql)
if not sql_res[0]:
sendmail = SendMail(not_email_dict)
sendmail.login()
#sendmail.add_attachment()
sendmail.sendMail()
sendmail.close()
else:
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()
#优化datetime类型写入excel后变成float
# -*- coding: utf-8 -*-
# Author: jmmei20191227
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
import time, datetime
#pip install email xlwt os
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]) # (行，列，数据)
#print(0, filed, fileds[filed][0])
for row in range(1, len(result)+1):
# 将数据从第二行开始写入
for col in range(0, len(fileds)):
print(type(result[row-1][col]))
if (isinstance(result[row-1][col],datetime.datetime)): #判断如果列值为datetime类型，则转换成str
row_str=(result[row-1][col]).strftime("%Y-%m-%d %H:%M:%S")
print("---------datetime:%s-------------->>>>"%result[row-1][col])
else:
row_str=result[row-1][col]
sheet1.write(row, col, row_str) #(行, 列, 数据第一行的第一列)
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, '#'))
print("%s:success"%nowdate)
except Exception as e:
print('Error:', e)
print("%s:error"%nowdate)
def close(self):
# 退出smtp服务
self.smtp.quit()
print('logout'.center(30, '#'))
if __name__ == '__main__':
#time
nowdate=datetime.date.today()
oneday=datetime.timedelta(days=1)
yesterday=nowdate-oneday
# 数据库连接信息
mysql_dict = {
'host': '192.168.1.x',
'port': 3306,
'user': 'read',
'passwd': 'read',
'db': 'wjj-physique'
}
# 邮件登录及内容信息
email_dict = {
# 手动填写，确保信息无误
"user": "system.jk@sb.com.cn",
"to":"xiaoming@126.com",
"server": "mail.wisepool.com.cn",
'port': 465, # values值必须int类型
"username": "system.jk@sb.com.cn",
"password": "sb123456",
"subject": "舌象数据报表-昨天的%s"%yesterday,
"content": '数据见附件--运维小组',
'file_path': '%s-example2.xls'%nowdate
}
not_email_dict = {
# 手动填写，确保信息无误
"user": "system.jk@wisepool.com.cn",
"to": "xiaoming@126.com",
"server": "mail.wisepool.com.cn",
'port': 465, # values值必须int类型
"username": "system.jk@sb.com.cn",
"password": "sb123456",
"subject": "舌象数据报表-昨天的%s"%yesterday,
"content": '昨天%s暂无数据导出---运维小组'%yesterday,
}
sql='''select @rownum := @rownum + 1 "序号", t.* from
(SELECT @rownum := 0) r,
(
select
case when c.app_key = '01' then 'APP'
when c.app_key = '03' then '微信小程序'
when c.app_key = '07' then '小康APP'
when c.app_key = '18' then '字节跳动小程序-舌诊'
when c.app_key = '19' then '微信小程序-舌诊'
else c.app_key end '最近一次测试渠道', a.account_no '用户编号',
b.phone_no '用户手机号',b.name '用户姓名',
case when b.sex = '1' then '男'
when b.sex = '2' then '女'
else '未知' end '性别',
case when
date_add(b.birthday,interval year(now())-year(b.birthday) year)>=now()
then
year(now())-year(b.birthday)-1
else
year(now())-year(b.birthday)
end '年龄',
c.upload_date '最近一次测试日期' ,c.constitution_explain '最近一次身体状况',c.constitution '最近一次测试结果（中医体质)',d.count '累计测试次数',
case when f.account_no is not null then '是'
else '否' end '是否国民员工',
case when e.orders_no is not null then '是'
else '否' end '是否已领取饮品',
e.orders_no '业务单号', e.create_time '领取时间', e.name '收件人姓名',e.phone_no '收件人手机', concat_ws(' ',e.province_name,e.city_name,e.district_name,e.town_name,e.address) '收件人地址', e.goods_name '品名', e.goods_num '数量', e.remark '备注', e.weight '重量', e.bulk '体积' from
(select * from `wjj-physique`.tongue where member_id = 0 and TO_DAYS(NOW()) - TO_DAYS(upload_date) = 1 group by account_no) a
left join (SELECT dd.*,ddd.app_key,ddd.constitution,ddd.constitution_explain FROM (SELECT account_no, member_id, max(upload_date) as upload_date FROM `wjj-physique`.tongue where member_id = 0 and TO_DAYS(NOW()) - TO_DAYS(upload_date) = 1 GROUP BY account_no) dd LEFT JOIN (select * from `wjj-physique`.tongue where member_id = 0) ddd ON ddd.account_no=dd.account_no and ddd.upload_date=dd.upload_date) c on c.account_no = a.account_no
left join (select account_no,member_id,count(*) count from `wjj-physique`.tongue where member_id = 0 and TO_DAYS(NOW()) - TO_DAYS(upload_date) = 1 group by account_no) d on d.account_no = a.account_no
left join (select * from `wjj-physique`.free_orders where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1) e on e.account_no = a.account_no
left join (select account_no from `wjj-account-system`.account_withdraw_black_info) f on f.account_no = a.account_no
left join (select bb.phone_no,bb.account_no,cc.name,cc.sex,cc.birthday from `wjj-account-system`.account_customer_info bb, `wjj-account-system`.account_customer_properties cc where bb.account_no = cc.account_no ) b on a.account_no = b.account_no
order by c.upload_date desc
) t;'''
#sql = "select t.orders_no '业务单号', t.name '收件人姓名', t.phone_no '收件人手机', concat_ws(t.province_name,t.city_name,t.district_name,t.town_name,t.address) '收件人地址', t.goods_name '品名', t.goods_num '数量', t.remark '备注', t.weight '重量', t.bulk '体积' from free_orders t where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1"
# filename = 'example.xls'
create_excel = CreateExcel(mysql_dict)
sql_res = create_excel.getUserData(sql)
if not sql_res[0]:
sendmail = SendMail(not_email_dict)
sendmail.login()
#sendmail.add_attachment()
sendmail.sendMail()
sendmail.close()
else:
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()
2):shell && crontab
#sendmail excel to sb chanping
50 09 * * * python3 /srv/py_export.py>/tmp/excel.log &
#!/bin/sh
function excel {
Count=`cat /tmp/excel.log|grep "success"|wc -l`
if [ $Count -eq 0 ];then
echo 0
else
echo 1
fi
}
$1
3)效果图
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
