SQLAlquemy: INSERT only if specific item(s) dont exist

Question

I was looking for a simple way to create an object only if a specific combination of objects doesn't exist.

Example: I have this class. And my goal is to only Insert a new one if the object in "facts" isn't the same.

class Facts(Base):
    __tablename__ = "facts"

    id = sqlColumn(Integer, primary_key=True)
    fact = sqlColumn(String(500))
    created_at = sqlColumn(DateTime)
    updated_at = sqlColumn(DateTime)

I already found this code at Does SQLAlchemy have an equivalent of Django's get_or_create?

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

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

But this doesn't really fit my needs, since the object gets created anyways because of the different timestamp at "created at".

Furthermore I would like to add as much filter elements as I want.

If you define a duplicate as an exact match on the `fact` string attribute then just make that the primary key. — Gord Thompson, Jul 29 '21 at 22:31

score 0 · Accepted Answer · answered Jul 31 '21 at 18:59

You can make the fact column unique across all rows by using a unique constraint, ie. unique=True. Then just pass the datetime columns separately on create only:


class Facts(Base):
    __tablename__ = "facts"

    id = sqlColumn(Integer, primary_key=True)
    # Makes sure there are no duplicate facts on the database level
    fact = sqlColumn(String(500), unique=True)
    created_at = sqlColumn(DateTime)
    updated_at = sqlColumn(DateTime)

def get_or_create(session, model, created_at, **unique_kwargs):
    instance = session.query(model).filter_by(**unique_kwargs).one_or_none()

    if instance:
        return instance
    else:
        instance = model(created_at=created_at, updated_at=created_at, **unique_kwargs)
    session.add(instance)
    return instance

You could use this like:

result = get_or_create(session, Facts, datetime.now(), fact='A new fact')

The drawback of this approach is that one thread using a different session could create a fact at the same time another thread was creating the same thread and the code would fail on commit. The database integrity would be preserved with the constraint. You could remedy this by catching the intregity exception and trying get_or_create again. If you are using postgresql you should do an upsert, explained here: Upsert Documentation

Here is the SQLAlchemy documentation for creating unique constraints: Unique Constraints

Great, thanks for the explanation. I will work on that. – timbeckss Aug 02 '21 at 14:25 — timbeckss, Aug 02 '21 at 14:25

SQLAlquemy: INSERT only if specific item(s) dont exist

1 Answers1