On 10/27/15 9:01 PM, Uri Okrent wrote:
> Hello, I've created a TypeDecorator for use with postgresql's JSON type,
> for the purpose of adapting it to sqlite and it's producing an incorrect
> bind parameter when using JSON's column index operation.
>
> I'm using sqlalchemy 0.9.4 (I haven't been able to install a more
> updated version as yet, but I didn't see anything mentioning behavior
> like this in the changelogs so I thought I'd ask the mailing list).
>
> I have the following class:
> |
> classDynamicProperties(Base):
> __tablename__ ='dynamic_properties'
> guid =Column(Text,primary_key=True)
> dynamic_properties =Column(JSONDict)
> |
>
> Where JSONDict is simply this:
> |
> classJSONDict(TypeDecorator):
> """Backend-agnostic JSON type
>
> Uses native JSON type on postgres or manually serializes/deserializes
> JSON to Text on others
>
> """
> impl =JSON
OK we have a documentation problem here, what you need to know for now
is just do this:
class Foo(TypeDecorator):
impl = JSON
def coerce_compared_value(self, op, value):
return self.impl.coerce_compared_value(op, value)
the TypeDecorator normally assumes that in comparison operations, you'd
want all the things you compare your custom type towards to also be of
that type. But a comparison using "->", you don't want the right side
to be a JSON because it's going to JSON-encode the index.
I don't have an idea at the moment how to make this easier to know, I
certainly didn't remember it either so nobody else will.
> |
>
> I removed the actual implementation of the TypeDecorator since this
> alone seems to be enough to trigger the issue.
>
> If I create a query like this:
> |
>>>>q =session.query(DynamicProperties.dynamic_properties['vol_state'])
>>>>q.all()
> |
>
> It produces this:
> |
> 2015-10-2802:51:48,445-sqlalchemy.engine.base.Engine.db -INFO
> -base::_execute_context:903-SELECT dynamic_properties.dynamic_properties
> ->%(dynamic_properties_1)s AS anon_1
> FROM dynamic_properties
> 2015-10-2802:51:48,445-sqlalchemy.engine.base.Engine.db -INFO
> -base::_execute_context:905-{'dynamic_properties_1':'"vol_state"'}
> |
>
> It looks like the "vol_state" bind parameter is actually being
> serialized and this always produces a result of (None,) even though the
> 'vol_state' key is in the JSON dict.
>
> If I change the Column type in the class definition from my JSONDict to
> plain old JSON:
> |
> classDynamicProperties(Base):
> __tablename__ ='dynamic_properties'
> guid =Column(Text,primary_key=True)
> dynamic_properties =Column(JSON)
> |
>
> Then the same query produces a correctly quoted bind param:
> |
> 2015-10-2802:57:05,060-sqlalchemy.engine.base.Engine.xmsdb -INFO
> -base::_execute_context:903-SELECT dynamic_properties.dynamic_properties
> ->%(dynamic_properties_1)s AS anon_1
> FROM dynamic_properties
> 2015-10-2802:57:05,061-sqlalchemy.engine.base.Engine.xmsdb -INFO
> -base::_execute_context:905-{'dynamic_properties_1':'vol_state'}
> |
>
> and a corresponding correct result: (u'active',).
>
> Any clues?
>
> TIA,
> Uri
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
