Added :meth:`.ColumnOperators.notin_`,

:meth:`.ColumnOperators.notlike`,
:meth:`.ColumnOperators.notilike` to :class:`.ColumnOperators`.
[ticket:2580]

4 files changed, 101 insertions, 4 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 2efcee98e..999b2ebbc 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -1352,3 +1352,12 @@
       name was omitted which apparently creates the
       index in the default schema, rather than that
       of the table.
+
+    .. change::
+        :tags: sql, feature
+        :tickets: 2580
+
+        Added :meth:`.ColumnOperators.notin_`,
+        :meth:`.ColumnOperators.notlike`,
+        :meth:`.ColumnOperators.notilike` to :class:`.ColumnOperators`.
+
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 5b6e4d82d..1681b26f4 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -2097,6 +2097,8 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "eq": (_boolean_compare, operators.ne),
         "like_op": (_boolean_compare, operators.notlike_op),
         "ilike_op": (_boolean_compare, operators.notilike_op),
+        "notlike_op": (_boolean_compare, operators.like_op),
+        "notilike_op": (_boolean_compare, operators.ilike_op),
         "contains_op": (_boolean_compare, operators.notcontains_op),
         "startswith_op": (_boolean_compare, operators.notstartswith_op),
         "endswith_op": (_boolean_compare, operators.notendswith_op),
@@ -2105,6 +2107,7 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "nullsfirst_op": (_scalar, nullsfirst),
         "nullslast_op": (_scalar, nullslast),
         "in_op": (_in_impl, operators.notin_op),
+        "notin_op": (_in_impl, operators.in_op),
         "is_": (_boolean_compare, operators.is_),
         "isnot": (_boolean_compare, operators.isnot),
         "collate": (_collate_impl,),
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 2e2ff3af1..8c5b9b3d5 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -368,6 +368,51 @@ class ColumnOperators(Operators):
         """
         return self.operate(in_op, other)
 
+    def notin_(self, other):
+        """implement the ``NOT IN`` operator.
+
+        This is equivalent to using negation with :meth:`.ColumnOperators.in_`,
+        i.e. ``~x.in_(y)``.
+
+        .. versionadded:: 0.8
+
+        .. seealso::
+
+            :meth:`.ColumnOperators.in_`
+
+        """
+        return self.operate(notin_op, other)
+
+    def notlike(self, other, escape=None):
+        """implement the ``NOT LIKE`` operator.
+
+        This is equivalent to using negation with :meth:`.ColumnOperators.like`,
+        i.e. ``~x.like(y)``.
+
+        .. versionadded:: 0.8
+
+        .. seealso::
+
+            :meth:`.ColumnOperators.like`
+
+        """
+        return self.operate(notlike_op, other, escape=escape)
+
+    def notilike(self, other, escape=None):
+        """implement the ``NOT ILIKE`` operator.
+
+        This is equivalent to using negation with :meth:`.ColumnOperators.ilike`,
+        i.e. ``~x.ilike(y)``.
+
+        .. versionadded:: 0.8
+
+        .. seealso::
+
+            :meth:`.ColumnOperators.ilike`
+
+        """
+        return self.operate(notilike_op, other, escape=escape)
+
     def is_(self, other):
         """Implement the ``IS`` operator.
 
@@ -583,13 +628,13 @@ def like_op(a, b, escape=None):
     return a.like(b, escape=escape)
 
 def notlike_op(a, b, escape=None):
-    return ~a.like(b, escape=escape)
+    return a.notlike(b, escape=escape)
 
 def ilike_op(a, b, escape=None):
     return a.ilike(b, escape=escape)
 
 def notilike_op(a, b, escape=None):
-    return ~a.ilike(b, escape=escape)
+    return a.notilike(b, escape=escape)
 
 def between_op(a, b, c):
     return a.between(b, c)
@@ -598,7 +643,7 @@ def in_op(a, b):
     return a.in_(b)
 
 def notin_op(a, b):
-    return ~a.in_(b)
+    return a.notin_(b)
 
 def distinct_op(a):
     return a.distinct()
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 48cb40c0a..eb2885658 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1,4 +1,4 @@
-from sqlalchemy.testing import fixtures, eq_
+from sqlalchemy.testing import fixtures, eq_, is_
 from sqlalchemy import testing
 from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.sql import column, desc, asc, literal, collate
@@ -18,6 +18,10 @@ import datetime
 
 from sqlalchemy import text, literal_column
 
+class LoopOperate(operators.ColumnOperators):
+    def operate(self, op, *other, **kwargs):
+        return op
+
 class DefaultColumnComparatorTest(fixtures.TestBase):
 
     def _do_scalar_test(self, operator, compare_to):
@@ -25,6 +29,7 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         assert left.comparator.operate(operator).compare(
             compare_to(left)
         )
+        self._loop_test(operator)
 
     def _do_operate_test(self, operator, right=column('right')):
         left = column('left')
@@ -37,6 +42,15 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
             BinaryExpression(left, right, operator)
         )
 
+        self._loop_test(operator, right)
+
+    def _loop_test(self, operator, *arg):
+        l = LoopOperate()
+        is_(
+            operator(l, *arg),
+            operator
+        )
+
     def test_desc(self):
         self._do_scalar_test(operators.desc_op, desc)
 
@@ -52,6 +66,18 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
     def test_isnot_null(self):
         self._do_operate_test(operators.isnot, None)
 
+    def test_like(self):
+        self._do_operate_test(operators.like_op)
+
+    def test_notlike(self):
+        self._do_operate_test(operators.notlike_op)
+
+    def test_ilike(self):
+        self._do_operate_test(operators.ilike_op)
+
+    def test_notilike(self):
+        self._do_operate_test(operators.notilike_op)
+
     def test_is(self):
         self._do_operate_test(operators.is_)
 
@@ -81,6 +107,20 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
                     operators.in_op
                 )
             )
+        self._loop_test(operators.in_op, [1, 2, 3])
+
+    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 test_collate(self):
         left = column('left')