Search code examples
pythonmysqltkintertreeviewprepared-statement

Python tkinter treeview and MySQL queries


How to populate a treeview with results from many tables?

I've a set of tables: athletes, grades, categories, rel_ath_grad_cath.

athletes grades categories rel_ath_grad_cath
id_ath id_grad id_cat id_ath
name_ath name_grad name_cat id_grad
id_cat

I want my treeview to show: id_ath | name_ath | name_grad | name_cat

When I work on a single table, there's no drama

def afficher_donnees():
    my_conn.execute('SELECT * FROM `athletes`')
    rqsa_aths = my_conn.fetchall()
    for ath in rqsa_aths:
        trv.insert('', 'end', values=(ath[0], ath['1'], ath[2]))

It gives me all of my athletes table (id, firstname, and lastname).

But when I want to take all the rest there's my nightmare

The code I tried:

def afficher_donnees():
    # Select all athletes
    my_conn.execute('SELECT * FROM `athletes`')
    rqsa_aths = my_conn.fetchall()
    
    # nb_athl
    nb_athl = len(rqsa_aths)

    i = 0
    data_agt = []

    # I want to take the grad corresponding to the ath
    for ath in rqsa_aths:
        while nb_athl+1 > i:
            # For the i ath i take the id
            id_ath = ath[i]
            name = ath[1]
            # Then take the grad which corresponding to the id
            my_conn.execute('SELECT `name_grad` FROM `grades`
                           JOIN rel_ath_grad_cath RAGC
                           ON RAGC.id_grad = grades.id_grad 
                           JOIN athletes ATH
                           ON RAGC.id_ath = ATH.id_ath
                           WHERE ATH.id_ath = %s' %id_ath)                           
            grad_ath = my_conn.fetchone()
            
            trv.insert('', 'end', values=(ath[0], ath['1'], grad_ath[0]))
            i = i+1

But that is not working because nothing appears on the screen and no mistakes are shown in the console.

NOTHING APPEARS

Someone to help me seeing what's wrong?


Solution

  • Looking at your code to me it seems to be a couple of issues:

    main issue:

    for ath in rqsa_aths:
        while nb_athl+1 > i:
            id_ath = ath[i]  # This is the key problem
        
    

    Here's what's going wrong:

    1. You're using i to index into ath, but ath is a single row from the database
    2. When i increases, you're trying to access indexes that don't exist in ath. For example, if ath is [1, "John", "Smith"], trying to access ath[3] will fail

    connection issue:

    my_conn.execute('SELECT `name_grad` FROM `grades`...')
    
    1. Each new query overwrites the previous result set
    2. You might be getting cursor errors because you're reusing the same cursor without fetching all results

    and also the counter i will keep increasing until it reaches nb_athl, but you're still trying to use the same ath record.

    With that said I would change the afficher_donnees as following:

    def afficher_donnees():
        # clear existing items
        for item in trv.get_children():
            trv.delete(item)
            
        # single query to get all data
        my_conn.execute("""
            SELECT 
                a.id_ath,
                a.name_ath,
                g.name_grad,
                c.name_cat
            FROM athletes a
            LEFT JOIN rel_ath_grad_cath r ON a.id_ath = r.id_ath
            LEFT JOIN grades g ON r.id_grad = g.id_grad
            LEFT JOIN categories c ON r.id_cat = c.id_cat
            ORDER BY a.id_ath
        """)
        
        results = my_conn.fetchall()
        
        # insert into treeview
        for row in results:
            trv.insert('', 'end', values=(
                row[0],         # id_ath
                row[1],         # name_ath
                row[2] or 'N/A',  # name_grad (handle NULL)
                row[3] or 'N/A'   # name_cat (handle NULL)
            ))
    

    here we are using a single JOIN query instead of multiple queries in a loop, along with handling NULL values and clearing the existing treeview data.