基于python的通过sql自动生成excel数据

 

好久不更新 了... 今天来写一个 sql转换成excel的工具； 目的是给哪些linux用户使用此类工具！

且看源码

import operator
import pymysq
import xlwt
'''
__author__ =rianley
__blog__ = https://www.cnblogs.com/rianley

'''
'sql导出excel方法'
def toExcel(host,port,user,passwd,db,charset,sql,sheet_name,out_path):
    db = pymysql.connect(
        host=host,
        port=int(port),
        user=user,
        passwd=passwd,
        db=db,
        charset=charset
    )
    cursor = db.cursor()
    count = cursor.execute(sql)
    print("查询出" + str(count) + "条记录")

    # 来重置游标的位置
    cursor.scroll(0, mode='absolute')
    # 搜取所有结果
    results = cursor.fetchall()

    # 获取MYSQL里面的数据字段名称
    fields = cursor.description
    workbook = xlwt.Workbook()  # workbook是sheet赖以生存的载体。
    sheet = workbook.add_sheet(sheet_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)):
            if  results[row-1][col] is None:
                sheet.write(row, col, u'%s' % '')
            else:
                sheet.write(row, col, u'%s' % results[row - 1][col])

    workbook.save(out_path)


if __name__ == '__main__':



    host = "127.0.0.1"
    port = 3306
    user = 'root'
    passwd = "123456"
    db = "test"
    charset = "utf8"
    print("mysql连接是否使用默认配置？y/n")
    choose = input()
    if operator.eq('n',choose) or operator.eq('n',choose):
        print("host:")
        host = input()
        print("port:")
        port = input()
        print("userName:")
        user = input()
        print("passWord:")
        passwd = input()
        print("databaseName:")
        db = input()
    print("请输入Excel表格名称：")
    sheet_name = input()
    print("请粘贴您的SQL相对文件路径:")
    sql_path = input()
    with open(sql_path,'r',encoding='utf8')as f:
        sql=f.read()
    print("存储的路径")
    file_path=input()
    out_path = r''+file_path+'/'+sheet_name+'.xls'
    # 防止输入//不被系统识别
    out_path = out_path.replace('//','/')

    print('输入路径为',out_path)
    toExcel(host,port,user,passwd,db,charset,sql,sheet_name,out_path)
    print("导出成功您的文件位置为：",out_path)