How Can I Achieve INSERT ... ON DUPLICATE KEY UPDATE Functionality in SQLAlchemy?

Elegant ON DUPLICATE KEY UPDATE in SQLAlchemy

SQLAlchemy lacks a seamless solution for performing "INSERT ... ON DUPLICATE KEY UPDATE" operations using its ORM layer. However, there are workarounds to achieve similar functionality.

Built-in MySQL Support

For MySQL databases, SQLAlchemy now provides built-in support for ON DUPLICATE KEY UPDATE. This can be achieved using the following syntax:

inserter = my_table.insert()
inserter.execute(list_of_dictionaries)

Parameterized ON DUPLICATE KEY UPDATE

To include an ON DUPLICATE KEY UPDATE clause in the generated SQL statement for other databases, you can use a decorator to append the desired string:

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

ORM Functionality for Primary Keys

For ORM objects where the unique key is a primary key, SQLAlchemy's session.merge() function can be utilized to replicate ON DUPLICATE KEY UPDATE functionality:

session.merge(ModelObject)

Custom Implementation for Non-Primary Keys

For non-primary key unique constraints, a custom function resembling Django's get_or_create() can be created to achieve desired behavior:

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance
    else:
        params = {k: v for k, v in kwargs.items() 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 Achieve INSERT ... ON DUPLICATE KEY UPDATE Functionality in SQLAlchemy?. For more information, please follow other related articles on the PHP Chinese website!