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

the code to send data to the database is not that hard. But I want the data to be sent all at once. So depending on how much items I have in `vehicleid` — TangerCity, Jun 01 '21 at 11:23
All at once? Send one by one. You only have option to choose 1 set of values at a time. — Delrius Euphoria, Jun 01 '21 at 11:31
Actually what is the problem you have when you tried to *send all data at once*? — acw1668, Jun 01 '21 at 11:33
@CoolCloud Yes 1 set of values at a time, that needs to be saved somewhere then chose the next id then save again then chose the next one etc. until I click the button and then everything gets send to the database. — TangerCity, Jun 01 '21 at 11:33
@acw1668 I cannot send all the data at once, that is what I want.. I can only send one by one but that it not efficient. If I have 20 ID's I need to click the send button 20 times lol — TangerCity, Jun 01 '21 at 11:34
You have to click some button 20 times anyway, either to save data somewhere or to insert into database — Delrius Euphoria, Jun 01 '21 at 11:40
@CoolCloud That is true...what would be the most efficient way? — TangerCity, Jun 01 '21 at 11:50
There is nothing wrong in pressing button 20 times... Or you should ask them how many times they want to enter and then generate that many entry boxes. Then one button to insert data all at once. — Delrius Euphoria, Jun 01 '21 at 11:53
@CoolCloud How many times they want to enter depends on how many items are in `vehicleid` — TangerCity, Jun 01 '21 at 11:56
Does it mean that you want to insert the records in *one* SQL statement? — acw1668, Jun 01 '21 at 12:04
@CoolCloud in my current `vehicleid` I have 2 items, but sometimes it can be more. — TangerCity, Jun 01 '21 at 12:26
I've suggested the solution already, either adjust with what you have, or make howmuch ever entries you want to enter on there. — Delrius Euphoria, Jun 01 '21 at 12:34
How do you confirm the combination, i.e. after selecting the vehicle id and owner? Pressing a confirm button? If it is the case, then why don't you store the combination to database at that time? — acw1668, Jun 01 '21 at 12:34
@CoolCloud That is why I ask this question "How to confirm the one combination?". — acw1668, Jun 01 '21 at 12:35
Can we talk in this discussion further the 3 of us so I can explain better. https://chat.stackoverflow.com/rooms/232816/discussion — TangerCity, Jun 01 '21 at 12:35

Art · Answer 1 · 2021-06-01T13:07:36.917

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.

Your storing data in format `{"vehicle_id": [], "owner": []}` , why not just id as key because it is unique — Delrius Euphoria, Jun 01 '21 at 13:20
@CoolCloud I don't think id is unique OP's program since he is giving an option for the user to select from the given list, the uniqueness of the id is not checked by OP. I store the data `{"vehicle_id": [], "owner": []}` like this since he seems to want to send all the data at once to the database. If I used something like this `{'id1':'owner1'}` the value will be erased when writing new data. — Art, Jun 01 '21 at 13:26
Yes and that is called _updating_ the id. Was just a suggestion, you can ignore it — Delrius Euphoria, Jun 01 '21 at 13:27

score 1 · Accepted Answer · answered Jun 01 '21 at 13:33

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.

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

2 Answers2