引言

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

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

本文所使用的方法需要用到sqlite3（Python標(biāo)準(zhǔn)庫(kù)中的組件）和Spire.XLS for Python（PyPI: pip install Spire.XLS）。

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

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

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

代碼示例

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

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

# 獲取數(shù)據(jù)庫(kù)中所有的表名
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ù)庫(kù)中的每個(gè)表
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)
    
    # 將標(biāo)題行寫入工作表
    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ù)導(dǎo)入到數(shù)據(jù)庫(kù)

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

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

代碼示例

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

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

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

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

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

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

    # 獲取標(biāo)題行中的數(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ù)庫(kù)表
    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ù)庫(kù)表中
    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ù)庫(kù)表中
        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é)果

本文展示了如何使用 Python 代碼在 Excel 工作簿和數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)的導(dǎo)入與導(dǎo)出。

以上就是Python實(shí)現(xiàn)數(shù)據(jù)庫(kù)與Excel文件之間的數(shù)據(jù)自動(dòng)化導(dǎo)入與導(dǎo)出的詳細(xì)內(nèi)容，更多關(guān)于Python數(shù)據(jù)庫(kù)與Excel導(dǎo)入與導(dǎo)出的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論