'on_duplicate_key_update' for MySQL doesn't support using values from already inserted row

Issue #4172 resolved
Softbreakers
created an issue

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: 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 for the UPDATE part, 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")

Comments (5)

  1. Michael Bayer repo owner

    you would not want to put a SQL expression like "counter + 1" as a text string like that. you need to create a proper SQL expression or use the text() construct.

    given your UPDATE c=c+1 example, that is:

    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.dialects.mysql import insert
    from sqlalchemy.dialects import mysql
    
    m = MetaData()
    
    t1 = Table(
        't1', m,
        Column('id', Integer, primary_key=True),
        Column('a', Integer),
        Column('b', Integer),
        Column('c', Integer)
    )
    
    
    stmt = insert(t1).on_duplicate_key_update(c=t1.c.c + 1)
    
    print stmt.compile(dialect=mysql.dialect())
    

    output:

    INSERT INTO t1 (id, a, b, c) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE c = (t1.c + %s)
    

    that seems to be what you're asking for? please alter the above script to show what you're trying to do, thanks.

  2. Softbreakers reporter

    Thank you. Excuse me for being so clumsy...

    Just for reference of other newbies like me, if it's needed an UPDATE c=c+:parameter there is a bind parameter object:

    from sqlalchemy import Integer
    from sqlalchemy.sql import bindparam
    [...]
    stmt = insert(t1).on_duplicate_key_update(c=t1.c.c + bindparam('parameter', type_=Integer))
    

    I think I caught it. Thanks again.

  3. Log in to comment