Ah! Thanks so much. I spent the whole morning stepping through this in the
debugger trying to understand what was happening. I finally got to this
point:
When column is JSONDict,
> /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py(886)
_execute_context()
-> if context.compiled:
(Pdb) p context.compiled.bind_names
{BindParameter('%(4561467472 dynamic_properties)s', 'vol_state', type_=
JSONDict()): u'dynamic_properties_1'}
which of course has:
>>> JSONDict()._cached_bind_processor(db.engine.dialect)
<function process at 0x1079881b8>
>>> p = JSONDict()._cached_bind_processor(db.engine.dialect)
>>> p.__name__
'process'
>>> p.__module__
'sqlalchemy.dialects.postgresql.json'
but when the column was a plain JSON I'd get this:
> /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py(886)
_execute_context()
-> if context.compiled:
(Pdb) p context.compiled.bind_names
{BindParameter('%(4561467472 dynamic_properties)s', 'vol_state', type_=
String()): u'dynamic_properties_1'}
I was tearing my hair out trying to understand why it was String in one
instance and JSONDict in the other. Of course your explanation makes
perfect sense.
Now, just to follow up, my real JSONDict actually has the following
implementation:
class JSONDict(TypeDecorator):
"""Backend-agnostic JSON type
Uses native JSON type on postgres or manually serializes/deserializes
JSON to Text on others
"""
impl = JSON
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(JSON())
else:
return dialect.type_descriptor(Text())
def process_bind_param(self, value, dialect):
if value is None or dialect.name == 'postgresql':
return value
else:
return json.dumps(value)
def process_result_value(self, value, dialect):
if value is None or dialect.name == 'postgresql':
return value
elif not value:
return dict()
else:
return json.loads(value)
Do I need to do anything special when defining my coerce_compared_value
function?
Also, a side note, the documentation says that when defining
load_dialect_impl, the impl class variable should just be TypeDecorator as
a placeholder. But, I found that the JSON key indexing wouldn't work
against the pg backend (I'd get an Exception that the impl doesn't define
getitem) unless I set the class impl to JSON.
Thanks for the quick help!
On Wednesday, October 28, 2015 at 10:48:26 AM UTC-4, Michael Bayer wrote:
>
>
>
> 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',).
> >
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
