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.
Someone to help me seeing what's wrong?
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:
i
to index into ath
, but ath
is a single row from the databasei
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 failconnection issue:
my_conn.execute('SELECT `name_grad` FROM `grades`...')
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.