python查数据库写入excel_python查询MySQL将数据写入Excel

python查询MySQL将数据写入Excel

作者: py3study　　更新时间:2020-10-29 16:09:15　　原文链接

一、概述

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

三、高级写法

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