def test_unary_both_ops(self):
assert_raises_message(
exc.CompileError,
"Unary expression does not support operator and "
"modifier simultaneously",
UnaryExpression(literal("x"),
operator=operators.custom_op("x"),
modifier=operators.custom_op("y")).compile
)
def expect_column_values_to_not_match_regex(self,
column,
regex,
mostly=None,
result_format=None,
include_config=True,
catch_exceptions=None,
meta=None):
regex_fn = self._get_dialect_regex_fn(positive=False)
if regex_fn is None:
logger.warning("Regex is not supported for dialect %s" %
str(self.engine.dialect))
raise NotImplementedError
return BinaryExpression(sa.column(column), literal(regex),
custom_op(regex_fn))
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 = default_comparator._check_literal(
left, operator, right)
super(JSONElement, self).__init__(
left, right, operator, type_=result_type)
def _handle_regex(self, left, op, right, is_array):
oper = "~" if right[0] == 'regex' else "~*"
regex = right[1]
if is_array:
oper += '@'
cop = operators.custom_op(oper)
# this is a hack
compiler.OPERATORS[cop] = ' %s ' % oper
expr = left.any(regex, operator=cop)
if op == 'Eq':
return expr
if op == 'NotEq':
return not_(expr)
else:
if op == 'Eq':
return left.op(oper)(regex)
if op == 'NotEq':
return left.op("!" + oper)(regex)
raise ValueError('Unsupported operation for regex: %s' % op)
def _handle_regex(self, left, op, right, is_array):
oper = "~" if right[0] == 'regex' else "~*"
regex = right[1]
if is_array:
oper += '@'
cop = operators.custom_op(oper)
# this is a hack
compiler.OPERATORS[cop] = ' %s ' % oper
expr = left.any(regex, operator=cop)
if op == 'Eq':
return expr
if op == 'NotEq':
return not_(expr)
else:
if op == 'Eq':
return left.op(oper)(regex)
if op == 'NotEq':
return left.op("!" + oper)(regex)
raise ValueError('Unsupported operation for regex: %s' % op)
def test_custom_op(self, registry):
"""test #3162"""
my_op = operators.custom_op("my_op",
python_impl=lambda a, b: a + "_foo_" + b)
@registry.mapped
class SomeClass:
__tablename__ = "sc"
id = Column(Integer, primary_key=True)
data = Column(String)
@hybrid.hybrid_property
def foo_data(self):
return my_op(self.data, "bar")
eq_(SomeClass(data="data").foo_data, "data_foo_bar")
self.assert_compile(SomeClass.foo_data, "sc.data my_op :data_1")
class BitwiseAnd(ColumnElement):
"""
Bitwise and operation.
"""
type = Integer()
operator = custom_op("&", precedence=6)
def __init__(self, left, right):
if not isinstance(left, Visitable):
left = bindparam("bitand", left, unique=True)
if not isinstance(right, Visitable):
right = bindparam("bitand", right, unique=True)
self.left = left
self.right = right
def self_group(self, against=None):
if is_precedent(self.operator, against):
return Grouping(self)
else:
return self
def issubstructfp(self, other):
return self.operate(
operators.custom_op('<?'), other, result_type=sqltypes.Boolean
)
def tanimoto_sml(self, other):
return self.operate(
operators.custom_op('%'), other, result_type=sqltypes.Boolean
)
def dice_sml(self, other):
return self.operate(
operators.custom_op('#'), other, result_type=sqltypes.Boolean
)
def factorial_prefix(self):
return UnaryExpression(self.expr,
operator=operators.custom_op("!!"),
type_=MyInteger)
from sqlalchemy import types as sqltypes
from sqlalchemy.types import UserDefinedType
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.sql import operators
try:
from sqlalchemy.sql.functions import _FunctionGenerator
except ImportError: # SQLA < 0.9 # pragma: no cover
from sqlalchemy.sql.expression import _FunctionGenerator
EVER_EQUAL_TO = operators.custom_op("?=")
ALWAYS_EQUAL_TO = operators.custom_op("%=")
EVER_DIFFERENT_FROM = operators.custom_op("?<>")
ALWAYS_DIFFERENT_FROM = operators.custom_op("@<>")
EVER_LESS_THAN = operators.custom_op("?<")
EVER_GREATER_THAN = operators.custom_op("?>")
EVER_LESS_THAN_OR_EQUAL_TO = operators.custom_op("?<=")
EVER_GREATER_THAN_OR_EQUAL_TO = operators.custom_op("?>=")
ALWAYS_LESS_THAN = operators.custom_op("%<")
ALWAYS_GREATER_THAN = operators.custom_op("%>")
ALWAYS_LESS_THAN_OR_EQUAL_TO = operators.custom_op("%<=")
ALWAYS_GREATER_THAN_OR_EQUAL_TO = operators.custom_op("%>=")
TEMPORAL_EQUAL = operators.custom_op("#=")
TEMPORAL_NOT_EQUAL = operators.custom_op("#<>")
TEMPORAL_LESS_THAN = operators.custom_op("#<")
TEMPORAL_GREATER_THAN = operators.custom_op("#>")
TEMPORAL_LESS_THAN_OR_EQUAL_TO = operators.custom_op("#<=")
TEMPORAL_GREATER_THAN_OR_EQUAL_TO = operators.custom_op("#>=")
BBOX_ALWAYS_STRICTLY_LESS_THAN = operators.custom_op("<<")
Reference
---------
"""
from sqlalchemy import types as sqltypes
from sqlalchemy.types import UserDefinedType
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.sql import operators
try:
from sqlalchemy.sql.functions import _FunctionGenerator
except ImportError: # SQLA < 0.9 # pragma: no cover
from sqlalchemy.sql.expression import _FunctionGenerator
INTERSECTS = operators.custom_op('&&')
OVERLAPS_OR_TO_LEFT = operators.custom_op('&<')
OVERLAPS_OR_TO_RIGHT = operators.custom_op('&>')
OVERLAPS_OR_BELOW = operators.custom_op('&<|')
TO_LEFT = operators.custom_op('<<')
BELOW = operators.custom_op('<<|')
TO_RIGHT = operators.custom_op('>>')
CONTAINED = operators.custom_op('@')
OVERLAPS_OR_ABOVE = operators.custom_op('|&>')
ABOVE = operators.custom_op('|>>')
CONTAINS = operators.custom_op('~')
SAME = operators.custom_op('~=')
DISTANCE_CENTROID = operators.custom_op('<->')
DISTANCE_BOX = operators.custom_op('<#>')
def not_iregexp(self, other):
return RegexMatchExpression(self.expr, literal(other),
custom_op('!~*'))
)
else:
return array([
self._bind_param(operator, o, _assume_scalar=True, type_=type_)
for o in obj
])
def self_group(self, against=None):
if against in (operators.any_op, operators.all_op, operators.getitem):
return expression.Grouping(self)
else:
return self
CONTAINS = operators.custom_op("@>", precedence=5)
CONTAINED_BY = operators.custom_op("<@", precedence=5)
OVERLAP = operators.custom_op("&&", precedence=5)
class ARRAY(sqltypes.ARRAY):
"""PostgreSQL ARRAY type.
.. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now
a subclass of the core :class:`.types.ARRAY` type.
The :class:`.postgresql.ARRAY` type is constructed in the same way
as the core :class:`.types.ARRAY` type; a member type is required, and a
number of dimensions is recommended if the type is to be used for more
Reference
---------
"""
from sqlalchemy import types as sqltypes
from sqlalchemy.types import UserDefinedType
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.sql import operators
try:
from sqlalchemy.sql.functions import _FunctionGenerator
except ImportError: # SQLA < 0.9 # pragma: no cover
from sqlalchemy.sql.expression import _FunctionGenerator
INTERSECTS = operators.custom_op('&&')
OVERLAPS_OR_TO_LEFT = operators.custom_op('&<')
OVERLAPS_OR_TO_RIGHT = operators.custom_op('&>')
OVERLAPS_OR_BELOW = operators.custom_op('&<|')
TO_LEFT = operators.custom_op('<<')
BELOW = operators.custom_op('<<|')
TO_RIGHT = operators.custom_op('>>')
CONTAINED = operators.custom_op('@')
OVERLAPS_OR_ABOVE = operators.custom_op('|&>')
ABOVE = operators.custom_op('|>>')
CONTAINS = operators.custom_op('~')
SAME = operators.custom_op('~=')
DISTANCE_CENTROID = operators.custom_op('<->')
DISTANCE_BOX = operators.custom_op('<#>')
def __eq__(self, other):
return self.operate(
operators.custom_op('@='), other, result_type=sqltypes.Boolean
)
def get_dialect_regex_expression(column, regex, dialect, positive=True):
try:
# postgres
if issubclass(dialect.dialect, sa.dialects.postgresql.dialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except AttributeError:
pass
try:
# redshift
# noinspection PyUnresolvedReferences
if issubclass(dialect.dialect,
sqlalchemy_redshift.dialect.RedshiftDialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# MySQL
if issubclass(dialect.dialect, sa.dialects.mysql.dialect):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("REGEXP"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT REGEXP"))
except AttributeError:
pass
try:
# Snowflake
if issubclass(
dialect.dialect,
snowflake.sqlalchemy.snowdialect.SnowflakeDialect,
):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("RLIKE"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT RLIKE"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Bigquery
if hasattr(dialect, "BigQueryDialect"):
if positive:
return sa.func.REGEXP_CONTAINS(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_CONTAINS(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
logger.debug(
"Unable to load BigQueryDialect dialect while running get_dialect_regex_expression in expectations.metrics.util",
exc_info=True,
)
pass
try:
# Dremio
if hasattr(dialect, "DremioDialect"):
if positive:
return sa.func.REGEXP_MATCHES(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_MATCHES(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Teradata
if issubclass(dialect.dialect,
teradatasqlalchemy.dialect.TeradataDialect):
if positive:
return sa.func.REGEXP_SIMILAR(column, literal(regex),
literal("i")) == 1
else:
return sa.func.REGEXP_SIMILAR(column, literal(regex),
literal("i")) == 0
except (AttributeError, TypeError):
pass
return None
def issubstructfp(self, other):
return self.operate(
operators.custom_op('<?'), other, result_type=sqltypes.Boolean
)
def dice_sml(self, other):
return self.operate(
operators.custom_op('#'), other, result_type=sqltypes.Boolean
)
def tanimoto_sml(self, other):
return self.operate(
operators.custom_op('%%'), other, result_type=sqltypes.Boolean
)
def __eq__(self, other):
return self.operate(
operators.custom_op('@='), other, result_type=sqltypes.Boolean
)
from __future__ import absolute_import
from .base import colspecs
from .base import ischema_names
from sqlalchemy import types as sqltypes
from sqlalchemy import util
from sqlalchemy.sql import operators
__all__ = ("JSON", "JSONB")
idx_precedence = operators._PRECEDENCE[operators.json_getitem_op]
ASTEXT = operators.custom_op(
"->>",
precedence=idx_precedence,
natural_self_precedent=True,
eager_grouping=True,
)
JSONPATH_ASTEXT = operators.custom_op(
"#>>",
precedence=idx_precedence,
natural_self_precedent=True,
eager_grouping=True,
)
HAS_KEY = operators.custom_op(
"?",
precedence=idx_precedence,
natural_self_precedent=True,
def get_dialect_regex_expression(column, regex, dialect, positive=True):
try:
# postgres
if issubclass(dialect.dialect, sa.dialects.postgresql.dialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except AttributeError:
pass
try:
# redshift
if issubclass(dialect.dialect,
sqlalchemy_redshift.dialect.RedshiftDialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# MySQL
if issubclass(dialect.dialect, sa.dialects.mysql.dialect):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("REGEXP"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT REGEXP"))
except AttributeError:
pass
try:
# Snowflake
if issubclass(
dialect.dialect,
snowflake.sqlalchemy.snowdialect.SnowflakeDialect,
):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("RLIKE"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT RLIKE"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Bigquery
if issubclass(dialect.dialect,
pybigquery.sqlalchemy_bigquery.BigQueryDialect):
if positive:
return sa.func.REGEXP_CONTAINS(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_CONTAINS(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
return None
def factorial(self):
return UnaryExpression(self.expr,
modifier=operators.custom_op("!"),
type_=MyInteger)
def ttl_delete(expr):
return UnaryExpression(expr, modifier=custom_op('DELETE'))
def factorial_prefix(self):
return UnaryExpression(self.expr,
operator=operators.custom_op("!!"),
type_=MyInteger)
def regexp(self, other):
print("EXECUTOU")
return RegexMatchExpression(self.expr, literal(other), custom_op('=~'))
def factorial(self):
return UnaryExpression(self.expr,
modifier=operators.custom_op("!"),
type_=MyInteger)
