python从mysql导出数据导exc

发布时间：2019-09-17 07:45:14编辑：auto阅读（1912）

    # 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')



关键字：

上一篇： Python：***测试开源项目

下一篇： Python运行环境与异常处理