title: SQLAlchemy query: Problems with parameterized filtering with != on columns that are NULL/None
body: <p>I am currently refining a common data interfacing concept.
  I use filter masks in the form of <code>['key', 'comparison method', 'value']</code> for data filtering tasks.</p>
  <p>Comparison methods are taken from</p>
  <pre class="lang-py prettyprint-override"><code>CMD = {
      &quot;==&quot;: lambda x, y: x == y,
      &quot;!=&quot;: lambda x, y: x != y,
      &quot;has&quot;: lambda x, y: y in x,
      &quot;not_has&quot;: lambda x, y: y not in x,
      &quot;in&quot;: lambda x, y: x in y,
      &quot;not_in&quot;: lambda x, y: x not in y
  }
  </code></pre>
  <p>Now filtering looks like this</p>
  <pre class="lang-py prettyprint-override"><code>filters = [['id', '==', 6], [&quot;type&quot;, &quot;!=&quot;, &quot;game&quot;]]
  
  with self.session_factory() as session:
      result = session.query(my_dataclass).filter(
                  CMD[f[1]](getattr(my_dataclass, f[0]), f[2]) for f in filters
               ).first()
  </code></pre>
  <p>Note that <code>my_dataclass</code> is a dynamically generated mapped class, thus the getattr-approach extracts the attribute from the mapped class (e.g. <code>Media.id</code>) and forwards it to the lambda expression for comparison resulting in <code>filter(Media.id == 6, Media.type != 'game')</code> for the example above. This might look absolutely over-complicated but makes sense in the broader view of the common data interface, so please bear with me here.</p>
  <p>For batch-operations the concept works like this:</p>
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy import and_, or_, not_
  
  filters = [[['id', '==', 6], [&quot;type&quot;, &quot;==&quot;, &quot;game&quot;]], [['id', '==', 1], [&quot;type&quot;, &quot;!=&quot;, &quot;game&quot;]]]
  
  with self.session_factory() as session:
      result = session.query(my_dataclass).filter(
          or_(
              and_(
                  CMD[f[1]](getattr(my_dataclass, f[0]), f[2]) for f in filter_mask
              ) for filter_mask in filters)
          ).all()
  </code></pre>
  <h1>The main problem</h1>
  <p>The concept works just fine for the examples above, until I filter for with <code>!=</code> on acolumns, that holds a NULL/None value.
  When I add <code>[&quot;inactive&quot;, &quot;!=&quot;, &quot;X&quot;]</code> to a filter (where 'inactive' is a sqlalchemy.CHAR column that initiates with NULL/None and is set to 'X' in case of soft deletes) the query results in None.</p>
  <p>Filtering for <code>[&quot;inactive&quot;, &quot;==&quot;, None]</code> works just fine. Filtering with <code>&quot;!=&quot;</code> on not-Null/None fields works just fine as well...</p>
  <p>I tried to narrow down the problem's possible causes for some time now but just hit a dead end here. Has anyone stumbled across a similar problem?</p>
  <p><strong>Thank you for your time in advance!</strong></p>
  <h3>Further Information:</h3>
  <p>Python==3.8, SQLAlchemy==1.4.31</p>
  <p>I also tried a more SQLAlchemy-near step-by-step approach with iterative query filtering to sort out the problem:</p>
  <pre><code>    def filter_query(self, my_dataclass: Any, query: Any, filters: list) -&gt; Any:
          &quot;&quot;&quot;
          Method for filtering query.
          :param my_dataclass: Data class.
          :param query: Query to filter.
          :param filters: Filter masks.
          :return: Filtered query.
          &quot;&quot;&quot;
          for filter_mask in filters:
              if filter_mask[1] == &quot;==&quot;:
                  query = query.filter(getattr(my_dataclass, filter_mask[0]) == filter_mask[2])
              elif filter_mask[1] == &quot;!=&quot;:
                  query = query.filter(not_(getattr(my_dataclass, filter_mask[0]) == filter_mask[2]))
              elif filter_mask[1] == &quot;in&quot;:
                  query = query.filter(getattr(my_dataclass, filter_mask[0]).in_(filter_mask[2]))
              elif filter_mask[1] == &quot;not_in&quot;:
                  query = query.filter(not_(getattr(my_dataclass, filter_mask[0]).in_(filter_mask[2])))
              elif filter_mask[1] == &quot;has&quot;:
                  query = query.filter(getattr(my_dataclass, filter_mask[0]).contains(filter_mask[2]))
              elif filter_mask[1] == &quot;not_has&quot;:
                  query = query.filter(not_(getattr(my_dataclass, filter_mask[0]).contains(filter_mask[2])))
          return query
  </code></pre>
  <p>But the results are the same.</p>