广告

扫码关注官方微信

扫码下载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://www.lsjlt.com/news/193693.html(转载时请注明来源链接)

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

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • 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+
    2022-10-18
  • Python如何写入excel表格
    这篇“Python如何写入excel表格”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Python如何写入excel表格”文...
    99+
    2023-06-27
  • SQL查询的数据插入到Excel 2007版
    INSERT INTO --插入 OPENROWSET --打开 ( 'Microsoft.Ace.OleDb.12.0' --OLEDB驱动程...
    99+
    2022-10-18
  • python实现读取excel写入mysql的小工具详解
    Python是数据分析的强大利器 利用Python做数据分析，第一步就是学习如何读取日常工作中产生各种excel报表并存入数据中，方便后续数据处理。 这里向大家分享python如何读取excel，并使用P...
    99+
    2022-06-04
    详解 小工具 python
  • MySQL查询将小写更改为大写？
    您可以使用 MySQL 的内置函数 UPPER() 将小写字母更改为大写字母。语法如下，带有 select 语句。SELECT UPPER(‘yourStringValue’);以下是显示小写字符串的示例 ...
    99+
    2023-10-22
  • 【Python】将数据写入excel文件中
    目的： python实现将数据写入excel文件中。 步骤： 导入依赖包xlwt 注意：这里的xlwt是python的第三方模块，需要下载安装才能使用（如果没安装可直接在终端输入pip install...
    99+
    2023-09-02
    python excel
  • Python如何实现数据写入Excel
    这篇文章主要讲解了“Python如何实现数据写入Excel”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Python如何实现数据写入Excel”吧！写入后的格式如下图所示：以下为数据用例：i...
    99+
    2023-06-15
  • MySQL入门教程2 —— 输入查询及退出查询命令
    确保你连接上了服务器，如在上一章节讨论的。连接上服务器并不代表选择了任何数据库，但这样就可以了。知道关于如何查询的基本知识，比马上跳至创建表、给他们装载数据并且从他们检索数据更重要。本节描述输入命令的基本原则，使...
    99+
    2022-05-27
    MySQL SQL 命令 查询 终止
  • mysql update语句根据子查询结果把子查询数据写入修改字段
    需求,应用场景 table1是统计信息表，里面存储了商店id,一个商店一条数据，table2是订单表，里面存储了多个订单，每条订单有一个字段是table1的商店id，table3是商品表，存储了多个商品，table2里面的每条数据在tab...
    99+
    2019-09-22
    mysql update语句根据子查询结果把子查询数据写入修改字段 数据库入门 数据库基础教程
  • MySQL怎么重写查询语句
    这篇文章主要介绍MySQL怎么重写查询语句，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！在优化存在问题的查询时，我们需要改变方式去获取查询结果——但这并不意味着从 MySQL获取同样的结果集。有些时候我们可以将查询转...
    99+
    2023-06-15
  • mysql子查询语句怎么写
    小编给大家分享一下mysql子查询语句怎么写，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ mysql子查询语句是指在另一个查询语句中的SELECT子句...
    99+
    2022-10-19
  • mysql查询区分大小写吗
    本篇内容主要讲解“mysql查询区分大小写吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql查询区分大小写吗”吧! mysq...
    99+
    2022-10-19
  • 深入探究Mysql模糊查询是否区分大小写
    前言 近期，一直在忙着写一个小小的个人博客项目，在实现 “全局搜索” 功能时，发现mysql模糊查询语句有点儿神奇（本小白刚刚步入编程阶段，所以可能让大家见笑了，哈哈哈），有时候 m...
    99+
    2022-11-12
  • 【MySQL】基本查询（插入查询结果、聚合函数、分组查询）
    目录 一、插入查询结果二、聚合函数三、分组查询（group by & having）四、SQL查询的执行顺序五、OJ练习 一、插入查询结果 语法： INSERT INTO tab...
    99+
    2023-09-25
    mysql
  • 如何使用Python给Excel写入数据
    openpyxl三步走 获取work book 获取 work sheet 再然后 获取单元格 进行操作 保存文件 安装OpenpyXl pip install OpenpyXl 导包方式以下两种都可以 from openpyxl i...
    99+
    2023-10-12
    excel python 信息可视化 数据分析 powerpoint
  • Python爬虫爬数据写入到EXCEL中
    Python抓数据写到EXCEL中。以前都是写到txt中然后再导入到excel。现在直接写到excel中。#coding=utf-8 import xlwt import requests from bs4 import Beautiful...
    99+
    2023-01-31
    爬虫 数据 Python
  • Python 操作pdf pdfplumber读取PDF写入Excel
    目录1. Python 操作pdf(pdfplumber读取PDF写入Excel1.1 安装pdfplumber模块库1.2 常用操作1.2.1 Python读取pdf文件案例1.2...
    99+
    2022-11-11
  • python写入Excel表格的方法详解
    目录一、写入Excel数据二、项目：更新一个电子表格2.1案例需求2.2案例源码总结一、写入Excel数据 週用openpyxl也提供了一些方法写入数据，这意味着你的程序可以创建和编...
    99+
    2022-11-13
  • Python 如何写入Excel格式和颜色
    Python写入Excel有时需要合并单元格、或者改变文字内容的颜色 首先导入xlwt模块 import xlwt 创建文件名 创建Excel工作簿对象 创建工作表 创建样式字...
    99+
    2022-11-11
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作