On Thu, Jun 7, 2018 at 5:46 AM, Tony Locke <tlo...@tlocke.org.uk> wrote: > Thanks for your swift reply Mike. I should have said that I'm changing > the way that pg8000 works so that there's a pg8000.PGJson wrapper for > JSON values. The reason for doing so is to allow pg8000 to send the > correct type code to Postgres. Previously with pg8000, JSON was > represented as a string, and it was sent with the 'unknown' type to > allow Postgres to guess the type, which can cause problems in edge > cases. Anyway, in the SQLAlchemy dialect for pg8000 I now have the > following bind processor that returns a pg8000.PGJson object: > > class _PGJSON(JSON): > def bind_processor(self, dialect): > pg_json = dialect.dbapi.PGJson > > def process(value): > if value is self.NULL: > value = None > elif isinstance(value, Null) or ( > value is None and self.none_as_null): > return None > return pg_json(value) > > return process > > The problem is that now type_coerce returns a pg8000.PGJson type, > rather than a serialized JSON string, causing the > test_crit_against_string_coerce_type test to fail. I wonder if there's > a different approach that I should be taking?
so the test there is to confirm the use case documented at http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.JSON where one wants to compare the string value of a particular element of a JSON dictionary of strings to a specific value. The way we are doing this is somewhat interim, as on Postgresql, the canonical system is to use "astext" which means we're using the Postgresql ->> operator and I've decided we should try to get an "astext" equivalence into the other dialects at some point. However for now, what it means is: CAST(some_json->['some key'] AS VARCHAR) = '<some json value>' In Postgresql, when you pull a value from a json hash, it is itself a JSON datatype. Then, if you cast it as a string, you get the JSON string value, which if it's a single scalar string you get '"some string"'. using type_coerce(val, JSON) means we want to turn "val" into a JSON value, but we *dont* want to CAST it as JSON - we expect that Postgresql is seeing a string representation of a JSON string. I guess because pg8000 uses prepared statements you are able to send the fact that the bind is JSON separately from the value itself, which implies more or less a CAST rendering the type_coerce() as not really useful. So we need to just remove this guidance from http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.JSON to only refer to the "json.dumps()" versions of the recipes given. The type_coerce() version doesn't apply to arbitrary SQL expressions either so there's likely not much point in it. > > Thanks, > > Tony. > > On 6 June 2018 at 14:56, Mike Bayer <mike...@zzzcomputing.com> wrote: >> On Wed, Jun 6, 2018 at 9:04 AM, Tony Locke <tlo...@tlocke.org.uk> wrote: >>> Hi, I'm trying to get the latest pg8000 driver to pass the SQLAlchemy >>> dialect tests. I'm stuck on the following test in test_types.py: >>> >>> def test_crit_against_string_coerce_type(self): >>> name = self.tables.data_table.c.name >>> col = self.tables.data_table.c['data'] >>> >>> self._test_index_criteria( >>> and_(name == 'r6', >>> cast(col["b"], String) == type_coerce("some value", JSON)), >>> "r6", >>> test_literal=False >>> ) >>> >>> This executes the following SQL: >>> >>> SELECT data_table.name >>> FROM data_table >>> WHERE data_table.name = %s AND CAST((data_table.data -> %s) AS VARCHAR) = %s >>> ('r6', 'b', 'some value') >>> >>> the problem is that the: >>> >>> CAST((data_table.data -> %s) AS VARCHAR) >>> >>> gives '"some value"', which of course doesn't equal 'some value', and so the >>> test fails. I'm not sure what I need to fix in the driver to make it work, >>> so any help is greatly appreciated. >> >> type_coerce("some value", JSON) means the value will be run through >> the JSON datatype's bind processor first thus converting it to '"some >> value"'. >> >> using pg8000 1.11.0 the test seems to pass: >> >> $ py.test test/dialect/test_suite.py -k >> test_crit_against_string_coerce_type -s --log-debug=sqlalchemy.engine >> --dburi postgresql+pg8000://scott:tiger@localhost/test >> >> here's the relevant output you can see '"some value"': >> >> INFO:sqlalchemy.engine.base.Engine: >> CREATE TABLE data_table ( >> id SERIAL NOT NULL, >> name VARCHAR(30) NOT NULL, >> data JSON, >> nulldata JSON, >> PRIMARY KEY (id) >> ) >> >> >> INFO:sqlalchemy.engine.base.Engine:() >> INFO:sqlalchemy.engine.base.Engine:COMMIT >> INFO:sqlalchemy.engine.base.Engine:INSERT INTO data_table (name, data) >> VALUES (%s, %s) >> INFO:sqlalchemy.engine.base.Engine:(('r1', '{"key1": "value1", "key2": >> "value2"}'), ('r2', '{"Key \'One\'": "value1", "key two": "value2", >> "key three": "value \' three \'"}'), ('r3', '{"key1": [1, 2, 3], >> "key2": ["one", "two", "three"], "key3": [{"four": "five"}, {"six": >> "seven"}]}'), ('r4', '["one", "two", "three"]'), ('r5', '{"nested": >> {"elem1": [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}], "elem2": >> {"elem3": {"elem4": "elem5"}}}}'), ('r6', '{"a": 5, "b": "some value", >> "c": {"foo": "bar"}}')) >> INFO:sqlalchemy.engine.base.Engine:COMMIT >> INFO:sqlalchemy.engine.base.Engine:SELECT data_table.name >> FROM data_table >> WHERE data_table.name = %s AND CAST((data_table.data -> %s) AS VARCHAR) = %s >> INFO:sqlalchemy.engine.base.Engine:('r6', 'b', '"some value"') >> DEBUG:sqlalchemy.engine.base.Engine:Col (b'name',) >> DEBUG:sqlalchemy.engine.base.Engine:Row ('r6',) >> PASSED >> >> >> >>> >>> Thanks, >>> >>> Tony. >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> 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 https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to a topic in the >> Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/SohtCZ6zmDs/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
