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