使用Python将json解析为Insert语句 [英] Parsing json into Insert statements with Python

问题描述

我有一个包含几个json记录的文件。我必须解析此文件，并将每个json加载到特定的SQL Server表中。但是，该表可能不存在于数据库中，在这种情况下，我还必须在加载之前首先创建它。因此，我必须解析json文件并找出字段/列并创建表。然后，我必须将jsons 反序列化到记录中，并将它们插入到创建的表中。但是，需要注意的是json中的某些字段是可选的，即一个json记录中可能没有该字段，但可能在另一条记录中存在该字段。以下是具有3条记录的示例文件：-

I have a file which contains several json records. I have to parse this file and load each of the jsons to a particular SQL-Server table. However, the table might not exist on the database, in which case I have to also create it first before loading. So, I have to parse the json file and figure out the fields/columns and create the table. Then I will have to de-serialize the jsons into records and insert them into the table created. However, the caveat is that some fields in the json are optional i.e. a field might be absent from one json record but could be present in another record. Below is an example file with 3 records :-

{ id : 1001, 
  name : "John", 
  age : 30 
} , 

{ id : 1002,
  name : "Peter",
  age : 25
},

{ id : 1002,
  name : "Kevin",
  age : 35,
  salary : 5000
},

请注意， salary 字段仅出现在第三条记录中。结果应为：-

Notice that the field salary appears only in the 3rd record. The results should be :-

CREATE TABLE tab ( id int, name varchar(100), age int, salary int );

INSERT INTO tab (id, name, age, salary) values (1001, 'John', 30, NULL)
INSERT INTO tab (id, name, age, salary) values (1002, 'Peter', 25, NULL)
INSERT INTO tab (id, name, age, salary) values (1003, 'Kevin', 35, 5000)

由于我是Python的新手，有人可以帮助我提供一些建议。谢谢。

Can anyone please help me with some pointers as I am new to Python. Thanks.

推荐答案

您可以尝试以下方法：

import json

TABLE_NAME = "tab"

sqlstatement = ''
with open ('data.json','r') as f:
    jsondata = json.loads(f.read())

for json in jsondata:
    keylist = "("
    valuelist = "("
    firstPair = True
    for key, value in json.items():
        if not firstPair:
            keylist += ", "
            valuelist += ", "
        firstPair = False
        keylist += key
        if type(value) in (str, unicode):
            valuelist += "'" + value + "'"
        else:
            valuelist += str(value)
    keylist += ")"
    valuelist += ")"

    sqlstatement += "INSERT INTO " + TABLE_NAME + " " + keylist + " VALUES " + valuelist + "\n"

print(sqlstatement)

但是要使它起作用，您需要将JSON文件更改为像这样更正语法：

However for this to work, you'll need to change your JSON file to correct the syntax like this:

[{  
    "id" : 1001, 
    "name" : "John", 
    "age" : 30 
} , 

{   
    "id" : 1002,
    "name" : "Peter",
    "age" : 25
},

{
    "id" : 1003,
    "name" : "Kevin",
    "age" : 35,
    "salary" : 5000
}]

运行此命令将得到以下输出：

Running this gives the following output:

INSERT INTO tab (age, id, name) VALUES (30, 1001, 'John')
INSERT INTO tab (age, id, name) VALUES (25, 1002, 'Peter')
INSERT INTO tab (salary, age, id, name) VALUES (5000, 35, 1003, 'Kevin')

请注意，您无需指定NULL。如果您未在insert语句中指定列，则它应自动将NULL插入您遗漏的任何列中。

Note that you don't need to specify NULLs. If you don't specify a column in the insert statement, it should automatically insert NULL into any columns you left out.

这篇关于使用Python将json解析为Insert语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！