store json data with sqlobject in mysql database
Especially while developing the final database schema I find pleasant to store JSON data structures direct in the database. SQLObject has a PickleCol, but I has the downside, that serialized objects are binary based and thus not very well to debug.
The following codes need sqlobject as well as formencode:
from sqlobject import *
sqlhub.processConnection = connectionForURI("...")
import cjson
from formencode import validators
class JSONValidator(validators.Validator):
def to_python(self, value, state):
if value is None:
return None
try:
return cjson.decode(value)
except:
raise validators.Invalid("expected a decodable JSON object in the JSONCol '%s', got %s %r instead" % (self.name, type(value), value), value, state)
def from_python(self, value, state):
if value is None:
return None
try:
return cjson.encode(value)
except:
raise validators.Invalid("expected an encodable JSON object in the JSONCol '%s', got %s %r instead" % (self.name, type(value), value), value, state)
class SOJSONCol(SOStringCol):
def createValidators(self):
return [JSONValidator()] + super(SOJSONCol, self).createValidators()
class JSONCol(Col):
baseClass = SOJSONCol
class Person(SQLObject):
name = StringCol()
data = JSONCol()
The code was adopted from http://openxcap.org/browser/xcap/interfaces/backend/sipthor.py
