在JSON中将JSON存储到数据库中 [英] Storing JSON into database in python

问题描述

我正在定期从API提取一些数据，并希望将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获取此示例中的数据:

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'}]}}}'

我可以json_data = json.loads(above_data)，然后以nodes_data = json_data["data"]["files"]["nodes"]的形式获取nodes，从而给出nodes的列表.

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.

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

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.

如何存储? nodes有多个，我需要这样一种方式，将来我可以在数据库中已经可用的data列中添加/添加更多的nodes.

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.

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

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格式进行访问感到困惑.

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

使用Sqlite进行测试.将来可以使用PostgresSQL. Text列的类型是要点.

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

推荐答案

我找到了一种将JSON数据存储到数据库中的方法.由于我正在从远程服务访问nodes，该服务在每个请求上都返回节点的list，因此我需要构建适当的json以从db存储/检索.

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返回的json文本为:'{"cursor": null, "nodes" = [{"name": "Test1", "value: 1}, {"name": "Test2", "value: 2}, ...]}'

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})

因此，str_data将返回有效的字符串/缓冲区，我们可以使用"nodes"键将其存储到数据库中.

So, str_data would return a valid string/buffer, which we can store into DB with a "nodes" key.

对于进入DB列的第二个或后续条目，我们将执行以下操作:

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

这是从数据库中加载/转储数据的正确方法，同时添加/删除json并保持正确的格式.

It is a proper way to load/dump data from DB while adding/removing json and keeping proper format.

谢谢！

这篇关于在JSON中将JSON存储到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！