Storing JSON into database in python

Question

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:

'{"data": {"cursor": null, "files": {"nodes": [{u'code': u'BOPhmYQg5Vm', u'date': 1482244678,u'counts': 2, u'id': u'1409492981312099686'}, {u'code': u'g5VmBOPhmYQ', u'date': 1482244678,u'counts': 5, u'id': u'1209968614094929813'}]}}}'

I can json_data = json.loads(above_data) and then fetch nodes as nodes_data = json_data["data"]["files"]["nodes"] which gives a list of nodes.

I want to store this nodes data into DB column data = Column(db.Text) of Text type. Each time there are going to be 10-15 values in nodes list.

How do I store? There are multiple nodes and I need it in a way that in future I can append/add more nodes to already available data column in my db.

While I would like to do json.loads(db_data_col) so that I get valid json and can loop over all of nodes to get internal data and use later.

I'm confused on how to store in db and access later in valid json format.

Edit 1: Using Sqlite for testing. Can use PostgresSQL in future. Text type of column is main point.

Answer 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

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

Answer 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 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!