如何使用Python將Excel檔案匯入資料庫表格

在當今數據驅動的世界中，有效處理和處理數據是任何組織或個人的基本任務。 Python，擁有豐富的函式庫生態系統，提供了強大的工具來進行資料操作和管理，例如匯入 pandas 函式庫。一種常見的情景是需要從 Excel 電子表格並將數據存儲或插入到數據庫中以進行進一步分析或與其他系統集成。在本教程中，我們將探討如何創建一個自動化這一過程的Python腳本，使您能夠無縫地從Excel工作表讀取數據並將其插入數據庫。結束本教程後，您將能夠高效地處理數據遷移任務。開始吧!

如何使用Python將Excel文件導入數據庫表

1. 首先安裝IronXL庫。

2. 使用IronXL將Excel文件加載到內存中。

3. 加載你希望處理的具體電子表格。

4. 選擇你打算導入的精確數據範圍。

5. 使用Python與任何數據庫（如SQLite或MySQL）建立連接。

6. 在你的SQLite數據庫中創建一個新表，以適應導入的數據。

7. 將選擇的行從Excel文件插入到新創建的SQLite表中。

8. 從創建的SQLite表中檢索並選擇數據，以進行進一步的分析或處理。

在本教程中，我們將使用 IronXL，這是一個以處理 Excel 文件效率著稱的 Python 庫。通過將 IronXL 集成到我們的腳本中，我們確保可以順利地從 Excel 試算表中提取數據，從而能夠順利地插入數據庫進行進一步的分析和處理。

IronXL 是什麼？

IronXL 是由 Iron Software 開發的 Python 函式庫，提供強大的功能來 讀取, 生成，並直接在 Python 應用程式中編輯 Excel 文件。值得注意的是，IronXL 無需安裝 Microsoft Excel 即可運行，簡化了在不同環境中的部署。使用 IronXL，開發人員可以享受以下優勢：

跨平台支持： 在 Windows、macOS、Linux、Docker、Azure 和 AWS 平台上無縫運行，確保適用於多樣的開發設置。

數據導入和導出： 輕鬆處理來自 XLS、XLSX、CSV 和 TSV 文件的數據導入，並靈活地將工作表導出為這些格式，甚至導出為 JSON，以增強互操作性。

加密功能： 利用 IronXL 的加密功能確保數據安全，允許通過密碼保護 XLSX、XLSM 和 XLTX 文件。

公式和重新計算： 輕鬆處理 Excel 公式，並且每次編輯工作表都會自動重新計算，確保數據操作的準確性和可靠性。

單元格樣式：通過調整字體來自定義各個單元格的外觀 樣式, 尺寸, 背景圖案, 邊框和對齊方式，提升您的Excel文件的視覺呈現。

廣泛的文件格式支持: 支援多種格式，包括XLS, XLSX, XLST, XLSM, CSV和TSV，IronXL讓開發人員能夠輕鬆高效地處理多種情境下的數據。

現在，讓我們開始安裝IronXL。

步驟 1：安裝 IronXL 庫

第一步是安裝 IronXL 庫。在命令提示符中運行以下命令來安裝 IronXL。

pip install IronXL

pip install IronXL

Step 2: 載入 Excel 工作簿

下一步是載入 Excel 檔案。在這個教學中，我們將使用以下的 Excel 檔案。

以下程式碼將加載現有的 Excel 文件到記憶體中。

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

上述 Python 代碼片段展示了如何使用 IronXL 庫加載名為 “sample_excel.xlsx” 的 Excel 工作簿。首先，從 IronXL 導入必要的 Python 模組。然後，分配許可證密鑰以驗證庫的使用。您可以從 IronXL 網站最後，使用 Load 方法將指定的 Excel 工作簿打開並載入到記憶體中。這使得後續可以通過程式碼對其內容進行操作，例如讀取數據、修改單元格值或應用格式。

第三步：選擇工作表

要使用 IronXL 在 Excel 工作簿中選擇工作表，您可以指定工作表索引或名稱。

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

這行代碼選擇載入的 Excel 活頁簿中的第一個工作表，並將其賦值給變數 "worksheet"，允許在該特定工作表上進行後續操作。這將從 Excel 表格中載入數據到工作表變數。

步驟 4：開啟資料庫連接

在本教程中，我們使用 SQLite 資料庫而非 MySQL 資料庫伺服器。要開始資料庫操作，首先需要建立資料庫連接。

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

上述行建立了與名為 'data.db' 的SQLite數據庫的連接。如果指定的數據庫不存在，將自動創建該數據庫。該連接允許隨後與SQLite數據庫交互，例如執行查詢和進行數據操作。

第 5 步：建立表格

接下來的步驟是在資料庫中建立一個數據表，從 Excel 文件導入數據到該表中。要在 SQLite 資料庫中建立表格，可以使用連接物件執行 SQL 語句。

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

上述程式碼片段初始化了一個游標對象，以在SQLite數據庫連接中執行SQL命令。它創建了一個名為'customer'的表，表中包含'id'、'FirstName'、'LastName'、'Gender'、'Country'和'Age'等列。如果該表尚未存在，則根據指定的列數據類型創建該表。

第六步：使用 Python 將資料導入資料庫

現在，我們將把資料插入我們新建立的表格中。我們將匯入一個 Excel 檔案並將其資料插入 SQLite 資料庫中。

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

上述程式碼迭代 Excel 工作表中的第 2 到第 10 行，提取每行的 A 到 F 列的值。這些值被存儲在一個元組中，代表要插入到 'customer' 表中的數據。然後游標執行 SQL INSERT 命令，將值的元組插入到表中。此過程對每行重複，從而將 Excel 文件中的數據有效地導入到 SQLite 資料庫中。最後，conn.commit。() 提交交易，確保更改已保存並持久化在資料庫中。

第七步：從資料庫讀取資料

要確認資料是否正確插入，可以使用 SELECT 查詢從 SQLite 資料庫中的 'customer' 表讀取資料。例如：

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

上述程式碼在 SQLite 資料庫中的 'customer' 表執行 SELECT 查詢，檢索所有行。抓取的行使用 fetchall 存儲在 'rows' 變量中。() 方法。然後，逐行列印，顯示插入到「customer」表中的數據。最後，使用 close 關閉資料庫連接。() 釋放資源的方法。

完整的程式碼如下：

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

結論

總結，本教程展示了一種自動化方法來 讀取數據 釋放其在數據管理工作中的全部潛力。利用Python和IronXL的強大功能來優化您的數據處理工作流程，並自信地推動您的項目向前發展。