Python/MySQL Insert command & On Duplicate failing-CodePudding

I've got a Python script that is used to clear a table and update it with fresh data every minute or so with fresh data, and in making it better I thought hey let's not delete the data but update data that hasn't changed.

The rest of the script works, except for this line:

cursor.execute("INSERT INTO blocked_ip (IPAddress,UnBlock) VALUES (%s,%s) ON DUPLICATE KEY UPDATE IPAddress = VALUES(%s)", (i, '1', i))

When I run it I get the error:

syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''the_info_to_update')' at line 1

I've modified that line several times and I've run through every Google answer I could find. Any assistance would be greatly appreciated.

CodePudding user response：

Try this:

cursor.execute("INSERT INTO blocked_ip (IPAddress,UnBlock) VALUES ({var_1},{var_2}) ON DUPLICATE KEY UPDATE IPAddress = VALUES({var_3})".format(var_1 = i, var_2 = '1', var_3 = i))

CodePudding user response：

The problem is your query. You don't need VALUES at the end of the query. it should be rewritten as:

"INSERT INTO blocked_ip (IPAddress,UnBlock) VALUES (%s,%s) ON DUPLICATE KEY UPDATE IPAddress = %s"