python自动导出数据脚本_python实用脚本-定时导出数据库中的数据并且发送数据到邮箱...

#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("文件不存在，请联系管理员检查脚本是否有异常")