当前位置：首页 > article >正文

通过 SQLAlchemy 实现多表映射

article 2025/2/11 18:11:03

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

在这里插入图片描述

1、问题背景

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

items
- item_id
- item_handle

attributes
- attribute_id
- attribute_name

item_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 = 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())


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)


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."""

    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 = True
    session.flush()
    metadata.bind.echo = False

    session.close()