Wiki

Clone wiki

sqlalchemy / UsageRecipes / JSONColumn

A simple way of storing basic python types using JSON. This example uses simplejson, but any JSON module will work fine.

This can be used as an alternative to the PickleType column type where other, non-python, applications need to access the data. It's easier for humans to read too.

Note the json_null type allows differentiation between "null" as defined by JSON, and "NULL" as defined by SQL. Comparing to None will produce IS NULL where as comparing to json_null will produce = 'null'.

from sqlalchemy import types
import sqlalchemy
import simplejson

json_null = object()

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

    def process_bind_param(self, value, dialect):
        if value is json_null:
            value = None
        return simplejson.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        return simplejson.loads(value)

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


if __name__ == '__main__':
    from sqlalchemy import *
    engine = create_engine('sqlite://', echo=True)

    m = MetaData(engine)

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

    m.create_all()


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

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

===

Edit from Y.Chaouche : MangoDB ?

===

Updated