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.