Repository: kayak/pypika
Pull request #25 (2016-12-06 16:49:02)

Added isnotin method to Term

coveralls: 
[![Coverage Status](https://coveralls.io/builds/9152269/badge)](https://coveralls.io/builds/9152269)

Coverage decreased (-0.02%) to 98.306% when pulling **3a9cdbede56e0a9f0bda290a7d088a4403d9281c on x8lucas8x:add-not-in-term** into **9b959cb50a66b2519b11e7f9b18e15208ad62835 on kayak:master**.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/9166161/badge)](https://coveralls.io/builds/9166161)

Coverage increased (+0.03%) to 98.357% when pulling **ad30546c653b2bc305c823732ce58e87ce11bd68 on x8lucas8x:add-not-in-term** into **9b959cb50a66b2519b11e7f9b18e15208ad62835 on kayak:master**.

x8lucas8x: @twheys Please review this again.

Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index f0a30b4..c641bb3 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -53,10 +53,10 @@ class Term(object):
         return self == other
 
     def isnull(self):
-        return NullCriterion(self, True)
+        return NullCriterion(self)
 
     def notnull(self):
-        return NullCriterion(self, False)
+        return self.isnull().negate()
 
     def gt(self, other):
         return self > other
@@ -87,6 +87,9 @@ class Term(object):
             return ContainsCriterion(self, ListField([self._wrap(value) for value in arg]))
         return ContainsCriterion(self, arg)
 
+    def notin(self, arg):
+        return self.isin(arg).negate()
+
     def bin_regex(self, pattern):
         return BasicCriterion(Matching.bin_regex, self, self._wrap(pattern))
 
@@ -268,6 +271,9 @@ class ListField(object):
 
 
 class Criterion(Term):
+    def __init__(self, alias=None):
+        super(Criterion, self).__init__(alias)
+        self._is_negated = False
 
     def __and__(self, other):
         return ComplexCriterion(Boolean.and_, self, other)
@@ -278,6 +284,11 @@ class Criterion(Term):
     def __xor__(self, other):
         return ComplexCriterion(Boolean.xor_, self, other)
 
+    @builder
+    def negate(self):
+        self._is_negated = True
+        return self
+
     def fields(self):
         raise NotImplementedError()
 
@@ -287,7 +298,7 @@ class Criterion(Term):
 class BasicCriterion(Criterion):
     def __init__(self, comparator, left, right, alias=None):
         """
-        A wrapper for a basic criterion such as equality or inequality.  This wraps three parts, a left and right term
+        A wrapper for a basic criterion such as equality or inequality. This wraps three parts, a left and right term
         and a comparator which defines the type of comparison.
 
 
@@ -350,9 +361,10 @@ class ContainsCriterion(Criterion):
 
     def get_sql(self, **kwargs):
         # FIXME escape
-        return "{term} IN {container}".format(
+        return "{term} {not_}IN {container}".format(
             term=self.term.get_sql(**kwargs),
-            container=self.container.get_sql(**kwargs)
+            container=self.container.get_sql(**kwargs),
+            not_='NOT ' if self._is_negated else ''
         )
 
 
@@ -385,10 +397,9 @@ class BetweenCriterion(Criterion):
 
 
 class NullCriterion(Criterion):
-    def __init__(self, term, isnull, alias=None):
+    def __init__(self, term, alias=None):
         super(NullCriterion, self).__init__(alias)
         self.term = term
-        self.isnull = isnull
 
     @property
     def tables_(self):
@@ -402,7 +413,7 @@ class NullCriterion(Criterion):
     def get_sql(self, **kwargs):
         return "{term} IS{not_} NULL".format(
             term=self.term.get_sql(**kwargs),
-            not_='' if self.isnull else ' NOT'
+            not_=' NOT' if self._is_negated else ''
         )
 
     def fields(self):


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 827a502..88f2b98 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -278,7 +278,6 @@ class BetweenTests(unittest.TestCase):
     def test__function_between(self):
         c1 = fn.Coalesce(Field('foo'), 0)[0:1]
         c2 = fn.Coalesce(Field('foo', table=self.t), 0)[0:1]
-        c3 = fn.Coalesce(Field('foo', table=self.t), 0).between(0, 1)
 
         self.assertEqual('COALESCE("foo",0) BETWEEN 0 AND 1', str(c1))
         self.assertEqual('COALESCE("btw"."foo",0) BETWEEN 0 AND 1', str(c2))
@@ -333,6 +332,45 @@ class IsInTests(unittest.TestCase):
         self.assertEqual('COALESCE("isin"."foo",0) IN (0,1)', str(c2))
 
 
+class NotInTests(unittest.TestCase):
+    t = Table('abc', alias='notin')
+
+    def test__notin_number(self):
+        c1 = Field('foo').notin([0, 1])
+        c2 = Field('foo', table=self.t).notin([0, 1])
+
+        self.assertEqual('"foo" NOT IN (0,1)', str(c1))
+        self.assertEqual('"notin"."foo" NOT IN (0,1)', str(c2))
+
+    def test__notin_character(self):
+        c1 = Field('foo').notin(['a', 'b'])
+        c2 = Field('foo', table=self.t).notin(['a', 'b'])
+
+        self.assertEqual('"foo" NOT IN (\'a\',\'b\')', str(c1))
+        self.assertEqual('"notin"."foo" NOT IN (\'a\',\'b\')', str(c2))
+
+    def test__notin_date(self):
+        c1 = Field('foo').notin([date(2000, 1, 1), date(2000, 12, 31)])
+        c2 = Field('foo', table=self.t).notin([date(2000, 1, 1), date(2000, 12, 31)])
+
+        self.assertEqual('"foo" NOT IN (\'2000-01-01\',\'2000-12-31\')', str(c1))
+        self.assertEqual('"notin"."foo" NOT IN (\'2000-01-01\',\'2000-12-31\')', str(c2))
+
+    def test__notin_datetime(self):
+        c1 = Field('foo').notin([datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59)])
+        c2 = Field('foo', table=self.t).notin([datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59)])
+
+        self.assertEqual('"foo" NOT IN (\'2000-01-01T00:00:00\',\'2000-12-31T23:59:59\')', str(c1))
+        self.assertEqual('"notin"."foo" NOT IN (\'2000-01-01T00:00:00\',\'2000-12-31T23:59:59\')', str(c2))
+
+    def test__function_notin(self):
+        c1 = fn.Coalesce(Field('foo'), 0).notin([0, 1])
+        c2 = fn.Coalesce(Field('foo', table=self.t), 0).notin([0, 1])
+
+        self.assertEqual('COALESCE("foo",0) NOT IN (0,1)', str(c1))
+        self.assertEqual('COALESCE("notin"."foo",0) NOT IN (0,1)', str(c2))
+
+
 class ComplexCriterionTests(unittest.TestCase):
     table_abc, table_efg = Table('abc', alias='cx0'), Table('efg', alias='cx1')