diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 28 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 13 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 23 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 15 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 300 |
6 files changed, 318 insertions, 77 deletions
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 2ca549267..2fe6ea02c 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -879,22 +879,28 @@ class SQLCompiler(Compiled): else: return text + def _get_operator_dispatch(self, operator_, qualifier1, qualifier2): + attrname = "visit_%s_%s%s" % ( + operator_.__name__, qualifier1, + "_" + qualifier2 if qualifier2 else "") + return getattr(self, attrname, None) + def visit_unary(self, unary, **kw): if unary.operator: if unary.modifier: raise exc.CompileError( "Unary expression does not support operator " "and modifier simultaneously") - disp = getattr(self, "visit_%s_unary_operator" % - unary.operator.__name__, None) + disp = self._get_operator_dispatch( + unary.operator, "unary", "operator") if disp: return disp(unary, unary.operator, **kw) else: return self._generate_generic_unary_operator( unary, OPERATORS[unary.operator], **kw) elif unary.modifier: - disp = getattr(self, "visit_%s_unary_modifier" % - unary.modifier.__name__, None) + disp = self._get_operator_dispatch( + unary.modifier, "unary", "modifier") if disp: return disp(unary, unary.modifier, **kw) else: @@ -928,7 +934,7 @@ class SQLCompiler(Compiled): kw['literal_binds'] = True operator_ = override_operator or binary.operator - disp = getattr(self, "visit_%s_binary" % operator_.__name__, None) + disp = self._get_operator_dispatch(operator_, "binary", None) if disp: return disp(binary, operator_, **kw) else: diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 68ea5624e..ddb57da77 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -164,27 +164,7 @@ def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): def _getitem_impl(expr, op, other, **kw): if isinstance(expr.type, type_api.INDEXABLE): - if isinstance(other, slice): - if expr.type.zero_indexes: - other = slice( - other.start + 1, - other.stop + 1, - other.step - ) - other = Slice( - _literal_as_binds( - other.start, name=expr.key, type_=type_api.INTEGERTYPE), - _literal_as_binds( - other.stop, name=expr.key, type_=type_api.INTEGERTYPE), - _literal_as_binds( - other.step, name=expr.key, type_=type_api.INTEGERTYPE) - ) - else: - if expr.type.zero_indexes: - other += 1 - - other = _literal_as_binds( - other, name=expr.key, type_=type_api.INTEGERTYPE) + other = _check_literal(expr, op, other) return _binary_operate(expr, op, other, **kw) else: _unsupported_impl(expr, op, other, **kw) @@ -260,6 +240,8 @@ operator_lookup = { "mod": (_binary_operate,), "truediv": (_binary_operate,), "custom_op": (_binary_operate,), + "json_path_getitem_op": (_binary_operate, ), + "json_getitem_op": (_binary_operate, ), "concat_op": (_binary_operate,), "lt": (_boolean_compare, operators.ge), "le": (_boolean_compare, operators.gt), @@ -295,7 +277,7 @@ operator_lookup = { } -def _check_literal(expr, operator, other): +def _check_literal(expr, operator, other, bindparam_type=None): if isinstance(other, (ColumnElement, TextClause)): if isinstance(other, BindParameter) and \ other.type._isnull: @@ -310,7 +292,7 @@ def _check_literal(expr, operator, other): if isinstance(other, (SelectBase, Alias)): return other.as_scalar() elif not isinstance(other, Visitable): - return expr._bind_param(operator, other) + return expr._bind_param(operator, other, type_=bindparam_type) else: return other diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 70046c66b..774e42609 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -682,9 +682,10 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): def reverse_operate(self, op, other, **kwargs): return op(other, self.comparator, **kwargs) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, + type_=type_, _compared_to_type=self.type, unique=True) @property @@ -1952,11 +1953,12 @@ class Tuple(ClauseList, ColumnElement): def _select_iterable(self): return (self, ) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return Tuple(*[ BindParameter(None, o, _compared_to_operator=operator, - _compared_to_type=type_, unique=True) - for o, type_ in zip(obj, self._type_tuple) + _compared_to_type=compared_to_type, unique=True, + type_=type_) + for o, compared_to_type in zip(obj, self._type_tuple) ]).self_group() @@ -3637,10 +3639,11 @@ class ColumnClause(Immutable, ColumnElement): else: return name - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.key, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) def _make_proxy(self, selectable, name=None, attach=True, diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 6cfbd12b3..3c654bf67 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -256,16 +256,18 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return self.select().execute() - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, - _compared_to_type=self.type, unique=True) + _compared_to_type=self.type, unique=True, + type_=type_) def self_group(self, against=None): # for the moment, we are parenthesizing all array-returning # expressions against getitem. This may need to be made # more portable if in the future we support other DBs # besides postgresql. - if against is operators.getitem: + if against is operators.getitem and \ + isinstance(self.type, sqltypes.ARRAY): return Grouping(self) else: return super(FunctionElement, self).self_group(against=against) @@ -423,10 +425,11 @@ class Function(FunctionElement): FunctionElement.__init__(self, *clauses, **kw) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.name, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) @@ -659,7 +662,7 @@ class array_agg(GenericFunction): """support for the ARRAY_AGG function. The ``func.array_agg(expr)`` construct returns an expression of - type :class:`.Array`. + type :class:`.types.ARRAY`. e.g.:: @@ -670,11 +673,11 @@ class array_agg(GenericFunction): .. seealso:: :func:`.postgresql.array_agg` - PostgreSQL-specific version that - returns :class:`.ARRAY`, which has PG-specific operators added. + returns :class:`.postgresql.ARRAY`, which has PG-specific operators added. """ - type = sqltypes.Array + type = sqltypes.ARRAY def __init__(self, *args, **kwargs): args = [_literal_as_binds(c) for c in args] @@ -694,7 +697,7 @@ class OrderedSetAgg(GenericFunction): func_clauses = self.clause_expr.element order_by = sqlutil.unwrap_order_by(within_group.order_by) if self.array_for_multi_clause and len(func_clauses.clauses) > 1: - return sqltypes.Array(order_by[0].type) + return sqltypes.ARRAY(order_by[0].type) else: return order_by[0].type @@ -719,7 +722,7 @@ class percentile_cont(OrderedSetAgg): modifier to supply a sort expression to operate upon. The return type of this function is the same as the sort expression, - or if the arguments are an array, an :class:`.Array` of the sort + or if the arguments are an array, an :class:`.types.ARRAY` of the sort expression's type. .. versionadded:: 1.1 @@ -736,7 +739,7 @@ class percentile_disc(OrderedSetAgg): modifier to supply a sort expression to operate upon. The return type of this function is the same as the sort expression, - or if the arguments are an array, an :class:`.Array` of the sort + or if the arguments are an array, an :class:`.types.ARRAY` of the sort expression's type. .. versionadded:: 1.1 diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index da3576466..f4f90b664 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -12,7 +12,6 @@ from .. import util - from operator import ( and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg, getitem, lshift, rshift @@ -720,7 +719,6 @@ def istrue(a): def isfalse(a): raise NotImplementedError() - def is_(a, b): return a.is_(b) @@ -837,6 +835,14 @@ def nullslast_op(a): return a.nullslast() +def json_getitem_op(a, b): + raise NotImplementedError() + + +def json_path_getitem_op(a, b): + raise NotImplementedError() + + _commutative = set([eq, ne, add, mul]) _comparison = set([eq, ne, lt, gt, ge, le, between_op, like_op]) @@ -879,7 +885,8 @@ def mirror(op): _associative = _commutative.union([concat_op, and_, or_]) -_natural_self_precedent = _associative.union([getitem]) +_natural_self_precedent = _associative.union([ + getitem, json_getitem_op, json_path_getitem_op]) """Operators where if we have (a op b) op c, we don't want to parenthesize (a op b). @@ -894,6 +901,8 @@ _PRECEDENCE = { from_: 15, any_op: 15, all_op: 15, + json_getitem_op: 15, + json_path_getitem_op: 15, getitem: 15, mul: 8, truediv: 8, diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 4abb9b15a..b65d39ba1 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -11,9 +11,12 @@ import datetime as dt import codecs +import collections +import json +from . import elements from .type_api import TypeEngine, TypeDecorator, to_instance -from .elements import quoted_name, TypeCoerce as type_coerce, _defer_name +from .elements import quoted_name, TypeCoerce as type_coerce, _defer_name, Slice, _literal_as_binds from .. import exc, util, processors from .base import _bind_or_error, SchemaEventTarget from . import operators @@ -85,20 +88,16 @@ class Indexable(object): """ - zero_indexes = False - """if True, Python zero-based indexes should be interpreted as one-based - on the SQL expression side.""" - class Comparator(TypeEngine.Comparator): def _setup_getitem(self, index): raise NotImplementedError() def __getitem__(self, index): - operator, adjusted_right_expr, result_type = \ + adjusted_op, adjusted_right_expr, result_type = \ self._setup_getitem(index) return self.operate( - operator, + adjusted_op, adjusted_right_expr, result_type=result_type ) @@ -1496,7 +1495,221 @@ class Interval(_DateAffinity, TypeDecorator): return self.impl.coerce_compared_value(op, value) -class Array(Indexable, Concatenable, TypeEngine): +class JSON(Indexable, TypeEngine): + """Represent a SQL JSON type. + + .. note:: :class:`.types.JSON` is provided as a facade for vendor-specific + JSON types. Since it supports JSON SQL operations, it only + works on backends that have an actual JSON type, currently + Postgresql as well as certain versions of MySQL. + + :class:`.types.JSON` is part of the Core in support of the growing + popularity of native JSON datatypes. + + The :class:`.types.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"} + ) + + The base :class:`.types.JSON` provides these two operations: + + * Keyed index operations:: + + data_table.c.data['some key'] + + * Integer index operations:: + + data_table.c.data[3] + + * Path index operations:: + + data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] + + Additional operations are available from the dialect-specific versions + of :class:`.types.JSON`, such as :class:`.postgresql.JSON` and + :class:`.postgresql.JSONB`, each of which offer more operators than + just the basic type. + + Index operations return an expression object whose type defaults to + :class:`.JSON` by default, so that further JSON-oriented instructions + may be called upon the result type. + + 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. + + When working with NULL values, the :class:`.JSON` type recommends the + use of two specific constants in order to differentiate between a column + that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string + of ``"null"``. To insert or select against a value that is SQL NULL, + use the constant :func:`.null`:: + + from sqlalchemy import null + conn.execute(table.insert(), json_value=null()) + + To insert or select against a value that is JSON ``"null"``, use the + constant :attr:`.JSON.NULL`:: + + conn.execute(table.insert(), json_value=JSON.NULL) + + The :class:`.JSON` type supports a flag + :paramref:`.JSON.none_as_null` which when set to True will result + in the Python constant ``None`` evaluating to the value of SQL + NULL, and when set to False results in the Python constant + ``None`` evaluating to the value of JSON ``"null"``. The Python + value ``None`` may be used in conjunction with either + :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL + values, but care must be taken as to the value of the + :paramref:`.JSON.none_as_null` in these cases. + + .. seealso:: + + :class:`.postgresql.JSON` + + :class:`.postgresql.JSONB` + + :class:`.mysql.JSON` + + .. versionadded:: 1.1 + + + """ + __visit_name__ = 'JSON' + + hashable = False + NULL = util.symbol('JSON_NULL') + """Describe the json value of NULL. + + This value is used to force the JSON value of ``"null"`` to be + used as the value. A value of Python ``None`` will be recognized + either as SQL NULL or JSON ``"null"``, based on the setting + of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL` + constant can be used to always resolve to JSON ``"null"`` regardless + of this setting. This is in contrast to the :func:`.sql.null` construct, + which always resolves to SQL NULL. E.g.:: + + from sqlalchemy import null + from sqlalchemy.dialects.postgresql import JSON + + obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL + obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" + + session.add_all([obj1, obj2]) + session.commit() + + """ + + def __init__(self, none_as_null=False): + """Construct a :class:`.types.JSON` type. + + :param none_as_null=False: if True, persist the value ``None`` as a + SQL NULL value, not the JSON encoding of ``null``. Note that + when this flag is False, the :func:`.null` construct can still + be used to persist a NULL value:: + + from sqlalchemy import null + conn.execute(table.insert(), data=null()) + + .. seealso:: + + :attr:`.types.JSON.NULL` + + """ + self.none_as_null = none_as_null + + class JSONIndexType(TypeEngine): + """Placeholder for the datatype of a JSON index value. + + This allows execution-time processing of JSON index values + for special syntaxes. + + """ + + class JSONPathType(TypeEngine): + """Placeholder type for JSON path operations. + + This allows execution-time processing of a path-based + index value into a specific SQL syntax. + + """ + + class Comparator(Indexable.Comparator, Concatenable.Comparator): + """Define comparison operations for :class:`.types.JSON`.""" + + @util.dependencies('sqlalchemy.sql.default_comparator') + def _setup_getitem(self, default_comparator, index): + if not isinstance(index, util.string_types) and \ + isinstance(index, collections.Sequence): + index = default_comparator._check_literal( + self.expr, operators.json_path_getitem_op, + index, bindparam_type=JSON.JSONPathType + ) + + operator = operators.json_path_getitem_op + else: + index = default_comparator._check_literal( + self.expr, operators.json_getitem_op, + index, bindparam_type=JSON.JSONIndexType + ) + operator = operators.json_getitem_op + + return operator, index, self.type + + comparator_factory = Comparator + + @property + def should_evaluate_none(self): + return not self.none_as_null + + @util.memoized_property + def _str_impl(self): + return String(convert_unicode=True) + + def bind_processor(self, dialect): + string_process = self._str_impl.bind_processor(dialect) + + json_serializer = dialect._json_serializer or json.dumps + + def process(value): + if value is self.NULL: + value = None + elif isinstance(value, elements.Null) or ( + value is None and self.none_as_null + ): + return None + + serialized = json_serializer(value) + if string_process: + serialized = string_process(serialized) + return serialized + + return process + + def result_processor(self, dialect, coltype): + string_process = self._str_impl.result_processor(dialect, coltype) + json_deserializer = dialect._json_deserializer or json.loads + + def process(value): + if value is None: + return None + if string_process: + value = string_process(value) + return json_deserializer(value) + return process + + +class ARRAY(Indexable, Concatenable, TypeEngine): """Represent a SQL Array type. .. note:: This type serves as the basis for all ARRAY operations. @@ -1506,17 +1719,17 @@ class Array(Indexable, Concatenable, TypeEngine): with PostgreSQL, as it provides additional operators specific to that backend. - :class:`.Array` is part of the Core in support of various SQL standard + :class:`.types.ARRAY` is part of the Core in support of various SQL standard functions such as :class:`.array_agg` which explicitly involve arrays; however, with the exception of the PostgreSQL backend and possibly some third-party dialects, no other SQLAlchemy built-in dialect has support for this type. - An :class:`.Array` type is constructed given the "type" + An :class:`.types.ARRAY` type is constructed given the "type" of element:: mytable = Table("mytable", metadata, - Column("data", Array(Integer)) + Column("data", ARRAY(Integer)) ) The above type represents an N-dimensional array, @@ -1529,11 +1742,11 @@ class Array(Indexable, Concatenable, TypeEngine): data=[1,2,3] ) - The :class:`.Array` type can be constructed given a fixed number + The :class:`.types.ARRAY` type can be constructed given a fixed number of dimensions:: mytable = Table("mytable", metadata, - Column("data", Array(Integer, dimensions=2)) + Column("data", ARRAY(Integer, dimensions=2)) ) Sending a number of dimensions is optional, but recommended if the @@ -1555,10 +1768,10 @@ class Array(Indexable, Concatenable, TypeEngine): >>> expr = table.c.column[5] # returns ARRAY(Integer, dimensions=1) >>> expr = expr[6] # returns Integer - For 1-dimensional arrays, an :class:`.Array` instance with no + For 1-dimensional arrays, an :class:`.types.ARRAY` instance with no dimension parameter will generally assume single-dimensional behaviors. - SQL expressions of type :class:`.Array` have support for "index" and + SQL expressions of type :class:`.types.ARRAY` have support for "index" and "slice" behavior. The Python ``[]`` operator works normally here, given integer indexes or slices. Arrays default to 1-based indexing. The operator produces binary expression @@ -1575,9 +1788,9 @@ class Array(Indexable, Concatenable, TypeEngine): mytable.c.data[2:7]: [1, 2, 3] }) - The :class:`.Array` type also provides for the operators - :meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all`. - The PostgreSQL-specific version of :class:`.Array` also provides additional + The :class:`.types.ARRAY` type also provides for the operators + :meth:`.types.ARRAY.Comparator.any` and :meth:`.types.ARRAY.Comparator.all`. + The PostgreSQL-specific version of :class:`.types.ARRAY` also provides additional operators. .. versionadded:: 1.1.0 @@ -1589,9 +1802,13 @@ class Array(Indexable, Concatenable, TypeEngine): """ __visit_name__ = 'ARRAY' + zero_indexes = False + """if True, Python zero-based indexes should be interpreted as one-based + on the SQL expression side.""" + class Comparator(Indexable.Comparator, Concatenable.Comparator): - """Define comparison operations for :class:`.Array`. + """Define comparison operations for :class:`.types.ARRAY`. More operators are available on the dialect-specific form of this type. See :class:`.postgresql.ARRAY.Comparator`. @@ -1601,11 +1818,32 @@ class Array(Indexable, Concatenable, TypeEngine): def _setup_getitem(self, index): if isinstance(index, slice): return_type = self.type - elif self.type.dimensions is None or self.type.dimensions == 1: - return_type = self.type.item_type + if self.type.zero_indexes: + index = slice( + index.start + 1, + index.stop + 1, + index.step + ) + index = Slice( + _literal_as_binds( + index.start, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.stop, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.step, name=self.expr.key, + type_=type_api.INTEGERTYPE) + ) else: - adapt_kw = {'dimensions': self.type.dimensions - 1} - return_type = self.type.adapt(self.type.__class__, **adapt_kw) + if self.type.zero_indexes: + index += 1 + if self.type.dimensions is None or self.type.dimensions == 1: + return_type = self.type.item_type + else: + adapt_kw = {'dimensions': self.type.dimensions - 1} + return_type = self.type.adapt( + self.type.__class__, **adapt_kw) return operators.getitem, index, return_type @@ -1635,7 +1873,7 @@ class Array(Indexable, Concatenable, TypeEngine): :func:`.sql.expression.any_` - :meth:`.Array.Comparator.all` + :meth:`.types.ARRAY.Comparator.all` """ operator = operator if operator else operators.eq @@ -1670,7 +1908,7 @@ class Array(Indexable, Concatenable, TypeEngine): :func:`.sql.expression.all_` - :meth:`.Array.Comparator.any` + :meth:`.types.ARRAY.Comparator.any` """ operator = operator if operator else operators.eq @@ -1683,18 +1921,18 @@ class Array(Indexable, Concatenable, TypeEngine): def __init__(self, item_type, as_tuple=False, dimensions=None, zero_indexes=False): - """Construct an :class:`.Array`. + """Construct an :class:`.types.ARRAY`. E.g.:: - Column('myarray', Array(Integer)) + Column('myarray', ARRAY(Integer)) Arguments are: :param item_type: The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like - ``INTEGER[][]``, are constructed as ``Array(Integer)``, not as - ``Array(Array(Integer))`` or such. + ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as + ``ARRAY(ARRAY(Integer))`` or such. :param as_tuple=False: Specify whether return results should be converted to tuples from lists. This parameter is @@ -1706,7 +1944,7 @@ class Array(Indexable, Concatenable, TypeEngine): on the database, how it goes about interpreting Python and result values, as well as how expression behavior in conjunction with the "getitem" operator works. See the description at - :class:`.Array` for additional detail. + :class:`.types.ARRAY` for additional detail. :param zero_indexes=False: when True, index values will be converted between Python zero-based and SQL one-based indexes, e.g. @@ -1714,7 +1952,7 @@ class Array(Indexable, Concatenable, TypeEngine): to the database. """ - if isinstance(item_type, Array): + if isinstance(item_type, ARRAY): raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " "handles multi-dimensional arrays of basetype") if isinstance(item_type, type): |