Storing JSON in a database in python

Question

I get some data from the API at a regular interval and want to store JSON data in a database for access and use later.

From the API, I get data in this example every 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 the nodes as nodes_data = json_data["data"]["files"]["nodes"] , which gives a list of nodes .

I want to store the nodes data in a DB data = Column(db.Text) type Text . Each time there will be 10-15 values ​​in the list of nodes.

How can i store? There are several nodes , and I need it so that in the future I can add / add more nodes to the already available data column in my db.

While I would like to do json.loads(db_data_col) to get a valid json and loop through all nodes to get the internal data and use it later.

I am confused about how to store in db and access later in a valid json format.

Edit 1: Using Sqlite for testing. May use PostgresSQL in the future. Text The column type is the main point.

Answer 1

I found a way to store JSON data in a DB. Since I access the nodes from a remote service that returns a list nodes for each request, I need to build the correct 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 the list of nodes as:

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

Now for the first record in the DB column we need to do the following:

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

So str_data will return a valid string / buffer, which we can save to the database using the "nodes" key.

For two or subsequent entries in the DB column, we will do the 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 

This is the right way to load / unload data from the database when adding / removing json and maintaining the proper format.

Thanks!

Answer 2

If you are using Django 1.8, you can create your own model field that json can store. This class will ensure that you have the correct JSON format.

 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