MySQL's type isn't wrong,but MySQL tell the wrong 1064

thank you very much! I use 'renew' to replace 'update'. and it success! @MrTux

Thank you for help me.

mysql error:1064,but my type isn't wrong

python3+mysql+pymysql

mysql table：(you just need to see the 'update') (if I insert into not include update , it will success.)

mysql> desc House;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int(11)      | NO   | PRI | NULL    | auto_increment |
| num      | varchar(30)  | NO   | UNI | NULL    |                |
| url      | varchar(150) | YES  |     | NULL    |                |
| name     | varchar(50)  | YES  |     | NULL    |                |
| maintain | varchar(30)  | YES  |     | NULL    |                |
| update   | varchar(30)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Code:

            update = "a str"
            sql2 = "INSERT INTO House (num,update) VALUES ('{0}' , '{1}'  )".format(num,update)
            print(sql2)


            try:
                cursor.execute(sql2)
                print("sql2 success")
                connect.rollback()
                print("rollback success")
            except Exception as e:
                print("sql2 wrong：" + str(e))

Error:

INSERT INTO House (num,update) VALUES ('NJ2578781985216667648' , 'a str'  )
sql2 wrong：(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update) VALUES ('NJ2578781985216667648' , 'a str'  )' at line 1")

Solution

You get a syntax error because update is a keyword in MySQL. You need to use a different name or quote using backticks:

sql2 = "INSERT INTO House (num,`update`) VALUES ('{0}' , '{1}'  )".format(num,update)

PS: please also be aware of possible SQL injections.