Storing JSON into database in python
Solution 1
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!
Solution 2
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
Related videos on Youtube
nml
Updated on June 04, 2022Comments
-
nml almost 2 years
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 fetchnodes
asnodes_data = json_data["data"]["files"]["nodes"]
which gives a list ofnodes
.I want to store this
nodes
data into DB columndata = Column(db.Text)
ofText
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 morenodes
to already availabledata
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 ofnodes
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.-
Moinuddin Quadri over 7 yearsYou didn't mentioned the database in which you want to store. Implementation is dependent on database type. Also, if you'll google write json data source to <database-type>, you will get what you are looking for
-
-
nml over 7 yearsNope, Django is not required.
-
Edo Edo over 4 yearsnice, have you looked into tinydb askjerri.com/?topic=72