On Aug 27, 2012, at 3:54 PM, Michael Bayer wrote: > > 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 > .
re: "or dictionaries". Use attribute_mapped_collection: http://docs.sqlalchemy.org/en/rel_0_7/orm/collections.html#dictionary-collections "dictionaries plus scalars": combine both attribute_mapped_collection and association_proxy: http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/associationproxy.html#proxying-to-dictionary-based-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.
