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

Answer 2

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

An implementation might look like

import json import sqlalchemy from sqlalchemy.types import TypeDecorator  SIZE = 256  class 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.