Source code for examples.vertical.dictlike

"""Mapping a vertical table as a dictionary.

This example illustrates accessing and modifying a "vertical" (or
"properties", or pivoted) table via a dict-like interface.  These are tables
that store free-form object properties as rows instead of columns.  For
example, instead of::

  # A regular ("horizontal") table has columns for 'species' and 'size'
  Table('animal', metadata,
        Column('id', Integer, primary_key=True),
        Column('species', Unicode),
        Column('size', Unicode))

A vertical table models this as two tables: one table for the base or parent
entity, and another related table holding key/value pairs::

  Table('animal', metadata,
        Column('id', Integer, primary_key=True))

  # The properties table will have one row for a 'species' value, and
  # another row for the 'size' value.
  Table('properties', metadata
        Column('animal_id', Integer, ForeignKey(''),
        Column('key', UnicodeText),
        Column('value', UnicodeText))

Because the key/value pairs in a vertical scheme are not fixed in advance,
accessing them like a Python dict can be very convenient.  The example below
can be used with many common vertical schemas as-is or with minor adaptations.


from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy import UnicodeText
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.collections import attribute_keyed_dict

class ProxiedDictMixin:
    """Adds obj[key] access to a mapped class.

    This class basically proxies dictionary access to an attribute
    called ``_proxied``.  The class which inherits this class
    should have an attribute called ``_proxied`` which points to a dictionary.


    def __len__(self):
        return len(self._proxied)

    def __iter__(self):
        return iter(self._proxied)

    def __getitem__(self, key):
        return self._proxied[key]

    def __contains__(self, key):
        return key in self._proxied

    def __setitem__(self, key, value):
        self._proxied[key] = value

    def __delitem__(self, key):
        del self._proxied[key]

if __name__ == "__main__":
    Base = declarative_base()

    class AnimalFact(Base):
        """A fact about an animal."""

        __tablename__ = "animal_fact"

        animal_id = Column(ForeignKey(""), primary_key=True)
        key = Column(Unicode(64), primary_key=True)
        value = Column(UnicodeText)

    class Animal(ProxiedDictMixin, Base):
        """an Animal"""

        __tablename__ = "animal"

        id = Column(Integer, primary_key=True)
        name = Column(Unicode(100))

        facts = relationship(
            "AnimalFact", collection_class=attribute_keyed_dict("key")

        _proxied = association_proxy(
            creator=lambda key, value: AnimalFact(key=key, value=value),

        def __init__(self, name):
   = name

        def __repr__(self):
            return "Animal(%r)" %

        def with_characteristic(self, key, value):
            return self.facts.any(key=key, value=value)

    engine = create_engine("sqlite://")

    session = Session(bind=engine)

    stoat = Animal("stoat")
    stoat["color"] = "reddish"
    stoat["cuteness"] = "somewhat"

    # dict-like assignment transparently creates entries in the
    # stoat.facts collection:


    critter = session.query(Animal).filter( == "stoat").one()

    critter["cuteness"] = "very"

    print("changing cuteness:")

    marten = Animal("marten")
    marten["color"] = "brown"
    marten["cuteness"] = "somewhat"

    shrew = Animal("shrew")
    shrew["cuteness"] = "somewhat"
    shrew["poisonous-part"] = "saliva"

    loris = Animal("slow loris")
    loris["cuteness"] = "fairly"
    loris["poisonous-part"] = "elbows"

    q = session.query(Animal).filter(
            and_(AnimalFact.key == "color", AnimalFact.value == "reddish")
    print("reddish animals", q.all())

    q = session.query(Animal).filter(
        Animal.with_characteristic("color", "brown")
    print("brown animals", q.all())

    q = session.query(Animal).filter(
        ~Animal.with_characteristic("poisonous-part", "elbows")
    print("animals without poisonous-part == elbows", q.all())

    q = session.query(Animal).filter(Animal.facts.any(value="somewhat"))
    print('any animal with any .value of "somewhat"', q.all())