开始免费使用
当前位置：首页 > Python > Python实现MySQL数据导出至Excel：从基础到自适应列宽

Python实现MySQL数据导出至Excel：从基础到自适应列宽

山海之间
山海之间
发布时间：2026-10-15| 5人浏览过
原创

本文介绍如何使用Python将MySQL数据库中的数据导出为Excel文件。内容涵盖环境配置、基础字段映射写法，以及通过DictCursor动态获取表结构的高级实现。针对长字段导致的显示问题，提供了基于xlwt的自适应列宽计算方法，确保导出的Excel表格美观且信息完整。

Python实现MySQL数据导出至Excel：从基础到自适应列宽

概述

本文旨在演示如何将MySQL数据库中的用户表数据导出并写入Excel文件。

环境配置与数据准备

环境说明

使用的MySQL版本为5.7。

数据库端口设置为3306。

目标数据库名称为test。

需要导出的数据表名为users。

该表的具体结构定义如下：


CREATE TABLE `users` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `username` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
 `password` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '密码',
 `phone` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '手机号',
 `email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '邮箱',
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表中已预置3行示例数据。


INSERT INTO `test`.`users` (`id`, `username`, `password`, `phone`, `email`, `create_time`) VALUES ('1', 'xiao', '123', '12345678910', '123@qq.com', '2020-04-10 01:22:07');
INSERT INTO `test`.`users` (`id`, `username`, `password`, `phone`, `email`, `create_time`) VALUES ('2', 'zhang', '123', '12345678910', '123@qq.com', '2020-04-10 01:22:07');
INSERT INTO `test`.`users` (`id`, `username`, `password`, `phone`, `email`, `create_time`) VALUES ('3', 'lisi', '123', '12345678910', '123@qq.com', '2020-04-10 01:22:07');

基础导出实现

基本实现方法

首先需安装必要的Python依赖模块。


pip3 install xlwt pymysql

核心脚本文件命名为test_excel.py。


#!/usr/bin/env python3
# coding: utf-8
import os
import xlwt
import pymysql
import datetime

class MysqlToExcel(object):
  def __init__(self):
    self.host = '10.212.21.92'
    self.user = 'root'
    self.passwd = 'abcd1234'
    self.db_name = 'test'
    self.port = 3306
    self.file_name = 'data.xls'

  def get_query_results(self):
    sql = "select * from test.users"

    conn = pymysql.connect(
      host=self.host,
      user=self.user,
      passwd=self.passwd,
      port=self.port,
      database=self.db_name,
      charset='utf8',
      cursorclass=pymysql.cursors.DictCursor
    )
    cur = conn.cursor() # 创建游标
    cur.execute(sql) # 执行sql命令
    result = cur.fetchall() # 获取执行的返回结果
    # print(result)
    cur.close()
    conn.close() # 关闭mysql 连接
    return result

  def generate_table(self):
    """
    生成excel表格
    :return:
    """
    # 删除已存在的文件
    if os.path.exists(self.file_name):
      os.remove(self.file_name)

    result = self.get_query_results()
    # print(result)
    if not result:
      print("查询结果为空")
      return False

    # 创建excel对象
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)

    # 列字段
    column_names = ['id','username','password','phone','email']

    # 写第一行，也就是列所在的行
    for i in range(0, len(column_names)):
      sheet1.write(0, i, column_names[i])

    # 写入多行
    num = 0 # 计数器
    for i in result:
      sheet1.write(num + 1, 0, i['id'])
      sheet1.write(num + 1, 1, i['username'])
      sheet1.write(num + 1, 2, i['password'])
      sheet1.write(num + 1, 3, i['phone'])
      sheet1.write(num + 1, 4, i['email'])
      # 日期转换为字符串
      value = i['create_time'].strftime('%Y-%m-%d %H:%M:%S')
      sheet1.write(num + 1, 5, value)

      num += 1 # 自增1

    # 保存文件
    f.save(self.file_name)

    # 判断文件是否存在
    if not os.path.exists(self.file_name):
      print("生成excel失败")
      return False

    print("生成excel成功")
    return True

if __name__ == '__main__':
  MysqlToExcel().generate_table()

运行脚本后的控制台输出结果如下。

生成的Excel文件内容查看效果如图所示。

动态字段获取与类型转换

进阶实现方案

在基础写法中，通常需要手动指定Excel表的列字段，例如：['id','username','password','phone','email']。

若遇到包含70个字段的庞大表，手动编写字段列表不仅耗时且易出错，能否实现动态获取表字段？答案是肯定的。

在创建数据库游标时，若指定了pymysql.cursors.DictCursor，则查询返回的每一行数据均为字典格式。

利用字典的keys()方法，即可直接获取表的所有字段名。

此外，还需处理非字符串类型的数据，将其转换为字符串格式以便写入Excel。

优化后的test_excel.py脚本代码如下。


#!/usr/bin/env python3
# coding: utf-8
import os
import xlwt
import pymysql
import datetime

class MysqlToExcel(object):
  def __init__(self):
    self.host = '10.212.21.92'
    self.user = 'root'
    self.passwd = 'abcd1234'
    self.db_name = 'test'
    self.port = 3306
    self.file_name = 'data.xls'

  def get_query_results(self):
    sql = "select * from test.users"

    conn = pymysql.connect(
      host=self.host,
      user=self.user,
      passwd=self.passwd,
      port=self.port,
      database=self.db_name,
      charset='utf8',
      cursorclass=pymysql.cursors.DictCursor
    )
    cur = conn.cursor() # 创建游标
    cur.execute(sql) # 执行sql命令
    result = cur.fetchall() # 获取执行的返回结果
    # print(result)
    cur.close()
    conn.close() # 关闭mysql 连接
    return result

  def generate_table(self):
    """
    生成excel表格
    :return:
    """
    # 删除已存在的文件
    if os.path.exists(self.file_name):
      os.remove(self.file_name)

    result = self.get_query_results()
    # print(result)
    if not result:
      print("查询结果为空")
      return False

    # 创建excel对象
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)

    # 第一行结果
    row0 = result[0]
    # 列字段
    column_names = list(row0)

    # 写第一行，也就是列所在的行
    for i in range(0, len(row0)):
      sheet1.write(0, i, column_names[i])

    # 写入多行
    # 行坐标，从第2行开始，也是1
    for row_id in range(1, len(result) + 1):
      # 列坐标
      for col_id in range(len(column_names)):
        # 写入的值
        value = result[row_id - 1][column_names[col_id]]
        # 判断为日期时
        if isinstance(value, datetime.datetime):
          value = result[row_id - 1][column_names[col_id]].strftime('%Y-%m-%d %H:%M:%S')

        # 写入表格
        sheet1.write(row_id, col_id, value)

    # 保存文件
    f.save(self.file_name)

    # 判断文件是否存在
    if not os.path.exists(self.file_name):
      print("生成excel失败")
      return False

    print("生成excel成功")
    return True

if __name__ == '__main__':
  MysqlToExcel().generate_table()

执行该脚本，生成的Excel结果与前述基本写法一致。

Excel列宽自适应优化

列宽自适应处理

上述生成的表格存在列宽固定、显示不美观的问题，未能实现自适应宽度。

解决思路如下：

新增一个方法用于计算并获取最佳列宽。


def get_maxlength(self,value, col):
  """
  获取value最大占位长度，用于确定导出的xlsx文件的列宽
  col : 表头，也参与比较，解决有时候表头过长的问题
  """
  # 长度列表
  len_list = []
  # 表头长度
  width = 256 * (len(col) + 1)
  len_list.append(width)

  # 数据长度
  if len(value) >= 10:
    width = 256 * (len(value) + 1)
    len_list.append(width)

  return max(len_list)

整合后的完整代码如下。


#!/usr/bin/env python3
# coding: utf-8
import os
import xlwt
import pymysql
import datetime

class MysqlToExcel(object):
  def __init__(self):
    self.host = '10.212.21.92'
    self.user = 'root'
    self.passwd = 'abcd1234'
    self.db_name = 'test'
    self.port = 3306
    self.file_name = 'data.xls'

  def get_query_results(self):
    sql = "select * from test.users"

    conn = pymysql.connect(
      host=self.host,
      user=self.user,
      passwd=self.passwd,
      port=self.port,
      database=self.db_name,
      charset='utf8',
      cursorclass=pymysql.cursors.DictCursor
    )
    cur = conn.cursor() # 创建游标
    cur.execute(sql) # 执行sql命令
    result = cur.fetchall() # 获取执行的返回结果
    # print(result)
    cur.close()
    conn.close() # 关闭mysql 连接
    return result

  def get_maxlength(self,value, col):
    """
    获取value最大占位长度，用于确定导出的xlsx文件的列宽
    col : 表头，也参与比较，解决有时候表头过长的问题
    """
    # 长度列表
    len_list = []
    # 表头长度
    width = 256 * (len(col) + 1)
    len_list.append(width)

    # 数据长度
    if len(value) >= 10:
      width = 256 * (len(value) + 1)
      len_list.append(width)

    return max(len_list)


  def generate_table(self):
    """
    生成excel表格
    :return:
    """
    # 删除已存在的文件
    if os.path.exists(self.file_name):
      os.remove(self.file_name)

    result = self.get_query_results()
    # print(result)
    if not result:
      print("查询结果为空")
      return False

    # 创建excel对象
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)

    # 第一行结果
    row0 = result[0]
    # 列字段
    column_names = list(row0)

    # 写第一行，也就是列所在的行
    for i in range(0, len(row0)):
      sheet1.write(0, i, column_names[i])

    # 写入多行
    # 行坐标，从第2行开始，也是1
    for row_id in range(1, len(result) + 1):
      # 列坐标
      for col_id in range(len(column_names)):
        # 写入的值
        value = result[row_id - 1][column_names[col_id]]
        # 判断为日期时
        if isinstance(value, datetime.datetime):
          value = result[row_id - 1][column_names[col_id]].strftime('%Y-%m-%d %H:%M:%S')

        # 获取表格对象
        col = sheet1.col(col_id)
        if value:
          if isinstance(value, int):
            value = str(value)

          # 获取宽度
          width = self.get_maxlength(value,column_names[col_id])

          # 设置宽度
          col.width = width
        # 写入表格
        sheet1.write(row_id, col_id, value)

    # 保存文件
    f.save(self.file_name)

    # 判断文件是否存在
    if not os.path.exists(self.file_name):
      print("生成excel失败")
      return False

    print("生成excel成功")
    return True

if __name__ == '__main__':
  MysqlToExcel().generate_table()

再次执行脚本并查看生成的Excel文件，列宽已自动调整。

 

以上即为利用Python查询MySQL并将数据写入Excel的完整流程，更多相关技术细节可参考本站其他文章。

相关文章

相关标签: Python SQL PyMySQL MySQL Excel

免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。

作者最新文章

全部文章

Python 相关产品和应用

全部下载
PythonSelenium
Selenium

Selenium

4.51.0 · Windows

用于浏览器自动化和 Web 测试的 Python 工具。

Python机器学习
scikit-learn

scikit-learn

1.9.1 · Windows

常用 Python 机器学习算法与模型评估工具库。

Python代码质量
Black

Black

26.10.1 · Windows

强调一致性的 Python 自动代码格式化工具。

Python代码质量
Ruff

Ruff

0.17.1 · macOS

由 Rust 编写的高速 Python 代码检查和格式化工具。

Pythonpytest
pytest

pytest

9.1.1 · Windows

简洁而强大的 Python 自动化测试框架。

PythonFastAPI
FastAPI

FastAPI

0.146.0 · Windows

高性能 Python API 框架，支持类型提示和自动接口文档。

PythonFlask
Flask

Flask

3.1.3 · Windows

轻量灵活的 Python Web 应用框架。

PythonDjango
Django

Django

6.1.2 · Windows

成熟的 Python Web 框架，适合构建内容站点和业务系统。

PythonSpyder
Spyder

Spyder

6.1.7 · Windows

面向科学计算与数据分析的 Python 集成开发环境。

PythonSciPy
SciPy

SciPy

1.18.1 · Windows

面向科学、工程和技术计算的 Python 算法库。