On Mon, Aug 27, 2012 at 4:16 PM, Jacob Biesinger <jake.biesin...@gmail.com>wrote:
> 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. > Is the use case of having nested collections really all that out of mainstream? It seems like a fairly straightforward requirement, though perhaps with a lot of boilerplate. A simple list-of-lists seems achievable using something like: all_lists = Table('all_lists', metadata, Column('id', Integer, primary_key=True), Column('type', String(32)), # polymorphic type... one of 'list' or 'scalar' Column('scalar_id', Integer, ForeignKey('scalars.id')), Column('list_id', Integer, ForeignKey('all_lists.id'))) with an appropriate polymorphic identifier. Am I going down a dead-end here? -- 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.
