Well I had written another type to emulate appengines Key, basically an
object that will serialize/deserialize itself to json when asked and what I
thought were all the mechanics needed for type coercion in sa.
from sqlalchemy import types as sqltypes
from sqlalchemy import UnicodeText
import json
class Key(object):
"""
identifier very similar to appengines key
serialization via json
_provider is set when session is created and we're assuming it's
done once
"""
_provider = None
def __init__(self, kind, id, ns=None):
self._identity = dict(kind=str(kind),
id=int(id),
ns=ns)
@property
def kind(self):
return self._identity["kind"]
@property
def id(self):
return self._identity["id"]
@property
def ns(self):
return self._identity["ns"]
def __repr__(self):
return "Key(%s)" % self.serialize()
def serialize(self):
return json.dumps(self._identity)
@classmethod
def deserialize(cls, k):
value = json.loads(k)
return cls(**value)
def get(self):
assert self._provider, "_provider not set"
return self._provider.get_for_key(self)
class KEY(sqltypes.TypeEngine):
"""
Key Ref type for use in columns
"""
__visit_name__ = UnicodeText.__visit_name__
def __init__(self, kind=None):
self._kind = kind
def _assert_kind(self, value):
if value and self._kind:
assert value.kind == self._kind,\
"%s != %s" % (value.kind, self._kind)
def bind_processor(self, dialect):
def x(value):
self._assert_kind(value)
if value is not None:
return value.serialize()
else:
return value
return x
def result_processor(self, dialect, coltype):
def x(value):
self._assert_kind(value)
if value is not None:
return Key.deserialize(value)
else:
return None
return x
So that + the code I wrote previously I figured I could do something like
this...
class T(B):
__tablename__ = "t"
id = Column(Integer, primary_key=True)
v = ListColumn(KEY)
kv = Key(**dict(kind=B.__name__, id=1, ns=1))
kv2 = Key(**dict(kind=B.__name__, id=2, ns=2))
t = T()
t.v = [kv, kv2]
S.add(t)
S.commit()
self.assert_(S.query(T).filter_by(v=kv).count())
self.assert_(S.query(T).filter_by(v=kv2).count())
Running this I get a statement error because whats passed in to the
ListComparator is str(Key) not Key.serialize() which I thought would have
happened due to the KEY class.
E DataError: (DataError) malformed array literal: "{Key({"kind":
"Base", "ns": 1, "id": 1})}"
E LINE 4: WHERE t.v && '{Key({"kind": "Base", "ns": 1, "id": 1})}')
AS...
E ^
E 'SELECT count(*) AS count_1 \nFROM (SELECT t.v AS t_v, t.id AS
t_id \nFROM t \nWHERE t.v && %(v_1)s) AS anon_1' {'v_1': u'{Key({"kind":
"Base", "ns": 1, "id": 1})}'}
if I temporarily make the comparator call serialize() on what is passed in
I get a different DataError
def do_execute(self, cursor, statement, parameters, context=None):
> cursor.execute(statement, parameters)
E DataError: (DataError) malformed array literal: "{{"kind": "Base",
"ns": 1, "id": 1}}"
E LINE 4: WHERE t.v && '{{"kind": "Base", "ns": 1, "id": 1}}') AS
anon...
E ^
E 'SELECT count(*) AS count_1 \nFROM (SELECT t.v AS t_v, t.id AS
t_id \nFROM t \nWHERE t.v && %(v_1)s) AS anon_1' {'v_1': u'{{"kind":
"Base", "ns": 1, "id": 1}}'}
So now it's not escaped properly which I guess I can accept but it seems
like I'm doing it wrong and really there's some other piece that should
know how to make an array literal out of what is passed to it somewhere.
But as I said in my original email, I don't think I fully grok all the
moving parts in sqla.
On Wednesday, June 13, 2012 2:34:38 PM UTC-4, Michael Bayer wrote:
>
> I wouldn't be too optimistic about an SO response on this one as the "guts
> of SQLA" questions all hang out there until I answer them anyway.
>
> haven't had time to play with this, but if the question regards the
> "insides" of the ARRAY, ARRAY accepts another SQLAlchemy data type as the
> argument for the elements, which does whatever is needed to get the type
> along to the database.
>
> psycopg2 itself may or may not handle all the built-in types correctly
> here; for example, an ARRAY of dates or intervals might have quirks.
> psycopg2 also has its own type coercion customization mechanics which may
> or may not need to be involved.
>
> What specifically is the type that is failing ?
>
>
>
> On Jun 13, 2012, at 2:24 PM, Tom Willis wrote:
>
> 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.
>
>
>
--
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/-/qX1bnfLFB5MJ.
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.
