How to trigger a run button based on all the values in a list?

Question

I have a Tkinter app and inside that app I have an OptionMenu which is giving me all the id's that are located in the list vehicleid. Please note that this list can become bigger or smaller.

Now I want my button to send the data of owner and vehicleid to a database based on what the user selects. So if I have for example 2 vehicleid's, I first need to select a specific vehicleid and for every vehicleid I need to select a specific owner.

So in case of 2 vehicleid my database should look like this:

vehicleid        owner
C161      ---    Spain
C162      ---    United Kingdom

App looks like this:

This is my code:

owner = ['Spain', 'United Kingdom', 'Malaysia']
vehicleid = ['C161', 'C162']

window = Tk()
window.title("Running Python Script") # Create window
window.geometry('550x300') # Geo of the window

##These are the option menus
dd_owner = StringVar(window)
dd_owner.set(owner[0]) # the first value
w = OptionMenu(window, dd_owner, *owner)
w.grid(row=1, column=1)

dd_id = StringVar(window)
dd_id.set(vehicleid[0])
w0 = OptionMenu(window, dd_id, *vehicleid)
w0.grid(row=0, column=1)

##The run button 
run_list_button =Button(window, text="Send data of ID's to database!")
run_list_button.grid(column=0, row=3)

##These are the titles
l1 = Label(window, text='Select Owner', width=15)
l1.grid(row=1, column=0)

l0 = Label(window, text='Select vehicle id:', width = 30)
l0.grid(row=0, column=0)

mainloop()

Answer 1

To begin with, you should store the data somewhere(a dictionary or a file..) and then read the data when the user presses the button.

import mysql.connector as mysql
....

mydb = mysql.connect(host = 'localhost',user = 'root',passwd = '****.',database = 'table_data')

data = {}
def store():
    if dd_id.get() not in data:
        data[dd_id.get()] = dd_owner.get()
    print(data)

def upload():
    cur = mydb.cursor()
    for item in data.items():
        sql = 'INSERT INTO table_data VALUES (%s,%s)'
        params = (item[0],item[1])

        cur.execute(sql,params)
        mydb.commit()
    
    print('Done')

....
# The store button
Button(window, text="Store data!",command=store).grid(column=0, row=3)

# The database button
Button(window, text="Send to database",command=upload).grid(column=0, row=4)

This will store the data in the database when the respective buttons are clicked, also duplicate entries or updatiion of entries will not be allowed.

Answer 2

Though your question is confusing. After looking at your discussion I understand that you want to send all data to the database only after the users have confirmed their choice.

In that case, you probably need a dictionary where you store both vehicle_id and owner {"vehicle_id": [], "owner": []} until the user clicks on the update database button. Once you have updated the database make sure to empty the dictionary so the previously selected items are not inserted into the database again.

_{Note: you would still need another button to be pressed several times to insert data into the dictionary. You can choose not to have the button by using the trace method of the control variable}

Here is an example

from tkinter import *
import sqlite3


CREATE_QUERY = "CREATE TABLE IF NOT EXISTS vehicle(vehicle_id VARCHAR(5), owner VARCHAR(100));"
INSERT_QUERY = "INSERT INTO vehicle(vehicle_id, owner) VALUES(?, ?);"
SELECT_QUERY = "SELECT * FROM vehicle;"

sql_file = "sample.db"

id_dict = {"vehicle_id": [], "owner": []}

def create_data_base():

     with sqlite3.connect(sql_file) as conn:
         conn.execute(CREATE_QUERY)
         conn.commit()

def insert_to_db():
    global id_dict
    with sqlite3.connect(sql_file) as conn:

        for value in zip(*id_dict.values()):
    
            conn.execute(INSERT_QUERY, value)

        conn.commit()
    
    id_dict = {"vehicle_id": [], "owner": []}  # empty the list once you insert the data
    display_data()
    
def display_data():
    with sqlite3.connect(sql_file) as conn:
        curr = conn.cursor()
        curr.execute(SELECT_QUERY)
        items = curr.fetchall()

    print(items)


def add():

    id_dict["vehicle_id"].append(dd_id.get())
    id_dict["owner"].append(dd_owner.get())
    print(id_dict)


owner = ['Spain', 'United Kingdom', 'Malaysia']
vehicleid = ['C161', 'C162']

window = Tk()
window.title("Running Python Script") # Create window
window.geometry('550x300') # Geo of the window


create_data_base()

##These are the option menus
dd_owner = StringVar(window)
dd_owner.set(owner[0]) # the first value
w = OptionMenu(window, dd_owner, *owner)
w.grid(row=1, column=1)

dd_id = StringVar(window)
dd_id.set(vehicleid[0])
w0 = OptionMenu(window, dd_id, *vehicleid)
w0.grid(row=0, column=1)


Button(window, text='Add', command=add).grid(column=1, row=3)

##The run button 
run_list_button =Button(window, text="Send data of ID's to database!", command=insert_to_db)
run_list_button.grid(column=0, row=3)

##These are the titles
l1 = Label(window, text='Select Owner', width=15)
l1.grid(row=1, column=0)

l0 = Label(window, text='Select vehicle id:', width = 30)
l0.grid(row=0, column=0)

window.mainloop()

The above code will insert all the data from the dictionary to the database.