sql - Error while loading json in Python -
i'm parsing json in python 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
r_geocode_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
[{"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"} [{"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"} [{"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_string=[] row in cursor.fetchall(): id.append(row[0]) json_string.append(row[1]) address_fields = { 'intersection': [], 'political': [], 'country': [], 'administrative_area_level_1': [], 'administrative_area_level_2': [], 'administrative_area_level_3': [], 'administrative_area_level_4': [], 'administrative_area_level_5': [], 'colloquial_area': [], 'locality': [], 'ward': [], 'neighborhood': [], 'premise': [], 'subpremise': [], 'natural_feature': [], 'postal_code': [], 'airport': [], 'park': [], 'point_of_interest': [], 'street_address': [], 'street_number': [], 'route': [], 'sublocality': [], } dumpdata = json.dumps(json_string) json_all = json.loads(dumpdata) #print(json_all) #json_all = json.loads(json_string) id_index = 0 json_str in json_all: address_fields = { 'intersection': [], 'political': [], 'country': [], 'administrative_area_level_1': [], 'administrative_area_level_2': [], 'administrative_area_level_3': [], 'administrative_area_level_4': [], 'administrative_area_level_5': [], 'colloquial_area': [], 'locality': [], 'ward': [], 'neighborhood': [], 'premise': [], 'subpremise': [], 'natural_feature': [], 'postal_code': [], 'airport': [], 'park': [], 'point_of_interest': [], 'street_address': [], 'street_number': [], 'route': [], 'sublocality': [], } json_results = json.loads(json_str) if not json_results or not json_results[0].get('address_components'): id_index = id_index + 1 continue 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['long_name']) # convert lists single strings address_fields = {key: ', '.join(values) key, values in address_fields.items()} sql = "update mst_nhb_r_geocode set intersection=?, political=?, country=?, administrative_area_level_1=?, administrative_area_level_2=?, administrative_area_level_3=?, administrative_area_level_4=?, administrative_area_level_5=?, colloquial_area=?, locality=?, ward=?, neighborhood=?, premise=?, subpremise=?, postal_code=?, natural_feature=?, airport=?, park=?, point_of_interest=?, street_address=?, street_number=?, route=?, sublocality=? r_geocode_id=?" params = ( address_fields['intersection'], address_fields['political'], address_fields['country'], address_fields['administrative_area_level_1'], address_fields['administrative_area_level_2'], address_fields['administrative_area_level_3'], address_fields['administrative_area_level_4'], address_fields['administrative_area_level_5'], address_fields['colloquial_area'], address_fields['locality'], address_fields['ward'], address_fields['neighborhood'], address_fields['premise'], address_fields['subpremise'], address_fields['postal_code'], address_fields['natural_feature'], address_fields['airport'], address_fields['park'], address_fields['point_of_interest'], address_fields['street_address'], address_fields['street_number'], address_fields['route'], address_fields['sublocality'], id[id_index] ) cursor.execute(sql, params) id_index = id_index + 1 cnxn.commit() cursor.close() cnxn.close() but code giving me error
file "d:/deepesh/testforupdatetablejson.py", line 34, in <module> address_fields[field_key].append(item['long_name']) attributeerror: 'str' object has no attribute 'append' any suggestions on code getting wrong .
thanks
i found this:
for row in cursor.fetchall(): id.append(row[0]) json_string.append(row[1]) dumpdata = json.dumps(json_string) json_all = json.loads(dumpdata) notice dumpdata 2 level json. see json_string array of json. when json_all[0], json. complain typeerror: string indices must integers. should loads first.
i think should update sql every row, should include update in loop.
fix :
id_index = 0 json_str in json_all: address_fields = { 'street_number': [], 'route': [], 'sublocality': [], } json_results = json.loads(json_str) if isinstance(json_results,dict): first_address_components = json_results['results'][0]['address_components'] else: 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['long_name']) # convert lists single strings address_fields = {key: ', '.join(values) key, values in address_fields.items()} sql = "update mst_nhb_r_geocode set street_numb=?, route=?, sublocality=? r_geocode_id=?" params = ( address_fields['street_number'], address_fields['route'], address_fields['sublocality'], id[id_index] ) cursor.execute(sql, params) id_index = id_index + 1
Comments
Post a Comment