I am having an issue getting my UPDATE Query to work. I am using python 3.7 and building a gui using tkinter. I have an entry box that a user can select an Item by ID number, that auto-populates the following entry boxes and the user can then modify the entries. My error occurs when it comes time to save the changes. I have a button that calls a function save when clicked. see below
def save(): conn = mysql.connect( host="localhost", user="XXXX", passwd="XXXX", database="inventory") c = conn.cursor() a0 = selectent.get() a1 = item.get() a2 = asset_tag.get() a3 = amount.get() a4 = notes.get() c.execute(""" UPDATE items SET item = 'a1', asset_tag = 'a2', amount = 'a3', notes = 'a4' WHERE id = 'a0' """) conn.commit() conn.close()
I am self-teaching myself mySQL, and from what I have read about this error it appears that the error occurs when trying to compare a number and string in a WHERE clause. This makes sense since my traceback takes me to that line in my code.
Traceback (most recent call last): File "C:UsersmbrowAppDataLocalProgramsPythonPython37libtkinter__init__.py", line 1705, in __call__ return self.func(*args) File "C:UsersmbrowAppDataLocalProgramsPythonPython37Inventory3editmodule.py", line 61, in save WHERE id = 'a0' """) File "C:UsersmbrowAppDataLocalProgramsPythonPython37libsite-packagesmysqlconnectorcursor_cext.py", line 266, in execute raw_as_string=self._raw_as_string) File "C:UsersmbrowAppDataLocalProgramsPythonPython37libsite-packagesmysqlconnectorconnection_cext.py", line 475, in cmd_query sqlstate=exc.sqlstate) mysql.connector.errors.DataError: 1292 (22007): Truncated incorrect DOUBLE value: 'a0'
Can someone explain this to me so I better understand what is going on?
I know amount is spelled wrong.
Use parameterized query and prepared statement
c.execute(""" UPDATE items SET item = %s, asset_tag = %s, amount = %s, notes = %s WHERE id = %s """,(a1,a2,a3,a4,a0))