python - 通过python解析Json数据并更新sql表

Question

我第一次在 python 中解析 JSON，所以无法找出获取数据的正确方法。我正在 python 中从名为 "table1" 的 SQL 表访问 JSON 数据，解析数据并将这些记录更新回“table1”中。这是我的 sql 表

ID   JSON            Street_Numb   Street_Name   Route   sublocality   Country   ...
12  <json_objects>   NA            NA            NA      NA            NA   ...
40  <json_objects>   NA            NA            NA      NA            NA   ...
30  <json_objects>   NA            NA            NA      NA            NA   ...

上表中实际的JSON数据无法累积，所以我将其单独粘贴在下面JSON

 {"results":[{"address_components":[{"long_name":"16","short_name":"16","types":["street_number"]},{"long_name":"Bhagwan Tatyasaheb Kawade Road","short_name":"BT Kawde Road","types":["route"]},{"long_name":"Palmgrove Society","short_name":"Palmgrove Society","types":["neighborhood","political"]},{"long_name":"Uday Baug","short_name":"Uday Baug","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Ghorpadi","short_name":"Ghorpadi","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]},{"long_name":"411001","short_name":"411001","types":["postal_code"]}],"formatted_address":"16, BT Kawade Road, Palmgrove Society, Uday Baug, Ghorpadi, Pune, Maharashtra 411001, India","geometry":{"location":{"lat":18.5132611,"lng":73.907346},"location_type":"ROOFTOP","viewport":{"northeast":{"lat":18.5146100802915,"lng":73.90869498029151},"southwest":{"lat":18.51191211970849,"lng":73.90599701970851}}},"place_id":"ChIJo1QsU7nBwjsRiewRdiNc2i4","types":["street_address"]},{"address_components":[{"long_name":"Jambhulkar Mala","short_name":"Jambhulkar Mala","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Jambhulkar Mala, Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}},"location":{"lat":18.508659,"lng":73.9029138},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}}},"place_id":"ChIJJ0lCDMfBwjsRrPmUq2ZOxd8","types":["political","sublocality","sublocality_level_2"]},{"address_components":[{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}},"location":{"lat":18.4828904,"lng":73.9016832},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}}},"place_id":"ChIJ8TQ7l8LBwjsROUGpMh25HMw","types":["political","sublocality","sublocality_level_1"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Prabhag 36","short_name":"Prabhag 36","types":["administrative_area_level_3","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}},"location":{"lat":18.5204303,"lng":73.8567437},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}}},"place_id":"ChIJARFGZy6_wjsRQ-Oenb9DjYI","types":["locality","political"]},{"address_components":[{"long_name":"411040","short_name":"411040","types":["postal_code"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra 411040, India","geometry":{"bounds":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}},"location":{"lat":18.492095,"lng":73.90017759999999},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}}},"place_id":"ChIJ-64DzdvBwjsRKzv08MBrn18","types":["postal_code"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":19.38404,"lng":75.16309},"southwest":{"lat":17.89324,"lng":73.32352}},"location":{"lat":18.6832564,"lng":74.0300122},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":19.38404,"lng":75.1447465},"southwest":{"lat":17.89324,"lng":73.32352}}},"place_id":"ChIJQ97RPE_AwjsR5zbDDbo3wHI","types":["administrative_area_level_2","political"]},{"address_components":[{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":22.028441,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}},"location":{"lat":19.7514798,"lng":75.7138884},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":22.0279091,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}}},"place_id":"ChIJ-dacnB7EzzsRtk_gS5IiLxs","types":["administrative_area_level_1","political"]},{"address_components":[{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"India","geometry":{"bounds":{"northeast":{"lat":35.5087008,"lng":97.39535869999999},"southwest":{"lat":6.4626999,"lng":68.1097}},"location":{"lat":20.593684,"lng":78.96288},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":35.5087008,"lng":97.39498069999999},"southwest":{"lat":6.7535159,"lng":68.16288519999999}}},"place_id":"ChIJkbeSa_BfYzARphNChaFPjNc","types":["country","political"]},{"address_components":[{"long_name":"Uday Baug","short_name":"Uday Baug","types":["bus_station","establishment","point_of_interest","transit_station"]},{"long_name":"Uday Baug","short_name":"Uday Baug","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Ghorpadi","short_name":"Ghorpadi","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]},{"long_name":"411001","short_name":"411001","types":["postal_code"]}],"formatted_address":"Uday Baug, Ghorpadi, Pune, Maharashtra 411001, India","geometry":{"location":{"lat":18.510892,"lng":73.906956},"location_type":"GEOMETRIC_CENTER","viewport":{"northeast":{"lat":18.5122409802915,"lng":73.9083049802915},"southwest":{"lat":18.5095430197085,"lng":73.90560701970848}}},"place_id":"ChIJ5YdfqcDBwjsR5VJutWLCsfA","types":["bus_station","establishment","point_of_interest","transit_station"]}],"status":"OK"}
 {"results":[{"address_components":[{"long_name":"Canal Road","short_name":"Canal Rd","types":["route"]},{"long_name":"Empress Garden View Society","short_name":"Empress Garden View Society","types":["political","sublocality","sublocality_level_3"]},{"long_name":"Uday Baug","short_name":"Uday Baug","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Ghorpadi","short_name":"Ghorpadi","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]},{"long_name":"411001","short_name":"411001","types":["postal_code"]}],"formatted_address":"Canal Rd, Empress Garden View Society, Uday Baug, Ghorpadi, Pune, Maharashtra 411001, India","geometry":{"bounds":{"northeast":{"lat":18.510637,"lng":73.9070137},"southwest":{"lat":18.5102785,"lng":73.9016839}},"location":{"lat":18.510445,"lng":73.90438309999999},"location_type":"GEOMETRIC_CENTER","viewport":{"northeast":{"lat":18.5118067302915,"lng":73.9070137},"southwest":{"lat":18.50910876970849,"lng":73.9016839}}},"place_id":"ChIJ88lKe8fBwjsRCRKukkS6-nM","types":["route"]},{"address_components":[{"long_name":"Jambhulkar Mala","short_name":"Jambhulkar Mala","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Jambhulkar Mala, Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}},"location":{"lat":18.508659,"lng":73.9029138},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}}},"place_id":"ChIJJ0lCDMfBwjsRrPmUq2ZOxd8","types":["political","sublocality","sublocality_level_2"]},{"address_components":[{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}},"location":{"lat":18.4828904,"lng":73.9016832},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}}},"place_id":"ChIJ8TQ7l8LBwjsROUGpMh25HMw","types":["political","sublocality","sublocality_level_1"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Prabhag 36","short_name":"Prabhag 36","types":["administrative_area_level_3","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}},"location":{"lat":18.5204303,"lng":73.8567437},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}}},"place_id":"ChIJARFGZy6_wjsRQ-Oenb9DjYI","types":["locality","political"]},{"address_components":[{"long_name":"411040","short_name":"411040","types":["postal_code"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra 411040, India","geometry":{"bounds":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}},"location":{"lat":18.492095,"lng":73.90017759999999},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}}},"place_id":"ChIJ-64DzdvBwjsRKzv08MBrn18","types":["postal_code"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":19.38404,"lng":75.16309},"southwest":{"lat":17.89324,"lng":73.32352}},"location":{"lat":18.6832564,"lng":74.0300122},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":19.38404,"lng":75.1447465},"southwest":{"lat":17.89324,"lng":73.32352}}},"place_id":"ChIJQ97RPE_AwjsR5zbDDbo3wHI","types":["administrative_area_level_2","political"]},{"address_components":[{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":22.028441,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}},"location":{"lat":19.7514798,"lng":75.7138884},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":22.0279091,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}}},"place_id":"ChIJ-dacnB7EzzsRtk_gS5IiLxs","types":["administrative_area_level_1","political"]},{"address_components":[{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"India","geometry":{"bounds":{"northeast":{"lat":35.5087008,"lng":97.39535869999999},"southwest":{"lat":6.4626999,"lng":68.1097}},"location":{"lat":20.593684,"lng":78.96288},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":35.5087008,"lng":97.39498069999999},"southwest":{"lat":6.7535159,"lng":68.16288519999999}}},"place_id":"ChIJkbeSa_BfYzARphNChaFPjNc","types":["country","political"]}],"status":"OK"}
 {"results":[{"address_components":[{"long_name":"Canal Road","short_name":"Canal Rd","types":["route"]},{"long_name":"Empress Garden View Society","short_name":"Empress Garden View Society","types":["political","sublocality","sublocality_level_3"]},{"long_name":"Uday Baug","short_name":"Uday Baug","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Ghorpadi","short_name":"Ghorpadi","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]},{"long_name":"411001","short_name":"411001","types":["postal_code"]}],"formatted_address":"Canal Rd, Empress Garden View Society, Uday Baug, Ghorpadi, Pune, Maharashtra 411001, India","geometry":{"bounds":{"northeast":{"lat":18.510637,"lng":73.9070137},"southwest":{"lat":18.5102785,"lng":73.9016839}},"location":{"lat":18.510445,"lng":73.90438309999999},"location_type":"GEOMETRIC_CENTER","viewport":{"northeast":{"lat":18.5118067302915,"lng":73.9070137},"southwest":{"lat":18.50910876970849,"lng":73.9016839}}},"place_id":"ChIJ88lKe8fBwjsRCRKukkS6-nM","types":["route"]},{"address_components":[{"long_name":"Jambhulkar Mala","short_name":"Jambhulkar Mala","types":["political","sublocality","sublocality_level_2"]},{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Jambhulkar Mala, Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}},"location":{"lat":18.508659,"lng":73.9029138},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.510584,"lng":73.9071699},"southwest":{"lat":18.5064841,"lng":73.89949709999999}}},"place_id":"ChIJJ0lCDMfBwjsRrPmUq2ZOxd8","types":["political","sublocality","sublocality_level_2"]},{"address_components":[{"long_name":"Wanowrie","short_name":"Wanowrie","types":["political","sublocality","sublocality_level_1"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Wanowrie, Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}},"location":{"lat":18.4828904,"lng":73.9016832},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.512962,"lng":73.9174169},"southwest":{"lat":18.480897,"lng":73.8890401}}},"place_id":"ChIJ8TQ7l8LBwjsROUGpMh25HMw","types":["political","sublocality","sublocality_level_1"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Prabhag 36","short_name":"Prabhag 36","types":["administrative_area_level_3","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}},"location":{"lat":18.5204303,"lng":73.8567437},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.6357545,"lng":73.9864569},"southwest":{"lat":18.4134784,"lng":73.7394779}}},"place_id":"ChIJARFGZy6_wjsRQ-Oenb9DjYI","types":["locality","political"]},{"address_components":[{"long_name":"411040","short_name":"411040","types":["postal_code"]},{"long_name":"Pune","short_name":"Pune","types":["locality","political"]},{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra 411040, India","geometry":{"bounds":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}},"location":{"lat":18.492095,"lng":73.90017759999999},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":18.5105018,"lng":73.9287002},"southwest":{"lat":18.4785059,"lng":73.87890030000001}}},"place_id":"ChIJ-64DzdvBwjsRKzv08MBrn18","types":["postal_code"]},{"address_components":[{"long_name":"Pune","short_name":"Pune","types":["administrative_area_level_2","political"]},{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Pune, Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":19.38404,"lng":75.16309},"southwest":{"lat":17.89324,"lng":73.32352}},"location":{"lat":18.6832564,"lng":74.0300122},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":19.38404,"lng":75.1447465},"southwest":{"lat":17.89324,"lng":73.32352}}},"place_id":"ChIJQ97RPE_AwjsR5zbDDbo3wHI","types":["administrative_area_level_2","political"]},{"address_components":[{"long_name":"Maharashtra","short_name":"MH","types":["administrative_area_level_1","political"]},{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"Maharashtra, India","geometry":{"bounds":{"northeast":{"lat":22.028441,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}},"location":{"lat":19.7514798,"lng":75.7138884},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":22.0279091,"lng":80.890924},"southwest":{"lat":15.6024121,"lng":72.659363}}},"place_id":"ChIJ-dacnB7EzzsRtk_gS5IiLxs","types":["administrative_area_level_1","political"]},{"address_components":[{"long_name":"India","short_name":"IN","types":["country","political"]}],"formatted_address":"India","geometry":{"bounds":{"northeast":{"lat":35.5087008,"lng":97.39535869999999},"southwest":{"lat":6.4626999,"lng":68.1097}},"location":{"lat":20.593684,"lng":78.96288},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":35.5087008,"lng":97.39498069999999},"southwest":{"lat":6.7535159,"lng":68.16288519999999}}},"place_id":"ChIJkbeSa_BfYzARphNChaFPjNc","types":["country","political"]}],"status":"OK"}

因此，对于表中值为 NA 的列，我希望通过从每条记录中提取 JSON 对象来填充它们。

这是我尝试过的代码，

import pyodbc
import json
import numpy as np
import pandas as pd
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=myServer;DATABASE=temp;UID=ADMIN;PWD=abc@123;autocommit=True')
cursor = cnxn.cursor()
cursor.execute("select TOP 3 ID, JSON from table1 where JSON is NOT NULL AND Country is NULL")

ID=[]
JSON=[]

for row in cursor.fetchall():
     ID.append(row[0]) 
     JSON.append(row[1])
    jsonData = jsonResponse["data"]
    for item in jsonData:
        Street_Numb = item.get("Street_Numb")[1]  #access only first address component
        Street_Name = item.get("Street_Name")[1] #access only first address component
        Route       = item.get("Route")[1] #access only first address component
        sublocality = item.get("sublocality")[1] #access only first address component
        Country = item.get("Country")[1] #access only first address component



    for i in data:
           cursor.execute("UPDATE table1 SET  Street_Numb = ?, Street_Name=?,Route=?,sublocality=?,Country=?)

因此，对于每个 JSON 对象，都有多个地址组件，因此我只想从中访问第一个地址组件。但实际上无法找到可行的方法。

任何建议都会非常有帮助。谢谢

Answer 1

So for each JSON object there are multiple address components so from them I just want to access the first address component only.

这是你面临的第一个挑战。其他挑战是:

• 地址组件类型在“types”属性中指定为值，因此您无法通过名称访问它们，并且
• 某些类型(例如“次区域”)可以出现多次。

在 for row in cursor.fetchall(): 循环中，您可以将“ID”和“JSON”列值保存为简单变量，

id = row[0]
json_string = row[1]

您可以将地址组件值提取到列表字典中，然后将列表连接成单个字符串:

address_fields = {
    'street_number': [],
    'route': [],
    'sublocality': [],
    }
json_all = json.loads(json_string)
json_results = json_all['results']
first_address_components = json_results[0]['address_components']
for item in first_address_components:
    for field_key in address_fields.keys():
        if field_key in item['types']:
            address_fields[field_key].append(item['short_name'])
# convert lists to single strings
address_fields = {key: ', '.join(values) for key, values in address_fields.items()}

pprint(address_fields)

使用pprint(仅用于演示目的)生成

{'route': 'BT Kawde Road',
 'street_number': '16',
 'sublocality': 'Uday Baug, Ghorpadi'}

然后您可以使用如下代码继续更新表:

sql = """\
UPDATE table1 SET Street_Numb=?, Route=?, sublocality=? WHERE ID=?
"""
params = (
    address_fields['street_number'],
    address_fields['route'],
    address_fields['sublocality'],
    id
    )
crsr.execute(sql, params)