基于特定邮箱拆分数据：用SQL+Python循环批量生成Excel至文件夹

Great question! You’ve got two solid approaches to split your data into folders and Excel files by email—let’s break down both with code examples tailored to your existing setup.

This is straightforward if your dataset fits comfortably in memory. We’ll load all data once, then split it by email, create folders, and export each subset.

import os
import pandas as pd
import cx_Oracle

# Connect to Oracle and load all data
orc_con = cx_Oracle.connect('xxx/xxx@xxxx.world')
SQL_Statement = 'select * from PYTHON_TEST'
df_ora = pd.read_sql(SQL_Statement, con=orc_con)
orc_con.close()  # Clean up connection after loading data

# Replace 'EMAIL_COLUMN' with your actual email column name (e.g., 'USER_EMAIL')
email_column = 'EMAIL_COLUMN'
unique_emails = df_ora[email_column].unique()

for email in unique_emails:
    # Create a valid folder name (replace invalid OS characters like @/. )
    folder_name = email.replace('@', '_at_').replace('.', '_dot_')
    os.makedirs(folder_name, exist_ok=True)  # Create folder if it doesn't exist
    
    # Filter the dataframe for this specific email
    filtered_df = df_ora[df_ora[email_column] == email]
    
    # Export to Excel in the dedicated folder
    excel_path = os.path.join(folder_name, f"{folder_name}_Monthly_Claims.xlsx")
    with pd.ExcelWriter(excel_path, engine='xlsxwriter') as writer:
        filtered_df.to_excel(writer, index=False, sheet_name='Claims')
    
    print(f"Successfully exported data for {email} to {excel_path}")

Key Details: #

• os.makedirs(..., exist_ok=True) prevents errors if the folder already exists (handles reruns smoothly).
• Cleaning the email to make a valid folder name avoids issues with operating system restrictions on special characters.
• Using a with statement for ExcelWriter ensures the file is properly closed after writing.

If your dataset is too large to load all at once, fetching data per email directly from the database is more memory-efficient. This avoids loading the entire dataset into RAM.

import os
import pandas as pd
import cx_Oracle

# Connect to Oracle
orc_con = cx_Oracle.connect('xxx/xxx@xxxx.world')
email_column = 'EMAIL_COLUMN'

# First get the list of unique emails from the database
get_emails_sql = f'SELECT DISTINCT {email_column} FROM PYTHON_TEST'
unique_emails = pd.read_sql(get_emails_sql, con=orc_con)[email_column].tolist()

for email in unique_emails:
    # Create valid folder name
    folder_name = email.replace('@', '_at_').replace('.', '_dot_')
    os.makedirs(folder_name, exist_ok=True)
    
    # Use parameterized query to avoid SQL injection and handle special characters
    filter_sql = f'SELECT * FROM PYTHON_TEST WHERE {email_column} = :email'
    filtered_df = pd.read_sql(filter_sql, con=orc_con, params={'email': email})
    
    # Export to Excel
    excel_path = os.path.join(folder_name, f"{folder_name}_Monthly_Claims.xlsx")
    with pd.ExcelWriter(excel_path, engine='xlsxwriter') as writer:
        filtered_df.to_excel(writer, index=False, sheet_name='Claims')
    
    print(f"Successfully exported data for {email} to {excel_path}")

orc_con.close()

Key Details: #

• Parameterized queries (:email) are critical here—they prevent SQL injection and handle emails with special characters (like hyphens or plus signs) correctly.
• We only fetch the data needed for each email, keeping memory usage low even for huge datasets.

• Python Loop: Best for small/medium datasets (fits in memory). Faster because you load data once instead of making multiple database calls.
• SQL WHERE: Better for large datasets. Uses less memory, though it makes multiple database queries (which is worth it to avoid overwhelming your system).

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