Python將Excel表中數據插入MySQL（附代碼總結，可直接抄作業）

在現代資訊科技領域裡，資料庫管理系統（Database Management System, DBMS）被廣泛應用於各種商業應用程式和網際網路服務之中。其中，MySQL是最受歡迎的開源關係型資料庫之一，而Microsoft Excel則是一個功能強大的電子試算表軟體，可以輕鬆地處理大量的結構化資料。本文將探討如何透過Python語言來實現自動化的資料轉移流程，從Microsoft Excel工作簿中的資料欄位，將其資料匯入到MySQL資料庫中。

前置作業與環境設定

首先，您需要確保您的開發環境已經安裝了以下必要套件：

1. Python 3及其相關的套件管理器（例如pip）。

2. `pandas` – 用於讀取和操作Excel檔案。

3. `PyMySQL` – 用於連接到MySQL資料庫並執行SQL語句。

4. 如果使用的Excel檔案包含密碼保護的加密內容或使用了特定的檔案格式（如xlsb），可能還需額外依賴其他第三方套件，例如`xlrd`或`openpyxl`。

步驟概述

要使用Python將Excel資料匯入到MySQL資料庫中，一般遵循以下幾個主要步驟：

1. 開啟Excel檔案 – 使用`pandas`中的`read_excel()`函數來載入Excel資料為DataFrame物件。

2. 建立MySQL連線 – 使用`PyMySQL`中的`connect()`函數來建立到MySQL伺服器的連線。

3. 準備資料庫結構 – 在資料庫中創建所需的資料表，或者更新現有的資料表結構以匹配Excel檔案的資料欄位。

4. 插入資料 – 使用INSERT INTO SQL語句來將資料從DataFrame物件插入到資料庫中。

5. 關閉資源 – 完成資料匯入後，記得正確地關閉所有開啟的資料庫連線和文件物件，以釋放資源。

範例代碼

以下是一段簡化的範例代碼，展示了上述步驟的運作方式：

import pandas as pd
from pymysql import connect

# Step 1: Open the Excel file and read data into a DataFrame
excel_file = r"C:\path\to\your\excel\example.xlsx"  # 請根據實際路徑修改此處
df = pd.read_excel(excel_file)

# Step 2: Establish a connection to your MySQL database
connection = connect(host="localhost",
port=3306,
user="username",
password="password",
database="mydatabase",
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)

# Step 3: Prepare the destination table in MySQL if needed
sql_create_table = "CREATE TABLE IF NOT EXISTS mytable (id INT AUTO_INCREMENT PRIMARY KEY, col1 VARCHAR(255), col2 DATE)"
with connection.cursor() as cursor:
cursor.execute(sql_create_table)

# Step 4: Insert data from the DataFrame to the MySQL table
for index, row in df.iterrows():
values = [row["col1"], row["col2"]]
sql_insert = f"INSERT INTO mytable (col1, col2) VALUES (%s, %s)"
with connection.cursor() as cursor:
cursor.executemany(sql_insert, values)

# Step 5: Commit changes and close resources
connection.commit()
connection.close()
print(f"Successfully inserted {len(df)} rows of data into 'mytable'.")

請注意，上述代碼假設您的資料庫名稱為`mydatabase`，並且有一個空白的表格`mytable`存在。如果您的資料庫結構不同或有特定需求，可能需要調整SQL語句。此外，請務必小心輸入用戶端認證（例如使用者名稱和密碼）以及Excel檔案的絕對路徑。

小結

透過上述方法，您可以快速且有效地將Excel檔案中的資料導入到MySQL資料庫中，這對於資料遷移、ETL過程或其他需要自動化資料整合的工作來說是非常實用的技巧。然而，在使用這些技術之前，建議您先備份好重要的資料，並且熟悉所涉及的API和SQL語法，以確保資料的安全性和準確性。