¿Cómo asignar una clase contra varias tablas con SQLAlchemy?

Digamos que tengo una estructura de base de datos con tres tablas que se parecen a esto:

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 a Item y no es posible agregar atributos al objeto Item.

¿Lo que estoy tratando de lograr es posible con SQLAlchemy? ¿Existe una mejor manera de estructurar la base de datos para obtener el mismo comportamiento de “columnas dinámicas”?

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

Si está usando PostgreSQL, entonces también está el módulo hstore contrib que puede almacenar una cadena a la cadena de asignación. Si está interesado, tengo algo de código para un tipo personalizado que hace posible 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.

El enlace a vertical / vertical.py está roto. El ejemplo había 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()