From 64288c7d6ffc021e2388aa764e9a3b921506c7a0 Mon Sep 17 00:00:00 2001 From: nathan Date: Mon, 9 Dec 2013 11:46:36 -0500 Subject: sqlalchemy/dialects/postgresql/__init__.py: - Added import references to JSON class sqlalchemy/dialects/postgresql/base.py: - Added visitor method for JSON class sqlalchemy/dialects/postgresql/pgjson (new): - JSON class, supports automatic serialization and deserialization of json data, as well as basic json operators. --- lib/sqlalchemy/dialects/postgresql/pgjson.py | 109 +++++++++++++++++++++++++++ 1 file changed, 109 insertions(+) create mode 100644 lib/sqlalchemy/dialects/postgresql/pgjson.py (limited to 'lib/sqlalchemy/dialects/postgresql/pgjson.py') diff --git a/lib/sqlalchemy/dialects/postgresql/pgjson.py b/lib/sqlalchemy/dialects/postgresql/pgjson.py new file mode 100644 index 000000000..aef54709b --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/pgjson.py @@ -0,0 +1,109 @@ +# 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 + +import json + +from .base import ARRAY, ischema_names +from ... import types as sqltypes +from ...sql import functions as sqlfunc +from ...sql.operators import custom_op +from ... import util + +__all__ = ('JSON', 'json') + + +class JSON(sqltypes.TypeEngine): + """Represent the Postgresql HSTORE 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 two operations: + + * Index operations:: + + data_table.c.data['some key'] == 'some value' + + * Path Index operations:: + + data_table.c.data.get_path('{key_1, key_2, ..., key_n}'] + + Please be aware that when used with the SQL Alchemy ORM, you will need to + replace the JSON object present on an attribute with a new object in order + for any changes to be properly persisted. + + .. 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`.""" + + def __getitem__(self, other): + """Text expression. Get the value at a given key.""" + # I'm choosing to return text here so the result can be cast, + # compared with strings, etc. + # + # The only downside to this is that you cannot dereference more + # than one level deep in json structures, though comparator + # support for multi-level dereference is lacking anyhow. + return self.expr.op('->>', precedence=5)(other) + + def get_path(self, other): + """Text expression. Get the value at a given path. Paths are of + the form {key_1, key_2, ..., key_n}.""" + return self.expr.op('#>>', precedence=5)(other) + + 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 f285b3536fe01f21409e201fbeeac559ab423a9d Mon Sep 17 00:00:00 2001 From: nathan Date: Tue, 10 Dec 2013 10:01:51 -0500 Subject: sqlalchemy/dialects/postgresql/pgjson: - Fixed reference to HSTORE - Corrected spelling of SQLAlchemy sqlalchemy/dialects/postgresql/psycopg2: - Added psycopg2 specific wrapper type for JSON which uses inherent json deserialization facilities - Added code to detect and utilize the JSON wrapper if psycopg2 >= 2.5 test/dialect/postgresql/test_types: - removed reference to use_native_hstore --- lib/sqlalchemy/dialects/postgresql/pgjson.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/pgjson.py') diff --git a/lib/sqlalchemy/dialects/postgresql/pgjson.py b/lib/sqlalchemy/dialects/postgresql/pgjson.py index aef54709b..161fe83fa 100644 --- a/lib/sqlalchemy/dialects/postgresql/pgjson.py +++ b/lib/sqlalchemy/dialects/postgresql/pgjson.py @@ -16,7 +16,7 @@ __all__ = ('JSON', 'json') class JSON(sqltypes.TypeEngine): - """Represent the Postgresql HSTORE type. + """Represent the Postgresql JSON type. The :class:`.JSON` type stores arbitrary JSON format data, e.g.:: @@ -41,7 +41,7 @@ class JSON(sqltypes.TypeEngine): data_table.c.data.get_path('{key_1, key_2, ..., key_n}'] - Please be aware that when used with the SQL Alchemy ORM, you will need to + Please be aware that when used with the SQLAlchemy ORM, you will need to replace the JSON object present on an attribute with a new object in order for any changes to be properly persisted. -- cgit v1.2.1 From 015c73c83a1bd915cd34cc61370a6d89228c40d6 Mon Sep 17 00:00:00 2001 From: nathan Date: Wed, 11 Dec 2013 10:21:08 -0500 Subject: sqlalchemy/dialects/postgresql/pgjson: - Added support for additional operators - Made return as json default (rather than text) --- lib/sqlalchemy/dialects/postgresql/pgjson.py | 16 ++++++++++++++-- 1 file changed, 14 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/pgjson.py') diff --git a/lib/sqlalchemy/dialects/postgresql/pgjson.py b/lib/sqlalchemy/dialects/postgresql/pgjson.py index 161fe83fa..b41446dae 100644 --- a/lib/sqlalchemy/dialects/postgresql/pgjson.py +++ b/lib/sqlalchemy/dialects/postgresql/pgjson.py @@ -39,7 +39,7 @@ class JSON(sqltypes.TypeEngine): * Path Index operations:: - data_table.c.data.get_path('{key_1, key_2, ..., key_n}'] + data_table.c.data.get_path("'{key_1, key_2, ..., key_n}'"] Please be aware that when used with the SQLAlchemy ORM, you will need to replace the JSON object present on an attribute with a new object in order @@ -71,11 +71,23 @@ class JSON(sqltypes.TypeEngine): # The only downside to this is that you cannot dereference more # than one level deep in json structures, though comparator # support for multi-level dereference is lacking anyhow. + return self.expr.op('->', precedence=5)(other) + + def get_item_as_text(self, other): + """Text expression. Get the value at the given key as text. Use + this when you need to cast the type of the returned value.""" return self.expr.op('->>', precedence=5)(other) def get_path(self, other): - """Text expression. Get the value at a given path. Paths are of + """Text expression. Get the value at a given path. Paths are of the form {key_1, key_2, ..., key_n}.""" + return self.expr.op('#>', precedence=5)(other) + + def get_path_as_text(self, other): + """Text expression. Get the value at a given path, as text. + Paths are of the form '{key_1, key_2, ..., key_n}' (quotes are + required). Use this when you need to cast the type of the + returned value.""" return self.expr.op('#>>', precedence=5)(other) def _adapt_expression(self, op, other_comparator): -- cgit v1.2.1 From c64b7aabab3357b6587a23cf010c4299479291d0 Mon Sep 17 00:00:00 2001 From: nathan Date: Wed, 11 Dec 2013 10:27:10 -0500 Subject: sqlalchemy/dialects/postgresql/pgjson: - Updated documentation for JSON class --- lib/sqlalchemy/dialects/postgresql/pgjson.py | 21 ++++++++++++++------- 1 file changed, 14 insertions(+), 7 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/pgjson.py') diff --git a/lib/sqlalchemy/dialects/postgresql/pgjson.py b/lib/sqlalchemy/dialects/postgresql/pgjson.py index b41446dae..a29d0bbcc 100644 --- a/lib/sqlalchemy/dialects/postgresql/pgjson.py +++ b/lib/sqlalchemy/dialects/postgresql/pgjson.py @@ -31,15 +31,23 @@ class JSON(sqltypes.TypeEngine): data = {"key1": "value1", "key2": "value2"} ) - :class:`.JSON` provides two operations: + :class:`.JSON` provides several operations: * Index operations:: - data_table.c.data['some key'] == 'some value' + data_table.c.data['some key'] - * Path Index operations:: + * Index operations returning text (required for text comparison or casting):: - data_table.c.data.get_path("'{key_1, key_2, ..., key_n}'"] + data_table.c.data.get_item_as_text('some key') == 'some value' + + * Path index operations:: + + data_table.c.data.get_path("{key_1, key_2, ..., key_n}") + + * Path index operations returning text (required for text comparison or casting):: + + data_table.c.data.get_path("{key_1, key_2, ..., key_n}") == 'some value' Please be aware that when used with the SQLAlchemy ORM, you will need to replace the JSON object present on an attribute with a new object in order @@ -85,9 +93,8 @@ class JSON(sqltypes.TypeEngine): def get_path_as_text(self, other): """Text expression. Get the value at a given path, as text. - Paths are of the form '{key_1, key_2, ..., key_n}' (quotes are - required). Use this when you need to cast the type of the - returned value.""" + Paths are of the form {key_1, key_2, ..., key_n}. Use this when + you need to cast the type of the returned value.""" return self.expr.op('#>>', precedence=5)(other) def _adapt_expression(self, op, other_comparator): -- cgit v1.2.1