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 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.
