sql server - Parse Json data and update in sql table via python -
i'm parsing json in python first time couldn't figure out correct way data. i'm accessing json data sql table called "table1" in python parsing data , updating records in "table1". sql table
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 ... in above table actual json data not accumulated i'm pasting separately below 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"} so column in table values na wanted them fill extracting json objects each records.
so here's code tried,
import pyodbc import json import numpy np import pandas 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 table1 json not null , country null") id=[] json=[] row in cursor.fetchall(): id.append(row[0]) json.append(row[1]) jsondata = jsonresponse["data"] item in jsondata: street_numb = item.get("street_numb")[1] #access first address component street_name = item.get("street_name")[1] #access first address component route = item.get("route")[1] #access first address component sublocality = item.get("sublocality")[1] #access first address component country = item.get("country")[1] #access first address component in data: cursor.execute("update table1 set street_numb = ?, street_name=?,route=?,sublocality=?,country=?) so each json object there multiple address components them want access first address component only. can't way done.
any suggestion helpful. thanks
so each json object there multiple address components them want access first address component only.
that's first challenge. other challenges are:
- the address component type specified value in "types" attribute, can't access them name, and
- some types (e.g., "sublocality") can appear more once.
inside for row in cursor.fetchall(): loop can save "id" , "json" column values simple variables,
id = row[0] json_string = row[1] you can extract address component values dictionary of lists, , concatenate lists single strings:
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'] item in first_address_components: field_key in address_fields.keys(): if field_key in item['types']: address_fields[field_key].append(item['short_name']) # convert lists single strings address_fields = {key: ', '.join(values) key, values in address_fields.items()} pprint(address_fields) with pprint (for demonstration purposes only) producing
{'route': 'bt kawde road', 'street_number': '16', 'sublocality': 'uday baug, ghorpadi'} you proceed update table code this:
sql = """\ update table1 set street_numb=?, route=?, sublocality=? id=? """ params = ( address_fields['street_number'], address_fields['route'], address_fields['sublocality'], id ) crsr.execute(sql, params)
Comments
Post a Comment