Skip None values in entry widgets Tkinter Python

Question

Let's say we have a Tkinter app with many entry widgets like this:

root = Tk()

entry1 = Entry(root, width=40)
entry1.grid(row=0, column=0)
entry2 = Entry(root, width=40)
enrtry2.grid(row=1,column=0)
entry3 = Entry(root, width=40)
enrtry3.grid(row=2,column=0)
entry4 = Entry(root, width=40)
enrtry4.grid(row=3,column=0)

root.mainloop()

The values from these widgets are used to UPDATE a database. The thing I want is to be able to skip a value in anyone of them and not use it in the UPDATE statement. For now with my code I check if there is something written in the widgets like this:

db = fdb.connector
c = db.cursor

values = [entry1.get(), entry2.get(), entry3.get(), entry4.get()]

for v in values:
   if len(v) == 0 or v is None:
     pass
   elif lev(v) != 0:
     c.execute(f"""UPDATE table1 SET column1 = {v[0]}, column2 = {v[1]}, column3 = {v[2]}, column4 = {v[3]} WHERE ID = 1;""")

The problem is that when one of the values is None in the database I get a value of NULL which is correct, because there is no value in the widget. How can I 'tell' python that when there is no value presented it should skip that value and continue to update just the others?

EDIT: Thanks to @TheLizzard this solution works like a charm:

string = "UPDATE table1 SET "
        at_least_one_set = False
        for i in range(12):
             if v[i] != "":
                 string += columns[i] + " = " + "'" + str(v[i]) + "'" + ", "
                 at_least_one_set = True
        if at_least_one_set:
            string = string[:-2]
            string += f" WHERE column_a = '{v[0]}' OR column_b = '{v[1]}';"
            c.execute(string)
        else:
            print("No values were set. Raise an error?")

EDIT2: After a bit of research, and thanks to @TheLizzard, @Arioch 'The, @Mark Rotteveel I've come with the following working code and following one of the suggestions here this is safe now:

v = ["", "2", "3", ""]
column_names = ["column_a", "column_b", "column_c", "column_z"]
rowids = [row[0] for row in c.execute("SELECT id FROM table1 WHERE column_a = ? OR column_b = ?", (v[0], v[1], ))]

string = "UPDATE table1 SET "
at_least_one_set = False
for i in range(12):
    if v[i] != "":
        string += columns[i] + " = '{}'".format(v[i]) + ", "
        at_least_one_set = True
if at_least_one_set:
   string = string[:-2]
   string += " WHERE id = {}".format(rowids[0]) + ";"
   c.execute(string)

And the result is:

UPDATE table1 SET column_b = '2', column_c = '3' WHERE id = 1;

So you want to convert `v = ["", "b", "c", ""]` into `"UPDATE table1 SET column2 = b, column3 = c;"` right? — TheLizzard, Apr 07 '21 at 13:32
Update table without WHERE clause will update all records in the table. Is it really what you want? — acw1668, Apr 07 '21 at 16:22
@MarkRotteveel This: ```c.execute(f"""UPDATE table1 SET column1 = ?, column2 = ?, column3 = ?, column4 = ?;""", (v[0],v[1],v[2],v[3]))``` is the way I will go after I get over the problem. @acw1668 No I would add the `WHERE` with `ID` column that is present in the table and is its primary key. But again I'm getting the `None` values in the table which I don't want. — Tony, Apr 07 '21 at 17:18

TheLizzard · Accepted Answer · 2021-04-07T13:50:18.360

1

Try this:

v = ["", "2", "3", ""]
column_names = ["column_a", "column_b", "column_c", "column_z"]


string = "UPDATE table1 SET "
at_least_one_set = False
for i in range(4):
    if v[i] != "":
        string += column_names[i] + " = " + v[i] + ", "
        at_least_one_set = True
if at_least_one_set:
    string = string[:-2]
    string += ";"
    print(string)
else:
    print("No values were set. Raise an error?")

I dynamically create a string starting with "UPDATE table1 SET " and ending in ";" where I skip all of the values that are "".

edited Apr 07 '21 at 13:50

answered Apr 07 '21 at 13:36

TheLizzard

7,248
2
11
31

This looks nice, but what if the columns are with different names? – Tony Apr 07 '21 at 13:45
1

@T0ny1234 Done. Try it again and tell me if you need any more improvements. – TheLizzard Apr 07 '21 at 13:50
Thank you, this is really genius. I will try it update the question. – Tony Apr 07 '21 at 13:53
1

Be aware, this solution is vulnerable to SQL injection – Mark Rotteveel Apr 07 '21 at 14:54
@MarkRotteveel Forgot about SQL injections. I still don't get how SQL works for the most part. If you know how to fix the problem, you can just edit my answer :D. – TheLizzard Apr 07 '21 at 14:56
@TheLizzard not only injections but also data type mismatches, different i18n settings for date time and whole can of worms. Use SQL parameters for data transfer, try to never have data in the query text. Specifics are in the documentation of your database access library. Some examples are at http://bobby-tables.com/ and at https://github.com/petdance/bobby-tables/issues – Arioch 'The Apr 08 '21 at 09:11
@Arioch'The I am terrible with SQL so please feel free to edit my answer to fix all of those potential bugs. – TheLizzard Apr 08 '21 at 09:15
It is not about SQL, it is soleley about Python and the libraries availabel for SQL in Python. And i know exactly zero about Python... That being said, it seems Tony's knowledge of both Python and SQL is limited too. So perhaps he bettere try using SQLAlchemy and just hope it would work automagically? – Arioch 'The Apr 08 '21 at 09:17
Updated with safe solution. – Tony Apr 09 '21 at 09:32

Skip None values in entry widgets Tkinter Python

1 Answers1