How to refresh MySQL data in Treeview Tkinter?

Question

Code

I have this function:

#Refresh MySQL data to Treeview
    def refresh(self):
        self.table.delete(*self.table.get_children())

        cursor = mydb.cursor()
        cursor.execute("select * from requested order by done")
        for row in cursor:
            self.table.insert('','end', values = (row[8], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[9], row[10], row[12]))

If I insert a record or update one - using workbench. When I press refresh in Tkinter, it does not show any new or amended data. Just stays as it is.

But - If I quit Tkinter app - re-open it - click refresh it will show new amended data.

If the tkinter app is running - If I try to run the following query: truncate table using workbench - MySQL will not complete the query the action until I close the tkinter app

What it should do

When I activate the function refresh - it should remove all the current data in treeview and update it with existing values within MySQL.

Quesiton

How can I achieve this?

Have you verfied your `.execute(...` yields new or even any data? — stovfl, Dec 11 '19 at 16:18
@stovfl not sure what you mean. But here is an additional information - If the tkinter app is running - If I try to `truncate` from workbench - it will not do anything until I close the tkinter app — Eduards, Dec 11 '19 at 16:20
First step, add `print('in refresh')` inside `def refresh(...)` to verify it get called at ***"I press refresh"***. — stovfl, Dec 11 '19 at 16:23
@stovfl yes, it is printing every-time I click the button - do you need the full code? — Eduards, Dec 11 '19 at 16:25
This ***"it is printing every-time I click the button"*** is contradict to ***"it will not do anything until I close the tkinter app"***. Second step, add `print('row[8]')` inside `for row in ...` to verify you get what you think from `mysql` — stovfl, Dec 11 '19 at 16:29
@stovfl My bad wording - What I meant was... ***If I try to run the following query: truncate table using workbench - MySQL will not complete the query the action until I close the tkinter app*** — Eduards, Dec 11 '19 at 16:32
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204071/discussion-between-stovfl-and-lv98). — stovfl, Dec 11 '19 at 16:34
You probably have an issue with [MySQL transaction isolation level](https://mariadb.com/kb/en/library/set-transaction/#isolation-levels) — bruno desthuilliers, Dec 11 '19 at 16:59

score 1 · Accepted Answer · answered Dec 12 '19 at 09:41

1

From @brunodesthuilliers link in comments MySQL transaction isolation level has helped me find what was wrong!

By running this query in MySQL Workbench:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Has allowed me to update records from MySQL and run this function below - without a problem!

#Refresh MySQL data to Treeview
    def refresh(self):
        self.table.delete(*self.table.get_children())

        cursor = mydb.cursor()
        cursor.execute("select * from requested order by done")
        for row in cursor:
            self.table.insert('','end', values = (row[8], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[9], row[10], row[12]))

answered Dec 12 '19 at 09:41

Eduards

1,734
2
12
37

You probably want to set the transaction isolation level in your own code (cf [this post](https://stackoverflow.com/questions/5677379/how-do-you-change-the-sql-isolation-level-from-python-using-mysqldb)) when opening the db connection, so you're sure you always get it right. – bruno desthuilliers Dec 12 '19 at 10:12
That would make sense! – Eduards Dec 12 '19 at 10:23

How to refresh MySQL data in Treeview Tkinter?

1 Answers1