快捷導航

Python實現(xiàn)數(shù)據(jù)庫與Excel文件之間的數(shù)據(jù)自動化導入與導出

更新時間：2024年06月28日 09:40:08 作者：Eiceblue

數(shù)據(jù)庫和Excel文件是兩種常見且重要的數(shù)據(jù)存儲方式,本文將介紹如何使用Python有效地實現(xiàn)數(shù)據(jù)庫與Excel文件之間的數(shù)據(jù)自動化導入與導出,以SQLite數(shù)據(jù)庫為例,需要的朋友可以參考下

引言

數(shù)據(jù)庫和Excel文件是兩種常見且重要的數(shù)據(jù)存儲方式。數(shù)據(jù)庫通常用于大規(guī)模數(shù)據(jù)的高效存儲、管理和查詢，而Excel則以其直觀的界面和簡單的操作方式廣泛應(yīng)用于數(shù)據(jù)分析、報告生成和可視化等領(lǐng)域。在實際工作中，可能需要在這兩者之間進行數(shù)據(jù)的導入與導出。例如，從數(shù)據(jù)庫中提取數(shù)據(jù)到Excel進行深入分析和圖表繪制，或者將Excel文件中的數(shù)據(jù)清洗整理后導入數(shù)據(jù)庫進行集中管理和進一步處理。而Python因其便捷性和豐富的生態(tài)，能夠幫助用戶通過簡單的代碼處理數(shù)據(jù)庫與Excel文件之間數(shù)據(jù)轉(zhuǎn)換。

本文將介紹如何使用Python有效地實現(xiàn)數(shù)據(jù)庫與Excel文件之間的數(shù)據(jù)自動化導入與導出，以SQLite數(shù)據(jù)庫為例。

本文所使用的方法需要用到sqlite3（Python標準庫中的組件）和Spire.XLS for Python（PyPI: pip install Spire.XLS）。

用Python將數(shù)據(jù)庫數(shù)據(jù)導出到Excel表格

我們可以使用 sqlite3 模塊從數(shù)據(jù)庫讀取數(shù)據(jù)，并利用 Spire.XLS 模塊創(chuàng)建 Excel 文件并將數(shù)據(jù)寫入其中，從而實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的導出。詳細步驟如下：

導入必要的模塊。
連接數(shù)據(jù)庫：使用 sqlite3.connect() 連接 SQLite 數(shù)據(jù)庫，并創(chuàng)建游標 cursor 執(zhí)行 SQL 命令。
獲取表名：執(zhí)行 SQL 查詢獲取數(shù)據(jù)庫中所有表的名稱，并存儲在 tableNames 列表中。
創(chuàng)建 Excel 工作簿：初始化一個 Workbook 對象，并使用 Workbook.Worksheets.Clear() 方法清除默認工作表。
遍歷數(shù)據(jù)庫表：對于 tableNames 中的每一個表名：
- 查詢表的列信息，提取列名并添加到 columnNames 列表中。
- 獲取表中的所有數(shù)據(jù)行到 rows 中。
- 使用 Workbook.Worksheets.Add(sheetname) 方法在 Excel 中添加一個以表名命名的新工作表。
- 使用 Worksheet.Range[row, col].Value 屬性將 columnNames 作為標題寫入工作表。
- 遍歷數(shù)據(jù)行，并使用相同的屬性將數(shù)據(jù)寫入對應(yīng)的單元格。
- 格式化工作表。
使用 Workbook.SaveToFile() 方法將工作簿保存到文件。
釋放 workbook 的資源并關(guān)閉數(shù)據(jù)庫連接。

代碼示例

from spire.xls import *
from spire.xls.common import *
import sqlite3

# 連接到數(shù)據(jù)庫
conn = sqlite3.connect("output/CompanyInfo.db")
cursor = conn.cursor()

# 獲取數(shù)據(jù)庫中所有的表名
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tableNames = [name[0] for name in cursor.fetchall()]

# 創(chuàng)建 Excel 文件
workbook = Workbook()
workbook.Worksheets.Clear()

# 遍歷數(shù)據(jù)庫中的每個表
for tableName in tableNames:
    # 獲取表的列名
    cursor.execute(f"PRAGMA table_info('{tableName}')")
    columnsInfo = cursor.fetchall()
    columnNames = [columnInfo[1] for columnInfo in columnsInfo]

    # 獲取表的數(shù)據(jù)
    cursor.execute(f"SELECT * FROM {tableName}")
    rows = cursor.fetchall()
    
    # 創(chuàng)建工作表
    sheet = workbook.Worksheets.Add(tableName)
    
    # 將標題行寫入工作表
    for i in range(len(columnNames)):
        sheet.Range[1, i + 1].Value = columnNames[i]
    
    # 將數(shù)據(jù)寫入工作表
    for j in range(1, len(rows)):
        column = rows[j]
        for k in range(len(column)):
            sheet.Range[j + 1, k + 1].Value = column[k]
    
    # 設(shè)置工作表格式
    sheet.AllocatedRange.Style.Font.FontName = "Times New Roman"
    sheet.AllocatedRange.Style.Font.Size = 12.0
    sheet.AllocatedRange.AutoFitRows()
    sheet.AllocatedRange.AutoFitColumns()

# 保存 Excel 文件
workbook.SaveToFile("output/DatabaseToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
conn.close()

結(jié)果

用Python將Excel表格數(shù)據(jù)導入到數(shù)據(jù)庫

我們也可以使用 Spire.XLS 從 Excel 文件讀取各種數(shù)據(jù)類型，然后使用 sqlite3 將數(shù)據(jù)寫入數(shù)據(jù)庫。詳細步驟如下：

導入必要的模塊。
創(chuàng)建 Workbook 實例：初始化一個 Workbook 對象以操作 Excel 工作簿。
加載 Excel 文件：使用 LoadFromFile 方法從指定路徑加載 Excel 文件。
連接數(shù)據(jù)庫：使用 sqlite3.connect() 連接 SQLite 數(shù)據(jù)庫，并創(chuàng)建游標 cursor 執(zhí)行 SQL 命令。
遍歷工作表：對于工作簿中的每個工作表：
- 使用 Worksheet.Name 屬性獲取工作表對象和名稱，并去除名稱中的空格。
- 提取標題：使用 Worksheet.Range[row, col].Value 屬性收集第一行數(shù)據(jù)作為數(shù)據(jù)庫表的列名，并去除空格。
- 創(chuàng)建數(shù)據(jù)庫表：基于提取的標題動態(tài)生成 SQL 語句以創(chuàng)建表（如果不存在）。
- 插入數(shù)據(jù)：遍歷工作表的每一行，使用 Worksheet.Range[row, col].Value 屬性收集數(shù)據(jù)，然后構(gòu)造 SQL 插入語句將數(shù)據(jù)插入相應(yīng)的數(shù)據(jù)庫表。
提交并關(guān)閉數(shù)據(jù)庫連接：將所有更改提交到數(shù)據(jù)庫并關(guān)閉數(shù)據(jù)庫連接。
釋放 Workbook 資源：清理 Workbook 對象所使用的資源。

代碼示例

from spire.xls import *
from spire.xls.common import *
import sqlite3

# 創(chuàng)建 Workbook 實例
workbook = Workbook()

# 加載 Excel 文件
workbook.LoadFromFile("Sample.xlsx")

# 連接到數(shù)據(jù)庫
conn = sqlite3.connect("output/ExcelToDatabase.db")
cursor = conn.cursor()

for s in range(workbook.Worksheets.Count):
    # 獲取一個工作表
    sheet = workbook.Worksheets.get_Item(s)

    # 獲取工作表名稱
    sheetName = sheet.Name
    sheetName = sheetName.replace(" ", "")

    # 獲取標題行中的數(shù)據(jù)
    header = []
    for i in range(sheet.AllocatedRange.ColumnCount):
        headerValue = sheet.Range[1, i + 1].Value
        headerValue = headerValue.replace(" ", "")
        header.append(headerValue)

    # 創(chuàng)建數(shù)據(jù)庫表
    createTableSql = f"CREATE TABLE IF NOT EXISTS {sheetName} ({', '.join([f'{header[i]} TEXT' for i in range(len(header))])})"
    cursor.execute(createTableSql)

    # 插入數(shù)據(jù)到數(shù)據(jù)庫表中
    for row in range(1, sheet.AllocatedRange.RowCount):
        data = []
        for col in range(sheet.AllocatedRange.ColumnCount):
            # 獲取單元格值
            value = sheet.Range[row + 1, col + 1].Value
            data.append(value)
        # 插入單元格值到數(shù)據(jù)庫表中
        insertSql = f"INSERT INTO {sheetName} ({', '.join(header)}) VALUES ({', '.join(['?' for _ in data])})"
        cursor.execute(insertSql, data)

# 提交更改并關(guān)閉連接
conn.commit()
conn.close()

workbook.Dispose()

結(jié)果