AH ok thanks for the tip. I'll poke around with that and see what I can
come up with.
Thanks for your help
On Wednesday, June 13, 2012 3:11:56 PM UTC-4, Michael Bayer wrote:
>
> there's not a lot of moving parts in SQLA here. the first step would be,
> figure out how this needs to render with psycopg2 directly. If you can get
> it to work with psycopg2 alone, passing the desired value as a bound
> parameter, then we can work out what the type engine needs to do or not.
>
>
> On Jun 13, 2012, at 3:07 PM, Tom Willis wrote:
>
> 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.
>
>
>
--
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/-/MS2M9g2uwrMJ.
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.
