扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >python查询MySQL写入Excel
  • 657

0
分享到

python查询MySQL写入Excel

pythonMySQLExcel 2023-01-31 08:01:28 657人浏览 独家记忆
摘要

 现有一个用户表，需要将表数据写入到excel中。环境说明Mysql版本：5.7端口：3306数据库：test表名：users 表结构如下：CREATE TABLE `users` ( &n

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

 

查看excel表

1.png

 

在基础写法中，需要指定表的字段，比如：['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

1.png

 

 这样看着，就比较舒服了。

 

本文参考链接：

https://blog.csdn.net/baidu_41743195/article/details/103001210

Https://blog.csdn.net/dl1456074580/article/details/87364999


您可能感兴趣的文档:

--结束END--

本文标题: python查询MySQL写入Excel

本文链接: https://lsjlt.com/news/193693.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • python查询MySQL写入Excel
     现有一个用户表，需要将表数据写入到excel中。环境说明mysql版本：5.7端口：3306数据库：test表名：users 表结构如下：CREATE TABLE `users` ( &n...
    99+
    2023-01-31
    python MySQL Excel
  • influxdb查询写入操作
    influxdb的几种操作方法，有喜欢用http API的方式来写入数据，或者通过influxdb的终端来操作（我喜欢的类型），每种方式都适合的场景。介绍通过API接口和终端方式来操作：http://192...
    99+
    2024-04-02
  • Python如何写入excel表格
    这篇“Python如何写入excel表格”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Python如何写入excel表格”文...
    99+
    2023-06-27
  • Python3.5 写入excel
    Python3.5 写入excel望共同进步这里需要安装xlwt模块，具体步骤请看我的博客Python3.5读取excel（http://blog.csdn.net/weixin_39701039/article/details/7949...
    99+
    2023-01-31
    excel
  • mysql子查询怎么写
    mysql 子查询是一个嵌套在另一个查询中的查询，用于执行多个查询并利用其结果筛选或修改外部查询。子查询的类型包括相关、不相关和 cte。子查询有助于简化复杂查询、提高效率，并实现高级数...
    99+
    2024-05-21
    mysql
  • python写excel
    首先需要pip install XlsxWriter#coding=utf-8import xlsxwriter# Create an new Excel file and add a worksheet.workbook = xlsxwr...
    99+
    2023-01-31
    python excel
  • mysql查询语法怎么写
    mysql查询语法用于从数据库中检索数据，包括：select关键字、列名、from关键字、where子句和order by子句。where子句使用运算符过滤...
    99+
    2024-05-22
    mysql
  • mysql查询语句怎么写
    mysql 查询语句用于从数据库检索数据，遵循特定语法：select 子句指定要检索的列。from 子句指定要查询的表。where 子句筛选数据。group by 子...
    99+
    2024-05-30
    mysql 字符串常量
  • SQL查询的数据插入到Excel 2007版
    INSERT INTO --插入 OPENROWSET --打开 ( 'Microsoft.Ace.OleDb.12.0' --OLEDB驱动程...
    99+
    2024-04-02
  • python实现读取excel写入mysql的小工具详解
    Python是数据分析的强大利器 利用Python做数据分析，第一步就是学习如何读取日常工作中产生各种excel报表并存入数据中，方便后续数据处理。 这里向大家分享python如何读取excel，并使用P...
    99+
    2022-06-04
    详解 小工具 python
  • Python如何实现数据写入Excel
    这篇文章主要讲解了“Python如何实现数据写入Excel”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Python如何实现数据写入Excel”吧！写入后的格式如下图所示：以下为数据用例：i...
    99+
    2023-06-15
  • 【Python】将数据写入excel文件中
    目的： python实现将数据写入excel文件中。 步骤： 导入依赖包xlwt 注意：这里的xlwt是python的第三方模块，需要下载安装才能使用（如果没安装可直接在终端输入pip install...
    99+
    2023-09-02
    python excel
  • python怎么读取pdf并写入excel
    要读取PDF文件并将其内容写入Excel文件，可以使用PyPDF2库来读取PDF文件的内容，然后使用openpyxl库来创建和写入E...
    99+
    2024-03-05
    python
  • MySQL查询将小写更改为大写？
    您可以使用 MySQL 的内置函数 UPPER() 将小写字母更改为大写字母。语法如下，带有 select 语句。SELECT UPPER(‘yourStringValue’);以下是显示小写字符串的示例 ...
    99+
    2023-10-22
  • Java如何写入写出Excel
    这篇文章将为大家详细讲解有关Java如何写入写出Excel，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。数据原来是这样的，不同的实验有一个专门的文件夹，实验名的文件夹下有不同班级的文件夹，班级文件夹下有该...
    99+
    2023-05-30
    java excel
  • Python读写Excel
     读Excel 1 #打开Excek,xlsfile为Excel路径+文件名 2 boorRead = xlrd.open_workbook(xlsfile) 3 #读取sheet,sheet_name为Excel中sheet的名称...
    99+
    2023-01-30
    Python Excel
  • mysql子查询语句怎么写
    小编给大家分享一下mysql子查询语句怎么写，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ mysql子查询语句是指在另一个查询语句中的SELECT子句...
    99+
    2024-04-02
  • mysql查询区分大小写吗
    本篇内容主要讲解“mysql查询区分大小写吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql查询区分大小写吗”吧! mysq...
    99+
    2024-04-02
  • MySQL怎么重写查询语句
    这篇文章主要介绍MySQL怎么重写查询语句，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！在优化存在问题的查询时，我们需要改变方式去获取查询结果——但这并不意味着从 MySQL获取同样的结果集。有些时候我们可以将查询转...
    99+
    2023-06-15
  • mysql update语句根据子查询结果把子查询数据写入修改字段
    需求,应用场景 table1是统计信息表，里面存储了商店id,一个商店一条数据，table2是订单表，里面存储了多个订单，每条订单有一个字段是table1的商店id，table3是商品表，存储了多个商品，table2里面的每条数据在tab...
    99+
    2019-09-22
    mysql update语句根据子查询结果把子查询数据写入修改字段 数据库入门 数据库基础教程
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作