Python将mysql中表导出为Excel表格

需求：Python将mysql中表导出为Excel表格

mysql表中的数据：

测试环境：

平台：Windows10、centos7
python版本：python3.7(以下脚本兼容python2)

python库安装：

pip install pymysql
pip install MySQLdb
pip install xlwt

新建py——python_mysql_excel.py

# coding: utf-8
# author: sirxy
# created_time: 2020/12/23

import sys
import xlwt

# 当前的python版本
if sys.version_info >= (3, 0):
   import pymysql as mysql
else:
   import MySQLdb as mysql

# mysql信息
host = '127.0.0.1'
port = 3306
user = 'root'
passwd = 'sirxy'
db = 'ceshi'
charset = 'utf8'

# 连接mysql，获取cursor
conn = mysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
cursor = conn.cursor()

table = 'runoob_w3cnote'
count = cursor.execute("select * from {table};".format(table=table))
print(count)

# 重置游标的位置
cursor.scroll(0, mode='absolute')
# 拿到该条SQL所有结果
results = cursor.fetchall()
print(results)

# 拿到该表里面的字段名称
fields = cursor.description
print(fields)

workbook = xlwt.Workbook()
sheet = workbook.add_sheet(table, 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)):
      value = results[row - 1][col]
      if not value:
         value = ''
      sheet.write(row, col, '%s' % value)

workbook.save(r'./{db}_{table}.xlsx'.format(db=db, table=table))

运行代码，输出成果：

打开即可看到文件内容：

以上。