I went ahead and asked this on stackoverflow if anyone would like to have a stab at it.
http://stackoverflow.com/questions/11021020/emulating-appengine-list-property-with-postgresql-array-and-sqlalchemy On Tuesday, June 12, 2012 7:34:42 AM UTC-4, Tom Willis wrote: > > Hello, > I'm hoping that some of the functionality I actually like in appengine > datastore can be duplicated in postgresql via sqlalchemy. However I'm not > quite grokking how all the moving pieces for a dialect(if that's the right > term) fit together. > > On appengine there are list properties or repeated properties. This is > simply an array of values that has a custom behavior for the "=" in a > query. In that an object with a property named my_list who's value is > [1,2,3] will be true in the following where clauses... > > where my_list=1 > where my_list=2 > where my_list=3 > > I feel like it is possible to get the same kind of behavior on postgresql > with the ARRAY column type, but I'm having trouble finding examples of how > one might do this. > > Here's the code I have so far. > > from sqlalchemy import Column > from sqlalchemy.dialects.postgresql import ARRAY > from sqlalchemy.orm.properties import ColumnProperty > from sqlalchemy.orm import column_property > > > > class ListComparator(ColumnProperty.ColumnComparator): > """ > other operators as they make sense > > optimization: override set operators for one filter instead of > multiples and'd > > todo: non-string types? > """ > def __eq__(self, other): > return self.__clause_element__().op("&&")(u"{%s}" % other) > > > def ListColumn(*args, **kw): > """ > makes a column of an array of types args[0] > and uses ListComparator to emulate appengine list property > """ > if not isinstance(args[0], ARRAY): > largs = list(args) > largs[0] = ARRAY(args[0]) > args = tuple(largs) > else: > raise ValueError("%s is an array which is not allowed" % args[0]) > > return column_property(Column(*args, **kw), > comparator_factory=ListComparator) > > > example usage.... > > > class T(B): > __tablename__ = "t" > id = Column(Integer, primary_key=True) > v = ListColumn(Integer) > > > t = T() > t.v = [1, 2] > S.add(t) > S.commit() > S.query(T).count() > S.query(T).filter(T.v==1).count() > S.query(T).filter_by(v =1).count() > > > > > An array of strings or ints works just fine for this, but any other data > types I would have to convert to strings and in some cases escape for the > array literal syntax in postgresql. It seems like I'm missing some piece of > the puzzle in regards to how all the types in sqla relate to each other for > queries. Is it the dialect the drives that, is there behavior there for > ARRAY that I'm somehow missing or trying to duplicate? > > Thanks in advance for any information you have. > > > -- 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/-/wLflA-VmBmEJ. 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.
