溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

使用python實現(xiàn)查詢MySQL并將數(shù)據(jù)寫入Excel

發(fā)布時間：2020-10-30 14:17:44 來源：億速云 閱讀：416 作者：Leah 欄目：開發(fā)技術

使用python實現(xiàn)查詢MySQL并將數(shù)據(jù)寫入Excel？相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

表結構如下：

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 '創(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)建游標
    cur.execute(sql) # 執(zhí)行sql命令
    result = cur.fetchall() # 獲取執(zhí)行的返回結果
    # 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

    # 創(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表

使用python實現(xiàn)查詢MySQL并將數(shù)據(jù)寫入Excel

三、高級寫法

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

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

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

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

另外，我還得將查詢結構中非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)建游標
    cur.execute(sql) # 執(zhí)行sql命令
    result = cur.fetchall() # 獲取執(zhí)行的返回結果
    # 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

    # 創(chuàng)建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()

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

四、自適應寬度

上面表格看著不美觀，寬度沒有自適應。

解決方法：

增加一個方法，獲取寬度

def get_maxlength(self,value, col):
  """
  獲取value最大占位長度，用于確定導出的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)建游標
    cur.execute(sql) # 執(zhí)行sql命令
    result = cur.fetchall() # 獲取執(zhí)行的返回結果
    # 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)

    # 數(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("查詢結果為空")
      return False

    # 創(chuàng)建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()

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

 使用python實現(xiàn)查詢MySQL并將數(shù)據(jù)寫入Excel

看完上述內(nèi)容，你們掌握使用python實現(xiàn)查詢MySQL并將數(shù)據(jù)寫入Excel的方法了嗎？如果還想學到更多技能或想了解更多相關內(nèi)容，歡迎關注億速云行業(yè)資訊頻道，感謝各位的閱讀！

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內(nèi)容。

AI
助
手