在日常數(shù)據(jù)處理工作中，將 Excel 文件內(nèi)容導(dǎo)入數(shù)據(jù)庫是一個(gè)常見需求。Python 生態(tài)中雖然有 pandas、openpyxl 等成熟方案，但當(dāng)遇到超大型 Excel 文件或需要精細(xì)控制單元格格式時(shí)，借助專用組件往往能提升開發(fā)效率。

本文基于輕量級(jí) Excel 處理庫完成 Excel 文件解析，結(jié)合 Python 內(nèi)置的 SQLite 數(shù)據(jù)庫（無需獨(dú)立部署），實(shí)現(xiàn)多工作表自動(dòng)識(shí)別、動(dòng)態(tài)創(chuàng)建表結(jié)構(gòu)、批量數(shù)據(jù)入庫的完整方案。

一、應(yīng)用場(chǎng)景與方案優(yōu)勢(shì)

適用場(chǎng)景

1. 企業(yè) Excel 報(bào)表數(shù)據(jù)遷移至數(shù)據(jù)庫持久化存儲(chǔ)；
2. 自動(dòng)化辦公：定期將 Excel 導(dǎo)出數(shù)據(jù)同步到數(shù)據(jù)庫；
3. 輕量級(jí)數(shù)據(jù)中臺(tái)：多 Excel 文件整合入庫，方便后續(xù)查詢分析； 4.測(cè)試數(shù)據(jù)構(gòu)造：快速將 Excel 測(cè)試數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫。

方案核心優(yōu)勢(shì)

1. 無環(huán)境依賴：無需安裝 Microsoft Office/WPS，純 Python 庫解析 Excel；
2. 多工作表適配：自動(dòng)遍歷 Excel 所有 sheet，無需手動(dòng)指定；
3. 動(dòng)態(tài)建表：根據(jù) Excel 表頭自動(dòng)生成數(shù)據(jù)庫表結(jié)構(gòu)；
4. 安全穩(wěn)定：參數(shù)化 SQL 防注入，事務(wù)管理保證數(shù)據(jù)一致性；
5. 輕量免費(fèi)：適用于中小型 Excel 文件處理，無額外成本。

二、環(huán)境準(zhǔn)備

僅需安裝 Excel 解析庫（Free Spire.XLS for Python），SQLite 為 Python 內(nèi)置庫，無需額外安裝：

pip install FreeSpire.XLS

三、核心執(zhí)行流程

整個(gè)程序分為 5 個(gè)核心步驟，數(shù)據(jù)流轉(zhuǎn)清晰無冗余：

加載Excel文件 → 連接數(shù)據(jù)庫 → 遍歷工作表 → 讀取表頭+動(dòng)態(tài)建表 → 逐行數(shù)據(jù)插入 → 提交事務(wù)+釋放資源

3.1 完整代碼

from spire.xls import Workbook
import sqlite3

def excel_to_sqlite(excel_path, db_path):
    # 1. 加載 Excel 文件
    workbook = Workbook()
    workbook.LoadFromFile(excel_path)

    # 2. 連接數(shù)據(jù)庫
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 3. 遍歷每個(gè)工作表
    for sheet_index in range(workbook.Worksheets.Count):
        sheet = workbook.Worksheets.get_Item(sheet_index)
        sheet_name = sheet.Name.replace(" ", "")          # 表名中去掉空格

        # 4. 讀取表頭（第一行）
        header = []
        for col in range(sheet.AllocatedRange.ColumnCount):
            raw_value = sheet.Range[1, col + 1].Value
            # 字段名中去掉空格，并避免空字段
            field_name = raw_value.replace(" ", "") if raw_value else f"col_{col}"
            header.append(field_name)

        # 5. 創(chuàng)建數(shù)據(jù)庫表（所有字段暫定為 TEXT 類型）
        create_sql = f"""
        CREATE TABLE IF NOT EXISTS {sheet_name} (
            {', '.join([f'[{h}] TEXT' for h in header])}
        )
        """
        cursor.execute(create_sql)

        # 6. 逐行插入數(shù)據(jù)（跳過表頭行）
        for row in range(1, sheet.AllocatedRange.RowCount):  # row=1 對(duì)應(yīng) Excel 第二行
            row_data = []
            for col in range(sheet.AllocatedRange.ColumnCount):
                cell_value = sheet.Range[row + 1, col + 1].Value
                row_data.append(cell_value)
            
            # 使用參數(shù)化查詢防止 SQL 注入
            placeholders = ','.join(['?' for _ in row_data])
            insert_sql = f"INSERT INTO {sheet_name} ({','.join(header)}) VALUES ({placeholders})"
            cursor.execute(insert_sql, row_data)

    # 7. 提交并清理
    conn.commit()
    conn.close()
    workbook.Dispose()

if __name__ == "__main__":
    excel_to_sqlite("Sample.xlsx", "output/Report.db")

3.2 關(guān)鍵點(diǎn)解析

1. 工作表遍歷與表名清洗

workbook.Worksheets.Count 獲取工作表總數(shù)，get_Item(s) 按索引獲取。工作表名稱可能包含空格、特殊字符，直接用作 SQLite 表名會(huì)導(dǎo)致語法錯(cuò)誤，因此使用 .replace(" ", "") 去除空格。更嚴(yán)謹(jǐn)?shù)淖龇稍黾诱齽t過濾，只保留字母數(shù)字和下劃線。

2. 動(dòng)態(tài)建表與字段類型

示例將所有字段定義為 TEXT 類型，適配 Excel 中字符串、數(shù)字、日期等通用格式（可根據(jù)業(yè)務(wù)修改數(shù)據(jù)類型）。

3. 數(shù)據(jù)讀取的范圍

sheet.AllocatedRange 返回已使用的單元格區(qū)域（包含數(shù)據(jù)的最大矩形），比直接遍歷全部行列更高效。注意 RowCount 和 ColumnCount 是基于 1 的計(jì)數(shù)。

4. 參數(shù)化插入

使用 ? 占位符配合 cursor.execute(insert_sql, row_data) 能自動(dòng)處理字符串轉(zhuǎn)義，避免因 Excel 單元格內(nèi)容包含單引號(hào)導(dǎo)致的 SQL 錯(cuò)誤，同時(shí)防范注入風(fēng)險(xiǎn)。

四、擴(kuò)展：適配其他數(shù)據(jù)庫

只需修改數(shù)據(jù)庫連接部分，即可遷移到 MySQL、PostgreSQL 等。注意不同數(shù)據(jù)庫的標(biāo)識(shí)符引用符不同（MySQL 用反引號(hào) `，PostgreSQL 用雙引號(hào) "），以及字段類型映射的差異。例如連接 MySQL：

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', db='test')
cursor = conn.cursor()
# 建表時(shí)將 [field] 改為 `field`

五、注意事項(xiàng)與最佳實(shí)踐

• 檢查 Excel 列名與數(shù)據(jù)庫表字段名是否匹配。to_sql 默認(rèn)使用 DataFrame 的列名作為數(shù)據(jù)庫字段名，如果數(shù)據(jù)庫表已存在且字段名不同，要么重命名 DataFrame 列，要么設(shè)置 if_exists='replace' 重建表。
• 處理超大 Excel 文件：pd.read_excel 會(huì)將整個(gè)文件加載到內(nèi)存，如果 Excel 文件超過幾百 MB，可以改用 openpyxl 的只讀模式或分塊讀取。但更推薦將大 Excel 拆分成多個(gè)小文件，或者使用 dask。
• 事務(wù)處理：to_sql 默認(rèn)在自動(dòng)提交模式下運(yùn)行，如果中途出錯(cuò)，已寫入的數(shù)據(jù)不會(huì)回滾。要保證原子性，可以手動(dòng)管理事務(wù)（使用 engine.begin() 或連接對(duì)象的 begin()）。
• 性能對(duì)比：對(duì)于十萬行以下的數(shù)據(jù)，to_sql 默認(rèn)方式已經(jīng)足夠；對(duì)于百萬行級(jí)別，建議使用 chunksize=5000 并配合數(shù)據(jù)庫的批量提交優(yōu)化。
• 日志與錯(cuò)誤處理：建議用 try...except 捕獲異常，并記錄失敗的行或文件位置。

六、知識(shí)擴(kuò)展

1.使用 executemany 手動(dòng)批量插入

如果你需要更精細(xì)的控制（例如在插入前做復(fù)雜轉(zhuǎn)換），也可以手動(dòng)使用 cursor.executemany：

import pymysql
import pandas as pd
df = pd.read_excel('data.xlsx')
records = df.to_dict('records')  # 轉(zhuǎn)換為字典列表
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test')
cursor = conn.cursor()
sql = "INSERT INTO table_name (col1, col2) VALUES (%s, %s)"
values = [(r['col1'], r['col2']) for r in records]
cursor.executemany(sql, values)
conn.commit()
cursor.close()
conn.close()

這種方式和 to_sql 的 chunksize 本質(zhì)類似，但你可以自定義 SQL 語句。

2.從 Excel 批量導(dǎo)入 MySQL

import pandas as pd
from sqlalchemy import create_engine
# 讀取 Excel
df = pd.read_excel('sales.xlsx')
# 確保日期列為 datetime
df['sale_date'] = pd.to_datetime(df['sale_date'])
# 連接 MySQL
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/testdb')
# 寫入數(shù)據(jù)庫（如果表存在則替換）
df.to_sql('sales', engine, if_exists='replace', index=False)
print("數(shù)據(jù)導(dǎo)入完成！")

七、結(jié)語

本文實(shí)現(xiàn)了一套輕量化、高可用的 Excel 數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫方案，核心優(yōu)勢(shì)為多工作表自動(dòng)適配、動(dòng)態(tài)表結(jié)構(gòu)生成、安全的數(shù)據(jù)插入，代碼簡(jiǎn)潔且易于二次開發(fā)。方案適用于日常數(shù)據(jù)遷移、報(bào)表導(dǎo)入等輕量級(jí)場(chǎng)景，無需復(fù)雜配置即可快速落地使用。

到此這篇關(guān)于Python輕松實(shí)現(xiàn)將Excel數(shù)據(jù)批量導(dǎo)入數(shù)據(jù)庫的文章就介紹到這了,更多相關(guān)Python Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論