Tkinter多选框取值与MySQL批量插入问题求助

问题1：MySQL批量插入重复最后一条记录 #

问题原因 #

循环生成warframe_part_list对应控件时，所有StringVar（如clicked_relic）和Entry（如relic_no_entry）都是全局变量，每次循环会覆盖之前的变量引用。提交时只能获取最后一组控件的值，导致data_list中非part字段全为最后一条记录的内容。

解决方案 #

用字典存储每个部件对应的控件实例，提交时按部件匹配对应值：

修改控件生成代码

warframe_part_list = [
        'Blueprint',
        'Neuroptics',
        'Chassis',
        'Systems'
    ]

# 新增：存储每个部件的控件实例
part_widgets = {}

current_row = 5
for part in warframe_part_list:
    part_label = Label(add_relics_window, text=part, font=heading_font, bg=bg_color, fg=font_color)
    part_label.grid(row=current_row, column=0)

    # 为每个部件创建独立的控件集合
    widget_set = {}
    
    clicked_relic = StringVar()
    clicked_relic.set("Select Option")
    relic_menu = OptionMenu(add_relics_window, clicked_relic, *relic_list)
    relic_menu.config(bg=btn_color, fg=font_color, font=text_font)
    relic_menu.grid(row=current_row, column=1, padx=(15, 0))
    widget_set['relic_var'] = clicked_relic

    relic_no_entry = Entry(add_relics_window, width=5, justify=CENTER, font=text_font, bg=label_color, fg=label_font_color)
    relic_no_entry.grid(row=current_row, column=2)
    widget_set['relic_no_entry'] = relic_no_entry

    clicked_rarity = StringVar()
    clicked_rarity.set("Select Option")
    rarity_menu = OptionMenu(add_relics_window, clicked_rarity, *rarity_list)
    rarity_menu.config(bg=btn_color, fg=font_color, font=text_font)
    rarity_menu.grid(row=current_row, column=3)
    widget_set['rarity_var'] = clicked_rarity

    quantity_entry = Entry(add_relics_window, width=5, validate='key', validatecommand=(vcmd, '%P'), justify=CENTER, font=text_font, bg=label_color, fg=label_font_color)
    quantity_entry.grid(row=current_row, column=4)
    widget_set['quantity_entry'] = quantity_entry

    clicked_mission = StringVar()
    clicked_mission.set("Select Option")
    mission_menu = OptionMenu(add_relics_window, clicked_mission, *mission_list)
    mission_menu.config(bg=btn_color, fg=font_color, font=text_font)
    mission_menu.grid(row=current_row, column=5)
    widget_set['mission_var'] = clicked_mission

    clicked_planet = StringVar()
    clicked_planet.set("Select Option")
    planet_menu = OptionMenu(add_relics_window, clicked_planet, *planet_list)
    planet_menu.config(bg=btn_color, fg=font_color, font=text_font)
    planet_menu.grid(row=current_row, column=6, padx=(15, 0))
    widget_set['planet_var'] = clicked_planet

    location_entry = Entry(add_relics_window, width=25, justify=CENTER, font=text_font, bg=label_color, fg=label_font_color)
    location_entry.grid(row=current_row, column=7, padx=15)
    widget_set['location_entry'] = location_entry

    clicked_rotation = StringVar()
    clicked_rotation.set("Select Option")
    rotation_menu = OptionMenu(add_relics_window, clicked_rotation, *rotation_list)
    rotation_menu.config(bg=btn_color, fg=font_color, font=text_font)
    rotation_menu.grid(row=current_row, column=8)
    widget_set['rotation_var'] = clicked_rotation

    drop_chance_entry = Entry(add_relics_window, width=10, justify=CENTER, font=text_font, bg=label_color, fg=label_font_color)
    drop_chance_entry.grid(row=current_row, column=9)
    widget_set['drop_chance_entry'] = drop_chance_entry

    clicked_obtained = StringVar()
    clicked_obtained.set("Select Option")
    obtained_menu = OptionMenu(add_relics_window, clicked_obtained, *answer_list)
    obtained_menu.config(bg=btn_color, fg=font_color, font=text_font)
    obtained_menu.grid(row=current_row, column=10)
    widget_set['obtained_var'] = clicked_obtained

    # 将当前部件的控件集合存入总字典
    part_widgets[part] = widget_set

    current_row += 1

修改submit函数

def submit():
    data_list = []
    prime_val = prime_entry.get()
    for part in warframe_part_list:
        widgets = part_widgets[part]
        # 从对应部件的控件中获取值
        record = (
            prime_val,
            part,
            widgets['relic_var'].get(),
            widgets['relic_no_entry'].get(),
            widgets['rarity_var'].get(),
            widgets['quantity_entry'].get(),
            widgets['mission_var'].get(),
            widgets['planet_var'].get(),
            widgets['location_entry'].get(),
            widgets['rotation_var'].get(),
            widgets['drop_chance_entry'].get(),
            widgets['obtained_var'].get()
        )
        data_list.append(record)

    connections = mysql.connector.connect(user=user, password=password, host=host, database=database)
    cursors = connections.cursor()
    sql = "INSERT INTO relics (prime, part, relic, relic_no, rarity, quantity, drop_mission, drop_planet, drop_location, drop_rotation, drop_chance, obtained) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    cursors.executemany(sql, data_list)
    connections.commit()
    connections.close()

问题2：简化多列表批量插入代码 #

解决方案 #

将同伴类型与对应列表映射为字典，循环生成所有插入数据后批量提交：

# $$$$  Add All Companions  $$$$$
companion_map = {
    'Sentinels': ['Carrier', 'Dethcube', 'Diriga', 'Djinn', 'Helios', 'Nautilus', 'Oxylus', 'Shade', 'Taxon', 'Wyrm'],
    'MOA': ['Lambeo', 'Nychus', 'Oloro', 'Para'],
    'Hound': ['Bhaira', 'Dorma', 'Hec'],
    'Kubrow': ['Chesa', 'Charger', 'Huras', 'Raksa', 'Sahasa', 'Sunika'],
    'Kavat': ['Adarza', 'Smeeta', 'Vasca'],
    'Predasite': ['Medjay', 'Pharaoh', 'Vizier'],
    'Vulpaphyla': ['Crescent', 'Panzer', 'Sly']
}

connections = mysql.connector.connect(user=user, password=password, host=host, database=database)
cursors = connections.cursor()
cursors.execute('SELECT id FROM companions_list')
records = cursors.fetchone()

if records is None:
    sql = "INSERT INTO companions_list (companion, type, my_rank, reactor, forma, status) VALUES (%s, %s, %s, %s, %s, %s)"
    data_list = []
    # 循环生成所有插入数据
    for comp_type, companions in companion_map.items():
        for comp in companions:
            data_list.append((comp, comp_type, 0, 'No', 0, "None"))
    # 批量插入
    cursors.executemany(sql, data_list)
    connections.commit()

connections.close()

优化说明 #

• 用字典统一管理同伴类型与列表，结构清晰易维护
• 一次性生成所有数据后调用executemany，减少数据库交互次数，提升效率
• 移除重复的SQL语句和循环块，大幅精简代码

内容的提问来源于stack exchange，提问作者Dirk le Roux