在python中将JSON存储到数据库中

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.

我定期从API中获取一些数据,并希望将JSON数据存储到数据库中以便以后访问和使用。

From API, I get data in this sample each time:

从API,我每次都会获得此示例中的数据:

'{"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.

我可以json_data = json.loads(above_data)然后获取节点作为nodes_data = json_data [“data”] [“files”] [“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.

我想将此节点数据存储到文本类型的DB列data = Column(db.Text)中。每次节点列表中将有10-15个值。

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.

虽然我想做json.loads(db_data_col)以便我获得有效的json并且可以遍历所有节点以获取内部数据并在以后使用。

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

我对如何存储在db中以及稍后以有效的json格式访问感到困惑。

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

编辑1:使用Sqlite进行测试。以后可以使用PostgresSQL。列的文本类型是要点。

2 个解决方案

#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.

我找到了一种将JSON数据存储到DB中的方法。由于我从远程服务访问节点,每个请求都返回一个节点列表,我需要构建适当的json来存储/检索db。

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

假设API将json文本返回为:'{“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:

现在,对于第一次进入DB列,我们需要执行以下操作:

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

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.

因此,str_data将返回一个有效的字符串/缓冲区,我们可以使用“nodes”键将其存储到DB中。

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

对于DB列中的第二个或连续的条目,我们将执行以下操作:

# 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.

在添加/删除json并保持正确的格式时,这是从DB加载/转储数据的正确方法。

Thanks!

#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.

如果您使用的是Django 1.8,您可以创建自己的模型字段,可以存储json。该类将确保您具有正确的JSON格式。

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

#1