Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/default_comparator.py
2# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8"""Default implementation of SQL comparison operations.
9"""
11from . import operators
12from . import type_api
13from .elements import _clause_element_as_expr
14from .elements import _const_expr
15from .elements import _is_literal
16from .elements import _literal_as_text
17from .elements import and_
18from .elements import BinaryExpression
19from .elements import BindParameter
20from .elements import ClauseElement
21from .elements import ClauseList
22from .elements import collate
23from .elements import CollectionAggregate
24from .elements import ColumnElement
25from .elements import False_
26from .elements import Null
27from .elements import or_
28from .elements import TextClause
29from .elements import True_
30from .elements import Tuple
31from .elements import UnaryExpression
32from .elements import Visitable
33from .selectable import Alias
34from .selectable import ScalarSelect
35from .selectable import Selectable
36from .selectable import SelectBase
37from .. import exc
38from .. import util
41def _boolean_compare(
42 expr,
43 op,
44 obj,
45 negate=None,
46 reverse=False,
47 _python_is_types=(util.NoneType, bool),
48 result_type=None,
49 **kwargs
50):
52 if result_type is None:
53 result_type = type_api.BOOLEANTYPE
55 if isinstance(obj, _python_is_types + (Null, True_, False_)):
57 # allow x ==/!= True/False to be treated as a literal.
58 # this comes out to "== / != true/false" or "1/0" if those
59 # constants aren't supported and works on all platforms
60 if op in (operators.eq, operators.ne) and isinstance(
61 obj, (bool, True_, False_)
62 ):
63 return BinaryExpression(
64 expr,
65 _literal_as_text(obj),
66 op,
67 type_=result_type,
68 negate=negate,
69 modifiers=kwargs,
70 )
71 elif op in (operators.is_distinct_from, operators.isnot_distinct_from):
72 return BinaryExpression(
73 expr,
74 _literal_as_text(obj),
75 op,
76 type_=result_type,
77 negate=negate,
78 modifiers=kwargs,
79 )
80 else:
81 # all other None/True/False uses IS, IS NOT
82 if op in (operators.eq, operators.is_):
83 return BinaryExpression(
84 expr,
85 _const_expr(obj),
86 operators.is_,
87 negate=operators.isnot,
88 type_=result_type,
89 )
90 elif op in (operators.ne, operators.isnot):
91 return BinaryExpression(
92 expr,
93 _const_expr(obj),
94 operators.isnot,
95 negate=operators.is_,
96 type_=result_type,
97 )
98 else:
99 raise exc.ArgumentError(
100 "Only '=', '!=', 'is_()', 'isnot()', "
101 "'is_distinct_from()', 'isnot_distinct_from()' "
102 "operators can be used with None/True/False"
103 )
104 else:
105 obj = _check_literal(expr, op, obj)
107 if reverse:
108 return BinaryExpression(
109 obj, expr, op, type_=result_type, negate=negate, modifiers=kwargs
110 )
111 else:
112 return BinaryExpression(
113 expr, obj, op, type_=result_type, negate=negate, modifiers=kwargs
114 )
117def _custom_op_operate(expr, op, obj, reverse=False, result_type=None, **kw):
118 if result_type is None:
119 if op.return_type:
120 result_type = op.return_type
121 elif op.is_comparison:
122 result_type = type_api.BOOLEANTYPE
124 return _binary_operate(
125 expr, op, obj, reverse=reverse, result_type=result_type, **kw
126 )
129def _binary_operate(expr, op, obj, reverse=False, result_type=None, **kw):
130 obj = _check_literal(expr, op, obj)
132 if reverse:
133 left, right = obj, expr
134 else:
135 left, right = expr, obj
137 if result_type is None:
138 op, result_type = left.comparator._adapt_expression(
139 op, right.comparator
140 )
142 return BinaryExpression(left, right, op, type_=result_type, modifiers=kw)
145def _conjunction_operate(expr, op, other, **kw):
146 if op is operators.and_:
147 return and_(expr, other)
148 elif op is operators.or_:
149 return or_(expr, other)
150 else:
151 raise NotImplementedError()
154def _scalar(expr, op, fn, **kw):
155 return fn(expr)
158def _in_impl(expr, op, seq_or_selectable, negate_op, **kw):
159 seq_or_selectable = _clause_element_as_expr(seq_or_selectable)
161 if isinstance(seq_or_selectable, ScalarSelect):
162 return _boolean_compare(expr, op, seq_or_selectable, negate=negate_op)
163 elif isinstance(seq_or_selectable, SelectBase):
165 # TODO: if we ever want to support (x, y, z) IN (select x,
166 # y, z from table), we would need a multi-column version of
167 # as_scalar() to produce a multi- column selectable that
168 # does not export itself as a FROM clause
170 return _boolean_compare(
171 expr, op, seq_or_selectable.as_scalar(), negate=negate_op, **kw
172 )
173 elif isinstance(seq_or_selectable, (Selectable, TextClause)):
174 return _boolean_compare(
175 expr, op, seq_or_selectable, negate=negate_op, **kw
176 )
177 elif isinstance(seq_or_selectable, ClauseElement):
178 if (
179 isinstance(seq_or_selectable, BindParameter)
180 and seq_or_selectable.expanding
181 ):
183 if isinstance(expr, Tuple):
184 seq_or_selectable = seq_or_selectable._with_expanding_in_types(
185 [elem.type for elem in expr]
186 )
188 return _boolean_compare(
189 expr, op, seq_or_selectable, negate=negate_op
190 )
191 else:
192 raise exc.InvalidRequestError(
193 "in_() accepts"
194 " either a list of expressions, "
195 'a selectable, or an "expanding" bound parameter: %r'
196 % seq_or_selectable
197 )
199 # Handle non selectable arguments as sequences
200 args = []
201 for o in seq_or_selectable:
202 if not _is_literal(o):
203 if not isinstance(o, operators.ColumnOperators):
204 raise exc.InvalidRequestError(
205 "in_() accepts"
206 " either a list of expressions, "
207 'a selectable, or an "expanding" bound parameter: %r' % o
208 )
209 elif o is None:
210 o = Null()
211 else:
212 o = expr._bind_param(op, o)
213 args.append(o)
215 if len(args) == 0:
216 op, negate_op = (
217 (operators.empty_in_op, operators.empty_notin_op)
218 if op is operators.in_op
219 else (operators.empty_notin_op, operators.empty_in_op)
220 )
222 return _boolean_compare(
223 expr,
224 op,
225 ClauseList(_tuple_values=isinstance(expr, Tuple), *args).self_group(
226 against=op
227 ),
228 negate=negate_op,
229 )
232def _getitem_impl(expr, op, other, **kw):
233 if isinstance(expr.type, type_api.INDEXABLE):
234 other = _check_literal(expr, op, other)
235 return _binary_operate(expr, op, other, **kw)
236 else:
237 _unsupported_impl(expr, op, other, **kw)
240def _unsupported_impl(expr, op, *arg, **kw):
241 raise NotImplementedError(
242 "Operator '%s' is not supported on " "this expression" % op.__name__
243 )
246def _inv_impl(expr, op, **kw):
247 """See :meth:`.ColumnOperators.__inv__`."""
248 if hasattr(expr, "negation_clause"):
249 return expr.negation_clause
250 else:
251 return expr._negate()
254def _neg_impl(expr, op, **kw):
255 """See :meth:`.ColumnOperators.__neg__`."""
256 return UnaryExpression(expr, operator=operators.neg, type_=expr.type)
259def _match_impl(expr, op, other, **kw):
260 """See :meth:`.ColumnOperators.match`."""
262 return _boolean_compare(
263 expr,
264 operators.match_op,
265 _check_literal(expr, operators.match_op, other),
266 result_type=type_api.MATCHTYPE,
267 negate=operators.notmatch_op
268 if op is operators.match_op
269 else operators.match_op,
270 **kw
271 )
274def _distinct_impl(expr, op, **kw):
275 """See :meth:`.ColumnOperators.distinct`."""
276 return UnaryExpression(
277 expr, operator=operators.distinct_op, type_=expr.type
278 )
281def _between_impl(expr, op, cleft, cright, **kw):
282 """See :meth:`.ColumnOperators.between`."""
283 return BinaryExpression(
284 expr,
285 ClauseList(
286 _check_literal(expr, operators.and_, cleft),
287 _check_literal(expr, operators.and_, cright),
288 operator=operators.and_,
289 group=False,
290 group_contents=False,
291 ),
292 op,
293 negate=operators.notbetween_op
294 if op is operators.between_op
295 else operators.between_op,
296 modifiers=kw,
297 )
300def _collate_impl(expr, op, other, **kw):
301 return collate(expr, other)
304# a mapping of operators with the method they use, along with
305# their negated operator for comparison operators
306operator_lookup = {
307 "and_": (_conjunction_operate,),
308 "or_": (_conjunction_operate,),
309 "inv": (_inv_impl,),
310 "add": (_binary_operate,),
311 "mul": (_binary_operate,),
312 "sub": (_binary_operate,),
313 "div": (_binary_operate,),
314 "mod": (_binary_operate,),
315 "truediv": (_binary_operate,),
316 "custom_op": (_custom_op_operate,),
317 "json_path_getitem_op": (_binary_operate,),
318 "json_getitem_op": (_binary_operate,),
319 "concat_op": (_binary_operate,),
320 "any_op": (_scalar, CollectionAggregate._create_any),
321 "all_op": (_scalar, CollectionAggregate._create_all),
322 "lt": (_boolean_compare, operators.ge),
323 "le": (_boolean_compare, operators.gt),
324 "ne": (_boolean_compare, operators.eq),
325 "gt": (_boolean_compare, operators.le),
326 "ge": (_boolean_compare, operators.lt),
327 "eq": (_boolean_compare, operators.ne),
328 "is_distinct_from": (_boolean_compare, operators.isnot_distinct_from),
329 "isnot_distinct_from": (_boolean_compare, operators.is_distinct_from),
330 "like_op": (_boolean_compare, operators.notlike_op),
331 "ilike_op": (_boolean_compare, operators.notilike_op),
332 "notlike_op": (_boolean_compare, operators.like_op),
333 "notilike_op": (_boolean_compare, operators.ilike_op),
334 "contains_op": (_boolean_compare, operators.notcontains_op),
335 "startswith_op": (_boolean_compare, operators.notstartswith_op),
336 "endswith_op": (_boolean_compare, operators.notendswith_op),
337 "desc_op": (_scalar, UnaryExpression._create_desc),
338 "asc_op": (_scalar, UnaryExpression._create_asc),
339 "nullsfirst_op": (_scalar, UnaryExpression._create_nullsfirst),
340 "nullslast_op": (_scalar, UnaryExpression._create_nullslast),
341 "in_op": (_in_impl, operators.notin_op),
342 "notin_op": (_in_impl, operators.in_op),
343 "is_": (_boolean_compare, operators.is_),
344 "isnot": (_boolean_compare, operators.isnot),
345 "collate": (_collate_impl,),
346 "match_op": (_match_impl,),
347 "notmatch_op": (_match_impl,),
348 "distinct_op": (_distinct_impl,),
349 "between_op": (_between_impl,),
350 "notbetween_op": (_between_impl,),
351 "neg": (_neg_impl,),
352 "getitem": (_getitem_impl,),
353 "lshift": (_unsupported_impl,),
354 "rshift": (_unsupported_impl,),
355 "contains": (_unsupported_impl,),
356}
359def _check_literal(expr, operator, other, bindparam_type=None):
360 if isinstance(other, (ColumnElement, TextClause)):
361 if isinstance(other, BindParameter) and other.type._isnull:
362 other = other._clone()
363 other.type = expr.type
364 return other
365 elif hasattr(other, "__clause_element__"):
366 other = other.__clause_element__()
367 elif isinstance(other, type_api.TypeEngine.Comparator):
368 other = other.expr
370 if isinstance(other, (SelectBase, Alias)):
371 return other.as_scalar()
372 elif not isinstance(other, Visitable):
373 return expr._bind_param(operator, other, type_=bindparam_type)
374 else:
375 return other