pg8000 driver and JSON test

[Tony Locke]

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.

Thanks,

Tony.

[Mike Bayer]

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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Tony Locke]

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?

Thanks,

Tony.

> 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+...@googlegroups.com.

[Mike Bayer]

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.