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/-/9ZJ_qS0UuQkJ.
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.
