SQLAlchemy Proxy Association

Question

This source details how to use proxy associations to create views and objects with the values ​​of an ORM object.

However, when I add a value that matches an existing object in the database (and the specified value is unique or primary), it creates a conflicting object, so I cannot commit it.

So in my case, this is only useful as a representation, and I will need to use ORM queries to retrieve the object to be added.

Is this my only option or can I use a merge (I can only do this if it is a primary key and not a unique delimiter), or configure the constructor so that it uses an existing object in the database if it exists instead of creating a new object?

For example, from documents:

user.keywords.append('cheese inspector') # Is translated by the association proxy into the operation: user.kw.append(Keyword('cheese inspector')) 

But I would like to translate something more similar: (of course, the request may fail).

 keyword = session.query(Keyword).filter(Keyword.keyword == 'cheese inspector').one() user.kw.append(keyword) 

OR perfect

 user.kw.append(Keyword('cheese inspector')) session.merge() # retrieves identical object from the database, or keeps new one session.commit() # success! 

I suppose this might even be a good idea, but it may be in some cases :)

Answer 1

The example shown on the documentation page that you are referring to is a type of composition relation (in OOP terms) and as such represents a type of owns relationship, rather than uses in terms of verbs. Therefore, each owner will have his own copy of the same (in terms of meaning) keyword.

In fact, you can use the sentence from the documentation that you refer to in your question to create a custom creator method and hack it to reuse an existing object for a given key instead of creating a new one. In this case, the sample function code for User and creator will look like this:

 def _keyword_find_or_create(kw): keyword = Keyword.query.filter_by(keyword=kw).first() if not(keyword): keyword = Keyword(keyword=kw) # if aufoflush=False used in the session, then uncomment below #session.add(keyword) #session.flush() return keyword class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(64)) kw = relationship("Keyword", secondary=lambda: userkeywords_table) keywords = association_proxy('kw', 'keyword', creator=_keyword_find_or_create, # @note: this is the ) 

Answer 2

I recently ran into the same problem. Mike Bayer, creator of SQLAlchemy, refers to a "unique object", but also showed me an option that uses an event listener. The latter approach modifies the association proxy so that UserKeyword.keyword temporarily points to a simple string and only creates a new Keyword object if the keyword does not already exist.

 from sqlalchemy import event # Same User and Keyword classes from documentation class UserKeyword(Base): __tablename__ = 'user_keywords' # Columns user_id = Column(Integer, ForeignKey(User.id), primary_key=True) keyword_id = Column(Integer, ForeignKey(Keyword.id), primary_key=True) special_key = Column(String(50)) # Bidirectional attribute/collection of 'user'/'user_keywords' user = relationship( User, backref=backref( 'user_keywords', cascade='all, delete-orphan' ) ) # Reference to the 'Keyword' object keyword = relationship(Keyword) def __init__(self, keyword=None, user=None, special_key=None): self._keyword_keyword = keyword_keyword # temporary, will turn into a # Keyword when we attach to a # Session self.special_key = special_key @property def keyword_keyword(self): if self.keyword is not None: return self.keyword.keyword else: return self._keyword_keyword @event.listens_for(Session, "after_attach") def after_attach(session, instance): # when UserKeyword objects are attached to a Session, figure out what # Keyword in the database it should point to, or create a new one if isinstance(instance, UserKeyword): with session.no_autoflush: keyword = session.query(Keyword).\ filter_by(keyword=instance._keyword_keyword).\ first() if keyword is None: keyword = Keyword(keyword=instance._keyword_keyword) instance.keyword = keyword