How do we update an HSTORE field with Flask-Admin?

Question

How do I update an HSTORE field with Flask-Admin?

The regular ModelView doesn't show the HSTORE field in Edit view. It shows nothing. No control at all. In list view, it shows a column with data in JSON notation. That's fine with me.

Using a custom ModelView, I can change the HSTORE field into a TextAreaField. This will show me the HSTORE field in JSON notation when in edit view. But I cannot edit/update it. In list view, it still shows me the object in JSON notation. Looks fine to me.

class MyView(ModelView):
    form_overrides = dict(attributes=fields.TextAreaField)

When I attempt to save/edit the JSON, I receive this error:

sqlalchemy.exc.InternalError
InternalError: (InternalError) Unexpected end of string
LINE 1: UPDATE mytable SET attributes='{}' WHERE mytable.id = ...
                                         ^
 'UPDATE mytable SET attributes=%(attributes)s WHERE mytable.id = %(mytable_id)s' {'attributes': u'{}', 'mytable_id': 14L}

Now -- using code, I can get something to save into the HSTORE field:

class MyView(ModelView):
    form_overrides = dict(attributes=fields.TextAreaField)
    def on_model_change(self, form, model, is_created):
        model.attributes = {"a": "1"}
        return

This basically overrides the model and put this object into it. I can then see the object in the List view and the Edit view. Still not good enough -- I want to save/edit the object that the user typed in.

I tried to parse and save the content from the form into JSON and back out. This doesn't work:

class MyView(ModelView):
    form_overrides = dict(attributes=fields.TextAreaField)

    def on_model_change(self, form, model, is_created):
        x = form.data['attributes']
        y = json.loads(x)
        model.attributes = y
        return

json.loads(x) says this:

ValueError ValueError: Expecting property name: line 1 column 1 (char 1)

and here are some sample inputs that fail:

{u's': u'ff'}
{'s':'ff'}

However, this input works:

{}

Blank also works

This is my SQL Table:

CREATE TABLE mytable (
    id BIGSERIAL UNIQUE PRIMARY KEY,
    attributes hstore
);

This is my SQA Model:

class MyTable(Base):
    __tablename__ = u'mytable'

    id = Column(BigInteger, primary_key=True)
    attributes = Column(HSTORE)

Here is how I added the view's to the admin object

admin.add_view(ModelView(models.MyTable, db.session))

Add the view using a custom Model View

admin.add_view(MyView(models.MyTable, db.session))

But I don't do those views at the same time -- I get a Blueprint name collision error -- separate issue)

I also attempted to use a form field converter. I couldn't get it to actually hit the code.

class MyModelConverter(AdminModelConverter):
    def post_process(self, form_class, info):
        raise Exception('here I am') #but it never hits this
        return form_class

class MyView(ModelView):
    form_overrides = dict(attributes=fields.TextAreaField)

Answer 1

The answer gives you a bit more then asked

Fist of all it "extends" hstore to be able to store actually JSON, not just key-value So this structure is also OK:

{"key":{"inner_object_key":{"Another_key":"Done!","list":["no","problem"]}}}

So, first of all your ModelView should use custom converter

class ExtendedModelView(ModelView):
   model_form_converter=CustomAdminConverter

Converter itself should know how to use hstore dialect:

class CustomAdminConverter(AdminModelConverter):
    @converts('sqlalchemy.dialects.postgresql.hstore.HSTORE')
    def conv_HSTORE(self, field_args, **extra):
        return DictToHstoreField(**field_args)

This one as you can see uses custom WTForms field which converts data in both directions:

class DictToHstoreField(TextAreaField):
    def process_data(self, value):
        if value is None:
            value = {}
        else:
            for key,obj in value.iteritems():
                if (obj.startswith("{") and obj.endswith("}")) or (obj.startswith("[") and obj.endswith("]")):
                    try:
                        value[key]=json.loads(obj)
                    except:
                        pass #

        self.data=json.dumps(value)

    def process_formdata(self, valuelist):
        if valuelist:
            self.data = json.loads(valuelist[0])
            for key,obj in self.data.iteritems():
                if isinstance(obj,dict) or isinstance(obj,list):
                    self.data[key]=json.dumps(obj)
                if isinstance(obj,int):
                    self.data[key]=str(obj)

The final step will be to actual use this data in application

I did not make it in common nice way for SQLalchemy, since was used with flask-restful, so I have only adoption for flask-restful in one direction, but I think it's easy to get the idea from here and do the rest.

And if your case is simple key-value storage so nothing additionaly should be done, just use it as is. But if you want to unwrap JSON somewhere in code, it's simple like this whenever you use it, just wrap in function

 if (value.startswith("{") and value.endswith("}")) or (value.startswith("[") and value.endswith("]")):
        value=json.loads(value)

Creating dynamical field for actual nice non-JSON way for editing of data also possible by extending FormField and adding some javascript for adding/removing fields, but this is whole different story, in my case I needed actual json storage, with blackjack and lists :)

Answer 2

Was working on postgres JSON datatype. The above solution worked great with a minor modifications.

Tried

'sqlalchemy.dialects.postgresql.json.JSON',
'sqlalchemy.dialects.postgresql.JSON',
'dialects.postgresql.json.JSON',
'dialects.postgresql.JSON'

The above versions did not work.

Finally the following change worked

@converts('JSON')

And changed class DictToHstoreField to the following:

class DictToJSONField(fields.TextAreaField):
    def process_data(self, value):
        if value is None:
            value = {}

        self.data = json.dumps(value)

    def process_formdata(self, valuelist):
        if valuelist:
            self.data = json.loads(valuelist[0])
        else:
            self.data = '{}'

Answer 3

Although, this is might not be the answer to your question, but by default SQLAlchemy's ORM doesn't detect in-place changes to HSTORE field values. But fortunately there's a solution: SQLAlchemy's MutableDict type:

from sqlalchemy.ext.mutable import MutableDict

class MyClass(Base):
    __tablename__ = 'mytable'

    id = Column(Integer, primary_key=True)
    attributes = Column(MutableDict.as_mutable(HSTORE))

Now when you change something in-place:

my_object.attributes.['some_key'] = 'some value'

The hstore field will be updated after session.commit().