python的mysql.connector中的参数错误

Question

一个关于SQL的错误刚刚出现在我的程序中，而我根本没有改变代码，而且它以前是工作的。下面是给出错误的代码片段。为了给出一些背景，它是一个在RFID芯片的帮助下记录出勤情况的覆盆子pi系统。

...
if cursor.rowcount >= 1:
      lcd.clear()
      lcd.message("Overwrite\nexisting user?")
      overwrite = input("Overwite (Y/N)? ")
      if overwrite[0] == 'Y' or overwrite[0] == 'y':
        lcd.clear()
        lcd.message("Overwriting user.")
        time.sleep(1)
        sql_insert = "UPDATE users SET name, room = %s WHERE rfid_uid=%s"
      else:
        continue;
else:
    sql_insert = "INSERT INTO users (name, room, rfid_uid) VALUES (%s, %s, %s)"
lcd.clear()
        
lcd.message('Enter new name')
new_name = input("Name: ")
lcd.clear()
        
lcd.message('Enter room number')
new_room = input("Room: ")
lcd.clear()

####this line below gives error
cursor.execute(sql_insert, (new_name, new_room, id))

db.commit()
...

下面是我得到的一个错误：

Traceback (most recent call last):
  File "/home/pi/checkinsystem/ra.py", line 67, in <module>
    cursor.execute(sql_insert, (new_name, new_room, id))
  File "/usr/local/lib/python3.7/dist-packages/mysql/connector/cursor.py", line 561, in execute
    "Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

我尝试了一些轻微的语法更改，但考虑到它以前工作得很好，我在这一点上一无所知。对如何解决这个问题有什么建议吗？

Answer 1

嗨，我想我以前和你也有过同样的问题。尝尝这个

else:
    n = input('Enter new name')
    lcd.clear()
    r = input('Enter room number')
    lcd.clear()
    sql_insert = "INSERT INTO users (name, room, rfid_uid) VALUES ("+n+","+r+","+rfid_uid+")"
    lcd.clear()
    cursor.execute(sql_insert)
    db.commmit()

我真的希望这会有帮助，或者可能需要一些调整。

Answer 2

SQL语法错误。应为sql_insert = "UPDATE users SET name = %s, room = %s WHERE rfid_uid=%s"而不是sql_insert = "UPDATE users SET name, room = %s WHERE rfid_uid=%s"