How Can I Efficiently Use INSERT ... ON DUPLICATE KEY UPDATE with SQLAlchemy?

Elegant ON DUPLICATE KEY UPDATE with SQLAlchemy

Question: Is there a seamless way to execute INSERT ... ON DUPLICATE KEY UPDATE in SQLAlchemy using a syntax similar to inserter.insert().execute(list_of_dictionaries)?

Answer:

Built-in Functionality for MySQL (as of version 1.2)

For MySQL specifically, SQLAlchemy now includes support for ON DUPLICATE KEY UPDATE.

ON DUPLICATE KEY UPDATE in SQL Statements

To explicitly include ON DUPLICATE KEY UPDATE in the generated SQL, you can employ the @compiles decorator:

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

This allows you to append the necessary string to the generated insert statement:

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

ON DUPLICATE KEY UPDATE Functionality in the ORM

While SQLAlchemy lacks explicit ORM support for ON DUPLICATE KEY UPDATE or MERGE, it does have the session.merge() function. However, this function is only effective for primary keys.

To simulate ON DUPLICATE KEY UPDATE functionality for non-primary keys, you can implement a function like the following:

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

The above is the detailed content of How Can I Efficiently Use INSERT ... ON DUPLICATE KEY UPDATE with SQLAlchemy?. For more information, please follow other related articles on the PHP Chinese website!