I've got a problem with a presumably simple text insert into a MySQL database from Python. The text is a HTML string, with which I want to update an existing row based on this rows primary key. I'm using this statement with PyMySQL (line breaks added for readability):
cursor.execute("UPDATE my_table SET my_text_col = %s
AND another_int_col = %s WHERE my_table_pk_col = %s",
(some_html_string, some_int, this_rows_pk))
This should be fairly straightforward, but (as way to often with MySQL) it apparently isn't. I get
Warning: (1292, Truncated incorrect DOUBLE value: [Beginning of some_html_string])
and the my_text_col is set to 0.
I have no idea why this doesn't work. There is no double field in this table, the HTML is a string that gets properly escaped, the other two values are ints. I have seen that others reported similar issues, and there are even bug reports related to this (#43437, #46641), but these are more than 8 years old and haven't been fixed (I'm using MySQL 5.6.27 and PyMySQL 0.7.11).
If anyone has solution or a workaround to get this simple update done I would greatly appreciate it.
That's a simple but not obvious syntax error:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
This is the syntax from the docs (https://dev.mysql.com/doc/refman/5.7/en/update.html), multiple fields are seperated with commas not with AND in update statements.