如何用Python Pandas在已有Excel中新增工作表存储多SQL查询结果

Let's tweak your code to save each query's output as a distinct sheet in the same Excel file, instead of overwriting the file every time. Here's the revised, more efficient version:

import pyodbc as hive
import pandas as pd

filename = r'C:\Users\krkg039\Desktop\query.txt'

# Read SQL commands using a context manager (auto-closes the file)
with open(filename, 'r') as fd:
    sqlFile = fd.read()

# Split commands and clean up empty entries (from trailing/leading semicolons)
sqlCommands = [cmd.strip() for cmd in sqlFile.split(';') if cmd.strip()]

# Initialize Excel writer once (handles all sheets in one file)
with pd.ExcelWriter('Result.xlsx') as writer:
    # Connect to the database once (reuse the connection for all queries)
    try:
        con = hive.connect("DSN=SFO-LG", autocommit=True)
        # Loop through queries with an index for unique sheet names
        for idx, command in enumerate(sqlCommands, start=1):
            try:
                df = pd.read_sql(command, con)
                print(f"Successfully ran query {idx}")
                print(df)
                # Write to a uniquely named sheet (e.g., Sheet_1, Sheet_2)
                df.to_excel(writer, sheet_name=f'Sheet_{idx}', index=False)
            except Exception as e:
                print(f"Skipped query {idx}: {str(e)}")
                print(f"Query content: {command}")
    except Exception as e:
        print(f"Failed to connect to database: {str(e)}")

Key Improvements Breakdown: #

• Single Excel Writer Instance: We create the pd.ExcelWriter once outside the loop (using a with statement to auto-save/close) so all sheets get added to the same file, no overwrites.
• Unique Sheet Names: Using enumerate gives us an index to generate distinct sheet names like Sheet_1, Sheet_2—no more overwriting a single 'Test' sheet.
• Reusable Database Connection: Opening a new connection for every query wastes resources. Now we connect once and reuse that connection for all queries.
• Cleaned SQL Commands: The list comprehension filters out empty strings that often come from splitting on ; (e.g., if your file ends with a semicolon).
• Better Error Debugging: We print specific error messages and the problematic query text, so you can quickly identify which query failed.
• Context Managers: Using with for file handling and Excel Writer ensures resources are properly closed even if an error occurs mid-execution.

If you want more descriptive sheet names (instead of numbered ones), you could add comments in your query.txt file (like -- Q1: Monthly Sales before a query) and parse those to name the sheets. But the numbered approach works as a simple, reliable default.

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