def expect_column_values_to_match_regex_list(self,
column,
regex_list,
match_on="any",
mostly=None,
result_format=None,
include_config=True,
catch_exceptions=None,
meta=None):
if match_on not in ["any", "all"]:
raise ValueError("match_on must be any or all")
regex_fn = self._get_dialect_regex_fn(positive=True)
if regex_fn is None:
logger.warning("Regex is not supported for dialect %s" %
str(self.engine.dialect))
raise NotImplementedError
if match_on == "any":
condition = \
sa.or_(
*[BinaryExpression(sa.column(column), literal(regex), custom_op(regex_fn)) for regex in regex_list]
)
else:
condition = \
sa.and_(
*[BinaryExpression(sa.column(column), literal(regex), custom_op(regex_fn)) for regex in regex_list]
)
return condition
def _do_operate_test(self, operator, right=column('right')):
left = column('left')
assert left.comparator.operate(operator, right).compare(
BinaryExpression(left, right, operator))
assert operator(left,
right).compare(BinaryExpression(left, right, operator))
self._loop_test(operator, right)
def test_notin(self):
left = column('left')
assert left.comparator.operate(operators.notin_op, [1, 2, 3]).compare(
BinaryExpression(
left, Grouping(ClauseList(literal(1), literal(2), literal(3))),
operators.notin_op))
self._loop_test(operators.notin_op, [1, 2, 3])
def apply_soft_delete(expression, deleted_at):
if deleted_at is not None:
expression = BinaryExpression(
expression,
deleted_at.is_(None),
operator.and_,
)
return expression
def expect_column_values_to_not_match_regex_list(self, column, regex_list,
mostly=None,
result_format=None, include_config=False, 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 sa.and_(
*[BinaryExpression(sa.column(column), literal(regex), custom_op(regex_fn)) for regex in regex_list]
)
def _get_dialect_regex_expression(self, column, regex, positive=True):
try:
# postgres
if isinstance(self.engine.dialect, sa.dialects.postgresql.dialect):
if positive:
return BinaryExpression(sa.column(column), literal(regex), custom_op("~"))
else:
return BinaryExpression(sa.column(column), literal(regex), custom_op("!~"))
except AttributeError:
pass
try:
# redshift
if isinstance(self.engine.dialect, sqlalchemy_redshift.dialect.RedshiftDialect):
if positive:
return BinaryExpression(sa.column(column), literal(regex), custom_op("~"))
else:
return BinaryExpression(sa.column(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 isinstance(self.engine.dialect, sa.dialects.mysql.dialect):
if positive:
return BinaryExpression(sa.column(column), literal(regex), custom_op("REGEXP"))
else:
return BinaryExpression(sa.column(column), literal(regex), custom_op("NOT REGEXP"))
except AttributeError:
pass
try:
# Snowflake
if isinstance(self.engine.dialect, snowflake.sqlalchemy.snowdialect.SnowflakeDialect):
if positive:
return BinaryExpression(sa.column(column), literal(regex), custom_op("RLIKE"))
else:
return BinaryExpression(sa.column(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 isinstance(self.engine.dialect, pybigquery.sqlalchemy_bigquery.BigQueryDialect):
if positive:
return sa.func.REGEXP_CONTAINS(sa.column(column), literal(regex))
else:
return sa.not_(sa.func.REGEXP_CONTAINS(sa.column(column), literal(regex)))
except (AttributeError, TypeError): # TypeError can occur if the driver was not installed and so is None
pass
def expression(self, args):
if len(args) == 1:
# it's a list expression
return args[0]
left, op, right = args
inverse = False
if type(op) is tuple:
op = op[0]
inverse = True
exp = BinaryExpression(left, right, op)
if inverse:
exp = UnaryExpression(exp, operator=operator.inv)
return exp