Loading [MathJax]/jax/input/TeX/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >python查询MySQL写入Excel

python查询MySQL写入Excel

作者头像
py3study
发布于 2020-05-14 08:41:40
发布于 2020-05-14 08:41:40
9.5K10
代码可运行
举报
文章被收录于专栏:python3python3
运行总次数:0
代码可运行

一、概述

 现有一个用户表,需要将表数据写入到excel中。

环境说明

mysql版本:5.7

端口:3306

数据库:test

表名:users

表结构如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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行数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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');

二、基本写法

安装模块

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
pip3 install xlwt pymysql

test_excel.py

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#!/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()

执行输出:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
生成excel成功

查看excel表

三、高级写法

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

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

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

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

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

test_excel.py

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#!/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()

执行脚本,结果同上!

四、自适应宽度

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

解决方法:

增加一个方法,获取宽度

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)

完整代码如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#!/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

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

本文参考链接:

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

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2020/05/13 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
1 条评论
热度
最新
你好,作者,我是python及数据库初学者,如果我的字段有bigdecimal这样的字段类型,那使用vavlue的时候进行长度的对比的时候,会报错,请问这个情况应该如何优化代码(提供优化思路也可以),我暂时想不到解决办法,我还在熟悉你的代码过程,我的联系方式q
你好,作者,我是python及数据库初学者,如果我的字段有bigdecimal这样的字段类型,那使用vavlue的时候进行长度的对比的时候,会报错,请问这个情况应该如何优化代码(提供优化思路也可以),我暂时想不到解决办法,我还在熟悉你的代码过程,我的联系方式q
回复回复点赞举报
推荐阅读
编辑精选文章
换一批
python 统计MySQL表信息
需要利用的技术点,有2个。一个是pymysql(连接mysql),一个是xlwt(写入excel)
py3study
2020/01/20
5.2K0
python MySQL 插入Elasticsearch
有一张表,记录的数据特别的多,需要将7天前的记录,插入到Elasticsearch中,并删除原有表7天前的记录。
py3study
2020/02/25
6.8K0
Python如何操作MySQL
mysql的安装图解https://jingyan.baidu.com/art... navicat for mysql破解可以看下这个文章https://www.cnblogs.com/da199...
py3study
2020/01/06
6020
python 获取mysql数据库列表以及用户权限
一、需求分析 需要统计出当前数据库的所有数据库名,以及每个用户的授权信息。 获取所有数据库 在mysql里面,使用命令: show databases 就可以获取所有数据库了 获取所有用户 执行命令: select User from mysql.user 注意:需要排除到默认的用户,比如: "root", "mysql.sys", "mysql.session" 获取用户权限 语法: show grants for 用户名; 比如: show grants for test; 执行输出: GRANT US
py3study
2020/02/25
5.7K0
python 统计MySQL大于100万的表
线上的MySQL服务器,最近有很多慢查询。需要统计出行数大于100万的表,进行统一优化。
py3study
2020/01/12
1.6K0
[306]python操作excel表格(xlrd/xlwt)
python操作excel主要用到xlrd和xlwt这两个库,即xlrd是读excel,xlwt是写excel的库。可从这里下载https://pypi.python.org/pypi。下面分别记录python读和写excel.
周小董
2022/04/12
2.7K0
[306]python操作excel表格(xlrd/xlwt)
Python读写Excel表格,就是这么
于是我到处查找资料,基本解决了日常所需,终于算是完成了任务,因此撰写此文就算是总结吧,主要记录使用过程的常见问题及解决。
py3study
2020/01/06
9930
Python读写Excel表格,就是这么
爬取拉勾网招聘信息并使用xlwt存入Excel
通过浏览器自带的开发者工具查看是通过Post方式提交的,数据是通过Ajax(异步加载)得到的
Python攻城狮
2018/08/23
5270
爬取拉勾网招聘信息并使用xlwt存入Excel
python 长连接 mysql数据库
python链接mysql中没有长链接的概念,但我们可以利用mysql的ping机制,来实现长链接功能
py3study
2020/01/07
2.6K0
Python 封装MySQL类
 一个重量级的MySQL-Python 封装类:facebook python mysql
py3study
2020/01/06
3.2K0
bs4爬虫实战三:获取电影信息并存入mysql数据库
这次爬虫的目标网站是:http://dianying.2345.com,爬虫的搜索目标仅限于今年的电影,在网站打开搜索,在年代中选择2018
py3study
2020/01/20
1.6K0
python操作mysql数据库
先简单写一个操作mysql数据库类,后面再改进 # -*- coding:utf-8 -*- import pymysql class SunckSql(): def __init__(self,host,user,password,database,port): self.host=host self.user=user self.passwd=password self.dbName=database self.p
py3study
2020/01/19
1.5K0
python excel多行合并
从上面输出内容,可以看出。将多行合并为一行,并且将faq记录写入到一个字典里面了。接下来就可以写入到新表格了。
py3study
2020/11/27
2.5K0
基于Python的mysql与excel互相转换
getConn函数获取mysql连接,第1个参数database为要连接的数据库。 mysql2excel函数完成主要转换功能,第1个参数database为要连接的数据库,第2个参数为要转换的数据表,第3个参数为要保存的excel文件名。 在执行cursor.execute后,利用data_list = cursor.fetchall()获取数据库中所有数据,利用cursor.description获取函数中字段的相关信息, 字段的相关信息的数据类型为元组,其中第1个为字段名。 利用xlwt.Workbook()方法实例化对象赋值给excel变量,利用excel.add_sheet()方法获取新的表格,利用sheet.write()往excel文件中写入数据。
潇洒坤
2018/09/10
1.4K0
python脚本提取flutter项目的中文字符生成表格文件
思路: # 获取文件内连续的中文字符(筛选规则为冒号内的字符串如果出现\u4e00到\u9fa5字符串 提取冒号内容作为目标文本 (.*?)非贪婪模式) 用法:运行main.py,生成result e
用户4458175
2020/03/06
1.3K0
Python操作MSSQL
Python连接SQL Server数据库 - pymssql使用基础:https://www.cnblogs.com/baiyangcao/p/pymssql_basic.html
py3study
2020/01/09
3.1K0
【好消息】Python和Excel终于可以互通了!!
今天小编为大家分享一篇使用python将大量数据导出到Excel中的技巧心得,可以让Python和Excel的数据实现互通!具有很好的参考价值,希望对大家有所帮助(建议在电脑端阅读,代码案例较多)。一起跟随小编过来看看吧!
1480
2020/05/01
1.3K0
基于Python+django影片数据爬取与数据分析设计与实现
快速发展的社会中,人们的生活水平都在提高,生活节奏也在逐渐加快。为了节省时间和提高工作效率,越来越多的人选择利用互联网进行线上打理各种事务,通过线上管理影片数据爬取与数据分析也就相继涌现。
Python兴趣圈
2023/12/30
2660
基于Python+django影片数据爬取与数据分析设计与实现
Python构建私有代理IP库
至此我们就利用Python构建了一个属于自己的私有代理库,在进行爬去的时候可方便从数据库中获取使用。
KaliArch
2018/04/21
2.9K5
Python构建私有代理IP库
Python操作Mysql(连接、数据探查、写Excel)
事情是这样的,领导安排一个活,详细探查xx公司的数据治理,包括数据源,数据流,数据质量。
不吃西红柿
2022/09/19
6980
Python操作Mysql(连接、数据探查、写Excel)
相关推荐
python 统计MySQL表信息
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验