Python 利用pandas和mysql-connector獲取Excel資料寫入到MySQL資料庫

如何將Excel資料插入到MySQL資料庫中

在實際應用中，我們可能需要將Excel表格中的資料匯入到MySQL資料庫中，以便於進行進一步的資料分析和處理。本文將介紹如何使用Python將Excel表格中的資料插入到MySQL資料庫中。

匯入必要的庫

首先，我們需要匯入pandas庫和MySQL Connector/Python庫，以便於讀取Excel檔案和連線MySQL資料庫。

import pandas as pd  # 匯入pandas庫，用於讀取Excel檔案和處理資料
import mysql.connector  # 匯入MySQL Connector/Python庫，用於連線MySQL資料庫

連線資料庫

接下來，我們需要連線MySQL資料庫。可以通過MySQL Connector/Python庫提供的connect()方法來連線資料庫。

# 連線資料庫
mydb = mysql.connector.connect(
    host=host,  # 資料庫主機地址
    user=user,  # 資料庫使用者名稱
    password=password,  # 資料庫密碼
    database=database  # 資料庫名稱
)

其中，host、user、password和database分別是資料庫主機地址、資料庫使用者名稱、資料庫密碼和資料庫名稱，需要根據實際情況進行修改。

建立遊標物件

連線成功後，我們需要建立遊標物件。可以通過MySQL Connector/Python庫提供的cursor()方法來建立遊標物件。

# 建立遊標物件
mycursor = mydb.cursor()

讀取Excel檔案

接下來，我們需要讀取Excel檔案中的資料。可以使用pandas庫提供的read_excel()方法來讀取Excel檔案。

# 讀取Excel檔案
df = pd.read_excel(filename)

其中，filename是Excel檔案的路徑，需要根據實際情況進行修改。

將日期時間型別的列轉換為字串型別

在將資料插入到MySQL資料庫中之前，我們需要將日期時間型別的列轉換為字串型別。可以通過遍歷DataFrame中的每一列，並判斷該列的資料型別是否為日期時間型別，然後將該列的資料型別轉換為字串型別。

# 將日期時間型別的列轉換為字串型別
for col in df.columns:  # 遍歷DataFrame中的每一列
    if df[col].dtype == 'datetime64[ns]':  # 如果該列的資料型別是日期時間型別
        df[col] = df[col].astype(str)  # 將該列的資料型別轉換為字串型別

遍歷Excel表格中的每一行，並將每一行插入到資料庫中

接下來，我們需要遍歷Excel表格中的每一行，並將每一行插入到資料庫中。可以使用pandas庫提供的itertuples()方法來遍歷DataFrame中的每一行，並使用MySQL Connector/Python庫提供的execute()方法來執行SQL插入語句。

# 遍歷Excel表格中的每一行，並將每一行插入到資料庫中
for row in df.itertuples(index=False):  # 遍歷DataFrame中的每一行
    sql = f"INSERT INTO {table} (id, 姓名, 國家, 出生日期) VALUES (%s, %s, %s, %s)"  # SQL插入語句
    val = row  # 插入的資料
    mycursor.execute(sql, val)  # 執行SQL插入語句
    print("正在插入資料:", val)  # 輸出正在插入的資料

其中，table是資料庫表名，需要根據實際情況進行修改。

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

最後，我們需要提交更改並關閉資料庫連線。可以使用MySQL Connector/Python庫提供的commit()方法來提交更改，並使用close()方法來關閉遊標物件和資料庫連線。

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

完整程式碼如下：

import pandas as pd  # 匯入pandas庫，用於讀取Excel檔案和處理資料
import mysql.connector  # 匯入MySQL Connector/Python庫，用於連線MySQL資料庫

def insert_excel_data_to_mysql(filename, host, user, password, database, table):
    # 連線資料庫
    mydb = mysql.connector.connect(
        host=host,  # 資料庫主機地址
        user=user,  # 資料庫使用者名稱
        password=password,  # 資料庫密碼
        database=database  # 資料庫名稱
    )

    # 建立遊標物件
    mycursor = mydb.cursor()

    # 讀取Excel檔案
    df = pd.read_excel(filename)

    # 將日期時間型別的列轉換為字串型別
    for col in df.columns:  # 遍歷DataFrame中的每一列
        if df[col].dtype == 'datetime64[ns]':  # 如果該列的資料型別是日期時間型別
            df[col] = df[col].astype(str)  # 將該列的資料型別轉換為字串型別

    # 遍歷Excel表格中的每一行，並將每一行插入到資料庫中
    for row in df.itertuples(index=False):  # 遍歷DataFrame中的每一行
        sql = f"INSERT INTO {table} (id, 姓名, 國家, 出生日期) VALUES (%s, %s, %s, %s)"  # SQL插入語句
        val = row  # 插入的資料
        mycursor.execute(sql, val)  # 執行SQL插入語句
        print("正在插入資料:", val)  # 輸出正在插入的資料

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

# 使用範例
filename = r'C:\\Users\\Admin\\Desktop\\重新開始\\Python操作MySQL資料庫\\sheet1.xlsx'  # Excel檔案路徑
host = "localhost"  # 資料庫主機地址
user = "root"  # 資料庫使用者名稱
password = "123456"  # 資料庫密碼
database = "caiwu"  # 資料庫名稱
table = "yonghu"  # 資料庫表名

insert_excel_data_to_mysql(filename, host, user, password, database, table)  # 呼叫函數，將Excel資料插入到MySQL資料庫中