Enforcing uniqueness using SQLAlchemy association proxies

Question

I'm trying to use association proxies to make dealing with tag-style records a little simpler, but I'm running into a problem enforcing uniqueness and getting objects to reuse existing tags rather than always create new ones.

Here is a setup similar to what I have. The examples in the documentation have a few recipes for enforcing uniqueness, but they all rely on having access to a session and usually require a single global session, which I cannot do in my case.

from sqlalchemy import Column, Integer, String, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

Base = declarative_base()
engine = create_engine('sqlite://', echo=True)
Session = sessionmaker(bind=engine)


def _tag_find_or_create(name):
    # can't use global objects here, may be multiple sessions and engines
    # ?? No access to session here, how to do a query
    tag = session.query(Tag).filter_by(name=name).first()
    tag = Tag.query.filter_by(name=name).first()
    if not tag:
        tag = Tag(name=name)
    return tag


class Item(Base)
    __tablename__ = 'item'

    id = Column(Integer, primary_key=True)
    tags = relationship('Tag', secondary='itemtag')
    tagnames = association_proxy('tags', 'name', creator=_tag_find_or_create)


class ItemTag(Base)
    __tablename__ = 'itemtag'

    id = Column(Integer, primary_key=True)
    item_id = Column(Integer, ForeignKey('item.id'))
    tag_id = Column(Integer, ForeignKey('tag.id'))


class Tag(Base)
    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False) 


# Scenario 1
session = Session()
item = Item()
session.add(item)
item.tagnames.append('red')

# Scenario 2
item2 = Item()
item2.tagnames.append('blue')
item2.tagnames.append('red')
session.add(item2)

Without the creator function, I just get tons of duplicate Tag items. The creator function seems like the most obvious place to put this type of check, but I'm unsure how to do a query from inside the creator function.

Consider the two scenarios provided at the bottom of the example. In the first example, it seems like there should be a way to get access to the session in the creator function, since the object the tags are being added to is already associated with a session.

In the second example, the Item object isn't yet associated with a session, so the validation check can't happen in the creator function. It would have to happen later when the object is actually added to a session.

For the first scenario, how would I go about getting access to the session object in the creator function?

For the second scenario, is there a way to "listen" for when the parent object is added to a session and validate the association proxies at that point?

Answer 1

For the first scenario, you can use object_session.

As for the question overall: true, you need access to the current session; if using scoped_session in your application is appropriate, then the second part of the Recipe you link to should work fine to use. See Contextual/Thread-local Sessions for more info.

Working with events and change objects when they change from transient to persistent state will not make your code pretty or very robust. So I would immediately add new Tag objects to the session, and if the transaction is rolled back, they would not be in the database.

Note that in a multi-user environment you are likely to have race condition: the same tag is new and created in simultaneously by two users. The user who commits last will fail (if you have a unique constraint on the database). In this case you might consider be without the unique constraint, and have a (daily) procedure to clean those duplicates up (and reassign relations). With time there would be less and less new items, and less possibilities for such clashes.