Storing JSON into database in python

I found a way to store JSON data into DB. Since I'm accessing nodes from remote service which returns a list of nodes on every request, I need to build proper json to store/retrieve from db.

Say API returned json text as : '{"cursor": null, "nodes" = [{"name": "Test1", "value: 1}, {"name": "Test2", "value: 2}, ...]}'

So, first we need to access nodes list as:

data = json.loads(api_data)
nodes = data['nodes']

Now for 1st entry into DB column we need to do following:

str_data = json.dumps({"nodes": nodes})

So, str_data would return a valid string/buffer, which we can store into DB with a "nodes" key.

For 2nd or successive entries into DB column, we will do following:

# get data string from DB column and load into json
db_data = json.loads(db_col_data)
# get new/latest 'nodes' data from api as explained above
# append this data to 'db_data' json as
latest_data = db_data["nodes"] + new_api_nodes
# now add this data back to column after json.dumps()
db_col_data = json.dumps(latest_data)
# add to DB col and DB commit

It is a proper way to load/dump data from DB while adding/removing json and keeping proper format.

Thanks!

If you are using Django 1.8 you can create your own model field that can store a json. This class will make sure that you have the right JSON format as well.

import json
from django.db import models

class JsonField(models.TextField):
    """
    Stores json-able python objects as json.
    """
    def get_db_prep_value(self, value, connection, prepared=False):
        try:
            return json.dumps(value)
        except TypeError:
            BAD_DATA.error(
                "cannot serialize %s to store in a JsonField", str(value)
            )
            return ""

    def from_db_value(self, value, expression, connection, context):
        if value == "":
            return None
        try:
            return json.loads(value)
        except TypeError:
            BAD_DATA.error("cannot load dictionary field -- type error")
            return None