SELECT query result incorrect in Treeview Update

Question

this is quite a complicated question and I will endeavor to provide you with as much data/code you wish. Any help is appreciated.

Context

This is the tkinter frame for which a user can assign a badge to a scout. As you can see it consists of three different ttk.Treeviews . One contains all the badge names found in the badge table of a MySQL table and likewise another contains all the patrol names found in the patrol table in the MySQL database.

Upon selecting a patrol, a list of names appear in the last Treeview. These are the scouts who belong to that Patrol.

Example:

As...

The Problem

When the Return Key is pressed a function is called which should:

Clear the Treeview containing the names of the Scouts
Assign the badge to the scout by creating a new row in the owned badges table
Return the names of all scouts from the selected patrol who have not yet received the badge back to the Treeview.

The overall effect is that when the Scout is selected and the Return Key is pressed, that Scout is removed and the other Scouts stay in the Treeview. Do bare in mind however that It is possible to select more than one Scout

Below Is the function executed upon the pressing of the Return key.

def selectScout():
    global SelectedBadgeStr
    global SelectedPatrolStr
    Values=[]
    if SelectedPatrol.get()==("Selected Patrol: Please Select A Patrol"):
        tk.messagebox.showerror("ERROR","Please select a Patrol")
        return
    if SelectedBadge.get()==("Selected Badge: Please Select A Badge"):
        tk.messagebox.showerror("ERROR", "Please select a Badge")
        return
    print(SelectedBadgeStr)
    curItems = SelectScoutView.selection()
    for i in curItems:
        Values.append(SelectScoutView.item(i)['values'])
    print(Values)
    for i in Values:
        print(SelectedBadgeStr)
        print(i)
        print(i[0])
        print(i[1])
        print(type(i))
        print(type(i[0]))
        Test=("Test")
        print(repr(i[0]))
        GetBadgeID=("SELECT badgeID FROM badges WHERE badgename=%s")
        mycursor.execute(GetBadgeID,(SelectedBadgeStr,))
        BadgeID=mycursor.fetchone()
        print(BadgeID)
        GetScoutID=("SELECT scoutID FROM scoutinfo WHERE firstname=%s AND secondname=%s")
        mycursor.execute(GetScoutID,(i[0],i[1]))
        ScoutID=mycursor.fetchone()
        UpdateOwnedBadgesB="INSERT INTO ownedbadges (badgeID,scoutID) VALUES(%s,%s) "
        mycursor.execute(UpdateOwnedBadgesB,(BadgeID[0],ScoutID[0]))
        mydb.commit()
    #Removing Those Who Now Own The Badge
    ScoutNameList=[]
    Remove = SelectScoutView.get_children()
    for child in Remove:
        SelectScoutView.delete(child)
    print("The Badge:",SelectedBadgeStr,"ID Is",BadgeID[0])
    ScoutIDSWhoOwnBadge=("SELECT scoutID FROM ownedbadges WHERE badgeID=%s")
    mycursor.execute(ScoutIDSWhoOwnBadge,(BadgeID[0],))
    BadgeScoutIDS=mycursor.fetchall()
    print("BadgeScoutIDS")
    print(BadgeScoutIDS)
    for i in BadgeScoutIDS:
        print(i)
        print(i[0])
        print(repr(i[0]))
        ScoutNames="SELECT firstname AND secondname FROM scoutinfo WHERE scoutID=%s"
        mycursor.execute(ScoutNames,(i[0],))
        print("SELECT QUERY RESULTS:")
        print(mycursor.fetchall())
        Name=mycursor.fetchall()
        print("Should Be Names Below:")
        print(Name)
        ScoutNameList.append(Name)
    print(ScoutNameList)
    ScoutNamesByPatrol=("SELECT firstname,secondname FROM scoutinfo WHERE patrolID=(SELECT patrolID FROM patrols WHERE patrolname=%s)")
    mycursor.execute(ScoutNamesByPatrol,(SelectedPatrolStr,))
    ScoutNamedFromPatrol=mycursor.fetchall()
    print(ScoutNamedFromPatrol)
    print("Done")
    return

(I am well aware that this function does not meet the requirement of inserting data back into the Treeview)

The error arises in

Name=mycursor.fetchall()

The error:

mysql.connector.errors.InterfaceError: No result set to fetch from.

This error occurs because the SELECT query:

ScoutNames="SELECT firstname AND secondname FROM scoutinfo WHERE scoutID=%s"
mycursor.execute(ScoutNames,(i[0],))

Yields:

[(0,)]

Instead of the Scout's firstname and Secondname. Even though (i[0],)) = 51 Or Equivalent Scout ID.

Results

I will now take you through a example, so you get a better understanding of the problem.

As you can see the badge "Tracking Badge" has been selected, the patrol "Panther" has been selected and the Scout "Rory Adair" has been selected. We want to give Rory Adair, of the Panther Patrol the Tracking Badge

Expected Results:

The row in the Treeview containing "Rory Adair" is removed, leaving the two other Scouts who have not received the badge in the Treeview.

The ScoutID for "Rory Adair" will be added to the ownedbadges table along with the badgeID.

Actual Results:

All rows in the Treeview are deleted

The ownedbadges table is successfully modified as seen below.

(Ignore the "received" column, as it's purpose has not been defined yet...) (I know their is no code to add the rows/scoutnames bck into the Treeview, however I cannot to this until the problem is fixed)

SELECT query result incorrect in Treeview Update

0 Answers