Question: how do you work with key/value tables?

[jaitaiwan]

Hi Folks,

I'm trying to figure out how you should work with sqlalchemy and marshmallow to serialize and deserialize the values of a field which links to a key/value store table.

I'm currently using the setup described here: https://docs.sqlalchemy.org/en/13/_modules/examples/vertical/dictlike.html to create a table where one column name is "key" and another is "value" and the ProxiedDictMixin turns that into a dictionary where {"key": "value"}.

At this point in time the schema I've setup for it is using facts = fields.Nested(AnimalFactsSchema) but I simply get a dictionary where the keys have errors saying "unknown field".

Can you please provide some direction on how this might work OR what work needs to be done in marshmallow-sqlalchemy to make this a reality?

Thanks

[zedrdave]

Hi @jaitaiwan
Have you ever been able to figure this out?
Honestly, I can't make much sense of the doc around Related fields as to how it might apply here… :-/

[jaitaiwan]

Hey @zedrdave sorry for the delayed response. I think I did figure something out but it was AGES ago...

I used SQLAlchemyAutoSchema for the schemas in most cases.

I used a Marshmallow dictionary for the main schema vendorVariables = ma.Dict() and then used the following code in the main record:

class SomeModel(ProxiedDictMixin, db.Model):
    vendorVariables = db.relationship(
        'ClickbankOrderVendorVariable',
        collection_class=attribute_mapped_collection("name")
    )

    _proxied = association_proxy(
        "vendorVariables",
        "value",
        creator=lambda key, value: ClickbankOrderVendorVariable(
            key=key, value=value),
    )

    @classmethod
    def with_characteristic(self, key, value):
        return self.vendorVariables.any(key=key, value=value)

Proxied Dict Mixin:

class ProxiedDictMixin(object):
    """Adds obj[key] access to a mapped class.

    This class basically proxies dictionary access to an attribute
    called ``_proxied``.  The class which inherits this class
    should have an attribute called ``_proxied`` which points to a dictionary.

    Taken from https://docs.sqlalchemy.org/en/13/_modules/examples/vertical/dictlike.html
    
    """

    def __len__(self):
        return len(self._proxied)

    def __iter__(self):
        return iter(self._proxied)

    def __getitem__(self, key):
        return self._proxied[key]

    def __contains__(self, key):
        return key in self._proxied

    def __setitem__(self, key, value):
        self._proxied[key] = value

    def __delitem__(self, key):
        del self._proxied[key]

That's a ton of arbitrary code but hopefully you understand it more than I do currently (past me understood it better) and can make use of it.

[zedrdave]

@jaitaiwan Thanks a lot for your help!
Really wish this was somehow built into Marshmallow… :-|