Skip to content

Instantly share code, notes, and snippets.

@kung-foo
Created February 3, 2012 15:13
Show Gist options
  • Save kung-foo/1730627 to your computer and use it in GitHub Desktop.
Save kung-foo/1730627 to your computer and use it in GitHub Desktop.
ON DUPLICATE KEY UPDATE hack
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)
@alexcohaniuc
Copy link

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

@rclmenezes
Copy link

@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.

@rclmenezes
Copy link

@pawl

my_table = MyModel.__table__

@uskudnik
Copy link

@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?

@bivald
Copy link

bivald commented Apr 29, 2016

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)

@areshand
Copy link

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

@amirbehzad
Copy link

amirbehzad commented Aug 22, 2017

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

@631068264
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment