Python dicts in sqlalchemy

Question

I would like to load/save a dict to/from my sqlite DB, but am having some problems figuring out a simple way to do it. I don't really need to be able to filter, etc., based on the contents so a simple conversion to/from string is fine.

The next-best thing would be foreign keys. Please don't post links to huge examples, my head would explode if I ever set eyes on any those.

Answer 1

If you need to map a 1-N relation and map it as dict rather then list, then read 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)

Answer 2

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 it
s = 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 = Text

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