Loading text boxes with DB values from Combobox Selection

Question

I'm trying to populate values in text boxes from existing db based on selection made by user in GUI via combobox.

def lookupClassification(event):
    # clear the text boxes
    rate.delete(0, END)
    burden_factor.delete(0, END)
    total_cost.delete(0, END)

    # Create a database or connect to one
    conn = sqlite3.connect('labor_rates.db')
    # Create a cursor
    c = conn.cursor()

    classification_selected = classification.get()
    #query the database
    c.execute('SELECT * FROM laborrates WHERE classification = ?', [classification_selected])
    records = c.fetchall()
    #loop through results
    for record in records:
        rate.insert(0, record[1])
        burden_factor.insert(0, record[2])
        total_cost.insert(0, record[3])
        
#combobox options
def class_combo():
    conn = sqlite3.connect('labor_rates.db')
    c = conn.execute('SELECT classification FROM laborrates')
    result = []
    for row in c.fetchall():
        result.append(row[0])
    return result

# create text boxes
classification = ttk.Combobox(lr_editor, width = 30, values = class_combo())
classification.grid(row=1, column=1, padx=20, pady=(10,0))
classification.bind('<<ComboboxSelected>>', lookupClassification)

Once the selection is made no values are populated within the respected text boxes. I've tried all I can think of and would appreciate any insite.

You did not tell what went wrong. Either way, your code is prone to SQL injection — Delrius Euphoria
– Delrius Euphoria, Commented Aug 21, 2022 at 21:02
I’ve updated the post. Text boxes do not have value populate once combobox value is selected — Worm
– Worm, Commented Aug 21, 2022 at 21:44
You should be able to figure out what records is first, so print(records) before the loop and make sure it is the values you expect — Delrius Euphoria
– Delrius Euphoria, Commented Aug 21, 2022 at 23:23
<<ComboSelected>> should be <<ComboboxSelected>> instead. Also lookupClassification() should accept an argument, the Event object. — acw1668
– acw1668, Commented Aug 22, 2022 at 1:12
Updated and now getting the following error sqlite3.OperationalError: no such column: Foreman Also it should be noted that classification is text, rate is integer, burden_factor is real, and total_cost is real — Worm
– Worm, Commented Aug 22, 2022 at 2:21

acw1668 · Accepted Answer · 2022-08-22 12:25:05Z

1

If column classification is a string and for example classification_selected = "Foreman", then the final SQL of

'SELECT * FROM laborrates WHERE classification = ' + classification_selected

will be

SELECT * FROM laborrates WHERE classification = Foreman

It will cause the exception mentioned in the comment.

It is better to use placeholder in the SQL in order to avoid SQL injection:

c.execute('SELECT * FROM laborrates WHERE classification = ?', [classification_selected])

edited Aug 22, 2022 at 12:25

answered Aug 22, 2022 at 11:51

acw1668

48k5 gold badges30 silver badges39 bronze badges

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

1 Comment

Worm Over a year ago

Thanks for the help @acw1668. I've updated the code in the post to reflect the corrections!!!

Collectives™ on Stack Overflow

Loading text boxes with DB values from Combobox Selection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related