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.

I guess you want this layer of abstraction so you have the flexibility to change the DB schema without affecting the REST API? — Martin Konecny, May 10 '15 at 04:31
@MartinKonecny, yes, also, I may simply want to hide the semantics of the DB schema away from API users. — MLister, May 10 '15 at 04:35
I think what you need is a 3rd layer that sits above your DB that serializers and performs your queries/updates and represents the data in the form you want at the API level. e.g: [JSON Schema](http://json-schema.org/) which is nice because you can also validate, document and keep the resources and API flexible. — James Mills, May 10 '15 at 05:08
@JamesMills, good point, so I look up this: https://python-jsonschema.readthedocs.org/en/latest/, which seems to validate a piece of JSON data against a pre-defined JSON schema. It is nice, but I think what I really need is *a combination of validator and parser* that checks only permitted JSON keys are presented and parses these keys to their corresponding DB columns. Are you aware of any existing solution or I need to write my own? — MLister, May 10 '15 at 06:20

sirfz · Answer 1 · 2015-05-12T20:21:55.607

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.

edited May 12 '15 at 20:21

answered May 12 '15 at 19:26

sirfz

4,097
23
37

thank you for your answer. I am not looking for specific code for the toy example I gave (for which I already present a solution), I am more interested in the best possible approach (e.g. design patterns, libraries, etc) for the problem. – MLister May 20 '15 at 00:54
Yeah and IMHO the better approach is to define classes for your database tables similar to my example where you can define the field mappings clearly and pythonically. You also have the advantage of extending the functionality of your classes by adding generic methods in the parent class(es) that are overridable when necessary. – sirfz May 20 '15 at 08:18

VelikiiNehochuha · Answer 2 · 2015-05-15T17:47:00.603

1

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)

edited May 15 '15 at 17:47

answered May 15 '15 at 17:33

VelikiiNehochuha

3,775
2
15
32

thank you for your answer. I am not looking for specific code for the toy example I gave (for which I already present a solution), I am more interested in the best possible approach (e.g. design patterns, libraries, etc) for the problem. – MLister May 20 '15 at 00:53
I think, the litle problem does not require special solution. – VelikiiNehochuha May 20 '15 at 07:42

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

2 Answers2