On Aug 27, 2012, at 3:50 PM, Jacob Biesinger wrote: > Hi all, > > Re-asking a question from stackoverflow here. > http://stackoverflow.com/questions/12148503/arbitrary-collections-in-sqlalchemy-with-referential-integrity > > I'm converting a library to use SA as the datastore. I like the flexibility > of the PickleType column, but it doesn't seem to work well when pickling SA > objects (table rows). Even if I overload setstate and getstate to do a query > + session merge when unpickling, there's no referential integrity across that > pickle boundary. That means that I can't query collections of objects. > > class Bar(Base): > id = Column(Integer, primary_key=True) > __tablename__ = 'bars' > foo_id = Column(Integer, ForeignKey('foos.id'), primary_key=True) > > class Foo(Base): > __tablename__ = 'foos' > values = Column(PickleType) > #values = relationship(Bar) # list interface (one->many), but can't > assign a scalar or use a dictionary > def __init__(self): > self.values = [Bar(), Bar()] > > # only allowed with PickleType column > #self.values = Bar() > #self.values = {'one' : Bar()} > #self.values = [ [Bar(), Bar()], [Bar(), Bar()]] > > # get all Foo's with a Bar whose id=1 > session.query(Foo).filter(Foo.values.any(Bar.id == 1)).all() > > One workaround would be to implement my own mutable object type as is done > here: > https://github.com/ccat/sqlalchemy_examples/blob/master/mutable_example/mutable_alwayUpdate.py#L51 > and to have some kind of flattening scheme which traverses the collections > and appends them to a simpler one->many relationship. Perhaps the flattened > list might have to be weakrefs to the pickled collection's objects? Tracking > changes and references sounds like no fun. Any advice?
The PickleType is really a hacky way around edge cases where you have some arbitrary object you'd just like to shove away. It's a given that when you use PickleType, you're giving up any relational advantages, including being able to filter/query on them, etc. So putting an ORM mapped object in a Pickle is basically a terrible idea. If you want a collection of scalar values, use traditional mappings and relationship() in combination with association_proxy. See http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/associationproxy.html#simplifying-scalar-collections . -- 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.
