ON DUPLICATE KEY UPDATE hack

gistfile1.py

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)

@rclmenezes The second (mysql) append_string gives me KeyError:

File "/vagrant/src/.../migration.py", line 41, in <module>
Insert.argument_for("mysql", "append_string", None)
File "/vagrant-dev/opt/dev_virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/base.py", line 184, in argument_for
construct_arg_dictionary[cls][argument_name] = default
KeyError: <class 'sqlalchemy.sql.dml.Insert'>

Tried some alternate approaches but none came to anything working... Do you have any more ideas? Does your above mentioned solution work out of the box for you?

The following is for Postgres (ON CONFLICT DO NOTHING) and works out of the box for me for bulk_insert_mapping

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

@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
    return compiler.visit_insert(insert, **kw) + " ON CONFLICT DO NOTHING"

However in my case I don't need it to be optional (well, actually I use it for a specific table only, but not dynamically optional)

The problem is that it will modify all the insert statements. Is there a way to avoid this ?

@areshand try this:

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

@compiles(Insert, 'postgresql')
def ignore_duplicates(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    ignore = insert.kwargs.get('postgresql_ignore_duplicates', False)
    return s if not ignore else s + ' ON CONFLICT DO NOTHING'
Insert.argument_for('postgresql', 'ignore_duplicates', None)

Sample usage:

stmt = MyModel.__table__.insert(postgresql_ignore_duplicates = True)

# -- OR --

from sqlalchemy import insert
stmt = insert(MyModel, postgresql_ignore_duplicates = True)

All the method above can't work.

@compiles(Insert, 'mysql')
def mysql_upsert(insert, compiler, **kw):
    def _gen_fv_dict(fv_dict):
        pass

    s = compiler.visit_insert(insert, **kw)
    if 'on_duplicate_key_update' in insert.kwargs:
        return s + ' ON DUPLICATE KEY UPDATE ' + _gen_fv_dict(insert.kwargs['on_duplicate_key_update'])
    return s

Insert.argument_for("mysql", "mysql_upsert", None)

 stmt = User.__table__.insert(on_duplicate_key_update={
        'age': 'age + VALUES(age)',
    })

session.execute(stmt, data)

And get error

SAWarning: Can't validate argument 'on_duplicate_key_update'; can't locate any SQLAlchemy dialect named 'on'
  % (k, dialect_name)

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry 'bulko11' for key 'name'")
[SQL: INSERT INTO user (name, age) VALUES (%s, %s)]
[parameters: ('bulko11', 11)]