将包含两个不同值的相同键的json数据集列表中的值插入到MYSQL数据库中

Question

我在json file1中存储了一个dicts列表，具有相同的键，但每个重复键有两个不同的值，如下所示。我想插入到一个MySQL数据库中，其中键匹配某一列，然后在从另一个json file2获取密钥后，将这两个值插入到例如x和y列中。这是因为我试图更新从json文件2创建的表，以包含来自json file1的dicts列表中的其他值。

json file 1
[{"a": 0.022222222222162753,      
"b": 0.022222222222162753, 
"c":0.022222222222162753, 
"d": 0.022222222222162753,
"e": 2.6761620240410805e-12, 
"f": 0.022222222222162753},
{"a": 0.022222222222162753, 
"b": 0.022222222222162753, 
"c": 0.022222222222162753, 
"d": 0.022222222222162753, 
"e": 0.022222222222162753,
"f": 0.022222222222162753}]

json file 2
{"a":1,      
"b": 2, 
"c": 3, 
"d": 4,
"e": 5, 
"f": 6}

下面是我的代码，用于根据与表单中的重复键匹配的列将结果加载到MySQL数据库中，在另一个json文件中找到键后，键要值一个\\值为两个。

 for line3 in open("json file 2.json"):
    jline3=json.loads(line3)
    url3 =jline1["url"]

    for line4 in open("json file 1.json"):
        jline4 = json.load(line4)
        computedHITS=jline2[url3]

        """cursor.execute( """
          """  UPDATE `RANKED`.`EVALINDEX`
            SET `HITS`= %s
            WHERE `URL` = %s """
            """,  (computedHITS, url3))"""
        print "Number of rows inserted: %d" % cursor.rowcount
        db.commit() """

Answer 1

加载所有文件。然后循环json2键，如果pk在json1文件中，则执行更新

>>> import json
>>> with open("file_2.json") as f2, open("file_1.json") as f1:
...     json_pks     = json.loads(f2.read())
...     json_updates = json.loads(f1.read())
...     for pk in json_pks:
...         x_value = json_updates[0].get(pk,'')
...         y_value = json_updates[1].get(pk,'')
...         
...         if x_value and y_value:
...             #db stuff
...             print "update YOUR_TABLE set x=%r,y=%r where YOUR_PK=%s" % (x_value,y_value,pk)
... 
update YOUR_TABLE set x=0.022222222222162753,y=0.022222222222162753 where YOUR_PK=a
update YOUR_TABLE set x=0.022222222222162753,y=0.022222222222162753 where YOUR_PK=c
update YOUR_TABLE set x=0.022222222222162753,y=0.022222222222162753 where YOUR_PK=b
update YOUR_TABLE set x=2.6761620240410805e-12,y=0.022222222222162753 where YOUR_PK=e
update YOUR_TABLE set x=0.022222222222162753,y=0.022222222222162753 where YOUR_PK=d
update YOUR_TABLE set x=0.022222222222162753,y=0.022222222222162753 where YOUR_PK=f

Answer 2

您必须用json file1编写两个循环

一定就像

for json_raw_data in open("json file 2.json"):
    # Load full json data at a time
    json_objects = json.loads(json_raw_data)
    #Loop over each dict in json data.
    for each_date in json_objects:
        #Do your operation