Custom column type can't handle null values correctly

[Former user]

It could be that I'm just doing this wrong, but I have a custom column type

class JsonColumn( sqlalchemy.types.MutableType,
                  sqlalchemy.types.TypeDecorator ):
    impl = sqlalchemy.types.Unicode

    # As recommended by: http://www.sqlalchemy.org/trac/wiki/05Migration
    def bind_processor(self, dialect):
        def convert(value):
            return self.convert_bind_param(value, dialect)
        return convert

    def result_processor(self, dialect):
        def convert(value):
            return self.convert_result_value(value, dialect)
        return convert

    def convert_bind_param( self, value, engine ):
        return cjson.encode( value )

    def convert_result_value( self, value, engine ):
        if ( value == None or value == '' ):
            value = 'null'
        return cjson.decode( value )

    def copy_value( self, value ):
        return copy.deepcopy( value )

So if I have

Table A:
  id int primary key,
  value_json JsonColumn not null

and attempt to execute

table_a.select().where( table_a.c.value_json == None )

sqlalchemy generates

SELECT a.id, a.value_json FROM a WHERE value_json IS NULL

which isn't what I wanted. However, if I try:

table_a.select().where( table_a.c.value_json == 'null' )

sqlalchemy generates

SELECT a.id, a.value_json FROM a WHERE value_json == '"null"'

which is also incorrect.

The problem seems to be that whether or not {{{== None}}} should be turned into {{{IS NULL}}} depends on the column type. AbstractType.compare_values may be relevant, but it's not documented enough to tell. It looks like the problem is caused by sqlalchemy.sql.expression._CompareMixin, but I'm having trouble figuring out how to fix it based on the column type.

[Mike Bayer]

I've never heard of any kind of column in SQL that can be compared to null using col = null. what database backend is this that has a type called "JsonColumn" ?

[Former user]

Based on documentation at: http://www.sqlalchemy.org/docs/04/types.html#types_custom

someone contributed: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/JSONColumn

Is this not an acceptable usage?

[Mike Bayer]

Replying to guest:

someone contributed: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/JSONColumn

Is this not an acceptable usage?

oh thats entirely fine. Its ultimately a VARCHAR. To compare it in SQL to NULL, you use col IS NULL. Saying "col = null" will not produce the expected result in SQL.

[Mike Bayer]

Replying to guest:

which isn't what I wanted. However, if I try:

{{{ table_a.select().where( table_a.c.value_json == 'null' ) }}}

sqlalchemy generates {{{ SELECT a.id, a.value_json FROM a WHERE value_json == '"null"' }}}

OK I now realize that you'd like to compare your JSON value to the string "null". If you say col == 'null', SQLAlchemy will generate the SQL col = :bindparam and the bindparam will be assigned the string "null". The logic you're doing in the comparison, i.e. converting to the string "null" and then decoding it, is where you're getting '"null"' from. Currently the IS NULL can't be overridden at the type level for boolean comparisons, that is something we may add in 0.6. So make yourself a "null" token to override:

from sqlalchemy import *
import sqlalchemy
import simplejson

null = object()

class JsonColumn( sqlalchemy.types.MutableType,
                  sqlalchemy.types.TypeDecorator ):
    impl = sqlalchemy.types.Unicode

    # As recommended by: http://www.sqlalchemy.org/trac/wiki/05Migration
    def bind_processor(self, dialect):
        def convert(value):
            return self.convert_bind_param(value, dialect)
        return convert

    def result_processor(self, dialect):
        def convert(value):
            return self.convert_result_value(value, dialect)
        return convert

    def convert_bind_param( self, value, engine ):
        if value is null:
            value = None
        return simplejson.dumps( value )

    def convert_result_value( self, value, engine ):
        return simplejson.loads( value )

    def copy_value( self, value ):
        return copy.deepcopy( value )

engine = create_engine('sqlite://', echo=True)

m = MetaData(engine)

t1 = Table('table', m, 
    Column('data', JsonColumn())
)

m.create_all()


t1.insert().execute(
    {'data':"a string"},
    {'data':{"a":"dict"}},
    {'data':None},
)

print t1.select().where(t1.c.data==null).execute().fetchall()
print t1.select().where(t1.c.data!=null).execute().fetchall()

I'll add this to that recipe.

this part doesn't make sense. If you compare

which is also incorrect.

The problem seems to be that whether or not {{{== None}}} should be turned into {{{IS NULL}}} depends on the column type. AbstractType.compare_values may be relevant, but it's not documented enough to tell. It looks like the problem is caused by sqlalchemy.sql.expression._CompareMixin, but I'm having trouble figuring out how to fix it based on the column type.

[Mike Bayer]

I've removed all the superfluous stuff from the recipe and updated it for the latest TypeDecorator API.