#coding:utf8
importsysimportxlwtimportmysql.connectorimportdatetimeimportsend_mailimportosimporttime
nowtime= datetime.datetime.now().strftime('%Y%m%d')
oddir="/app/crontab/python"filename= "%s/test%s.xls"%(oddir,nowtime)
file_count=0
end_date=time.strftime("%Y-%m-%d", time.localtime())
sql='''select* from test t where t.create_time > '2019-07-01 00:00:00' and t.create_time
mail_list={'sender':'test@PH-LOTH-V159.localdomain', #发件人
'receivers':'test@yonyou.com', #收件人
'title':'注册统计', #邮件主题
'content':'统计数据', #邮件内容
'attfilename':'',#附件内容
'server':'localhost' #发送邮件服务器。默认为本机
}defget_conn():
conn= mysql.connector.connect(**config)returnconndefquery_all(cur, sql, args):'''#参数说明
:param cur:
:param sql: 执行的sql
:param args: sql中的桉树
:return:'''
print(sql)print(args)
cur.execute(sql, args)returncur.fetchall()defexport_excle(filename, sql, outputpath):'''# 参数说明
:param filename: 文件名称
:param sql: 执行脚本
:param outputpath: 输出路径
:return:'''file_count=0try:
conn=get_conn()
cur=conn.cursor()#查询结果
results = query_all(cur=cur, sql=sql, args=[end_date])#获取MYSQL里面的数据字段名称
fields =cur.description#获取excle对象
workbook =xlwt.Workbook()
sheet= workbook.add_sheet('sheet1', cell_overwrite_ok=True)#not isinstance(test[2][0],str)
#写入表头信息
for line inrange(0, len(fields)):ifisinstance(fields[line][0], (bytes, bytearray)):
sheet.write(0, line, u'%s' %(fields[line][0]).decode())else:
sheet.write(0, line, u'%s' %(fields[line][0]))#写入数据信息
row = 1 #第二行开始
col =0
file_count=len(results)for row in range(1, len(results)+1):for col inrange(0, len(fields)):if isinstance(results[row - 1][col], (bytes, bytearray)):
sheet.write(row, col, u'%s' % results[row - 1][col].decode())else:
sheet.write(row, col, u'%s' % results[row - 1][col])#workbook.save("%s/%s.xls"%(oddir,filename) ) #filename
workbook.save(filename)finally:ifconn:
conn.close()returnfile_count#结果测试
if __name__ == "__main__":#sql = input("请输入要查询的sql:")
#filename = input("请输入excle的名字")
file_count = export_excle(filename, sql, oddir) #执行导出结果
ifos.path.exists(filename):if file_count >=1:print("邮件已经发送 %s"%(mail_list['attfilename']))
mail_list['attfilename'] =filenameprint("邮件已经发送 %s"%(mail_list['attfilename']))
send_mail.sendmail(**mail_list)else:print("文件大小为空,请检查")else:print("文件不存在,请联系管理员检查脚本是否有异常")