On Mon, Aug 27, 2012 at 3:56 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> > 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. > > Point taken. > > 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 > > Thanks, I saw this example before but hadn't thought of it in this way. I suppose the creator here could be a polymorphic vertical column as in http://www.sqlalchemy.org/trac/browser/examples/vertical/dictlike-polymorphic.py, which could act as a scalar, as a list, or as a dictionary depending on an introspected type. Querying such a collection seems like it would be a pain though. -- 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.
