PySimpleGUI界面设计优化、按钮故障排查及DB Comparator Tool功能增强技术咨询

Let's break down each of your problems and walk through fixes plus enhancements for your tool. I've revised your code with all the core fixes and added the requested dropdown feature—let's dive in:

1. Add Table Name Dropdown from Excel #

To populate a dropdown with Table Name values from your uploaded Excel file, we'll add a sg.Combo widget below the file browse input. We'll bind the file selection event to trigger reading the Excel file and updating the dropdown options automatically.

2. Fix Layout & Text Box Alignment #

• Standardize input box sizes across both database credential sections so they align perfectly
• Use fixed-width text labels to ensure consistent spacing between labels and inputs
• Restructure the second window layout to keep related elements (file picker, dropdown) grouped logically

3. Fix Unresponsive Exit Button #

Your original code had a typo (secongwindow instead of secondwindow) and lacked a proper event loop for the second window. We'll fix the typo and add a dedicated loop for the compare window to handle exit events cleanly without freezing.

4. Text Box Alignment Fixes #

By setting consistent size parameters for both text labels and input fields, we'll ensure all elements line up neatly. For example, set sg.Text to size=(10,1) and input fields to size=(25,1) across both database columns.

Revised Full Code #

import PySimpleGUI as sg
import re
import datacompy
import cx_Oracle
import pandas as pd

supported_extensions = ['csv', 'xlsx', 'xlsm', 'json']

# --------------------------
# Layout for Database Credentials Window
# --------------------------
# Standardize label and input sizes for perfect alignment
label_size = (10, 1)
input_size = (25, 1)

DB_creds_one = [
    [sg.Text("UserName", size=label_size), sg.In(size=input_size, enable_events=True, key="-uname_db1-")],
    [sg.Text("Password", size=label_size), sg.In(size=input_size, enable_events=True, key="-pword_db1-", password_char='*')],
    [sg.Text("Hostname", size=label_size), sg.In(size=input_size, enable_events=True, key="-hname_db1-")],
    [sg.Text("Server", size=label_size), sg.In(size=input_size, enable_events=True, key="-sname_db1-")],
    [sg.Text("Port", size=label_size), sg.In(size=input_size, enable_events=True, key="-port_db1-")],
    [sg.B('Test Con1', key='B1')],
]

db_creds_two = [
    [sg.Text("UserName", size=label_size), sg.In(size=input_size, enable_events=True, key="-uname_db2-")],
    [sg.Text("Password", size=label_size), sg.In(size=input_size, enable_events=True, key="-pword_db2-", password_char='*')],
    [sg.Text("Hostname", size=label_size), sg.In(size=input_size, enable_events=True, key="-hname_db2-")],
    [sg.Text("Server", size=label_size), sg.In(size=input_size, enable_events=True, key="-sname_db2-")],
    [sg.Text("Port", size=label_size), sg.In(size=input_size, enable_events=True, key="-port_db2-")],
    [sg.B('Test Con2', key='B2')],
]

layout_pre_file = [
    [sg.Column(DB_creds_one), sg.VSeperator(), sg.Column(db_creds_two)],
    [sg.Output(size=(80, 25), key='-output-')],
    [sg.Submit('Proceed'), sg.Button('Exit')]
]

window = sg.Window("DB Comparator - Credentials", layout_pre_file)

# --------------------------
# Event Loop for First Window
# --------------------------
second_window_open = False
test_db1_ok = False
test_db2_ok = False

while True:
    event, values = window.read()
    
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    
    if event == 'Proceed':
        # Close first window and open compare window
        window.close()
        second_window_open = True
        
        # --------------------------
        # Layout for Compare Window
        # --------------------------
        layout_post_file = [
            [sg.Text('Select Excel File', size=label_size), 
             sg.InputText(key="-FILE_PATH-"), 
             sg.FileBrowse(file_types=(("Excel Files", "*.xlsx"), ("All Files", "*.*")))],
            # New dropdown for Table Names (populated from Excel)
            [sg.Text('Select Table', size=label_size), 
             sg.Combo([], size=(40, 1), key="-TABLE_DROPDOWN-", readonly=True)],
            [sg.Output(size=(80, 25), key='-output-')],
            [sg.Submit('Compare'), sg.Button('Exit')]
        ]
        
        compare_window = sg.Window('DB Comparator - Compare Results', layout_post_file)
        
        # --------------------------
        # Event Loop for Second Window
        # --------------------------
        while True:
            compare_event, compare_values = compare_window.read()
            
            if compare_event == sg.WIN_CLOSED or compare_event == 'Exit':
                compare_window.close()
                second_window_open = False
                break
            
            # Handle file selection to populate dropdown
            if compare_event == "-FILE_PATH-":
                file_path = compare_values["-FILE_PATH-"]
                if file_path.endswith('.xlsx'):
                    try:
                        df = pd.read_excel(file_path)
                        # Extract unique Table Names and sort them for usability
                        table_names = sorted(df['Table Name'].unique().tolist())
                        # Update dropdown values
                        compare_window["-TABLE_DROPDOWN-"].update(values=table_names)
                        print(f"Loaded {len(table_names)} table names from file.")
                    except Exception as e:
                        print(f"Error reading Excel file: {str(e)}")
            
            if compare_event == 'Compare':
                # Add your Datacompy comparison logic here using the selected table
                selected_table = compare_values["-TABLE_DROPDOWN-"]
                if not selected_table:
                    print("Please select a table first!")
                    continue
                print(f"Starting comparison for table: {selected_table}")
                # Insert your existing comparison code here (connect to DBs, run SQL, compare)

# Cleanup any open windows on exit
if second_window_open:
    compare_window.close()
window.close()

5. Suggested New Features & Code Optimization #

Here are some practical ideas to enhance your tool and improve code maintainability:

New Features #

• Save/Load Connection Profiles: Let users save database credentials to a JSON config file for quick reuse (no need to re-enter every time)
• Export Comparison Results: Add options to export differences to Excel, CSV, or formatted HTML reports for easy sharing
• Multi-Database Support: Extend beyond Oracle to support MySQL, PostgreSQL, or SQL Server using connectors like psycopg2 or pyodbc
• Custom Comparison Rules: Let users specify columns to ignore, primary keys for matching, or tolerance thresholds for numeric values
• Progress Indicators: Add a progress bar for long-running comparisons to keep users informed
• Input Validation: Add checks for valid port numbers, non-empty credentials, and valid file paths to prevent invalid inputs
• Side-by-Side Result View: Replace the plain output box with a scrollable table showing row-level differences between databases

Code Optimization #

• Modularize Code: Split logic into reusable functions (e.g., test_db_connection(), load_excel_table_names(), run_comparison()) for easier debugging and updates
• Robust Error Handling: Add try-except blocks around database connections and file operations to catch and display meaningful errors (instead of crashing)
• Logging: Implement Python's built-in logging module to track events and errors for debugging
• Type Hints: Add type hints to functions and variables to improve code clarity for future maintenance
• Configuration Management: Move hardcoded values (like supported file types, label sizes) to a dedicated constants section or config file

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