[大数据]python使用xlsxwriter将mysql表导出为excel

写个小练习，记录一下
（类似于navicat把一个表导出为excel）

跟人navicat比不了，人家处理的更好。。。日期导出来处理不好，本人只能做到这样了。欢迎大佬指教！

上代码：

import datetime
import pymysql
import xlsxwriter as xw

host = "127.0.0.1"
username = "root"
password = "root"
dbname = "kaoshi_db"
port = 3307
charset = "utf8"


def get_data(sql_param):
    conn = pymysql.connect(host=host, user=username, passwd=password, db=dbname, port=port, charset=charset)
    cur = conn.cursor()
    cur.execute(sql_param)
    data1 = cur.fetchall()
    col1 = cur.description
    return data1, col1


def export_excel(data_param, col_param):
    print(data_param)
    # 新建excel
    workbook = xw.Workbook('myexcel.xlsx')
    # 新建工作薄
    worksheet = workbook.add_worksheet()
    # 写入数据
    cal_list = []
    for coll in col_param:
        cal_list.append(coll[0])
    print("列：", cal_list)
    worksheet.write_row('A1', cal_list)
    index = 2
    for param in data_param:
        data_list = []
        for param_param in param:
            if isinstance(param_param, datetime.datetime):
                param_param = param_param.strftime('%Y-%m-%d %H:%M:%S')
            data_list.append(param_param)
        worksheet.write_row("A" + str(index), data_list)
        index = index + 1
    # 关闭保存
    workbook.close()


if __name__ == '__main__':
    table = input("Enter a table name: ")
    # table = "t_wendang"
    sql = "select * from " + table
    data, col = get_data(sql)
    export_excel(data, col)

主要包含俩方法，一个是查数据，一个是向excel写数据。
扔到idea里，可以直接运行。

最后附上xlsxwriter官网：
https://xlsxwriter.readthedocs.io/example_demo.html#ex-demo