python - SQLAlchemy，防止重复行

Question

我想知道是否可以防止向数据库提交重复项。例如假设有一个类如下

class Employee(Base):
   id = Column(Integer, primary_key=True)
   name = Column(String)

如果我要制作一系列这些元素，

employee1 = Employee(name='bob')
employee2 = Employee(name='bob')

session.add_all([employee1, employee2])
session.commit()

我只想将一行添加到数据库中，employee1 和 employee2 指向内存中的同一个对象(如果可能)。

SQLAlchemy 中是否有实现此功能的功能？或者我是否需要确保以编程方式不存在重复项？

Answer 1

另一种 get_or_create() 解决方案:

from sqlalchemy.orm.exc import NoResultFound
# ...

def get_or_create(self, model, **kwargs):
    """
    Usage:
    class Employee(Base):
        __tablename__ = 'employee'
        id = Column(Integer, primary_key=True)
        name = Column(String, unique=True)

    get_or_create(Employee, name='bob')
    """
    instance = get_instance(model, **kwargs)
    if instance is None:
        instance = create_instance(model, **kwargs)
    return instance


def create_instance(model, **kwargs):
    """create instance"""
    try:
        instance = model(**kwargs)
        sess.add(instance)
        sess.flush()
    except Exception as msg:
        mtext = 'model:{}, args:{} => msg:{}'
        log.error(mtext.format(model, kwargs, msg))
        sess.rollback()
        raise(msg)
    return instance


def get_instance(self, model, **kwargs):
    """Return first instance found."""
    try:
        return sess.query(model).filter_by(**kwargs).first()
    except NoResultFound:
        return