SQLAlchemy 是否与 Django 的 get_or_create 等效? [英] Does SQLAlchemy have an equivalent of Django's get_or_create?

问题描述

我想从数据库中获取一个对象，如果它已经存在(基于提供的参数)，或者如果它不存在则创建它.

I want to get an object from the database if it already exists (based on provided parameters) or create it if it does not.

Django 的 get_or_create(或来源) 做这个.SQLAlchemy 中是否有等效的快捷方式?

Django's get_or_create (or source) does this. Is there an equivalent shortcut in SQLAlchemy?

我目前正像这样明确地写出来:

I'm currently writing it out explicitly like this:

def get_or_create_instrument(session, serial_number):
    instrument = session.query(Instrument).filter_by(serial_number=serial_number).first()
    if instrument:
        return instrument
    else:
        instrument = Instrument(serial_number)
        session.add(instrument)
        return instrument

推荐答案

基本上就是这样，AFAIK 没有现成的快捷方式.

That's basically the way to do it, there is no shortcut readily available AFAIK.

你当然可以概括它:

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).one_or_none()
    if instance:
        return instance, False
    else:
        params = {k: v for k, v in kwargs.items() if not isinstance(v, ClauseElement)}
        params.update(defaults or {})
        instance = model(**params)
        try:
            session.add(instance)
            session.commit()
        except Exception:  # The actual exception depends on the specific database so we catch all exceptions. This is similar to the official documentation: https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html
            session.rollback()
            instance = session.query(model).filter_by(**kwargs).one()
            return instance, False
        else:
            return instance, True

---

2020 更新(仅限 Python 3.9+)

这是一个更简洁的版本，带有 Python 3.9 的 新的 dict 联合运算符(|=)

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(Model).filter_by(**kwargs).one_or_none()
    if instance:
        return instance, False
    else:
        kwargs |= defaults or {}
        instance = model(**params)
        try:
            session.add(instance)
            session.commit()
        except Exception:  # The actual exception depends on the specific database so we catch all exceptions. This is similar to the official documentation: https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html
            session.rollback()
            instance = session.query(model).filter_by(**kwargs).one()
            return instance, False
        else:
            return instance, True

注意:

与 Django 版本类似，这将捕获重复的键约束和类似的错误.如果您的 get 或 create 不能保证返回单个结果，它仍然可能导致竞争条件.

Note:

Similar to the Django version this will catch duplicate key constraints and similar errors. If your get or create is not guaranteed to return a single result it can still result in race conditions.

为了缓解部分问题，您需要在 session.commit() 之后添加另一个 one_or_none() 样式获取.除非您还使用 with_for_update() 或可序列化事务模式，否则这仍然不能 100% 保证避免竞争条件.

To alleviate some of that issue you would need to add another one_or_none() style fetch right after the session.commit(). This still is no 100% guarantee against race conditions unless you also use a with_for_update() or serializable transaction mode.

这篇关于SQLAlchemy 是否与 Django 的 get_or_create 等效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！