Got it, let's fix this for you—your current workflow is eating up too much memory and storage because you're loading entire million-row tables into RAM and creating unnecessary intermediate CSV files. We can streamline this to handle huge datasets efficiently, no bloat required.

Core Idea #

Instead of loading full tables or saving intermediate files, we'll:

1. Connect to your ODBC data source once
2. Read each table in small, manageable chunks (so only a tiny portion of data is in memory at any time)
3. Append each chunk directly to your final output CSV, writing the header only once

Optimized Code #

import pandas as pd
import pyodbc

# Configure your ODBC connection (replace with your DSN/connection string)
conn = pyodbc.connect('DSN=YourODBCDataSource')

# List of tables you want to merge (add/remove as needed)
target_tables = ['table1', 'table2', 'table3']
output_file = r'C:\Desktop\Output_file.csv'

# Flag to ensure we only write the CSV header once
write_header = True

for table_name in target_tables:
    print(f"Starting processing for table: {table_name}")
    # Read the table in chunks (adjust chunksize based on your available memory)
    # 10,000 rows per chunk is a safe starting point—tune up/down as needed
    chunk_iterator = pd.read_sql_query(
        f"SELECT * FROM {table_name}",
        conn,
        chunksize=10000
    )
    
    for chunk in chunk_iterator:
        # Append chunk to output CSV: write header only on the first chunk
        chunk.to_csv(
            output_file,
            mode='a',
            header=write_header,
            index=False,
            encoding='utf-8'
        )
        # After the first chunk, disable header writing
        if write_header:
            write_header = False
    
    print(f"Completed processing table: {table_name}")

# Clean up the database connection
conn.close()
print(f"All tables merged successfully into {output_file}")

Key Benefits Over Your Original Approach #

• No intermediate files: Saves storage space entirely—we skip writing file1.csv, file2.csv, etc.
• Memory-efficient: Only one chunk (e.g., 10k rows) is loaded into RAM at a time, preventing out-of-memory errors even for million-row tables.
• Faster end-to-end: Eliminates the need to read/write intermediate files, cutting down on I/O overhead.

Pro Tips for Further Optimization #

• Filter unnecessary columns: If you don't need every column from the tables, modify your SQL query to only select what you need (e.g., SELECT col1, col3, col5 FROM {table_name}). This reduces data volume drastically.
• Tune chunksize: Adjust the chunksize parameter based on your system's available memory. Larger chunks mean fewer iterations, but use more memory—find the sweet spot for your setup.
• Use faster CSV writing: If you need even more speed, you can pair pandas chunks with the native csv module for writing, but the above method is already efficient for most use cases.

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