Association Proxy SQLAlchemy

Question

This source details how to use association proxies to create views and objects with values of an ORM object.

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

So in my case is this only useful as a view, and I'll need to use ORM queries to retrieve the object to be appended.

Is this my only option or can I use merge (I may only be able to do this if it's a primary key and not a unique constraint), OR set up the constructor such that it will use an existing object in the database if it exists instead of creating a new object?

For example from the docs:

user.keywords.append('cheese inspector')

# Is translated by the association proxy into the operation:

user.kw.append(Keyword('cheese inspector'))

But I'd like to to be translated to something more like: (of course the query could fail).

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

OR ideally

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

I suppose this may not even be a good idea, but it could be in certain use cases :)

Answer 1

The example shown on the documentation page you link to is a composition type of relationship (in OOP terms) and as such represents the owns type of relationship rather then uses in terms of verbs. Therefore each owner would have its own copy of the same (in terms of value) keyword.

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

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 
            )