Extract a database field via 2 combined comboboxes. Problem in the function for extracting-CodePudding

By selecting two comboboxes (combined with bind), I would like to extract the ID of a field from Table1 and insert it into Table2. I have no problems extracting a field based on a combobox, but I have problems extracting a field based on 2 comboboxes, because they are combined with each other. The problem is only def id_rounds() function.

I have two comboboxes: one in which I select the name of the "Tournament" and another in which I select the number of Rounds (from 1 to 38 different rounds for each tournament). To choose which tournament the tournament ID must match, I use the combobox combo_Tournaments and function def combo_tournaments; while to choose the number of the Round I use the combobox combo_Rounds and the combo_rounds function. By selecting the Tournament and / or Round, the relevant ID is also automatically entered (as well as the actual data). So each combobox puts in 2 things each, for a total of 4.

Here is the database:

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),
    );

PROBLEM: Currently as I wrote the code of the function def id_rounds(), the ID of the selected Round is saved, but without exact correspondence to the chosen Tournament in the Tournament combobox. The problem is that each Tournament is each made up of 38 different Rounds, therefore in the All_Round table the numbers from 1 to 38 are repeated several times, each corresponding to the Tournament ID. For example Serie A from 1 to 38 Round; Serie B from 1 to 38 rounds; Premier League from 1 to 38 rounds. So I would like to enter the ID of the single Round corresponding to the Tournament (in relation to the tournament), because each tournament has 1 to 38 rounds, so there are many different "1 to 38 rounds" for each tournament.

#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

WHAT DO I WANT TO GET? So I would like to obtain for example that: if from the Tournament combobox I select Serie A and then Round 1, in the Results table the ID of Round 1 should be entered but corresponding to Serie A. Or, another example, if from the Tournament combobox I select Serie B and then Round 1, the ID of Round 1 should be entered in the Results table but corresponding to Serie B.

QUESTION: How can I fix the function def id_rounds and which inserts the number of the Round in correspondence (in relation) to the tournament? Currently I only enter the ID of the selected Round in the combobox without matching the championship chosen in the tournament combobox.

CodePudding user response：

Below is the modified code based on my understanding:

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)
...

Note that I have used the campionato and rounds (StringVar) to get the selected tournament and Number_Round.