CODEWITHSUNDEEP
pythonsql-serverjsonpandaspyodbc
Andre_k
Andre_k

Reputation: 1740

Parse Json data and update in sql table via python

I'm parsing JSON in python for the first time so couldn't figure out the correct way to get the data. I'm accessing JSON data from sql table called "table1" in python Parsing the data and updating those records back in "table1". So this is my 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 the actual JSON data could not be accumulated so I'm pasting it 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 for those column in table whose values are NA I wanted them to fill up by extracting the JSON objects from each records.

So here's a code I tried, 

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=?)

So for each JSON object there are multiple address components so from them I just want to access the first address component only. But can't actually get a way by which it could be done.

Any suggestion would be very helpful. Thanks

Upvotes: 1

Views: 5765

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123809

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

That's your first challenge. The other challenges are:

  • the address component type is specified as a value in the "types" attribute, so you can't access them by name, and
  • some types (e.g., "sublocality") can appear more than once.

Inside your for row in cursor.fetchall(): loop you can save the "ID" and "JSON" column values as simple variables,

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

You can extract the address component values into a dictionary of lists, and then concatenate the lists into 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']
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)

with the pprint (for demonstration purposes only) producing

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

You could then proceed to update the table with code like this:

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)

Upvotes: 2

SarathSprakash
SarathSprakash

Reputation: 4624

Guessing you are getting a proper json object from the database. If not you can dump and load the data.

dumpData = json.dumps(jsonResponse["data"])
jsonData = json.loads(dumpData)

The problem is the loop.

address_components = data['results'][0]["address_components"]
Street_Numb = None
for item in address_components:
  if 'street_number'in item['types']:
    Street_Numb = item.get("long_name")

DEMO

Upvotes: 0

Related Questions