How to count and display multiple rows of different column on TKINTER Treeview GUI?

Question

My program needs to count and display the number of available assets from the inventory on Tkinter Treeview. The way I did to print the equipment type column on the GUI(last image) was using the SELECT from the EQUIPMENT TYPE query which lists the 11 rows from Equipment type table shown on the GUI. Subsequently, I need to print the total, unavailable, and available items for each equipment type. For example, if there's a total of 10 INTEGRATED PANEL, 3 are available, 7 are unavailable. What is the most easiest and efficient way to accomplish this? The code fragment below is used to insert the Equipment types into the GUI.It was easier to list the equipment types because it was just one column of from the table. For the availability, I'm couldn't imagine an easier way to do it. Thanks.

command = "SELECT `Equipment Type` FROM `Equipment Types`"
mycursor.execute(command)
results = mycursor.fetchall()

for i in range(len(results)):
  results[i] = list(results[i])

  tree.insert("", "end", str(results[i][0]), text = str(results[i][0]), tag = [[i][0]], open = TRUE)

Equipment type

Inventory

GUI output

Treeview fragment

edited treeview

new GUI output

refresh

def refresh_clicked(self):
        self.destroy()
        self.__init__()
        
        button_refresh = tk.Button(topframe, text = "Refresh", state = NORMAL, command = self.timed_refresh)
        button_refresh.grid(row = 1, column = 2, sticky = W, padx = 5, pady = 2)

Your question requires more focus and detail. The question has less to do with treeviews and more to do with getting the data from the database. Much of what you are trying to do could likely be done with an SQL Query but since we have no details of the tables and their data we can't help with that. Investigate the COUNT() function for SQL. — scotty3785
– scotty3785, Commented Jan 4, 2021 at 16:08

acw1668 · Accepted Answer · 2021-01-04 16:27:51Z

1

You can use SQL to get what you want:

command = """
SELECT
  et.`Equipment Type`,
  COUNT(i.`Equipment Type`) Total,
  SUM(IF(`Status`="Unavailable",1,0)) Unavailable,
  SUM(IF(`Status`="Available",1,0)) Available
FROM `Equipment Types` et
LEFT JOIN Inventory i ON et.`Equipment Type` = i.`Equipment Type`
GROUP BY 1
ORDER BY 1
"""

mycursor.execute(command)

for row in mycursor:
    tree.insert("", "end", values=row)

answered Jan 4, 2021 at 16:27

acw1668

48k5 gold badges30 silver badges39 bronze badges

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

8 Comments

Delrius Euphoria Over a year ago

for row in mycursor: ? Shouldnt it be for row in mycursor.fetchall(): ?

acw1668 Over a year ago

@CoolCloud mycursor can be iterated in this way.

Daniel Over a year ago

@acw1668 I have edited my post to include the treeview fragment as the last Image. Could you identify what's missing?

acw1668 Over a year ago

Column #0 always refers to the tree column. So you should not use it as the index to Equipment Type column.

Daniel Over a year ago

@acw1668 Thanks. I made some changes to it and added the output Image in the description above. Not sure if this is what you meant, but the output looks good.

|

scotty3785 · Accepted Answer · 2021-01-04 16:28:14Z

0

Given the lack of details about your SQL tables, below is a "best guess" at a solution to get the number of available, unavailable assets of each type from the table.

SELECT 
 Inventory.'Equipment Type',
 COUNT(Inventory.'Equipment Type') AS 'Total',
 COUNT(case Inventory.Available when 'Available' then 1 else null end) AS 'Available',
 COUNT(case Inventory.Available when 'Unavailable' then 1 else null end) AS 'Unavailable' 
FROM 
 Inventory
GROUP BY 
 Inventory.'Equipment Type';

With my small fake data set this returns

Equipment Type |  Total | Available | Unavailable
     A         |    2   |     1     |   1
     B         |    2   |     2     |   0

answered Jan 4, 2021 at 16:28

scotty3785

7,0711 gold badge29 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to count and display multiple rows of different column on TKINTER Treeview GUI?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related