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/__init__.py | 3 +- lib/sqlalchemy/dialects/postgresql/base.py | 3 + lib/sqlalchemy/dialects/postgresql/pgjson.py | 109 +++++++++++++++++++++++++ 3 files changed, 114 insertions(+), 1 deletion(-) create mode 100644 lib/sqlalchemy/dialects/postgresql/pgjson.py (limited to 'lib/sqlalchemy') diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 408b67846..00bbc7268 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -14,6 +14,7 @@ from .base import \ DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All from .constraints import ExcludeConstraint from .hstore import HSTORE, hstore +from .pgjson import JSON from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -23,5 +24,5 @@ __all__ = ( 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON' ) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b80f269c1..6469f3b70 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1187,6 +1187,9 @@ class PGTypeCompiler(compiler.GenericTypeCompiler): def visit_HSTORE(self, type_): return "HSTORE" + def visit_JSON(self, type_): + return "JSON" + def visit_INT4RANGE(self, type_): return "INT4RANGE" 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 ++-- lib/sqlalchemy/dialects/postgresql/psycopg2.py | 17 +++++++++++++++++ 2 files changed, 19 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy') 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. diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index d7ce6eb90..1f4078500 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -168,6 +168,8 @@ from __future__ import absolute_import import re import logging +import psycopg2.extensions as ext + from ... import util, exc import decimal from ... import processors @@ -179,6 +181,7 @@ from .base import PGDialect, PGCompiler, \ ENUM, ARRAY, _DECIMAL_TYPES, _FLOAT_TYPES,\ _INT_TYPES from .hstore import HSTORE +from .pgjson import JSON logger = logging.getLogger('sqlalchemy.dialects.postgresql') @@ -243,6 +246,17 @@ class _PGHStore(HSTORE): else: return super(_PGHStore, self).result_processor(dialect, coltype) + +class _PGJSON(JSON): + # I've omitted the bind processor here because the method of serializing + # involves registering specific types to auto-serialize, and the adapter + # just a thin wrapper over json.dumps. + def result_processor(self, dialect, coltype): + if dialect._has_native_json: + return None + else: + return super(_PGJSON, self).result_processor(dialect, coltype) + # When we're handed literal SQL, ensure it's a SELECT-query. Since # 8.3, combining cursors and "FOR UPDATE" has been fine. SERVER_SIDE_CURSOR_RE = re.compile( @@ -327,6 +341,7 @@ class PGDialect_psycopg2(PGDialect): psycopg2_version = (0, 0) _has_native_hstore = False + _has_native_json = False colspecs = util.update_copy( PGDialect.colspecs, @@ -336,6 +351,7 @@ class PGDialect_psycopg2(PGDialect): sqltypes.Enum: _PGEnum, # needs force_unicode ARRAY: _PGArray, # needs force_unicode HSTORE: _PGHStore, + JSON: _PGJSON } ) @@ -363,6 +379,7 @@ class PGDialect_psycopg2(PGDialect): self._has_native_hstore = self.use_native_hstore and \ self._hstore_oids(connection.connection) \ is not None + self._has_native_json = self.psycopg2_version >= (2, 5) @classmethod def dbapi(cls): -- cgit v1.2.1 From 059039e0f7eaeeaf7ab49181a99a5edeb67d9f28 Mon Sep 17 00:00:00 2001 From: nathan Date: Tue, 10 Dec 2013 10:09:15 -0500 Subject: sqlalchemy/dialects/postgresql/psycopg2: - Removed unneeded import of psycopg2.extensions --- lib/sqlalchemy/dialects/postgresql/psycopg2.py | 2 -- 1 file changed, 2 deletions(-) (limited to 'lib/sqlalchemy') diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index 1f4078500..0dbdfe8fb 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -168,8 +168,6 @@ from __future__ import absolute_import import re import logging -import psycopg2.extensions as ext - from ... import util, exc import decimal from ... import processors -- 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') 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') 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