解析JSON中的数组以插入到SQL表中的函数

Question

尝试获取web API的JSON响应，并使用结果填充SQL数据库。

JSON响应的一部分包含以下数组：

"MediaLinks": [
            {
                "MediaType": "Datasheets",
                "SmallPhoto": "",
                "Thumbnail": "",
                "Title": "SN54HC374, SN74HC374",
                "Url": "http://www.ti.com/general/docs/suppproductinfo.tsp?distId=10&gotoUrl=http%3A%2F%2Fwww.ti.com%2Flit%2Fgpn%2Fsn74hc374"
            },
            {
                "MediaType": "Product Photos",
                "SmallPhoto": "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP_sml.jpg",
                "Thumbnail": "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP_tmb.jpg",
                "Title": "20-DIP,R-PDIP-Txx",
                "Url": "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP.jpg"
            },
            {
                "MediaType": "Featured Product",
                "SmallPhoto": "",
                "Thumbnail": "",
                "Title": "Logic Solutions",
                "Url": "https://www.digikey.com/en/product-highlight/t/texas-instruments/logic-solutions "
            },
            {
                "MediaType": "Featured Product",
                "SmallPhoto": "",
                "Thumbnail": "",
                "Title": "Analog Solutions",
                "Url": "https://www.digikey.com/en/product-highlight/t/texas-instruments/analog-solutions "
            },
            {
                "MediaType": "PCN Design/Specification",
                "SmallPhoto": "",
                "Thumbnail": "",
                "Title": "Copper Bond Wire Revision A 04/Dec/2013",
                "Url": "http://media.digikey.com/pdf/PCNs/Texas%20Instruments/PCN20120223003A_Copper-wire.pdf"
            },
            {
                "MediaType": "PCN Design/Specification",
                "SmallPhoto": "",
                "Thumbnail": "",
                "Title": "Material Set 30/Mar/2017",
                "Url": "http://media.digikey.com/pdf/PCNs/Texas%20Instruments/PCN20170310000.pdf"
            }
        ],

为了测试，我发出了请求，然后将响应写到了一个文件中，我正在试验这个文件，以得到正确的代码

conn.request("POST", "/services/partsearch/v2/partdetails", json.dumps(payload), headers)

res = conn.getresponse()
data = res.read()

data_return = json.loads(data)
print(json.dumps(data_return, indent=4))

with open(y["DigiKeyPartNumber"]+".json", "w") as write_file:
    json.dump(data_return, write_file, indent=4, sort_keys=True)
write_file.close()

然后在我的测试代码中，我尝试了：

import json

with open(r"C:\Users\george\OneDrive\Documents\296-1592-5-ND.json") as json_file:
    data = json.load(json_file)

values = ""
placeholder = '?'
thelist = []
thelist = list(data['PartDetails']['MediaLinks'])
print(type(thelist))
#print(thelist)

placeholders = ', '.join(placeholder for unused in (data['PartDetails']['MediaLinks']))
query = 'INSERT INTO thetable VALUES(%s)' % placeholders
print(query)

但这只会产生以下输出：

<class 'list'>
INSERT INTO thetable VALUES(?, ?, ?, ?, ?, ?)

作为参考，这创建了我认为可以工作的内容，除了尾随的逗号：

if len(data['PartDetails']['MediaLinks']):
    print('The length is: ' + str(len(data['PartDetails']['MediaLinks'])))
    #print(type(data['PartDetails']['MediaLinks']))
    for mediadata in data['PartDetails']['MediaLinks']:
        #print(mediadata)
        for element in mediadata:
            #print(element + ' is "' + mediadata[element] + '"')
            values += '"' + mediadata[element] + '", '
    #print(list(data['PartDetails']['MediaLinks'][1]))
        print(values + "\n")
        values = ""
else:
    print('It is empty')

这会产生这样的结果：

The length is: 6
"Datasheets", "", "", "SN54HC374, SN74HC374", "http://www.ti.com/general/docs/suppproductinfo.tsp?distId=10&gotoUrl=http%3A%2F%2Fwww.ti.com%2Flit%2Fgpn%2Fsn74hc374",

"Product Photos", "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP_sml.jpg", "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP_tmb.jpg", "20-DIP,R-PDIP-Txx", "http://media.digikey.com/photos/Texas%20Instr%20Photos/296-20-DIP.jpg",

"Featured Product", "", "", "Logic Solutions", "https://www.digikey.com/en/product-highlight/t/texas-instruments/logic-solutions ",

"Featured Product", "", "", "Analog Solutions", "https://www.digikey.com/en/product-highlight/t/texas-instruments/analog-solutions ",

"PCN Design/Specification", "", "", "Copper Bond Wire Revision A 04/Dec/2013", "http://media.digikey.com/pdf/PCNs/Texas%20Instruments/PCN20120223003A_Copper-wire.pdf",

"PCN Design/Specification", "", "", "Material Set 30/Mar/2017", "http://media.digikey.com/pdf/PCNs/Texas%20Instruments/PCN20170310000.pdf",

在我用SQL创建的表中，它使用与JSON数组中的键相同的列名。在JSON响应中有几个数组，所以我希望创建一个接受JSON数组的泛型函数，并创建正确的SQL INSERT语句来用JSON数据填充表。我计划使用pyodbc，最好的情况是同时适用于Python2.7和3.x

更新信息：

我找到了下面这段非常接近的代码片段：

for thedata in data['PartDetails']['MediaLinks']:
    keys, values = zip(*thedata.items())
    print(values) #This will create the VALUES for the INSERT Statement
print(keys) #This will create the COLUMNS, need to add the PartDetailsId field

在运行这个for循环之前，我试图找到一种获取键的方法，因为我必须用实际的SQL INSERT语句替换print语句。

在Python3.7.4中，当我检查type(newdata['PartDetails']['MediaLinks'])返回<class 'list'>时，尽管它看起来像一个字典，但它被当作一个列表处理，.keys()无法尝试获取键

Answer 1

使用cursor.executemany()对MediaLinks列表中的所有行执行查询。

但是，您不能直接传递字典，因为迭代字典返回的是键，而不是值。您需要使用How to convert list of dictionaries into list of lists中的一种方法将其转换为值列表

colnames = ", ".join (data['PartDetails']['MediaLinks'][0].keys())
placeholders = ", ".join(["?"] * len(data['PartDetails']['MediaLinks'][0]))
query = "INSERT INTO MediaLInks (" + colnames + ") VALUES (" + placeholders + ")"
cursor.executemany(query, [tuple(d.values()) for d in data['PartDetails']['MediaLinks']])

Answer 2

只是为了完整，我想发布一个格式化的代码片段，这是为我工作。如果没有@barmar的帮助，这是不可能的，所以再次感谢。

最终目标是将它转换成一个函数，这样我就可以从JSON响应传入数组，并让它用数据填充正确的SQL表。这已经接近完成，但还没有完全完成。

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server=GEORGE-OFFICE3\SQLEXPRESS01;Database=Components;')

cursor = conn.cursor()

with open(r"C:\Users\george\OneDrive\Documents\296-1592-5-ND.json") as json_file:
    data = json.load(json_file)

x = tuple(data['PartDetails']['MediaLinks'][0])
a = str(x).replace("'","").replace("(","")

query = "INSERT INTO MediaLinks (PartDetailsId, " + a + " VALUES(" + str(data['PartDetails']['PartId'])

b = ""
for i in range(len(x)):
    b += ", ?"
b += ")"

query += b

cursor.executemany(query, [tuple(d.values()) for d in data['PartDetails']['MediaLinks']])
cursor.commit()

conn.close()