# coding:utf8
import sys
reload(sys)
sys.setdefaultencoding('utf8')
# author： 'zkx'
# date： '2018/3/11'
# Desc：从数据库中导出数据到excel数据表中
#已封装，可以直接使用，只需更改sql语句即可
import xlwt
import MySQLdb
def export(host,user,password,dbname,table_name,outputpath):
conn = MySQLdb.connect(host,user,password,dbname,charset='utf8')
cursor = conn.cursor()
#时间戳相减，转换为时分秒 导出到excel时间格式要统一（"%Y-%m-%d %H:%i:%S"），不然导出到excel为null
#concat(floor((lasto/1000-first/1000)/3600),"小时",floor(mod((last/1000-first/1000),3600)/60),"分钟",round(mod(mod((last/1000-first/1000),3600),60)),"秒")
count = cursor.execute('select node,nodealias,alertgroup,FROM_UNIXTIME(firstoccurrence/1000,"%Y-%m-%d %H:%i:%S") zuizao,FROM_UNIXTIME(lastoccurrence/1000,"%Y-%m-%d %H:%i:%S")zuiwan,(lastoccurrence/1000-firstoccurrence/1000) shijiancha from '+table_name+' where alertgroup="OIDTableMonitor-mdTemp" and DATE_SUB(CURDATE(),INTERVAL 7 DAY) <=DATE(FROM_UNIXTIME(firstoccurrence/1000,"%Y-%m-%d %H:%i:%S"))')
print count
# 重置游标的位置
cursor.scroll(0,mode='absolute')
# 搜取所有结果
results = cursor.fetchall()
# 获取MYSQL里面的数据字段名称
fields = cursor.description
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('table_'+table_name,cell_overwrite_ok=True)
# 写上字段信息
for field in range(0,len(fields)):
sheet.write(0,field,fields[field][0])
# 获取并写入数据段信息
row = 1
col = 0
for row in range(1,len(results)+1):
for col in range(0,len(fields)):
sheet.write(row,col,u'%s'%results[row-1][col])
workbook.save(outputpath)
# 测试
if __name__ == "__main__":
#mysql-ip，用户，密码，要执行库，要查询表，存放路径
export('12.16.15.14','eccom','eco','nete','ftnt',r'/opt/datest.xls')
