如何用Python高效将百万级Excel数据导入SQL数据库？

Hey there! As someone who's tackled similar large-scale Excel-to-database automation tasks, I totally get your frustration with slow runtime and high memory usage. Let's break down the key bottlenecks in your code and fix them step by step.

1. Reduce Excel Read Time & Initial Memory Overhead #

The biggest slowdown here is likely repeated Excel file opening/closing. Your current code calls pd.read_excel() twice per file, which opens and closes the Excel file each time. Instead, use pd.ExcelFile to open the file once, then read both sheets from it. We'll also add small tweaks to optimize memory usage:

# Replace your per-file read section with this
for c, xl in enumerate(os.listdir(month_folder), 1):
    if '-Amazon' in xl:
        ttime = datetime.now()
        table_name = str(xl[11:-5])
        tables.append(table_name)
        
        # Open the Excel file ONCE, read both sheets
        file_path = os.path.join(month_folder, xl)
        with pd.ExcelFile(file_path) as xls:
            quote_sheet = pd.read_excel(xls, sheet_name='-Amazon-Quote')
            summary_sheet = pd.read_excel(xls, sheet_name='-Amazon-Summary')
        
        # Add metadata columns
        quote_sheet.insert(0,'reportmonth', reportmonth)
        quote_sheet.insert(0,'source_file', table_name)
        summary_sheet.insert(0,'reportmonth', reportmonth)
        summary_sheet.insert(0,'source_file', table_name)
        
        # Clean column names (combined for efficiency)
        quote_sheet.columns = quote_sheet.columns.str.strip().str.replace(' ', '_')
        summary_sheet.columns = summary_sheet.columns.str.strip().str.replace(' ', '_')
        
        # --- We'll handle appending to DB here instead of storing in lists ---
        # (See section 2 for this part)
        
        print(f'Step {c} complete: {datetime.now() - ttime} | Total elapsed: {datetime.now() - starttime}')

Additional memory-saving tweaks for pd.read_excel:

• Use dtype to specify smaller data types (e.g., dtype={'numeric_column': 'int32', 'category_column': 'category'}) if you know your data ranges.
• Use usecols to load only the columns you need later (skip unused columns entirely to save memory).

2. Eliminate Large DataFrame Lists (Cut Memory Usage Dramatically) #

Storing every small DataFrame in quote_combined and summary_combined forces all 3.4M rows to live in memory at once. Instead, write each file's data directly to SQLite as you process it (using if_exists='append'). This keeps only one file's data in memory at a time.

First, initialize your database connection once at the start:

# Move this to the top (after imports)
reportmonth = '2020-08'
month_folder = r'C:\syncedSharePointFolder'
db_path = fr'H:\AaronS\Databases\AMZN-Quote-files_{reportmonth}.sqlite'

# Create SQLAlchemy engine once
engine = create_engine(f'sqlite:///{db_path}', echo=False)

# Optional: Speed up SQLite writes with these pragmas
with engine.connect() as conn:
    conn.execute('PRAGMA synchronous = OFF')
    conn.execute('PRAGMA journal_mode = MEMORY')

Then, inside your file loop, replace appending to lists with appending to the database:

# Inside the for loop, after cleaning columns:
# Write quote sheet to DB (create table first, then append)
quote_sheet.to_sql('totalQuotes', engine, if_exists='append', index=False, chunksize=10000)
# Write summary sheet to DB
summary_sheet.to_sql('totalSummary', engine, if_exists='append', index=False, chunksize=10000)

Key improvements here:

• index=False: Avoids writing the pandas index as an extra column (saves space and time).
• chunksize=10000: Writes data in batches instead of one row at a time (massively speeds up SQLite inserts).
• No more pd.concat(): Skip the memory-heavy step of merging all DataFrames into one giant DF.

3. Additional Small Optimizations #

• Remove os.chdir: Use full file paths instead of changing directories (avoids path confusion and small overhead).
• Simplify counting: Use enumerate instead of manually incrementing c (cleaner code).
• Faster column cleaning: Combine str.strip() and str.replace() into one chained operation (reduces column traversals).

Full Optimized Code #

Here's the complete revised code incorporating all these changes:

import os
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
from playsound import playsound

# Configuration
reportmonth = '2020-08'
month_folder = r'C:\syncedSharePointFolder'
db_path = fr'H:\AaronS\Databases\AMZN-Quote-files_{reportmonth}.sqlite'

# Initialize engine and optimize SQLite for speed
engine = create_engine(f'sqlite:///{db_path}', echo=False)
with engine.connect() as conn:
    conn.execute('PRAGMA synchronous = OFF')
    conn.execute('PRAGMA journal_mode = MEMORY')

starttime = datetime.now()
print(f'Started at {starttime.strftime("%Y-%m-%d %H:%M:%S")}')
tables = list()

for c, xl in enumerate(os.listdir(month_folder), 1):
    if '-Amazon' in xl:
        ttime = datetime.now()
        table_name = str(xl[11:-5])
        tables.append(table_name)
        
        # Read Excel efficiently
        file_path = os.path.join(month_folder, xl)
        with pd.ExcelFile(file_path) as xls:
            quote_sheet = pd.read_excel(xls, sheet_name='-Amazon-Quote')
            summary_sheet = pd.read_excel(xls, sheet_name='-Amazon-Summary')
        
        # Add metadata columns
        quote_sheet.insert(0,'reportmonth', reportmonth)
        quote_sheet.insert(0,'source_file', table_name)
        summary_sheet.insert(0,'reportmonth', reportmonth)
        summary_sheet.insert(0,'source_file', table_name)
        
        # Clean column names
        quote_sheet.columns = quote_sheet.columns.str.strip().str.replace(' ', '_')
        summary_sheet.columns = summary_sheet.columns.str.strip().str.replace(' ', '_')
        
        # Append to database in batches
        quote_sheet.to_sql('totalQuotes', engine, if_exists='append', index=False, chunksize=10000)
        summary_sheet.to_sql('totalSummary', engine, if_exists='append', index=False, chunksize=10000)
        
        print(f'Step {c} complete: {datetime.now() - ttime} | Total elapsed: {datetime.now() - starttime}')

print(f'Finished. Total time taken: {datetime.now() - starttime}')
playsound('your_sound_file.wav')  # Keep if you need the completion alert

Expected Results #

• Memory usage: Should drop drastically (from holding 3.4M rows in memory to holding only one file's data at a time).
• Runtime: The 40-minute list generation step will disappear entirely, and database writes should take minutes instead of hours (thanks to batch inserts and SQLite optimizations).

If you run into issues with table schema consistency (e.g., columns don't match across files), you can pre-define the table schema using SQLAlchemy's Table object before appending data—just let me know if you need help with that!

内容的提问来源于stack exchange，提问作者SQLSqirrel