Storing Json Into Database In Python

I'm fetching some data from an API on regular interval and wants to store the JSON data into database to access and use later. From API, I get data in this sample each time: '{'dat

Solution 1:

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

classJsonField(models.TextField):
    """
    Stores json-able python objects as json.
    """defget_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""deffrom_db_value(self, value, expression, connection, context):
        if value == "":
            returnNonetry:
            return json.loads(value)
        except TypeError:
            BAD_DATA.error("cannot load dictionary field -- type error")
            returnNone

Solution 2:

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 jsondb_data = json.loads(db_col_data)
# get new/latest 'nodes' data from api as explained above# append this data to 'db_data' json aslatest_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!