python - postgres - ¿Cómo asignar una clase a varias tablas con SQLAlchemy?

sqlalchemy postgresql python

Question

Digamos que tengo una estructura de base de datos con tres tablas que se ven así:

items
 - item_id
 - item_handle

attributes
 - attribute_id
 - attribute_name

item_attributes
 - item_attribute_id
 - item_id
 - attribute_id
 - attribute_value

Me gustaría poder hacer esto en 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''

Soy nuevo en SQLAlchemy y encontré esto en la documentación ( http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables ):

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],
})

Solo agrega item_id y attribute_id al Item y no es posible agregar atributos al objeto Item.

¿Lo que intento lograr es posible con SQLAlchemy? ¿Hay una mejor manera de estructurar la base de datos para obtener el mismo comportamiento de "columnas dinámicas"?

Answer 1

El enlace a vertical / vertical.py está roto. El ejemplo ha sido renombrado a dictlike-polymorphic.py y dictlike.py.

Estoy pegando en los contenidos de 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()

Answer 2

Esto se llama patrón de entidad-valor-atributo . Hay un ejemplo sobre esto en el directorio de ejemplos de SQLAlchemy: vertical / .

Si está utilizando PostgreSQL, también existe el módulo contrib hstore que puede almacenar una cadena para mapeo de cadenas. Si está interesado, tengo un código para un tipo personalizado que permite usarlo para almacenar atributos extendidos a través de SQLAlchemy.

Otra opción para almacenar atributos personalizados es serializarlos en un campo de texto. En ese caso, perderá la capacidad de filtrar por atributos.