From 73013914e7eae2a0480492ece085b48c5938dd84 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 17 Dec 2013 15:13:39 -0500 Subject: - rework JSON expressions to be based off __getitem__ exclusively - add support for "standalone" JSON objects; this involves getting CAST to upgrade the given type of a bound parameter. should add a core-only test for this. - add tests for "standalone" json round trips both with and without unicode - add mechanism by which we remove psycopg2's "json" handler in order to get the effect of using our non-native result handlers --- lib/sqlalchemy/dialects/postgresql/json.py | 136 +++++++++++++++++++++++++++++ 1 file changed, 136 insertions(+) create mode 100644 lib/sqlalchemy/dialects/postgresql/json.py (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py new file mode 100644 index 000000000..5b8ad68f5 --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -0,0 +1,136 @@ +# postgresql/json.py +# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php +from __future__ import absolute_import + +import json + +from .base import ischema_names +from ... import types as sqltypes +from ...sql.operators import custom_op +from ... import util + +__all__ = ('JSON', ) + + +class JSON(sqltypes.TypeEngine): + """Represent the Postgresql JSON type. + + The :class:`.JSON` type stores arbitrary JSON format data, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', JSON) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), + data = {"key1": "value1", "key2": "value2"} + ) + + :class:`.JSON` provides several operations: + + * Index operations:: + + data_table.c.data['some key'] + + * Index operations returning text (required for text comparison or casting):: + + data_table.c.data.astext['some key'] == 'some value' + + * Path index operations:: + + data_table.c.data[('key_1', 'key_2', ..., 'key_n')] + + * Path index operations returning text (required for text comparison or casting):: + + data_table.c.data.astext[('key_1', 'key_2', ..., 'key_n')] == 'some value' + + The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not detect + in-place mutations to the structure. In order to detect these, the + :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will + allow "in-place" changes to the datastructure to produce events which + will be detected by the unit of work. See the example at :class:`.HSTORE` + for a simple example involving a dictionary. + + .. versionadded:: 0.9 + + """ + + __visit_name__ = 'JSON' + + def __init__(self, json_serializer=None, json_deserializer=None): + if json_serializer: + self.json_serializer = json_serializer + else: + self.json_serializer = json.dumps + if json_deserializer: + self.json_deserializer = json_deserializer + else: + self.json_deserializer = json.loads + + class comparator_factory(sqltypes.Concatenable.Comparator): + """Define comparison operations for :class:`.JSON`.""" + + class _astext(object): + def __init__(self, parent): + self.parent = parent + + def __getitem__(self, other): + return self.parent.expr._get_item(other, True) + + def _get_item(self, other, astext): + if hasattr(other, '__iter__') and \ + not isinstance(other, util.string_types): + op = "#>" + other = "{%s}" % (", ".join(util.text_type(elem) for elem in other)) + else: + op = "->" + + if astext: + op += ">" + + # ops: ->, ->>, #>, #>> + return self.expr.op(op, precedence=5)(other) + + def __getitem__(self, other): + """Get the value at a given key.""" + + return self._get_item(other, False) + + @property + def astext(self): + return self._astext(self) + + def _adapt_expression(self, op, other_comparator): + if isinstance(op, custom_op): + if op.opstring == '->': + return op, sqltypes.Text + return sqltypes.Concatenable.Comparator.\ + _adapt_expression(self, op, other_comparator) + + def bind_processor(self, dialect): + if util.py2k: + encoding = dialect.encoding + def process(value): + return self.json_serializer(value).encode(encoding) + else: + def process(value): + return self.json_serializer(value) + return process + + def result_processor(self, dialect, coltype): + if util.py2k: + encoding = dialect.encoding + def process(value): + return self.json_deserializer(value.decode(encoding)) + else: + def process(value): + return self.json_deserializer(value) + return process + + +ischema_names['json'] = JSON -- cgit v1.2.1 From fec03c88d659bf9a0b102dd328afac1ba3dc7f23 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 17 Dec 2013 17:46:09 -0500 Subject: - make the json serializer and deserializer per-dialect, so that we are compatible with psycopg2's per-connection/cursor approach. add round trip tests for both native and non-native. --- lib/sqlalchemy/dialects/postgresql/json.py | 33 +++++++++++++++++------------- 1 file changed, 19 insertions(+), 14 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 5b8ad68f5..7ba8b1abe 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -56,22 +56,25 @@ class JSON(sqltypes.TypeEngine): will be detected by the unit of work. See the example at :class:`.HSTORE` for a simple example involving a dictionary. + Custom serializers and deserializers are specified at the dialect level, + that is using :func:`.create_engine`. The reason for this is that when + using psycopg2, the DBAPI only allows serializers at the per-cursor + or per-connection level. E.g.:: + + engine = create_engine("postgresql://scott:tiger@localhost/test", + json_serializer=my_serialize_fn, + json_deserializer=my_deserialize_fn + ) + + When using the psycopg2 dialect, the json_deserializer is registered + against the database using ``psycopg2.extras.register_default_json``. + .. versionadded:: 0.9 """ __visit_name__ = 'JSON' - def __init__(self, json_serializer=None, json_deserializer=None): - if json_serializer: - self.json_serializer = json_serializer - else: - self.json_serializer = json.dumps - if json_deserializer: - self.json_deserializer = json_deserializer - else: - self.json_deserializer = json.loads - class comparator_factory(sqltypes.Concatenable.Comparator): """Define comparison operations for :class:`.JSON`.""" @@ -113,23 +116,25 @@ class JSON(sqltypes.TypeEngine): _adapt_expression(self, op, other_comparator) def bind_processor(self, dialect): + json_serializer = dialect._json_serializer or json.dumps if util.py2k: encoding = dialect.encoding def process(value): - return self.json_serializer(value).encode(encoding) + return json_serializer(value).encode(encoding) else: def process(value): - return self.json_serializer(value) + return json_serializer(value) return process def result_processor(self, dialect, coltype): + json_deserializer = dialect._json_deserializer or json.loads if util.py2k: encoding = dialect.encoding def process(value): - return self.json_deserializer(value.decode(encoding)) + return json_deserializer(value.decode(encoding)) else: def process(value): - return self.json_deserializer(value) + return json_deserializer(value) return process -- cgit v1.2.1 From 2104d0ba2d612a26d363a3049d5e49efe4284e15 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 27 Dec 2013 18:25:57 -0500 Subject: - rework the JSON expression system so that "astext" is called *after* the indexing. this is for more natural operation. - also add cast() to the JSON expression to complement astext. This integrates the CAST call which will be needed frequently. Part of [ticket:2687]. - it's a little unclear how more advanced unicode attribute-access is going to go, some quick attempts at testing yielded strange error messages from psycopg2. - do other cross linking as mentioned in [ticket:2687]. --- lib/sqlalchemy/dialects/postgresql/json.py | 120 +++++++++++++++++++++-------- 1 file changed, 89 insertions(+), 31 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 7ba8b1abe..7f8aad51e 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -10,9 +10,83 @@ import json from .base import ischema_names from ... import types as sqltypes from ...sql.operators import custom_op +from ... import sql +from ...sql import elements from ... import util -__all__ = ('JSON', ) +__all__ = ('JSON', 'JSONElement') + + +class JSONElement(elements.BinaryExpression): + """Represents accessing an element of a :class:`.JSON` value. + + The :class:`.JSONElement` is produced whenever using the Python index + operator on an expression that has the type :class:`.JSON`:: + + expr = mytable.c.json_data['some_key'] + + The expression typically compiles to a JSON access such as ``col -> key``. + Modifiers are then available for typing behavior, including :meth:`.JSONElement.cast` + and :attr:`.JSONElement.astext`. + + """ + def __init__(self, left, right, astext=False, opstring=None, result_type=None): + self._astext = astext + if opstring is None: + if hasattr(right, '__iter__') and \ + not isinstance(right, util.string_types): + opstring = "#>" + right = "{%s}" % (", ".join(util.text_type(elem) for elem in right)) + else: + opstring = "->" + + self._json_opstring = opstring + operator = custom_op(opstring, precedence=5) + right = left._check_literal(left, operator, right) + super(JSONElement, self).__init__(left, right, operator, type_=result_type) + + @property + def astext(self): + """Convert this :class:`.JSONElement` to use the 'astext' operator + when evaluated. + + E.g.:: + + select([data_table.c.data['some key'].astext]) + + .. seealso:: + + :meth:`.JSONElement.cast` + + """ + if self._astext: + return self + else: + return JSONElement( + self.left, + self.right, + astext=True, + opstring=self._json_opstring + ">", + result_type=sqltypes.String(convert_unicode=True) + ) + + def cast(self, type_): + """Convert this :class:`.JSONElement` to apply both the 'astext' operator + as well as an explicit type cast when evaulated. + + E.g.:: + + select([data_table.c.data['some key'].cast(Integer)]) + + .. seealso:: + + :attr:`.JSONElement.astext` + + """ + if not self._astext: + return self.astext.cast(type_) + else: + return sql.cast(self, type_) class JSON(sqltypes.TypeEngine): @@ -37,17 +111,26 @@ class JSON(sqltypes.TypeEngine): data_table.c.data['some key'] - * Index operations returning text (required for text comparison or casting):: + * Index operations returning text (required for text comparison):: + + data_table.c.data['some key'].astext == 'some value' + + * Index operations with a built-in CAST call:: - data_table.c.data.astext['some key'] == 'some value' + data_table.c.data['some key'].cast(Integer) == 5 * Path index operations:: data_table.c.data[('key_1', 'key_2', ..., 'key_n')] - * Path index operations returning text (required for text comparison or casting):: + * Path index operations returning text (required for text comparison):: - data_table.c.data.astext[('key_1', 'key_2', ..., 'key_n')] == 'some value' + data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == 'some value' + + Index operations return an instance of :class:`.JSONElement`, which represents + an expression such as ``column -> index``. This element then defines + methods such as :attr:`.JSONElement.astext` and :meth:`.JSONElement.cast` + for setting up type behavior. The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the structure. In order to detect these, the @@ -78,35 +161,10 @@ class JSON(sqltypes.TypeEngine): class comparator_factory(sqltypes.Concatenable.Comparator): """Define comparison operations for :class:`.JSON`.""" - class _astext(object): - def __init__(self, parent): - self.parent = parent - - def __getitem__(self, other): - return self.parent.expr._get_item(other, True) - - def _get_item(self, other, astext): - if hasattr(other, '__iter__') and \ - not isinstance(other, util.string_types): - op = "#>" - other = "{%s}" % (", ".join(util.text_type(elem) for elem in other)) - else: - op = "->" - - if astext: - op += ">" - - # ops: ->, ->>, #>, #>> - return self.expr.op(op, precedence=5)(other) - def __getitem__(self, other): """Get the value at a given key.""" - return self._get_item(other, False) - - @property - def astext(self): - return self._astext(self) + return JSONElement(self.expr, other) def _adapt_expression(self, op, other_comparator): if isinstance(op, custom_op): -- cgit v1.2.1 From f89d4d216bd7605c920b7b8a10ecde6bfea2238c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 5 Jan 2014 16:57:05 -0500 Subject: - happy new year --- lib/sqlalchemy/dialects/postgresql/json.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 7f8aad51e..2e29185e8 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -1,5 +1,5 @@ # postgresql/json.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -- cgit v1.2.1