回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>尝试获取webapi的JSON响应并用结果填充SQL数据库。你知道吗</p>
<p>JSON响应的一部分包含以下数组:</p>
<pre><code>"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"
}
],
</code></pre>
<p>为了进行测试,我已经发出了请求,然后将响应写入了一个文件,我正在试验这个文件,以获得正确的代码</p>
<pre><code>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()
</code></pre>
<p>在我的测试代码中,我尝试了以下方法:</p>
<pre><code>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)
</code></pre>
<p>但这只会产生以下输出:</p>
<pre><code><class 'list'>
INSERT INTO thetable VALUES(?, ?, ?, ?, ?, ?)
</code></pre>
<p>为了便于参考,我认为除了后面的逗号之外,这会产生一些效果:</p>
<pre><code>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')
</code></pre>
<p>从而产生:</p>
<pre><code>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",
</code></pre>
<p>在我用SQL创建的表中,它使用与JSON数组中的键相同的列名。JSON响应中有几个数组,因此我希望创建一个通用函数来接受JSON数组,并创建正确的sqlinsert语句来用JSON数据填充表。我计划使用pyodbc,best case既适用于python2.7,也适用于python3.x</p>
<p>更新信息:</p>
<p>我发现以下代码片段非常接近:</p>
<pre><code>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
</code></pre>
<p>在运行这个for循环之前,我试图找到一种获取键的方法,因为我必须用实际的sqlinsert语句替换print语句。你知道吗</p>
<p>当我检查<code>type(newdata['PartDetails']['MediaLinks'])</code>is时,在python3.7.4中返回<code><class 'list'></code>,因此即使它看起来像一个字典,它也被视为一个列表,并且.keys()无法尝试获取键</p>