通过2个组合框提取数据库字段。提取函数中的问题

Question

通过选择两个组合框（结合bind），我想从表1中提取字段的ID并将其插入表2中。基于组合框提取字段没有问题，但是基于两个组合框提取字段有问题，因为它们是相互组合的。问题只是def id_rounds（）函数

我有两个组合框：一个是我选择“锦标赛”的名称，另一个是我选择轮数（每个锦标赛1到38轮）。要选择锦标赛ID必须匹配的锦标赛，我使用combobox combo_Tournaments和函数def combo_Tournaments；在选择轮数时，我使用combobox combo_Rounds和combo_Rounds函数。通过选择锦标赛和/或回合，也会自动输入相关ID（以及实际数据）。因此，每个组合框分别放入2个内容，总共4个

以下是数据库：

CREATE TABLE "All_Tournament" (
    "ID_Tournament" INTEGER,
    "Tournament" TEXT,
    PRIMARY KEY("Tournament" AUTOINCREMENT)
);

    CREATE TABLE "All_Round" (
        "ID_Round"  INTEGER,
        "Number_Round"  INTEGER,
        "ID_Tournament" INTEGER,
        PRIMARY KEY("ID_Round" AUTOINCREMENT),
    );

问题：目前，在我编写函数def id_rounds（）的代码时，所选回合的id已保存，但与锦标赛组合框中所选的锦标赛没有精确的对应关系。问题是，每场比赛都由38个不同的回合组成，因此在All_圆桌会议中，1到38的数字重复了几次，每个数字对应于比赛ID。例如，意甲1到38轮；乙级1-38轮；英超联赛从1轮到38轮。所以我想输入对应于锦标赛的单轮ID（相对于锦标赛），因为每个锦标赛有1到38轮，所以每个锦标赛有许多不同的“1到38轮”

#Combobox Tournament
lbl_Tournament = Label(root, text="Tournament", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Tournament.place(x=6, y=60)
combo_Tournaments = ttk.Combobox(root, font=("Calibri", 11), width=30, textvariable=campionato, state="readonly")
combo_Tournaments.place(x=180, y=60)
combo_Tournaments.set("Select")
combo_Tournaments['values'] = combo_tournaments()
combo_Tournaments.bind('<<ComboboxSelected>>', combo_teams)

lbl_Rounds = Label(root, text="Rounds", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Rounds.place(x=600, y=60)
combo_Rounds = ttk.Combobox(root, font=("Calibri", 11), width=30, textvariable=rounds,  state="readonly") 
combo_Rounds.place(x=680, y=60)
combo_Rounds.set("Select")
combo_Rounds['values'] = combo_campionati()
combo_Tournaments.bind('<<ComboboxSelected>>', combo_rounds, add=True)


def combo_tournaments():
    tournaments = combo_tournaments.get()
    cursor.execute('SELECT Tournament FROM All_Tournament')
    result=[row[0] for row in cursor]
    return result

def id_tournaments():
    tournaments = combo_tournaments.get()
    cursor.execute('SELECT ID_Tournament FROM All_Tournament WHERE Tournament=?',(tournaments,))
    result=[row[0] for row in cursor]
    return result[0]

def combo_rounds(event=None):
    rounds = combo_rounds.get()        
    cursor.execute('SELECT Number_Round From All_Round WHERE ID_Tournament')
    result=[row[0] for row in cursor]
    combo_Rounds['value'] = result
    return result

#THE PROBLEM IS HERE 
def id_rounds():
    rounds = combo_rounds.get()    
    cursor.execute('SELECT ID_Round FROM All_Round WHERE Number_Round=? AND Tournament=?',(rounds, tournaments))
    result=[row[0] for row in cursor]
    return result[0]

def combo_teams(event=None):
    tournaments = combo_tournaments.get()
    cursor.execute('SELECT s.Name_Teams FROM All_Teams s, All_Tournament c WHERE s.ID_Tournament=c.ID_Tournament AND c.Tournament = ?', (tournaments,))
    result=[row[0] for row in cursor]
    combo_Teams_1['values'] = result
    combo_Teams_2['values'] = result
    return result

我想得到什么？因此我想得到一个例子：如果我从锦标赛组合框中选择了意甲，然后是第1轮，那么在结果表中应该输入第1轮的ID，但与意甲相对应。或者，另一个例子，如果我从锦标赛组合框中选择意乙，然后是第1轮，第一轮的ID应输入结果表中，但与乙级相对应

问题：我如何修复def id_rounds函数，以及该函数插入与锦标赛相对应的回合数？目前，我只在组合框中输入所选回合的ID，而不匹配在锦标赛组合框中选择的锦标赛

Answer 1

以下是根据我的理解修改的代码：

def combo_tournaments():
    cursor.execute('SELECT Tournament FROM All_Tournament')
    result=[row[0] for row in cursor]
    return result

def combo_rounds(event=None):
    # get all Number_Round for selected tournament
    cursor.execute('''
        SELECT Number_Round From All_Round r, All_Tournament t
        WHERE r.ID_Tournament = t.ID_Tournament AND Tournament = ?''', (campionato.get(),))
    result=[row[0] for row in cursor]
    combo_Rounds['value'] = result  # update combo_Rounds
    rounds.set('Select') # reset Rounds selection
    return result

def id_rounds(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    cursor.execute('''
        SELECT ID_Round FROM All_Round r, All_Tournament t
        WHERE r.ID_Tournament = t.ID_Tournament AND Number_Round = ? AND Tournament = ?''',
        (rounds.get(), campionato.get()))
    result = cursor.fetchone()
    if result:
        print(result[0])
        return result[0]
    return None

...

campionato = StringVar()
rounds = StringVar()

#Combobox Tournament
lbl_Tournament = Label(root, text="Tournament", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Tournament.place(x=6, y=60)
combo_Tournaments = ttk.Combobox(root, font=("Calibri", 11), width=30, textvariable=campionato, state="readonly")
combo_Tournaments.place(x=180, y=60)
combo_Tournaments.set("Select")
combo_Tournaments['values'] = combo_tournaments()
combo_Tournaments.bind('<<ComboboxSelected>>', combo_rounds)

lbl_Rounds = Label(root, text="Rounds", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Rounds.place(x=600, y=60)
combo_Rounds = ttk.Combobox(root, font=("Calibri", 11), width=30, textvariable=rounds,  state="readonly")
combo_Rounds.place(x=680, y=60)
combo_Rounds.set("Select")
combo_Rounds.bind('<<ComboboxSelected>>', id_rounds)
...

请注意，我使用了campionato和rounds（StringVar）来获取所选的锦标赛和Number_Round