NULL safe inequality comparisons in SQL Alchemy?

There's a few ways to plug an alternate operator in there, as well as creating a custom operator, but the most public/mainstream way to get at what happens when __ne__() is invoked is at the type level:

from sqlalchemy import TypeDecorator, type_coerce, String, or_

class NullComparisons(TypeDecorator):
    impl = String

    class comparator_factory(TypeDecorator.Comparator):
        def __ne__(self, other):
            expr = type_coerce(self.expr, String)
            return or_(expr == None, expr != other)

so that will do the OR thing:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)
    data = Column(NullComparisons(50))
print(Thing.data != 'hi')

gives us:

thing.data IS NULL OR thing.data != :param_1

then for the PG/MySQL operator, what we really should have is the ability to link @compiles to operators directly. But that hook isn't present right now, so with more effort than it ideally should require, we can make a custom column element to handle it:

from sqlalchemy import TypeDecorator, type_coerce, String
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import BinaryExpression
import operator
class IsDistinctFrom(BinaryExpression):
    pass

@compiles(IsDistinctFrom, "postgresql")
def pg_is_distinct_from(element, compiler, **kw):
    return "%s IS DISTINCT FROM %s" % (
                    compiler.process(element.left, **kw),
                    compiler.process(element.right, **kw),
                )

@compiles(IsDistinctFrom, "mysql")
def mysql_is_distinct_from(element, compiler, **kw):
    return "%s <=> %s" % (
                    compiler.process(element.left, **kw),
                    compiler.process(element.right, **kw),
                )

class AdvancedNullComparisons(TypeDecorator):
    impl = String

    class comparator_factory(TypeDecorator.Comparator):
        def __ne__(self, other):
            expr = type_coerce(self.expr, String)
            # this step coerces a literal into a SQL expression,
            # this can be done without the private API here but the private
            # function does the most thorough job, this could also be made
            # public
            other = self._check_literal(expr, operator.ne, other)
            return IsDistinctFrom(self.expr, other, operator.ne)

then we can try that out:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)
    data = Column(AdvancedNullComparisons(50))

from sqlalchemy.dialects import postgresql, mysql
print(Thing.data != 'hi').compile(dialect=postgresql.dialect())
print(Thing.data != 'hi').compile(dialect=mysql.dialect())

gives us:

thing.data IS DISTINCT FROM %(param_1)s
thing.data <=> %s

Use customize operator may help: sqlalchemy custom operator

eg:

outerjoin(A, B.outlet_id.op('<=>')(A.outlet_id))