CODEWITHSUNDEEP
sqlalchemy
Derek Litz
Derek Litz

Reputation: 10897

Association Proxy SQLAlchemy

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

Upvotes: 12

Views: 10227

Answers (2)

bfin
bfin

Reputation: 511

I recently ran into the same problem. Mike Bayer, creator of SQLAlchemy, refered me to the “unique object” recipe but also showed me a variant that uses an event listener. The latter approach modifies the association proxy so that UserKeyword.keyword temporarily points to a plain string and only creates a new Keyword object if the keyword doesn't 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

Upvotes: 3

van
van

Reputation: 77072

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 
            )

Upvotes: 12

Related Questions