tkinter listbox select and return values from MySQL database

Question

I am fairly new to Python and tkinter and the part I am struggling with now is getting additional values from a MySQL database once I select a business from a listbox.

The listbox will only list the business names, business id or username but when I select on the business, I have some labels that I need to automatically update from the database which do not appear in the listbox.

The labels that need to be updated are: First Name Username Password

The closest I can get is with the code below which updates the 'Username' label with the business name. I have tried so many ways to try and get the relevant value using indexes but can't get it to work.

# Select business from listbox
def select_business(event):
    selection = event.widget.curselection()
    #our_businesses.get(ANCHOR)
    index = selection[0]
    print('event:', event)
    print('widget:', event.widget)
    print('(event)  current:', event.widget.get(event.widget.curselection()))

    username_display_label.config(text=our_businesses.get(our_businesses.curselection()))
    print('---')

# Search businesses
def search_now():
    selected = drop.get()
    sql = ""
    if selected == "Search by...":
        searched_label.config(text="You forgot to pick an option!")
    if selected == "Business Name":
        sql = """SELECT * FROM Retailers WHERE Retailer_Name like ? ORDER BY Retailer_Name ASC"""
        searched_label.config(text="Businesses found:")
    if selected == "Business ID":
        sql = """SELECT * FROM Retailers WHERE Retailer like ? ORDER BY Retailer_Name ASC"""
        searched_label.config(text="Businesses found:")
    if selected == "Username":
        sql = """SELECT * FROM Retailers WHERE Account_ID like ? ORDER BY Retailer_Name ASC"""
        searched_label.config(text="Businesses found:")

    searched = search_box.get()
    #sql = "SELECT TOP 10 Retailer, Retailer_Name, Account_ID, Password FROM Retailers WHERE Retailer_Name = ?"
    name = (f'%{searched}%', )
    global businesses
    businesses = c.execute(sql, name)
    businesses = c.fetchall()

    #Clear the listbox
    our_businesses.delete(0, END)

    if not businesses:
        searched_label.config(text="Business not found")

    else:
        #global business
        for business in businesses:
            totals = len(businesses)
            #print(totals)
            our_businesses.insert(END, str(business[1]))
            searched_label.config(text="Businesses found: " + str(totals))

Any help would be much appreciated!

we can't run it - so maybe use print() to see which part is executed and what you have in variables. It is called "print debuging". — furas
– furas, Commented Feb 24, 2022 at 23:14
as for me in select_business() you should run SQL code to get data from database - but you don't do this. Or if you have all information in businesses then you should get from businesses - maybe businesses[index] — furas
– furas, Commented Feb 24, 2022 at 23:18

furas · Accepted Answer · 2022-02-24 23:28:01Z

1

I can't run it so I only guess.

It seems you have all information in global variable businesses so you could use it to get row with data

Something like this:

def select_business(event):
    selection = event.widget.curselection()
    index = selection[0]

    person = businesses[index]   # row from database

    first_name_display_label.config(text=peron[0])
    username_display_label.config(text=peron[1])
    password_display_label.config(text=peron[2])

answered Feb 24, 2022 at 23:28

furas

149k12 gold badges121 silver badges171 bronze badges

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

5 Comments

Calogero Savarino Over a year ago

thanks for your comments and for the above answer. I have been using 'print' to debug and I think I did it on the global 'business' variable and it just looped and printed: None, None None.... on separate lines. I will have another play around with this global variable and come back to you!

furas Over a year ago

maybe you could create minimal working code (with some data) so we people could copy and run it to search problem.

Calogero Savarino Over a year ago

after your comments and fixing the way I was using the indexes, it is now working as it should. Thanks so much for your help!

furas Over a year ago

if you want you may describe your fixes as answer and you may mark your answer as accepted, and few minutes later you can upvote your answer.

Calogero Savarino Over a year ago

added the fixes as a comment and accepted it as the answer. Unfortunately I am unable to upvote your answer or mine as I haven't got enough reputation points.

Calogero Savarino · Accepted Answer · 2022-03-01 17:03:29Z

0

The issue was within the 'select business' function. As I had the 'businesses' as a global variable, I could just get the row I needed with indexing.

However, you need to make sure your indexing is correct as I found that I was mis-counting and the selected indexes I used were blank in the database!

Here is the correct function:

def select_business(event):
    selection = event.widget.curselection()
    index = selection[0]
    retailer = businesses[index]
    businessname_display_label.config(text=retailer[1])
    firstname_display_label.config(text=retailer[3])
    username_display_label.config(text=retailer[33])
    password_display_label.config(text=retailer[34])
    email_display_label.config(text=retailer[13])

answered Mar 1, 2022 at 17:03

Calogero Savarino

376 bronze badges

1 Comment

acw1668 Over a year ago

Actually the logic is the same as the answer from @furas, why don't you accept his answer instead of posting duplicate answer?

Collectives™ on Stack Overflow

tkinter listbox select and return values from MySQL database

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related