通过 SQLAlchemy 实现多表映射

在使用 SQLAlchemy 进行多表映射时，我们可以使用 ORM（对象关系映射） 的方式将多个表与 Python 类进行映射。SQLAlchemy 提供了功能强大的机制，能够轻松地将数据库表和 Python 对象之间的关系建立起来。

在这里插入图片描述

1、问题背景

假设我们有一个数据库结构，由三个表组成：

items
- item_id
- item_handleattributes
- attribute_id
- attribute_nameitem_attributes
- item_attribute_id
- item_id
- attribute_id
- attribute_value

我们希望在 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'

但是，对于 SQLAlchemy 新手来说，我们遇到了困难。虽然我们在文档中找到了相关解决方案，但它只允许将 item_id 和 attribute_id 添加到 Item 中，而无法将属性添加到 Item 对象。

2、解决方案

我们可以在 SQLAlchemy 中通过实现“实体-属性-值模式”（Entity-Attribute-Value，EAV）来实现这种多表映射。EAV 是一种数据模型，它将实体的属性存储在一张单独的表中，而不是将它们作为实体本身的列。

一个解决方案是将属性存储在一个文本字段中。这种方法的好处在于它非常直观，并且很容易实现。但是，这种方法的缺点是无法对属性进行过滤。

另一种解决方案是使用 PostgreSQL 中的 hstore 模块，它可以存储字符串到字符串的映射。这种方法的好处是可以对属性进行过滤，但是它要求使用 PostgreSQL 数据库。

下面的代码示例展示了如何使用 SQLAlchemy 实现多表映射：

class VerticalProperty(object):"""A key/value pair.This class models rows in the vertical table."""def __init__(self, key, value):self.key = keyself.value = valuedef __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 includedwith multiple inheritence.Classes using this mixin must define two class properties::_property_type:The mapped type of the vertical key/value pair instances.  Will beinvoked with two positional arugments: key, value_property_mapping:A string, the name of the Python attribute holding a dict-basedrelationship 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].valuedef __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 = valuedef __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())class Animal(VerticalPropertyDictMixin):"""An animal.Animal facts are available via the 'facts' property or by usingdict-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 = namedef __repr__(self):return '<%s %r>' % (self.__class__.__name__, self.name)if __name__ == '__main__':from sqlalchemy import (MetaData, Table, Column, Integer, Unicode,ForeignKey, UnicodeText, and_, not_)from sqlalchemy.orm import mapper, relationship, create_sessionfrom sqlalchemy.orm.collections import attribute_mapped_collectionmetadata = 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."""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'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 = Truesession.flush()metadata.bind.echo = Falsesession.close()