Python 利用pymysql和openpyxl操作MySQL資料庫並插入Excel資料

1. 需求分析

本文將介紹如何使用Python連線MySQL資料庫，並從Excel檔案中讀取資料，將其插入到MySQL資料庫中。

2. 環境準備

在開始本文之前，請確保您已經安裝好了以下環境：

Python 3.x
PyMySQL庫
openpyxl庫
MySQL資料庫

3. 連線MySQL資料庫

我們可以使用pymysql庫來連線MySQL資料庫。請先建立一個名為test的MySQL資料庫，並在其中建立一個名為yonghu的表。

import pymysql

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

在上述程式碼中，我們呼叫了pymysql庫中的connect()函數來連線MySQL資料庫。其中，host引數是MySQL伺服器的主機名或IP地址，user引數是登入MySQL伺服器的使用者名稱，passwd引數是登入MySQL伺服器的密碼，db引數是要連線的資料庫名。

4. 開啟Excel檔案

在Python中，可以使用openpyxl庫來讀寫Excel檔案。請先安裝好openpyxl庫。

from openpyxl import load_workbook

# 開啟Excel檔案
wb = load_workbook(filename=r'E:\重新開始\Python操作MySQL資料庫\sheet1.xlsx')
sheet = wb.active

在上述程式碼中，我們使用load_workbook()函數載入Excel檔案，並使用active屬性獲取活動工作表。

5. 獲取表頭

在插入資料之前，我們需要先獲取Excel表格的表頭資訊。

# 獲取表頭
header = [cell.value for cell in sheet[1]]

在上述程式碼中，我們遍歷第一行的單元格，獲取每個單元格的值，作為表頭。

6. 遍歷Excel資料並插入資料庫

接下來，我們遍歷Excel檔案中的每一行資料，並將其插入到MySQL資料庫中。

# 遍歷每一行資料，並將其插入到資料庫中
cursor = mydb.cursor()
count = 0
for row in sheet.iter_rows(min_row=2, values_only=True):
    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    cursor.execute(sql, row)
    count += 1
    print(f"正在插入{count}條資料")

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

在上述程式碼中，我們使用iter_rows()函數從第二行開始遍歷每一行資料。在遍歷過程中，我們構建了一個插入資料的SQL語句，使用預留位置%s，然後使用遊標物件執行SQL語句，將行資料插入到MySQL資料庫中。

最後，我們提交了對資料庫的更改，並關閉了遊標物件和與MySQL資料庫的連線。

7. 完整程式碼

最終的Python程式碼如下：

from openpyxl import load_workbook  # 匯入load_workbook函數，用於載入Excel檔案
import pymysql  # 匯入pymysql庫，用於連線和操作MySQL資料庫

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

# 開啟Excel檔案
wb = load_workbook(filename=r'E:\重新開始\Python操作MySQL資料庫\sheet1.xlsx')  # 載入Excel檔案
sheet = wb.active  # 獲取活動工作表

# 獲取表頭
header = [cell.value for cell in sheet[1]]  # 獲取第一行的單元格值，作為表頭

# 遍歷每一行資料，並將其插入到資料庫中
cursor = mydb.cursor()  # 建立遊標物件，用於執行SQL語句
count = 0  # 計數器，用於記錄插入的資料條數
for row in sheet.iter_rows(min_row=2, values_only=True):  # 從第二行開始遍歷每一行資料
    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    # 構建插入資料的SQL語句，使用預留位置%s
    cursor.execute(sql, row)  # 執行SQL語句，將行資料插入到資料庫中
    count += 1  # 每插入一條資料，計數器加1
    print(f"正在插入{count}條資料")  # 輸出插入的資料條數

# 提交更改並關閉資料庫連線
mydb.commit()  # 提交對資料庫的更改
cursor.close()  # 關閉遊標物件
mydb.close()  # 關閉與資料庫的連線

8. 總結

本文介紹瞭如何使用Python連線MySQL資料庫，並從Excel檔案中讀取資料，將其插入到MySQL資料庫中。如果您有任何疑問或建議，請在評論區留言。