Partial updates via PATCH: how to parse JSON data for SQL updates?

Question

I am implementing 'PATCH' on the server-side for partial updates to my resources.

Assuming I do not expose my SQL database schema in JSON requests/responses, i.e. there exists a separate mapping between keys in JSON and columns of a table, how do I best figure out which column(s) to update in SQL given the JSON of a partial update?

For example, suppose my table has 3 columns: col_a, col_b, and col_c, and the mapping between JSON keys to table columns is: a -> col_a, b -> col_b, c -> col_c. Given JSON-PATCH data:

[
    {"op": "replace", "path": "/b", "value": "some_new_value"}
]

What is the best way to programmatically apply this partial update to col_b of the table corresponding to my resource?

Of course I can hardcode these mappings in a keys_to_columns dict somewhere, and upon each request with some patch_data, I can do sth like:

mapped_updates = {keys_to_columns[p['path'].split('/')[-1]]: p['value'] for p in patch_data}

then use mapped_updates to construct the SQL statement for DB update. If the above throws a KeyError I know the request data is invalid and can throw it away. And I will need to do this for every table/resource I have.

I wonder if there is a better way.

Answer 1

This is similar to what you're thinking of doing, but instead of creating maps, you can create classes for each table instead. For example:

class Table(object):
    """Parent class of all tables"""

    def get_columns(self, **kwargs):
        return {getattr(self, k): v for k, v in kwargs.iteritems()}

class MyTable(Table):
    """table MyTable"""

    # columns mapping
    a = "col_a"
    b = "col_b"

tbl = MyTable()
tbl.get_columns(a="value a", b="value b")
# the above returns {"col_a": "value a", "col_b": "value b"}
# similarly:
tbl.get_columns(**{p['path'].split('/')[-1]: p['value'] for p in patch_data})

This is just something basic to get inspired from, these classes can be extended to do much more.

Answer 2

patch_json = [
    {"op": "replace", "path": "/b", "value": "some_new_value"},
    {"op": "replace", "path": "/a", "value": "some_new_value2"}
]

def fix_key(item):
    item['path'] = item['path'].replace('/', 'col_')
    return item

print map(fix_key, patch_json)