Well, you dug into a really esoteric and complex example there.
association_proxy is a much easier way to get around these cases where you want
an object to act like a scalar, so here's that, without all that crazy
boilerplate of the "vertical" example, which I'd avoid as it is really too
complex. Your example seemed undecided about primary key style so I went with
the composite version. Surrogate + composite can't be mixed in a single
table (well it can, but its relationally incorrect. The key should be the
smallest unit that identifies a row - http://en.wikipedia.org/wiki/Unique_key
is a good top level read into various subjects regarding this).
from sqlalchemy import Integer, String, Column, create_engine, ForeignKey,
ForeignKeyConstraint
from sqlalchemy.orm import relationship, Session
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class AnimalFact(Base):
"""key/value attribute whose value can be either a string or a list of
strings"""
__tablename__ = 'animalfacts'
# use either surrogate PK id, or the composite animal_id/key - but
# not both. id/animal_id/key all together is not a proper key.
# Personally I'd go for "id" here, but here's the composite version.
animal_id = Column(Integer, ForeignKey('animal.id'), primary_key=True)
key = Column(String, primary_key=True)
# data
str_value = Column(String)
_list_value = relationship('StringEntry')
# proxy list strings
list_proxy = association_proxy('_list_value', 'value')
def __init__(self, key, value):
self.key = key
self.value = value
@property
def value(self):
if self.str_value is not None:
return self.str_value
else:
return self.list_proxy
@value.setter
def value(self, value):
if isinstance(value, basestring):
self.str_value = value
elif isinstance(value, list):
self.list_proxy = value
else:
assert False
class Animal(Base):
__tablename__ = 'animal'
id = Column(Integer, primary_key=True)
name = Column(String)
_facts = relationship(AnimalFact, backref='animal',
collection_class=attribute_mapped_collection('key'))
facts = association_proxy('_facts', 'value')
def __init__(self, name):
self.name = name
# dictionary interface around "facts".
# I'd just use "animal.facts" here, but here's how to skip that.
def __getitem__(self, key):
return self.facts.__getitem__(key)
def __setitem__(self, key, value):
self.facts.__setitem__(key, value)
def __delitem__(self, key):
self.facts.__delitem__(key)
def __contains__(self, key):
return self.facts.__contains__(key)
def keys(self):
return self.facts.keys()
class StringEntry(Base):
__tablename__ = 'myvalue'
id = Column(Integer, primary_key=True)
animal_id = Column(Integer)
key = Column(Integer)
value = Column(String)
# because AnimalFact has a composite PK, we need
# a composite FK.
__table_args__ = (ForeignKeyConstraint(
['key', 'animal_id'],
['animalfacts.key', 'animalfacts.animal_id']),
)
def __init__(self, value):
self.value = value
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
# create a new animal
a = Animal('aardvark')
a['eyes'] = ['left side', 'right side']
a['cute'] = 'sort of'
session.add(a)
session.commit()
session.close()
for animal in session.query(Animal):
print animal.name, ",".join(["%s" % animal[key] for key in animal.keys()])
On Aug 28, 2012, at 2:43 PM, Jacob Biesinger wrote:
>> Yes, this is the pattern I'm trying to describe though I've been thinking of
>> these as "typed collections", smallish tables for handling a particular
>> table's collections needs, which are all of a single type (so rows are
>> either a collection or a FK to ONE other table). It feels very doable,
>> given the many examples I've seen that don't go quite this far. If it's a
>> common enough use case, why not have an example on the best way to do this?
>
> Uh, scroll up, I said, "which you'll find in the **examples** with the
> distribution". start reading here:
> http://docs.sqlalchemy.org/en/rel_0_7/orm/examples.html#examples-generic-associations
>
>
> I'm sorry to be a pest here. I've read through all the examples and have
> spent several days with the documentation (which is really excellent). I
> think what I'm describing is distinct from these patterns. I'm looking for a
> polymorphic collection type capable of reflecting on the column (at run-time)
> and presenting a different ORM interface. Extending the polymorphic vertical
> column example at http://www.sqlalchemy.org/trac/browser/examples/vertical
> shows what I'm trying to do pretty well. Again, please forgive me if I'm just
> being dense.
>
> # set up declarative tables
> # from http://www.sqlalchemy.org/trac/browser/examples/vertical
> from sqlalchemy_examples.vertical import dictlike_polymorphic as dictlike
>
> metadata = MetaData()
> Base = declarative_base()
> engine = create_engine('sqlite://', echo=True)
> Base.metadata.bind = engine
> session = Session(engine)
>
>
> class AnimalFact(dictlike.PolymorphicVerticalProperty, Base):
> """key/value attribute whose value can be either a string or a list of
> strings"""
> __tablename__ = 'animalfacts'
> type_map = {str: ('string', 'str_value'),
> list: ('list', 'list_value')}
> id = Column(Integer, primary_key=True)
> animal_id = Column(Integer, ForeignKey('animal.id'), primary_key=True)
> key = Column(String, primary_key=True)
> type = Column(String)
> str_value = Column(String)
> list_value = relationship('StringEntry')
>
>
> class Animal(Base, dictlike.VerticalPropertyDictMixin):
> __tablename__ = 'animal'
> _property_type = AnimalFact
> _property_mapping = 'facts'
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
> facts = relationship(AnimalFact, backref='animal',
> collection_class=attribute_mapped_collection('key'))
>
> def __init__(self, name):
> self.name = name
>
>
> class StringEntry(Base):
> __tablename__ = 'myvalue'
> id = Column(Integer, primary_key=True)
> animalfacts_id = Column(Integer, ForeignKey('animalfacts.id'))
> value = Column(String)
>
> def __init__(self, value):
> self.value = value
>
> Base.metadata.create_all()
>
>
> # create a new animal
> a = Animal('aardvark')
>
> # create a new AnimalFact. This uses the list_value column, which is in turn
> a one-to-many relationship to StringEntry
> a['eyes'] = [StringEntry('left side'), StringEntry('right side')] # works
> great
>
> # this should use the str_value column, which is a simple string
> a['cute'] = 'sort of' # fails with message "TypeError: Incompatible
> collection type: None is not list-like"
>
>
> Thanks as always for any help you can give! I'll update the stackoverflow
> question once we get this squared away.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
