sql - 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

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))