ON DUPLICATE KEY UPDATE post version-1.2 for MySQL

This functionality is now built into SQLAlchemy for MySQL only. somada141's answer below has the best solution: https://stackoverflow.com/a/48373874/319066

ON DUPLICATE KEY UPDATE in the SQL statement

If you want the generated SQL to actually include ON DUPLICATE KEY UPDATE, the simplest way involves using a @compiles decorator.

The code (linked from a good thread on the subject on reddit) for an example can be found on github:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert

@compiles(Insert)
def append_string(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    if 'append_string' in insert.kwargs:
        return s + " " + insert.kwargs['append_string']
    return s


my_connection.execute(my_table.insert(append_string = 'ON DUPLICATE KEY UPDATE foo=foo'), my_values)

But note that in this approach, you have to manually create the append_string. You could probably change the append_string function so that it automatically changes the insert string into an insert with 'ON DUPLICATE KEY UPDATE' string, but I'm not going to do that here due to laziness.

ON DUPLICATE KEY UPDATE functionality within the ORM

SQLAlchemy does not provide an interface to ON DUPLICATE KEY UPDATE or MERGE or any other similar functionality in its ORM layer. Nevertheless, it has the session.merge() function that can replicate the functionality only if the key in question is a primary key.

session.merge(ModelObject) first checks if a row with the same primary key value exists by sending a SELECT query (or by looking it up locally). If it does, it sets a flag somewhere indicating that ModelObject is in the database already, and that SQLAlchemy should use an UPDATE query. Note that merge is quite a bit more complicated than this, but it replicates the functionality well with primary keys.

But what if you want ON DUPLICATE KEY UPDATE functionality with a non-primary key (for example, another unique key)? Unfortunately, SQLAlchemy doesn't have any such function. Instead, you have to create something that resembles Django's get_or_create(). Another StackOverflow answer covers it, and I'll just paste a modified, working version of it here for convenience.

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance
    else:
        params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
        if defaults:
            params.update(defaults)
        instance = model(**params)
        return instance

I just ran into a similar problem and creating a dictionary out of query.inserted solved it for me.

query = insert(table).values(record_list)
update_dict = {x.name: x for x in query.inserted}
upsert_query = query.on_duplicate_key_update(update_dict)

Adding SQL constructs can be done with custom ClauseElements. Someone already implemented ON DUPLICATE KEY in sqlalchemy_mysql_ext.

change your echo=True to echo='debug', then get the SQL output for your program and walk through every statement, the parameters being sent, and the results received for each. The database isn't lying about that error, so either a. there must be an INSERT preceding it which is putting in that key or b. your test for "key already exists" is using criteria that does not match the ultimate values that get put into the database.

The first thing to note is that you've only created, and discarded, an insert statement object. In order to execute it you have to pass it to one of the execute() methods of SQLAlchemy. The statement is also lacking the values to first try and insert.

from sqlalchemy.dialects.mysql.dml import Insert 

new_record = {'id': 'val1', 'col1': 'new val'}

# Here id is the primary key
# my_table is a Table object
stmt = Insert(my_table).values(new_record)
stmt = stmt.on_duplicate_key_update(col1=stmt.inserted.col1)

# This will actually execute the statement
engine.execute(stmt)

I know it is a bit late.. but if someone is still looking for solutions.

FOR: ON DUPLICATE KEY UPDATE

ins = insert(/* table_name*/ ).values(/*your values(with PK)*/)
do_update_stmt = ins.on_duplicate_key_update(/*your values(with out PK)*/)
connection.execute(do_update_stmt)

On duplicate key update docs

The ON DUPLICATE KEY UPDATE clause of INSERT supported by MySQL is now supported using a MySQL-specific version of the Insert object

This wont be avaliable with sqlalchemy.insert().

FOR: INSERT IGNORE

This is a bit hacky but works just fine.

 ins_address_stmt = insert(/* table_name*/ ).values(/*your values*/). \
            prefix_with('IGNORE')

Insert prefix_with

MySQL will suppress the error for duplicate primary key and gives a warning.