tkinter listbox select and return values from MySQL database-CodePudding

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!

CodePudding user response：

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])

CodePudding user response：

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])