python查詢MySQL將數(shù)據(jù)寫入Excel
更新時間：2020年10月29日 16:09:15 作者：py3study
這篇文章主要介紹了python如何查詢MySQL將數(shù)據(jù)寫入Excel，幫助大家利用python高效的辦公，感興趣的朋友可以了解下
一、概述
現(xiàn)有一個用戶表，需要將表數(shù)據(jù)寫入到excel中。
環(huán)境說明
mysql版本：5.7
端口：3306
數(shù)據(jù)庫：test
表名：users
表結(jié)構(gòu)如下：
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 '手機(jī)號', `email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '郵箱', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
插入3行數(shù)據(jù)
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() # 創(chuàng)建游標(biāo)
cur.execute(sql) # 執(zhí)行sql命令
result = cur.fetchall() # 獲取執(zhí)行的返回結(jié)果
# print(result)
cur.close()
conn.close() # 關(guān)閉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("查詢結(jié)果為空")
return False
# 創(chuàng)建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 # 計數(shù)器
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'])
# 日期轉(zhuǎn)換為字符串
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()
執(zhí)行輸出：
查看excel表
三、高級寫法
在基礎(chǔ)寫法中，需要指定表的字段，比如：['id','username','password','phone','email']
如果一個表有70個字段怎么辦？一個寫筆記耗時間，能不能動態(tài)獲取表字段呢？答案是可以的。
由于我在創(chuàng)建游標(biāo)時，指定了pymysql.cursors.DictCursor，它返回的每一行數(shù)據(jù)，都是一個字典。
因此，通過dict.keys()就可以獲取表字段了。
另外，我還得將查詢結(jié)構(gòu)中非string的轉(zhuǎn)換為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() # 創(chuàng)建游標(biāo)
cur.execute(sql) # 執(zhí)行sql命令
result = cur.fetchall() # 獲取執(zhí)行的返回結(jié)果
# print(result)
cur.close()
conn.close() # 關(guān)閉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("查詢結(jié)果為空")
return False
# 創(chuàng)建excel對象
f = xlwt.Workbook()
sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)
# 第一行結(jié)果
row0 = result[0]
# 列字段
column_names = list(row0)
# 寫第一行，也就是列所在的行
for i in range(0, len(row0)):
sheet1.write(0, i, column_names[i])
# 寫入多行
# 行坐標(biāo)，從第2行開始，也是1
for row_id in range(1, len(result) + 1):
# 列坐標(biāo)
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()
執(zhí)行腳本，結(jié)果同上！
四、自適應(yīng)寬度
上面表格看著不美觀，寬度沒有自適應(yīng)。
解決方法：
增加一個方法，獲取寬度
def get_maxlength(self,value, col):
"""
獲取value最大占位長度，用于確定導(dǎo)出的xlsx文件的列寬
col : 表頭，也參與比較，解決有時候表頭過長的問題
"""
# 長度列表
len_list = []
# 表頭長度
width = 256 * (len(col) + 1)
len_list.append(width)
# 數(shù)據(jù)長度
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() # 創(chuàng)建游標(biāo)
cur.execute(sql) # 執(zhí)行sql命令
result = cur.fetchall() # 獲取執(zhí)行的返回結(jié)果
# print(result)
cur.close()
conn.close() # 關(guān)閉mysql 連接
return result
def get_maxlength(self,value, col):
"""
獲取value最大占位長度，用于確定導(dǎo)出的xlsx文件的列寬
col : 表頭，也參與比較，解決有時候表頭過長的問題
"""
# 長度列表
len_list = []
# 表頭長度
width = 256 * (len(col) + 1)
len_list.append(width)
# 數(shù)據(jù)長度
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("查詢結(jié)果為空")
return False
# 創(chuàng)建excel對象
f = xlwt.Workbook()
sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)
# 第一行結(jié)果
row0 = result[0]
# 列字段
column_names = list(row0)
# 寫第一行，也就是列所在的行
for i in range(0, len(row0)):
sheet1.write(0, i, column_names[i])
# 寫入多行
# 行坐標(biāo)，從第2行開始，也是1
for row_id in range(1, len(result) + 1):
# 列坐標(biāo)
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])
# 設(shè)置寬度
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()
執(zhí)行腳本，查看excel
以上就是python查詢MySQL將數(shù)據(jù)寫入Excel的詳細(xì)內(nèi)容，更多關(guān)于python 查詢MySQL的資料請關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:
- 解決python mysql insert語句的問題
- python 在mysql中插入null空值的操作
- Python接入MySQL實現(xiàn)增刪改查的實戰(zhàn)記錄
- Python+MySQL隨機(jī)試卷及答案生成程序的示例代碼
- 詳解Python之Scrapy爬蟲教程NBA球員數(shù)據(jù)存放到Mysql數(shù)據(jù)庫
- Python從MySQL數(shù)據(jù)庫中面抽取試題，生成試卷
- python3 使用ssh隧道連接mysql的操作
- Python批量刪除mysql中千萬級大量數(shù)據(jù)的腳本分享
- Python操控mysql批量插入數(shù)據(jù)的實現(xiàn)方法
- Python下的Mysql模塊MySQLdb安裝詳解
- python中MySQLdb模塊用法實例
- Python中操作mysql的pymysql模塊詳解
- MySQL和Python交互的示例
相關(guān)文章
在VSCode中添加Python解釋器并安裝Python庫的方法
這篇文章主要介紹了在VSCode中添加Python解釋器并安裝Python庫的方法，本文分步驟通過圖文并茂的形式給大家介紹的非常詳細(xì)，需要的朋友可以參考下2023-02-02
這篇文章主要介紹了OpenCV基本操作之邊緣檢測，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
這篇文章主要介紹了Python解析并讀取PDF文件內(nèi)容的方法,結(jié)合實例形式分別描述了Python2.7在win32與win64環(huán)境下實現(xiàn)讀取pdf的相關(guān)操作技巧,需要的朋友可以參考下2018-05-05
python實現(xiàn)猜數(shù)游戲(保存游戲記錄）
這篇文章主要為大家詳細(xì)介紹了python實現(xiàn)猜數(shù)游戲，保存游戲記錄，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-06-06
Flask-SocketIO服務(wù)端安裝及使用代碼示例
這篇文章主要介紹了Flask-SocketIO服務(wù)端安裝及使用代碼示例,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-11-11
這篇文章主要介紹了python 如何用爬蟲爬取京東ps4售賣情況，幫助大家更好的利用python爬取自己想要的數(shù)據(jù)，感興趣的朋友可以了解下2020-12-12
YOLOv5車牌識別實戰(zhàn)教程(五)字符分割與識別
這篇文章主要介紹了YOLOv5車牌識別實戰(zhàn)教程(五)字符分割與識別,在這個教程中，我們將一步步教你如何使用YOLOv5進(jìn)行車牌識別,幫助你快速掌握YOLOv5車牌識別技能,需要的朋友可以參考下2023-04-04
python統(tǒng)計字符串中字母出現(xiàn)次數(shù)代碼實例
這篇文章主要介紹了python統(tǒng)計字符串中字母出現(xiàn)次數(shù)代碼實例,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-03-03
這篇文章主要介紹了python語句中end=‘ ‘的作用及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要介紹了Python 相對路徑絕對路徑的相關(guān)知識，結(jié)合實例代碼介紹了Python 相對路徑、絕對路徑的寫法實例演示,需要的朋友可以參考下2023-02-02