How to avoid inserting duplicate entries when adding values via a sqlalchemy relationship?

Question

Let's assume we have two tables in a many to many relationship as shown below:

class User(db.Model):
  __tablename__ = 'user'
  uid = db.Column(db.String(80), primary_key=True)
  languages = db.relationship('Language', lazy='dynamic',
                              secondary='user_language')

class UserLanguage(db.Model):
  __tablename__ = 'user_language'
  __tableargs__ = (db.UniqueConstraint('uid', 'lid', name='user_language_ff'),)

  id = db.Column(db.Integer, primary_key=True)
  uid = db.Column(db.String(80), db.ForeignKey('user.uid'))
  lid = db.Column(db.String(80), db.ForeignKey('language.lid'))

class Language(db.Model):
  lid = db.Column(db.String(80), primary_key=True)
  language_name = db.Column(db.String(30))

Now in the python shell:

In [4]: user = User.query.all()[0]

In [11]: user.languages = [Language('1', 'English')]

In [12]: db.session.commit()

In [13]: user2 = User.query.all()[1]

In [14]: user2.languages = [Language('1', 'English')]

In [15]: db.session.commit()

IntegrityError: (IntegrityError) column lid is not unique u'INSERT INTO language (lid, language_name) VALUES (?, ?)' ('1', 'English')

How can I let the relationship know that it should ignore duplicates and not break the unique constraint for the Language table? Of course, I could insert each language separately and check if the entry already exists in the table beforehand, but then much of the benefit offered by sqlalchemy relationships is gone.

score 8 · Accepted Answer · edited May 23 '17 at 12:09

The SQLAlchemy wiki has a collection of examples, one of which is how you might check uniqueness of instances.

The examples are a bit convoluted though. Basically, create a classmethod get_unique as an alternate constructor, which will first check a session cache, then try a query for existing instances, then finally create a new instance. Then call Language.get_unique(id, name) instead of Language(id, name).

I've written a more detailed answer in response to OP's bounty on another question.

score 5 · Answer 2 · answered Dec 30 '13 at 12:17

I would suggest to read Association Proxy: Simplifying Association Objects. In this case your code would translate into something like below:

# NEW: need this function to auto-generate the PK for newly created Language
# here using uuid, but could be any generator
def _newid():
    import uuid
    return str(uuid.uuid4())

def _language_find_or_create(language_name):
    language = Language.query.filter_by(language_name=language_name).first()
    return language or Language(language_name=language_name)


class User(Base):
  __tablename__ = 'user'
  uid = Column(String(80), primary_key=True)
  languages = relationship('Language', lazy='dynamic',
                              secondary='user_language')

  # proxy the 'language_name' attribute from the 'languages' relationship
  langs = association_proxy('languages', 'language_name',
            creator=_language_find_or_create,
            )

class UserLanguage(Base):
  __tablename__ = 'user_language'
  __tableargs__ = (UniqueConstraint('uid', 'lid', name='user_language_ff'),)

  id = Column(Integer, primary_key=True)
  uid = Column(String(80), ForeignKey('user.uid'))
  lid = Column(String(80), ForeignKey('language.lid'))

class Language(Base):
  __tablename__ = 'language'
  # NEW: added a *default* here; replace with your implementation
  lid = Column(String(80), primary_key=True, default=_newid)
  language_name = Column(String(30))

# test code
user = User(uid="user-1")
# NEW: add languages using association_proxy property
user.langs.append("English")
user.langs.append("Spanish")
session.add(user)
session.commit()

user2 = User(uid="user-2")
user2.langs.append("English") # this will not create a new Language row...
user2.langs.append("German")
session.add(user2)
session.commit()

`AttributeError: type object 'Language' has no attribute 'query'` — Marco Sulla, May 24 '16 at 08:23
The OP was using `flask-sqlalchemy` which adds this to each model. You can replace it with `session.query(Language)`. — van, May 24 '16 at 16:37
but a query doesn't solve OP's issue, which --in the case of many subsequent insertions, by setting `user.languages` to a collection of `Language` objects and committing -- would be slowed significantly for additions to the database of many users. Is there a solution that creates if not exists (upsert in PostgresQL) more efficiently than checking each time whether it exists? Maybe keep a `dict()` alongside the class of current values if the the list is like, a few hundred strings? — 123, Apr 01 '20 at 20:08

How to avoid inserting duplicate entries when adding values via a sqlalchemy relationship?

2 Answers2