一、概述

現(xiàn)有一個(gè)用戶表，需要將表數(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ī)號(hào)’, `email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ’郵箱’, `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT ’創(chuàng)建時(shí)間’, 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-8import osimport xlwtimport pymysqlimport datetimeclass 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對(duì)象 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 # 計(jì)數(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 Trueif __name__ == ’__main__’: MysqlToExcel().generate_table()

執(zhí)行輸出：

查看excel表

三、高級(jí)寫法

在基礎(chǔ)寫法中，需要指定表的字段，比如：[’id’,’username’,’password’,’phone’,’email’]

如果一個(gè)表有70個(gè)字段怎么辦？一個(gè)寫筆記耗時(shí)間，能不能動(dòng)態(tài)獲取表字段呢？答案是可以的。

由于我在創(chuàng)建游標(biāo)時(shí)，指定了pymysql.cursors.DictCursor，它返回的每一行數(shù)據(jù)，都是一個(gè)字典。

因此，通過dict.keys()就可以獲取表字段了。

另外，我還得將查詢結(jié)構(gòu)中非string的轉(zhuǎn)換為string類型。

test_excel.py

#!/usr/bin/env python3# coding: utf-8import osimport xlwtimport pymysqlimport datetimeclass 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對(duì)象 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]]# 判斷為日期時(shí)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 Trueif __name__ == ’__main__’: MysqlToExcel().generate_table()

執(zhí)行腳本，結(jié)果同上！

四、自適應(yīng)寬度

上面表格看著不美觀，寬度沒有自適應(yīng)。

解決方法：

增加一個(gè)方法，獲取寬度

def get_maxlength(self,value, col): ''' 獲取value最大占位長(zhǎng)度，用于確定導(dǎo)出的xlsx文件的列寬 col : 表頭，也參與比較，解決有時(shí)候表頭過長(zhǎng)的問題 ''' # 長(zhǎng)度列表 len_list = [] # 表頭長(zhǎng)度 width = 256 * (len(col) + 1) len_list.append(width) # 數(shù)據(jù)長(zhǎng)度 if len(value) >= 10: width = 256 * (len(value) + 1) len_list.append(width) return max(len_list)

完整代碼如下：

#!/usr/bin/env python3# coding: utf-8import osimport xlwtimport pymysqlimport datetimeclass 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最大占位長(zhǎng)度，用于確定導(dǎo)出的xlsx文件的列寬 col : 表頭，也參與比較，解決有時(shí)候表頭過長(zhǎng)的問題 ''' # 長(zhǎng)度列表 len_list = [] # 表頭長(zhǎng)度 width = 256 * (len(col) + 1) len_list.append(width) # 數(shù)據(jù)長(zhǎng)度 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對(duì)象 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]]# 判斷為日期時(shí)if isinstance(value, datetime.datetime): value = result[row_id - 1][column_names[col_id]].strftime(’%Y-%m-%d %H:%M:%S’)# 獲取表格對(duì)象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 Trueif __name__ == ’__main__’: MysqlToExcel().generate_table()

執(zhí)行腳本，查看excel

以上就是python查詢MySQL將數(shù)據(jù)寫入Excel的詳細(xì)內(nèi)容，更多關(guān)于python 查詢MySQL的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！