Python tkinter treeview and MySQL queries

Question

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?

Answer 1

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.