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)

got an error
Can't validate argument 'append_string'; can't locate any SQLAlchemy dialect named 'append'

@alexcohaniuc

By default, the first %s_ of each argument is supposed to be the dialect. So, if this is for mysql, simply:

@compiles(Insert, 'mysql')
def append_string(insert, compiler, **kw):
    """ This will allow us to add ON DUPLICATE KEY to our insert
    """
    s = compiler.visit_insert(insert, **kw)
    if insert.kwargs['mysql_append_string']:
        return s + " " + insert.kwargs['mysql_append_string']
    return s
Insert.argument_for("mysql", "append_string", None)

The Insert.argument_for() at the bottom makes sure the mysql dialect knows that this new argument exists.

@pawl

my_table = MyModel.__table__

@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)]