Python将MySQL的数据批量写入 Excel表

演示版本：Python-3.8.5、Mysql-8.0.26、WPS Office 2019

所需模块：xlwings、pymysql

代码如下：

import xlwings as xw

import pymysql as  py

#连接数据库

db = py.connect(host = 'localhost', user = 'root',\

                  password= 'qweiwei123', database= 'mydatabase')

cursor = db.cursor()

app = xw.App(visible = False)

wb = app.books.add()

sheet=wb.sheets.add('customers')

sql="select * from customers"

#获取数据库数据

cursor.execute(sql)

data = cursor.fetchall()

#获取表格的字段

table_field = [i[0] for i in cursor.description]

#拼接表格字段和数据

data = list(data)

data.insert(0,list(table_field))

#写入数据

sheet['A1'].expand('table').value = data

wb.save()

wb.close()

app.quit()

db.close()