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.

Community · Accepted Answer · 2017-05-23 12:09:24Z

8

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.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Dec 30, 2013 at 5:46

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

van · Accepted Answer · 2013-12-30 12:17:22Z

5

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()

answered Dec 30, 2013 at 12:17

van

77.6k13 gold badges174 silver badges179 bronze badges

3 Comments

Marco Sulla Over a year ago

AttributeError: type object 'Language' has no attribute 'query'

van Over a year ago

The OP was using flask-sqlalchemy which adds this to each model. You can replace it with session.query(Language).

123 Over a year ago

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?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related