经常用到数据库导入导出。

从csv导入mysql数据库前面已经有文章讲了。

链接如下：https://www.toutiao.com/a7019133460199522853/

这次讲讲把mysql数据导出成xls格式

import win32api
import win32con
import xlwt
import pymysql

host = '127.0.0.1'
user = 'root'
pwd = '13601994674'
db = 'wealth'
sql = 'select * from user_password'
sheet_name = 'user_password'
out_path = "user_password.xls"


def export():
    """导出数据"""


    conn = pymysql.connect(host='127.0.0.1', user='root', password='', database='wealth', charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute(sql)
    print(count)

    cursor.scroll(0, mode='absolute')
    results = cursor.fetchall()
    fields = cursor.description
    workbook = xlwt.Workbook()
    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):
        # print(results[row - 1][1])
        for col in range(0, len(fields)):
            print(results[row - 1][col])
            sheet.write(row, col, u'%s' % results[row - 1][col])
            # sheet.write(row,col,results[row-1][col])

    workbook.save(out_path)


try:
    export()
    win32api.MessageBox(0, "导出数据 成功", "提示", win32con.MB_OK)
except Exception as e:
    print(e)
    win32api.MessageBox(0, "导出数据 失败\n%s" % e, "提示", win32con.MB_OK)