1

I use MySQL 8.0, python 3.7.3, pymysql and Windows 10.

I keep getting this error when executing the following query:

sql =  "INSERT INTO personnel_duties(scenario_id, id, function, validfrom, validto, duty_type) VALUES (%s, %s, %s, %s, %s, %s) \
          ON DUPLICATE KEY UPDATE \
            function = %s, \
            validto = %s"

val_list =   [0, 2246, 'X', datetime.datetime(2019, 6, 27, 0, 0), datetime.datetime(2035, 1, 1, 0, 0), '555', 'X', datetime.datetime(2035, 1, 1, 0, 0)]

self.cursor.execute(sql, val_list)

error is:

pymysql.err.ProgrammingError: (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 'function, validfrom, validto, duty_type) VALUES (0, 2246, 'X', '2019-06-27 00:00:0' at line 1")

I couldn't find the problem. What is wrong?

alwbtc
  • 28,057
  • 62
  • 134
  • 188
  • Try this `function = VALUES(%s), validto = VALUES(%s)` after `ON DUPLICATE KEY UPDATE`. – shaik moeed Jun 27 '19 at 09:57
  • 2
    `function` is a [reserved keyword](https://dev.mysql.com/doc/refman/8.0/en/keywords.html) in MySQL. See [this answer](https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table) for how to escape it. – shmee Jun 27 '19 at 10:06
  • @shaikmoeed why do I have to use `VALUES`? I didn't need it before. – alwbtc Jun 27 '19 at 10:13
  • 1
    @alwbtc you don't *have* to use `VALUES` although it's helpful. In this case the problem is probably the `function` word, try quoting it in backticks (`\`function\``) – Vatev Jun 27 '19 at 11:48
  • @shmee your solution worked, I changed column name from `function` to `personnel_function` and it worked. Though, the error statement could give a more explicit message like "function is a reserved word". Thanks a lot – alwbtc Jun 28 '19 at 11:12

0 Answers0