Warning for custom insert (MySQL)

[theking-laptop]

I use MySQL as my database backend and I really want to use INSERT... ON DUPLICATE KEY UPDATE feature. This is my code:

@compiles(Insert, 'mysql')
def suffix_insert(insert, compiler, **kw):
    stmt = compiler.visit_insert(insert, **kw)
    if 'on_duplicate' in insert.kwargs:
        my_var = insert.kwargs['on_duplicate']
        stmt += ' ON DUPLICATE KEY UPDATE'
        # other stuff

It works perfectly when I use SqlAlchemy 0.9.1 or below. With higher version, everything is still fine, but I got warning:

lib/python2.7/site-packages/sqlalchemy/sql/dml.py:465: SAWarning: Can't validate argument 'on_duplicate_key_update'; can't locate any SQLAlchemy dialect named 'on'
  self._validate_dialect_kwargs(dialect_kw)

How can I workaround this problem?

[Mike Bayer]

call this:

Index.argument_for("mysql", "on_duplicate_key_update_cols", None)

then your argument is named, on the outside, "mysql_on_duplicate_key_update_cols".

example at http://docs.sqlalchemy.org/en/rel_0_9/core/dml.html?highlight=argument_for#sqlalchemy.sql.expression.Insert.argument_for

[Alex Cohaniuc]

How do we use that line? That wasn't helpfull at all...

[Mike Bayer]

did you read the documentation I linked?

[Mike Bayer]

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


@compiles(Insert, 'mysql')
def suffix_insert(insert, compiler, **kw):
    stmt = compiler.visit_insert(insert, **kw)
    if 'mysql_on_duplicate_key_update_cols' in insert.dialect_kwargs:
        my_var = insert.kwargs['mysql_on_duplicate_key_update_cols']
        stmt += ' ON DUPLICATE KEY UPDATE %s' % (", ".join(my_var))
    return stmt

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

if __name__ == '__main__':
    from sqlalchemy.sql import table, column
    from sqlalchemy.dialects import mysql

    print table('t', column('x')).insert(
        mysql_on_duplicate_key_update_cols=['x']).compile(
        dialect=mysql.dialect())

[nyov]

I had to amend this line to read

if 'mysql_on_duplicate_key_update_cols' in insert.dialect_kwargs and insert.dialect_kwargs['mysql_on_duplicate_key_update_cols'] is not None:

somehow it could be None and I would get an error on joining my_var then.