Python tkinter treeview and MySQL queries [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 12 months ago.

Improve this 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?

"Not working" is not a proper error description as we do not know what does not work and what the exact underlying error is. We can run your code either as we do not know your table structure, nor your data. Please, provide exact error message or more detailed description of unexpected behaviour! — Shadow
– Shadow, Commented Dec 1, 2024 at 7:33
Thank you Shadow but there's no errors shown and nothin appears on the screen — CodeOrion
– CodeOrion, Commented Dec 1, 2024 at 8:04
In this case I'm afraid we will not be able to help. You need to do additional debugging, such as checking for any sql errors. — Shadow
– Shadow, Commented Dec 1, 2024 at 8:42

Edoardo Balducci · Accepted Answer · 2024-12-01 09:44:25Z

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:

You're using i to index into ath, but ath is a single row from the database
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`...')

Each new query overwrites the previous result set
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.

answered Dec 1, 2024 at 9:44

Edoardo Balducci

4573 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodeOrion Dec 2, 2024 at 1:27

Thank you very much sir Edoardo Balducci and many thank's to all of you . But for now I've these errors: line 52, in <module> trv.column(4, anchor="center", stretch=False, width=120) _tkinter.TclError: Column index 4 out of bounds My line 52 trv.column(4, anchor="center", stretch=False, width=120)

Collectives™ on Stack Overflow

Python tkinter treeview and MySQL queries [closed]

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related