Tkinter with sql list for combobox

Python Help
1

Need a bit of help with my code, only been working with python for just over a week at this point.
I am trying to get a list of discount codes from my discount table, and put it into the combobox for the drop down list.

As of this moment it’s pulling the information all together.
If I run the code in it’s own python there is no issues. I’m sure I shouldn’t be setting it up as a def.

Here is the code I currently have setup for it.

def listdiscounts():
is the code to pull the sql data.

discount_combobox = ttk.Combobox(root, values=listdiscounts)
is the code to put it into the Combobox

Any input is welcome or a better way of going about it is more then welcome as well.

from tkinter import *
from PIL import Image,ImageTk
import tkinter as tk
from tkinter import ttk
import mysql.connector
import connection
import tkinter.messagebox

root = Tk()
root.title('Customers')
root.geometry("800x700")

# Connection to DB...  connection.py
cur, conn = connection.get_connection()

cur.execute("CREATE TABLE IF NOT EXISTS customers (f_name VARCHAR(255), \
	l_name VARCHAR(255), \
    address_1 VARCHAR(255),\
	address_2 VARCHAR(255),\
    city VARCHAR(50),\
    providence VARCHAR(2),\
	postal VARCHAR(6), \
    phone VARCHAR(255),\
    email VARCHAR(255),\
	payment_method VARCHAR(50),\
    discount_code VARCHAR(255), \
	user_id INT AUTO_INCREMENT PRIMARY KEY)")


# Pull list of discount codes
def listdiscounts():
	cur.execute("SELECT * FROM discount")
	discount = cur.fetchall()
	for discounts in discount:
		code_name = discounts[0]
		code_id = discounts[2]
		discount_combobox.insert(tk.END, f"{code_name}: {code_id}")
		discount_combobox.pack()

# Clear fields command 
def clear_fields():
	f_name_box.delete(0, END)
	l_name_box.delete(0, END)
	address1_box.delete(0, END)
	address2_box.delete(0, END)
	city_box.delete(0, END)
	providence_box.delete(0, END)
	postal_box.delete(0, END)
	phone_box.delete(0, END)
	email_box.delete(0, END)
	payment_method_box.delete(0, END)
	discount_combobox.delete(0, END)

# Submit Customer To Database
def add_customer():
	sql_command = "INSERT INTO customers (f_name, l_name, postal, email, address_1, address_2, city, providence, phone, payment_method, discount_code) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
	values = (f_name_box.get(), l_name_box.get(), postal_box.get(), email_box.get(), address1_box.get(), address2_box.get(), city_box.get(), providence_box.get(), phone_box.get(), payment_method_box.get(), discount_combobox.get())
	cur.execute(sql_command, values)

	# Commit the changes to the database
	conn.commit()
	# Clear the fields
	clear_fields()

#Create Main Form To Enter Customer Data
f_name_label = Label(root, text="First Name:").grid(row=1, column=0, sticky=W, padx=10)
l_name_label = Label(root, text="Last Name:").grid(row=2, column=0, sticky=W, padx=10)
address1_label = Label(root, text="Address 1:").grid(row=3, column=0, sticky=W, padx=10)
address2_label = Label(root, text="Address 2:").grid(row=4, column=0, sticky=W, padx=10)
city_label = Label(root, text="City:").grid(row=5, column=0, sticky=W, padx=10)
providence_label = Label(root, text="Providence:").grid(row=6, column=0, sticky=W, padx=10)
postal_label = Label(root, text="Postal code:").grid(row=7, column=0, sticky=W, padx=10)
phone_label = Label(root, text="Phone Number:").grid(row=8, column=0, sticky=W, padx=10)
email_label = Label(root, text="Email Address:").grid(row=9, column=0, sticky=W, padx=10)
payment_method_label = Label(root, text="Payment Method:").grid(row=10, column=0, sticky=W, padx=10)
discount_code_label = Label(root, text="Discount Code:").grid(row=11, column=0, sticky=W, padx=10)

# Create Entry Boxes
f_name_box = Entry(root)
f_name_box.grid(row=1, column=1)
l_name_box = Entry(root)
l_name_box.grid(row=2, column=1, pady=5)
address1_box = Entry(root)
address1_box.grid(row=3, column=1, pady=5)
address2_box = Entry(root)
address2_box.grid(row=4, column=1, pady=5)
city_box = Entry(root)
city_box.grid(row=5, column=1, pady=5)
providence_box = Entry(root)
providence_box.grid(row=6, column=1, pady=5)
postal_box = Entry(root)
postal_box.grid(row=7, column=1, pady=5)
phone_box = Entry(root)
phone_box.grid(row=8, column=1, pady=5)
email_box = Entry(root)
email_box.grid(row=9, column=1, pady=5)
payment_method_box = Entry(root)
payment_method_box.grid(row=10, column=1, pady=5)
#discount_code_box = Entry(root)
#discount_code_box.grid(row=11, column=1, pady=5)
discount_combobox = ttk.Combobox(root, values=listdiscounts)
discount_combobox.grid(row=11, column=1, pady=5)

# Create Buttons
add_customer_button = Button(root, text="Add Customer To Database", command=add_customer)
add_customer_button.grid(row=14, column=0, padx=10, pady=10)
clear_fields_button = Button(root, text="Clear Fields", command=clear_fields)
clear_fields_button.grid(row=14, column=1)

root.mainloop()
2

Hi,

for these three imports, condense it to (the first one is generally not used because of potential name clashes with current script variables … the tk qualifier prefix avoids this):

import tkinter as tk
import tkinter.ttk as ttk
1 Like
3

You have lines such as:

f_name_label = Label(root, text="First Name:").grid(row=1, column=0, sticky=W, padx=10)

This creates a label and puts it on the root widget, but grid returns None, so f_name_label will None.

You should do what you did for f_name_box:

f_name_box = Entry(root)
f_name_box.grid(row=1, column=1)

However, as you never refer to f_name_label, you might as well just omit the assignment:

Label(root, text="First Name:").grid(row=1, column=0, sticky=W, padx=10)

In listdiscounts you have:

	for discounts in discount:
		code_name = discounts[0]
		code_id = discounts[2]
		discount_combobox.insert(tk.END, f"{code_name}: {code_id}")
		discount_combobox.pack()

The insert will insert into the entry box of the combo box.

Are you trying to add items to the dropdown list?

If so, you can get the existing list of values and then add more onto the end. Note that it’ll actually return them as tuple, not as a list:

discount_combobox['values'] = list(discount_combobox['values']) + ['New item']

or:

discount_combobox['values'] = discount_combobox['values'] + ('New item',)

Of course, if you want to add multiple items, it’ll be quicker if you add them all in one go.

Also, pack is for putting the widget onto the parent widget, so what you’re doing is adding it again multiple times even though it has already been put onto the parent widget by the grid method.

4

Thank you for the reply.
Still having issues with it. However after spending a few hours again reading up on combobox I’m not finding the solution I need with it.
Due to how I’ve setup the MySQL DB which is the following,
Customers DB setup as main DB in that table have “discount_code” as an INT that reflects the “code_id” in discounts table as a primary key. I have setup a foreign key in customers to reflect this with the same ID from discount table.
I have done this for when I query the table it will pull the name of the discount reflected based on the ID number.

I figured I would go the easy route with combobox and just make a list inside the code to select. However I haven’t found a way to reflect the ID number instead of the name when it gets inserted into the DB.
What I’m thinking might work is something like this

# Submit Customer To Database
def add_customer():
	sql_command = "INSERT INTO customers (f_name, l_name, postal, email, address_1, address_2, city, providence, phone, payment_method, discount_code) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
	values = (f_name_box.get(), l_name_box.get(), postal_box.get(), email_box.get(), address1_box.get(), address2_box.get(), city_box.get(), providence_box.get(), phone_box.get(), payment_method_box.get(), discount_combobox.get())
	cur.execute(sql_command, values)

	# Commit the changes to the database
	conn.commit()
	# Clear the fields
	clear_fields()

# Drop Down Box
discount_combobox = ttk.Combobox(root, value=["Select Discount", "None", "Services Rendered", "Volunteer"])
discount_combobox.current(0)
discount_combobox.grid(row=11, column=1)

def discount_value():
		selected = discount_combobox.get()
		if selected == "Select Discount":
			test = Label(root, text="Must select a discount type")
			test.grid(row=2, column=0)
		if selected == "None":
			sql = "INSERT INTO customers (discount_code)"
			value = (0)
			
		if selected == "Services Rendered":
			sql = "INSERT INTO customers (discount_code)"
			value = (1)
			
		if selected == "Volunteer":
			sql = "INSERT INTO customers (discount_code)"
			value = (2)

I’m just trying to figure out how to impalement it fully at this point.

5

This is a bit to tackle if only programming Python for only a week. :wink:

In any case, as with any program, start small. Are you familiar with classes? Perhaps from programming in another language? If so, this might make things a bit easier when creating a combobox for your application. You can gradually build out to include additional functionality either by adding methods or by inheritance by way of additional classes (or both), for example.

The following is a simple example of creating a combobox using a class. Study it carefully and see how you can apply it to your current application. Note that the method titled: discount_selection can include the body of your discount_value() function since this is where the customer selection is being read.

I would recommend tackling one specific functionality in your application one at a time. Verify that you fully understand how that functionality works and that you have fully verified that it is working as expected. Once having done so, move on to adding the next layer. Rinse and repeat. But don’t attempt to create the entire application without having verified all of the intermediate steps first. Otherwise, it will make the debugging process and finding the root cause of the application not working much more difficult.

import tkinter as tk
import tkinter.ttk as ttk

class App(tk.Tk):

    def __init__(self):

        super().__init__()

        # Create window
        self.title("Customers")
        menu_options = ("Select Discount", "None", "Services Rendered", "Volunteer")
        self.label = ttk.Label(self, text = "Make Selection")
        self.combo = ttk.Combobox(self, values = menu_options)

        # Create two buttons
        btn_submit = ttk.Button(self, text = "Submit",
                                command = self.discount_selection)
        btn_clear = ttk.Button(self, text = "Clear",
                               command = self.clear_selection)

        self.label.pack(pady = 10)
        self.combo.pack(side = tk.LEFT, padx = 10, pady = 5)
        btn_submit.pack(side = tk.TOP, padx = 10, pady = 5)
        btn_clear.pack(padx = 10, pady=5)

    def discount_selection(self, *args):
        user_selection  = self.combo.get() # This 'gets' the user selection

        # Here, the action that is being executed is merely printing what
        # the user selected to the screen - instead add your code in the 'discount_value' function
        print("Your selection is: ", user_selection )

    def clear_selection(self):
        self.combo.set("")

if __name__ == "__main__":

    app = App()
    app.mainloop()
6

If a sequence of if statements are mutually exclusive, use elif so that it skips the following ifs that aren’t going to match:

    if selected == "Select Discount":
        test = Label(root, text="Must select a discount type")
        test.grid(row=2, column=0)
    elif selected == "None":
        sql = "INSERT INTO customers (discount_code)"
        value = 0
    elif selected == "Services Rendered":
        sql = "INSERT INTO customers (discount_code)"
        value = 1
    elif selected == "Volunteer":
        sql = "INSERT INTO customers (discount_code)"
        value = 2

In this case, what you have is basically a simple lookup, from a displayed string to a value.

When you see such a thing, consider a dict (dictionary).

However, not all of them are going from a string to a value.

The first one displays a message.

A simple solution is for the lookup to return a special value that you then check, like this:

    value_for_string = {
        "Select Discount": None,
        "None": 0,
        "Services Rendered": 1,
        "Volunteer": 2,
    }

    value = value_for_string[selected]

    if value is None:
        test = Label(root, text="Must select a discount type")
        test.grid(row=2, column=0)
7

Thanks for the info. I’ll look over it more tonight when I have a moment.
I haven’t done much programing in 20 years.
But use to do c++, Java, html, css, php and so on.
But I can read code and have a good grasp of it.
The program I’m make everything is working properly and well.
This is more of a feature I would like to use since a lot of the other coding will require it to be working as well to make it much easier for the end user.

My end goal is to have a very good understanding within a year for my daily job as IT admistrator.

8

Regarding your original post, I noticed that the code where you’re creating the entries and labels is a bit verbose. Try implementing list comprehensions to minimize your code coupled with for loops. The following is a simple example highlighting this technique.

import tkinter as tk

class App(tk.Tk):
    
    def __init__(self):
            
        super().__init__()

        # List with four strings
        fields = ["First name", "Last name", "Phone", "Email"]

        # Create four labels via list comprehension
        labels = [tk.Label(self, text = f) for f in fields]

        # Create four entries via list comprehension - use 'fields' as reference to parent container
        entries = [tk.Entry(self) for _ in fields]

        # Use the zip function to combine the labels and the entries and create an iterator
        self.widgets = list(zip(labels, entries))

        # Create button
        self.submit = tk.Button(self, text = "Print info", command = self.print_info)
        self.submit.bind("<Return>", self.print_info) # Add 'Enter' key option to button
        self.submit.grid(row = len(fields), column = 1, sticky = tk.E, padx = 10, pady = 10)
        
        entries[0].focus_set() # Put cursor in 'First Name' cell entry at start up 

        # Track the index of each tuple pair and pass it as the row number
        # for the placement of the labels and entries
        for i, (label, entry) in enumerate(self.widgets):
            
            label.grid(row = i, column = 0, padx = 10, sticky = tk.W)
            entry.grid(row = i, column = 1, padx = 10, pady = 5)
        
    def print_info(self, *args):
        
        for label, entry in self.widgets:
            print("{} = {}".format(label.cget("text"), entry.get()))

if __name__ == "__main__":
    app = App()
    app.mainloop()
9

Thanks for the info, I plan to condense everything once I got the app working fully.
Though I’m unsure if I could use the code in that manor since I do have a lot of special entry boxes that need to call on other areas to get the info from SQL and input it in another format.
Have a few int, date and time columns in the DB to make it easier for this person in the long run.

At this moment I am written each part of the certain app in it’s own container and making sure the code if working properly for it then will be adding it into the main program. Plan to as well make it with Treeview and pull down menus for better look and easier use.

10

Thank you for the info, I have added a dict to the discount codes, and I have found a way to make it work, though not fully how I want it to. Done a lot of reading and testing and still trying to find the best result for the code needed.

This is the code I found to work.

def callback1(e):
    discount = dc2.get()
    lbox.delete("0", "end")
    discount_values = list(set([record[1] for record in dv if record[0]==discount]))
    lbox['values'] = discount_values

Label(root, text='Discount: ').grid(row=11, column=0, padx=10, pady=10)
dc2 = ttk.Combobox(root,)
dc2.grid(row=11, column=1)
all_values = list(set([record[0] for record in dv]))
dc2['values'] = all_values
dc2.bind("<<ComboboxSelected>>", callback1)

Label(root, text='Select Discount Code: ').grid(row=12, column=0)
lbox = ttk.Combobox(root, width=10)
lbox.grid(row=12, column=1, padx=10, pady=10)

It’s not the final result and user has to select the int value that needs to be selected.
But does work for the time being. Thought still playing with it.

I might convert it into sqllite so can be seen how it works without having to have mysql installed. I do have my code posted on git incase I should post the link for it.