Python dicts in sqlalchemy

The SQLAlchemy PickleType is meant exactly for this.

class SomeEntity(Base):    __tablename__ = 'some_entity'    id = Column(Integer, primary_key=True)    attributes = Column(PickleType)# Just set the attribute to save its = SomeEntity(attributes={'baked': 'beans', 'spam': 'ham'})session.add(s)session.commit()# If mutable=True on PickleType (the default) SQLAlchemy automatically# notices modifications.s.attributes['parrot'] = 'dead'session.commit()

You can change the serialization mechanism by changing out the pickler with something else that has dumps() and loads() methods. The underlying storage mechanism by subclassing PickleType and overriding the impl attritbute:

class TextPickleType(PickleType):    impl = Textimport jsonclass SomeOtherEntity(Base):    __tablename__ = 'some_other_entity'    id = Column(Integer, primary_key=True)    attributes = Column(TextPickleType(pickler=json))

You can create a custom type by subclassing sqlalchemy.types.TypeDecorator to handle serialization and deserialization to Text.

An implementation might look like

import jsonimport sqlalchemyfrom sqlalchemy.types import TypeDecoratorSIZE = 256class TextPickleType(TypeDecorator):    impl = sqlalchemy.Text(SIZE)    def process_bind_param(self, value, dialect):        if value is not None:            value = json.dumps(value)        return value    def process_result_value(self, value, dialect):        if value is not None:            value = json.loads(value)        return value

Example usage:

class SomeModel(Base):    __tablename__ = 'the_table'    id = Column(Integer, primary_key=True)    json_field = Column(TextPickleType())s = SomeModel(json_field={'baked': 'beans', 'spam': 'ham'})session.add(s)session.commit()

This is outlined in an example in the SQLAlchemy docs, which also shows how to track mutations of that dictionary.

This approach should work for all versions of Python, whereas simply passing json as the value to the pickler argument of PickleType will not work correctly, as AlexGrönholm points out in his comment on another answer.

If you need to map a 1-N relation and map it as dict rather then list, then read Custom Dictionary-Based Collections

But if you mean a field, then what you can do it to have a DB field of type string, which is mapped to your Python object. But on the same python object you provide a property which will be kind-of proxy for this mapped string field of type dict().Code example (not tested):

class MyObject(object):    # fields (mapped automatically by sqlalchemy using mapper(...)    MyFieldAsString = None    def _get_MyFieldAsDict(self):        if self.MyFieldAsString:            return eval(self.MyFieldAsString)        else:            return {} # be careful with None and empty dict    def _set_MyFieldAsDict(self, value):        if value:            self.MyFieldAsString = str(value)        else:            self.MyFieldAsString = None    MyFieldAsDict = property(_get_MyFieldAsDict, _set_MyFieldAsDict)