SQLAlchemy는 Django의 get_or_create에 해당합니까?

SQLAlchemy는 Django의 get_or_create에 해당합니까?

---

제공된 매개 변수를 기반으로 데이터베이스가 이미 존재하는 경우 데이터베이스에서 객체를 가져 오거나없는 경우 객체를 생성하려고합니다.

장고 get_or_create(또는 소스 )가 이것을합니다. SQLAlchemy에 동등한 단축키가 있습니까?

현재 다음과 같이 명시 적으로 작성하고 있습니다.

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는 없습니다.

물론 일반화 할 수 있습니다.

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance, False
    else:
        params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
        params.update(defaults or {})
        instance = model(**params)
        session.add(instance)
        return instance, True

---

@WoLpH의 해결책에 따라, 이것은 나를 위해 일한 코드입니다 (간단한 버전).

def get_or_create(session, model, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance
    else:
        instance = model(**kwargs)
        session.add(instance)
        session.commit()
        return instance

이를 통해 모델의 객체를 get_or_create 할 수 있습니다.

내 모델 객체가 다음과 같다고 가정하십시오.

class Country(Base):
    __tablename__ = 'countries'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)

내 객체를 얻거나 만들려면 다음과 같이 작성하십시오.

myCountry = get_or_create(session, Country, name=countryName)

---

나는이 문제를 겪어 왔으며 상당히 강력한 해결책으로 끝났습니다.

def get_one_or_create(session,
                      model,
                      create_method='',
                      create_method_kwargs=None,
                      **kwargs):
    try:
        return session.query(model).filter_by(**kwargs).one(), False
    except NoResultFound:
        kwargs.update(create_method_kwargs or {})
        created = getattr(model, create_method, model)(**kwargs)
        try:
            session.add(created)
            session.flush()
            return created, True
        except IntegrityError:
            session.rollback()
            return session.query(model).filter_by(**kwargs).one(), False

방금 모든 세부 사항에 대해 상당히 광범위한 블로그 게시물 을 작성 했지만 왜 이것을 사용했는지에 대한 몇 가지 아이디어가 있습니다.

1. 객체의 존재 여부를 알려주는 튜플의 압축을 풉니 다. 이것은 종종 워크 플로에서 유용 할 수 있습니다.

2. 이 기능을 사용하면 @classmethod데코 레이팅 된 제작자 기능 (및 특정 특성) 으로 작업 할 수 있습니다.

3. 이 솔루션은 데이터 스토어에 둘 이상의 프로세스가 연결되어있을 때 경쟁 조건으로부터 보호합니다.

편집 : 변경 한 session.commit()에 session.flush()에 설명 된대로 이 블로그 게시물 . 이러한 결정은 사용 된 데이터 저장소 (이 경우 Postgres)에 따라 다릅니다.

EDIT 2: I’ve updated using a {} as a default value in the function as this is typical Python gotcha. Thanks for the comment, Nigel! If your curious about this gotcha, check out this StackOverflow question and this blog post.

---

A modified version of erik's excellent answer

def get_one_or_create(session,
                      model,
                      create_method='',
                      create_method_kwargs=None,
                      **kwargs):
    try:
        return session.query(model).filter_by(**kwargs).one(), True
    except NoResultFound:
        kwargs.update(create_method_kwargs or {})
        try:
            with session.begin_nested():
                created = getattr(model, create_method, model)(**kwargs)
                session.add(created)
            return created, False
        except IntegrityError:
            return session.query(model).filter_by(**kwargs).one(), True

• Use a nested transaction to only roll back the addition of the new item instead of rolling back everything (See this answer to use nested transactions with SQLite)
• Move create_method. If the created object has relations and it is assigned members through those relations, it is automatically added to the session. E.g. create a book, which has user_id and user as corresponding relationship, then doing book.user=<user object> inside of create_method will add book to the session. This means that create_method must be inside with to benefit from an eventual rollback. Note that begin_nested automatically triggers a flush.

Note that if using MySQL, the transaction isolation level must be set to READ COMMITTED rather than REPEATABLE READ for this to work. Django's get_or_create (and here) uses the same stratagem, see also the Django documentation.

---

This SQLALchemy recipe does the job nice and elegant.

The first thing to do is to define a function that is given a Session to work with, and associates a dictionary with the Session() which keeps track of current unique keys.

def _unique(session, cls, hashfunc, queryfunc, constructor, arg, kw):
    cache = getattr(session, '_unique_cache', None)
    if cache is None:
        session._unique_cache = cache = {}

    key = (cls, hashfunc(*arg, **kw))
    if key in cache:
        return cache[key]
    else:
        with session.no_autoflush:
            q = session.query(cls)
            q = queryfunc(q, *arg, **kw)
            obj = q.first()
            if not obj:
                obj = constructor(*arg, **kw)
                session.add(obj)
        cache[key] = obj
        return obj

An example of utilizing this function would be in a mixin:

class UniqueMixin(object):
    @classmethod
    def unique_hash(cls, *arg, **kw):
        raise NotImplementedError()

    @classmethod
    def unique_filter(cls, query, *arg, **kw):
        raise NotImplementedError()

    @classmethod
    def as_unique(cls, session, *arg, **kw):
        return _unique(
                    session,
                    cls,
                    cls.unique_hash,
                    cls.unique_filter,
                    cls,
                    arg, kw
            )

And finally creating the unique get_or_create model:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite://', echo=True)

Session = sessionmaker(bind=engine)

class Widget(UniqueMixin, Base):
    __tablename__ = 'widget'

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)

    @classmethod
    def unique_hash(cls, name):
        return name

    @classmethod
    def unique_filter(cls, query, name):
        return query.filter(Widget.name == name)

Base.metadata.create_all(engine)

session = Session()

w1, w2, w3 = Widget.as_unique(session, name='w1'), \
                Widget.as_unique(session, name='w2'), \
                Widget.as_unique(session, name='w3')
w1b = Widget.as_unique(session, name='w1')

assert w1 is w1b
assert w2 is not w3
assert w2 is not w1

session.commit()

The recipe goes deeper into the idea and provides different approaches but I've used this one with great success.

---

The closest semantically is probably:

def get_or_create(model, **kwargs):
    """SqlAlchemy implementation of Django's get_or_create.
    """
    session = Session()
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance, False
    else:
        instance = model(**kwargs)
        session.add(instance)
        session.commit()
        return instance, True

not sure how kosher it is to rely on a globally defined Session in sqlalchemy, but the Django version doesn't take a connection so...

The tuple returned contains the instance and a boolean indicating if the instance was created (i.e. it's False if we read the instance from the db).

Django's get_or_create is often used to make sure that global data is available, so I'm committing at the earliest point possible.

---

Depending on the isolation level you adopted, none of the above solutions would work. The best solution I have found is a RAW SQL in the following form:

INSERT INTO table(f1, f2, unique_f3) 
SELECT 'v1', 'v2', 'v3' 
WHERE NOT EXISTS (SELECT 1 FROM table WHERE f3 = 'v3')

This is transactionally safe whatever the isolation level and the degree of parallelism are.

Beware: in order to make it efficient, it would be wise to have an INDEX for the unique column.

---

I slightly simplified @Kevin. solution to avoid wrapping the whole function in an if/else statement. This way there's only one return, which I find cleaner:

def get_or_create(session, model, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()

    if not instance:
        instance = model(**kwargs)
        session.add(instance)

    return instance

참고URL : https://stackoverflow.com/questions/2546207/does-sqlalchemy-have-an-equivalent-of-djangos-get-or-create