函数解析JSON中的数组以插入到SQL选项卡中

Question

尝试获取webapi的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数组，并创建正确的sqlinsert语句来用JSON数据填充表。我计划使用pyodbc，best case既适用于python2.7，也适用于python3.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循环之前，我试图找到一种获取键的方法，因为我必须用实际的sqlinsert语句替换print语句。你知道吗

当我检查type(newdata['PartDetails']['MediaLinks'])is时，在python3.7.4中返回<class 'list'>，因此即使它看起来像一个字典，它也被视为一个列表，并且.keys（）无法尝试获取键

Answer 1

为了完整起见，我想发布一个格式化的代码片段，这是为我工作。如果没有@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()

Answer 2

使用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']])