Home > Database > Mysql Tutorial > How to Efficiently Perform INSERT ... ON DUPLICATE KEY UPDATE in SQLAlchemy?

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

Barbara Streisand
Release: 2024-11-25 15:14:11
Original
1094 people have browsed it

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
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template