Custom column type can't handle null values correctly
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.
Comments (5)
-
repo owner -
Account Deleted 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?
-
repo owner 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. -
repo owner - changed status to resolved
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 SQLcol = :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.
-
repo owner I've removed all the superfluous stuff from the recipe and updated it for the latest
TypeDecorator
API. - Log in to comment
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" ?