Python编程
搜索
分类

python查询MySQL将数据写入Excel

作者：py3study 时间：2023-12-15 13:48:11　

一、概述

现有一个用户表，需要将表数据写入到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');

二、基本写法

安装模块


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表

python查询MySQL将数据写入Excel

三、高级写法

在基础写法中，需要指定表的字段，比如：['id','username','password','phone','email']

如果一个表有70个字段怎么办？一个写笔记耗时间，能不能动态获取表字段呢？答案是可以的。

由于我在创建游标时，指定了pymysql.cursors.DictCursor，它返回的每一行数据，都是一个字典。

因此，通过dict.keys()就可以获取表字段了。

另外，我还得将查询结构中非string的转换为string类型。

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()

执行脚本，结果同上！

四、自适应宽度

上面表格看着不美观，宽度没有自适应。

解决方法：

增加一个方法，获取宽度


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

来源：https://cloud.tencent.com/developer/article/1628286

标签：python,MySQL,excel
0
投稿

猜你喜欢

  • 一文读懂Python版本管理工具Pyenv使用

    2023-04-23 12:59:17

  • sqlserver中比较一个字符串中是否含含另一个字符串中的一个字符

    2024-01-21 03:04:47

  • JsonServer安装及启动过程图解

    2023-08-12 20:06:02

  • 在ASP.NET 2.0中操作数据之四十六：使用SqlDataSource控件检索数据

    2023-07-04 14:31:37

  • Python 实现局域网远程屏幕截图案例

    2021-05-13 13:43:43

  • CSS实现完美垂直居中

    2007-09-22 09:29:00

  • python flask框架实现重定向功能示例

    2022-01-16 07:14:51

  • Python数据读写之Python读写CSV文件

    2023-04-04 00:59:46

  • Tensorflow实现多GPU并行方式

    2021-03-26 19:43:31

  • python切换hosts文件代码示例

    2023-07-19 15:41:43

  • 用CSS实现柱状图（Bar Graph）的方法（四）—table实现复杂柱状图

    2008-05-28 12:55:00

  • Python中Continue语句的用法的举例详解

    2023-04-04 13:12:37

  • Python seek()和tell()函数的具体使用

    2023-12-07 20:59:39

  • 手写Vue2.0 数据劫持的示例

    2024-05-22 10:43:17

  • Python语法学习之正则表达式的使用详解

    2023-02-11 15:39:26

  • Docker创建Mysql容器的简单步骤

    2024-01-28 20:20:40

  • 网易首页的新闻代码

    2022-02-24 16:01:15

  • NumPy 如何生成多维数组的方法

    2022-06-26 20:40:51

  • 服务器XMLHTTP（Server XMLHTTP in ASP）基础

    2008-11-11 12:45:00

  • chatgpt 1020 错误码成功解决的三种方案(推荐)

    2023-02-03 17:33:28
    • asp之家 网络编程 m.aspxhome.com