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?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/x4PsnsOyGwYJ.
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.
