如何使用SQLAlchemy将一个类映射到多个表?

Lets say that I have a database structure with three tables that look like this:

假设我有一个数据库结构，其中有三个表如下所示:

items
 - item_id
 - item_handle

attributes
 - attribute_id
 - attribute_name

item_attributes
 - item_attribute_id
 - item_id
 - attribute_id
 - attribute_value

I would like to be able to do this in SQLAlchemy:

我想在SQLAlchemy中这样做:

item = Item('item1')
item.foo = 'bar'

session.add(item)
session.commit()

item1 = session.query(Item).filter_by(handle='item1').one()
print item1.foo # => 'bar'

I'm new to SQLAlchemy and I found this in the documentation (http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables):

我是SQLAlchemy的新成员，我在文档中发现了这一点(http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class- list - multitables):

j = join(items, item_attributes, items.c.item_id == item_attributes.c.item_id). \
    join(attributes, item_attributes.c.attribute_id == attributes.c.attribute_id)

mapper(Item, j, properties={
    'item_id': [items.c.item_id, item_attributes.c.item_id],
    'attribute_id': [item_attributes.c.attribute_id, attributes.c.attribute_id],
})

It only adds item_id and attribute_id to Item and its not possible to add attributes to Item object.

它只将item_id和attribute_id添加到项中，并且不可能向Item对象添加属性。

Is what I'm trying to achieve possible with SQLAlchemy? Is there a better way to structure the database to get the same behaviour of "dynamic columns"?

我想用SQLAlchemy实现的目标是可能的吗?是否有更好的方法来构造数据库以获得“动态列”的相同行为?

2 个解决方案

#1

This is called the entity-attribute-value pattern. There is an example about this under the SQLAlchemy examples directory: vertical/.

这被称为实体-属性-值模式。在SQLAlchemy示例目录下有一个关于此的示例:vertical/。

If you are using PostgreSQL, then there is also the hstore contrib module that can store a string to string mapping. If you are interested then I have some code for a custom type that makes it possible to use that to store extended attributes via SQLAlchemy.

如果您使用的是PostgreSQL，那么还有hstore设计模块，它可以将字符串存储到字符串映射中。如果您感兴趣，那么我有一些定制类型的代码，可以使用它通过SQLAlchemy存储扩展属性。

Another option to store custom attributes is to serialize them to a text field. In that case you will lose the ability to filter by attributes.

存储自定义属性的另一个选项是将它们序列化为文本字段。在这种情况下，您将失去按属性进行筛选的能力。

#2

The link to vertical/vertical.py is broken. The example had been renamed to dictlike-polymorphic.py and dictlike.py.

垂直/垂直。py坏了。这个例子被重命名为dictlike-polymorphic。py和dictlike.py。

I am pasting in the contents of dictlike.py:

我正在粘贴dictlike.py的内容。

"""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('animal.id'),
               primary_key=True),
        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.

"""

class VerticalProperty(object):
    """A key/value pair.

    This class models rows in the vertical table.
    """

    def __init__(self, key, value):
        self.key = key
        self.value = value

    def __repr__(self):
        return '<%s %r=%r>' % (self.__class__.__name__, self.key, self.value)


class VerticalPropertyDictMixin(object):
    """Adds obj[key] access to a mapped class.

    This is a mixin class.  It can be inherited from directly, or included
    with multiple inheritence.

    Classes using this mixin must define two class properties::

    _property_type:
      The mapped type of the vertical key/value pair instances.  Will be
      invoked with two positional arugments: key, value

    _property_mapping:
      A string, the name of the Python attribute holding a dict-based
      relationship of _property_type instances.

    Using the VerticalProperty class above as an example,::

      class MyObj(VerticalPropertyDictMixin):
          _property_type = VerticalProperty
          _property_mapping = 'props'

      mapper(MyObj, sometable, properties={
        'props': relationship(VerticalProperty,
                          collection_class=attribute_mapped_collection('key'))})

    Dict-like access to MyObj is proxied through to the 'props' relationship::

      myobj['key'] = 'value'
      # ...is shorthand for:
      myobj.props['key'] = VerticalProperty('key', 'value')

      myobj['key'] = 'updated value']
      # ...is shorthand for:
      myobj.props['key'].value = 'updated value'

      print myobj['key']
      # ...is shorthand for:
      print myobj.props['key'].value

    """

    _property_type = VerticalProperty
    _property_mapping = None

    __map = property(lambda self: getattr(self, self._property_mapping))

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

    def __setitem__(self, key, value):
        property = self.__map.get(key, None)
        if property is None:
            self.__map[key] = self._property_type(key, value)
        else:
            property.value = value

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

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

    # Implement other dict methods to taste.  Here are some examples:
    def keys(self):
        return self.__map.keys()

    def values(self):
        return [prop.value for prop in self.__map.values()]

    def items(self):
        return [(key, prop.value) for key, prop in self.__map.items()]

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


if __name__ == '__main__':
    from sqlalchemy import (MetaData, Table, Column, Integer, Unicode,
        ForeignKey, UnicodeText, and_, not_)
    from sqlalchemy.orm import mapper, relationship, create_session
    from sqlalchemy.orm.collections import attribute_mapped_collection

    metadata = MetaData()

    # Here we have named animals, and a collection of facts about them.
    animals = Table('animal', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', Unicode(100)))

    facts = Table('facts', metadata,
                  Column('animal_id', Integer, ForeignKey('animal.id'),
                         primary_key=True),
                  Column('key', Unicode(64), primary_key=True),
                  Column('value', UnicodeText, default=None),)

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

    class Animal(VerticalPropertyDictMixin):
        """An animal.

        Animal facts are available via the 'facts' property or by using
        dict-like accessors on an Animal instance::

          cat['color'] = 'calico'
          # or, equivalently:
          cat.facts['color'] = AnimalFact('color', 'calico')
        """

        _property_type = AnimalFact
        _property_mapping = 'facts'

        def __init__(self, name):
            self.name = name

        def __repr__(self):
            return '<%s %r>' % (self.__class__.__name__, self.name)


    mapper(Animal, animals, properties={
        'facts': relationship(
            AnimalFact, backref='animal',
            collection_class=attribute_mapped_collection('key')),
        })
    mapper(AnimalFact, facts)


    metadata.bind = 'sqlite:///'
    metadata.create_all()
    session = create_session()

    stoat = Animal(u'stoat')
    stoat[u'color'] = u'reddish'
    stoat[u'cuteness'] = u'somewhat'

    # dict-like assignment transparently creates entries in the
    # stoat.facts collection:
    print stoat.facts[u'color']

    session.add(stoat)
    session.flush()
    session.expunge_all()

    critter = session.query(Animal).filter(Animal.name == u'stoat').one()
    print critter[u'color']
    print critter[u'cuteness']

    critter[u'cuteness'] = u'very'

    print 'changing cuteness:'
    metadata.bind.echo = True
    session.flush()
    metadata.bind.echo = False

    marten = Animal(u'marten')
    marten[u'color'] = u'brown'
    marten[u'cuteness'] = u'somewhat'
    session.add(marten)

    shrew = Animal(u'shrew')
    shrew[u'cuteness'] = u'somewhat'
    shrew[u'poisonous-part'] = u'saliva'
    session.add(shrew)

    loris = Animal(u'slow loris')
    loris[u'cuteness'] = u'fairly'
    loris[u'poisonous-part'] = u'elbows'
    session.add(loris)
    session.flush()

    q = (session.query(Animal).
         filter(Animal.facts.any(
           and_(AnimalFact.key == u'color',
                AnimalFact.value == u'reddish'))))
    print 'reddish animals', q.all()

    # Save some typing by wrapping that up in a function:
    with_characteristic = lambda key, value: and_(AnimalFact.key == key,
                                                  AnimalFact.value == value)

    q = (session.query(Animal).
         filter(Animal.facts.any(
           with_characteristic(u'color', u'brown'))))
    print 'brown animals', q.all()

    q = (session.query(Animal).
           filter(not_(Animal.facts.any(
                         with_characteristic(u'poisonous-part', u'elbows')))))
    print 'animals without poisonous-part == elbows', q.all()

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

    # Facts can be queried as well.
    q = (session.query(AnimalFact).
         filter(with_characteristic(u'cuteness', u'very')))
    print 'just the facts', q.all()


    metadata.drop_all()