Python使用pymysql和xlrd2將Excel資料匯入MySQL資料庫

qwerrt9發表於2023-10-29
PythonMySqlExcel資料庫

在資料處理和管理中，有時候需要將Excel檔案中的資料匯入到MySQL資料庫中進行進一步的分析和操作。本文將介紹如何使用Python程式語言實現這個過程。

匯入所需庫

import xlrd2  # 匯入xlrd2庫，用於讀取Excel檔案
import pymysql  # 匯入pymysql庫，用於連線和操作MySQL資料庫
from datetime import datetime  # 匯入datetime庫，用於處理日期和時間

這部分程式碼匯入了xlrd2庫用於讀取Excel檔案，pymysql庫用於連線和操作MySQL資料庫，以及datetime庫用於處理日期和時間。

連線到MySQL資料庫

mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="123456",
    db="test"
)

透過pymysql.connect()函式連線到MySQL資料庫。需要提供資料庫的主機名、使用者名稱、密碼和資料庫名稱。

開啟Excel檔案並獲取表頭

workbook = xlrd2.open_workbook(r'E:\重新開始\Python操作MySQL資料庫\sheet1.xlsx')
sheet = workbook.sheet_by_index(0)  # 獲取第一個工作表

header = [cell.value for cell in sheet.row(0)]

使用xlrd2.open_workbook()函式開啟Excel檔案，並使用sheet_by_index()方法獲取第一個工作表。然後透過sheet.row(0)獲取第一行的單元格物件，並使用列表推導式將每個單元格的值新增到header列表中。

 建立遊標物件

cursor = mydb.cursor()

使用mydb.cursor()方法建立遊標物件，用於執行SQL語句。

遍歷每一行資料並插入到資料庫中

for row_idx in range(1, sheet.nrows):  # 從第二行開始遍歷
    row_data = []
    for cell in sheet.row(row_idx):
        if cell.ctype == xlrd2.XL_CELL_DATE:
            cell_value = xlrd2.xldate.xldate_as_datetime(cell.value, workbook.datemode)
            row_data.append(cell_value.strftime('%Y-%m-%d %H:%M:%S'))
        else:
            row_data.append(cell.value)

    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    cursor.execute(sql, row_data)

    print(f"正在插入第{row_idx}條資料")

透過for迴圈遍歷Excel檔案的每一行資料（從第二行開始）。在內部迴圈中，判斷單元格的資料型別是否為日期型別，如果是，則將其轉換為字串格式並按照指定的格式進行調整；否則，直接將其新增到row_data列表中。

然後，使用', '.join(header)和', '.join(['%s'] * len(header))構建插入資料的SQL語句，其中header為表頭的欄位名，'%s' * len(header)表示佔位符的數量與欄位數相同。

最後，使用遊標物件的execute()方法執行SQL語句，並傳入row_data作為引數，將行資料插入到資料庫中。

提交更改並關閉資料庫連線

mydb.commit()
cursor.close()
mydb.close()

使用mydb.commit()提交對資料庫的更改，並使用cursor.close()關閉遊標物件。最後，使用mydb.close()關閉與資料庫的連線。

完整程式碼如下：

import xlrd2  # 匯入xlrd2庫，用於讀取Excel檔案
import pymysql  # 匯入pymysql庫，用於連線和操作MySQL資料庫
from datetime import datetime  # 匯入datetime庫，用於處理日期和時間

# 連線到MySQL資料庫
mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="123456",
    db="test"
)

# 開啟Excel檔案
workbook = xlrd2.open_workbook(r'E:\重新開始\Python操作MySQL資料庫\sheet1.xlsx')
sheet = workbook.sheet_by_index(0)  # 獲取第一個工作表

# 獲取表頭（即Excel檔案的第一行資料）
header = [cell.value for cell in sheet.row(0)]

# 建立遊標物件，用於執行SQL語句
cursor = mydb.cursor()

# 遍歷每一行資料，並將其插入到資料庫中
for row_idx in range(1, sheet.nrows):  # 從第二行開始遍歷
    row_data = []
    for cell in sheet.row(row_idx):
        # 處理時間型別的欄位
        if cell.ctype == xlrd2.XL_CELL_DATE:  # 判斷單元格的資料型別是否為日期型別
            cell_value = xlrd2.xldate.xldate_as_datetime(cell.value, workbook.datemode)
            # 將日期型別轉換為字串格式，並按照指定的格式進行調整
            row_data.append(cell_value.strftime('%Y-%m-%d %H:%M:%S'))
        else:
            row_data.append(cell.value)  # 將其他型別的資料直接新增到行資料列表中

    # 構建插入資料的SQL語句
    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    # 執行SQL語句，將行資料插入到資料庫中
    cursor.execute(sql, row_data)

    # 顯示當前正在插入第幾條資料
    print(f"正在插入第{row_idx}條資料")

# 提交更改並關閉資料庫連線
mydb.commit()
cursor.close()
mydb.close()

 

相關文章