[Sqlalchemy-tickets] Issue #4172: 'on_duplicate_key_update' for MySQL doesn't let using values from already inserted row (zzzeek/sqlalchemy)

[Softbreakers <iss...@bi...>]

New issue 4172: 'on_duplicate_key_update' for MySQL doesn't let using values from already inserted row
https://bitbucket.org/zzzeek/sqlalchemy/issues/4172/on_duplicate_key_update-for-mysql-doesnt

Softbreakers:

>From sqlalchemy.dialects.mysql.base code comments:

*``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already
existing row, using any combination of new values as well as values
from the proposed insertion.*

But in case of duplicate, MySQL 5.7 supports update using new values, values from proposed insertion **and values from the already existing row**.

[For example](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html):
*INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;*

Suppose a table with two columns: 'name' as string and 'counter' as integer.
If I try something like:

   *insert_stmt = insert(tableobject)*

  *on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(counter='counter+1')*

   *values =[{'name':valname, 'counter':1, }]*

   *conexBD.execute(on_duplicate_key_stmt, values)*

The engine assumes 'counter+1' as a literal value, and generates a SQL statement using quotes like:

*'INSERT INTO mytable (name, counter) VALUES (%s, %s) ON DUPLICATE KEY UPDATE counter = %s'] [parameters: ('any_name', 1, 'counter+1')]*

Which results in a exception message like:

*(1366, "Incorrect integer value: 'counter+1' for column 'counter' at row 1")*