How to Efficiently Perform INSERT ... ON DUPLICATE KEY UPDATE in SQLAlchemy?

ON DUPLICATE KEY UPDATE in SQLAlchemy

Question: Is there a concise way to perform an "INSERT ... ON DUPLICATE KEY UPDATE" operation using SQLAlchemy's ORM?

Answer:

SQLAlchemy includes built-in support for "ON DUPLICATE KEY UPDATE" for MySQL after version 1.2. To use this functionality, refer to the solution provided here: https://stackoverflow.com/a/48373874/319066

SQL Statement Approach:

For general situations or non-MySQL databases, you can use a @compiles decorator to include "ON DUPLICATE KEY UPDATE" in the generated SQL statement. An example is provided here: https://github.com/somada141/sqlalchemy-insert-duplicate-update

ORM Approach:

SQLAlchemy's ORM does not directly support "ON DUPLICATE KEY UPDATE." However, for primary key updates, you can use the session.merge() function, which checks for an existing record and updates it accordingly.

Custom Implementation for Non-Primary Keys:

For situations involving non-primary key updates, you can create a function similar to Django's get_or_create() that first checks for an existing record and performs an update or insert accordingly. An example of such a function is provided here:

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 to Efficiently Perform INSERT ... ON DUPLICATE KEY UPDATE in SQLAlchemy?. For more information, please follow other related articles on the PHP Chinese website!