Q:

Querying a dict collection in SQLAlchemy by key/value pair

I have an SQLAlchemy model that associates tags (key/value pairs) with elements, like so:
from sqlalchemy import create_engine, Column, ForeignKey, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

engine = create_engine("sqlite:///:memory:")
base = declarative_base(bind=engine)

class Tag(base):
    __tablename__ = "tags"

    tag_id = Column(Integer, primary_key=True)

    key = Column(Unicode(256))
    value = Column(Unicode(256))

    def __init__(self, key="", value="", **kwargs):
        self.key = key
        self.value = value
        base.__init__(self, **kwargs)

class Element(base):
    __tablename__ = "elements"

    element_id = Column(Integer, primary_key=True)
    tags = association_proxy("elements_tags", "tag_value",
                             creator=lambda k, v: ElementsTags(tag_key=k, tag_value=v))

class ElementsTags(base):
    __tablename__ = "elements_tags"

    map_id = Column(Integer, primary_key=True)

    element_id = Column(Integer, ForeignKey('elements.element_id'))
    tag_id = Column(Integer, ForeignKey('tags.tag_id'))

    element = relationship(Element, foreign_keys=[element_id],
                           backref=backref("elements_tags",
                                           collection_class=attribute_mapped_collection("tag_key"),
                                           cascade="all, delete-orphan"))

    tag = relationship(Tag, foreign_keys=[tag_id])
    tag_key = association_proxy("tag", "key")
    tag_value = association_proxy("tag", "value")

base.metadata.create_all()

Element.tags is a dictionary collection, with some proxying to make it appear just like a plain dict, which works really well:
session = sessionmaker(bind=engine)()

element = Element()
element.tags = {u"foo": u"bar", u"bang": u"baz"}

session.add(element)
session.commit()

Now, how would I go about getting all Elements that have a certain key/value pair attached as tag, using session.query(Element)?
I found this documentation from which I'd derive session.query(Element).filter(Element.tags.has(key="foo", value="bar")).all(). However, this throws:
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py", line 409, in has
    if self._target_is_object:
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 754, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py", line 244, in _target_is_object
    return getattr(self.target_class, self.value_attr).impl.uses_objects
AttributeError: 'AssociationProxy' object has no attribute 'impl'

A:

Unfortunately, such queries are not yet supported in SQLAlchemy up to 1.1.0~b3. One has to construct the query manually by joining the tables otherwise linked through the association proxy.
Support will come in a future version and there is already a patch: https://bitbucket.org/zzzeek/sqlalchemy/issues/3769/chained-any-has-with-association-proxy
It is also trivially possible to monkey patch the support into current SQLAlchemy versions: https://github.com/Natureshadow/OSMAlchemy/blob/master/osmalchemy/util/patch.py

