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;
Advertisement
Answer
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 ""
.