Phone

+919997782184

Email

support@roboticswithpython.com

Geeks of Coding

Join us on Telegram

Home Forums Assignment courserra Python for Everybody – Specialization Using Database with Python Week 5 Assignment Databases and Visualization (peer-graded)

Viewing 1 reply thread
  • Author
    Posts
    • #921
      Abhishek TyagiAbhishek Tyagi
      Keymaster
      import sqlite3
      import json
      import codecs
      
      conn = sqlite3.connect('geodata.sqlite')
      cur = conn.cursor()
      
      cur.execute('SELECT * FROM Locations')
      fhand = codecs.open('where.js','w', "utf-8")
      fhand.write("myData = [\n")
      count = 0
      for row in cur :
          data = str(row[1])
          try: js = json.loads(str(data))
          except: continue
      
          if not('status' in js and js['status'] == 'OK') : continue
      
          lat = js["results"][0]["geometry"]["location"]["lat"]
          lng = js["results"][0]["geometry"]["location"]["lng"]
          if lat == 0 or lng == 0 : continue
          where = js['results'][0]['formatted_address']
          where = where.replace("'","")
          try :
              print (where, lat, lng)
      
              count = count + 1
              if count > 1 : fhand.write(",\n")
              output = "["+str(lat)+","+str(lng)+", '"+where+"']"
              fhand.write(output)
          except:
              continue
      
      fhand.write("\n];\n")
      cur.close()
      fhand.close()
      print (count, "records written to where.js")
      print ("Open where.html to view the data in a browser")
    • #922
      Abhishek TyagiAbhishek Tyagi
      Keymaster

      Week 5 Assignment Databases and Visualization (peer-graded)/geodata/geoload.py

      import urllib
      import sqlite3
      import json
      import time
      import ssl
      
      # If you are in China use this URL:
      # serviceurl = "http://maps.google.cn/maps/api/geocode/json?"
      serviceurl = "http://maps.googleapis.com/maps/api/geocode/json?"
      
      # Deal with SSL certificate anomalies Python > 2.7
      # scontext = ssl.SSLContext(ssl.PROTOCOL_TLSv1)
      scontext = None
      
      conn = sqlite3.connect('geodata.sqlite')
      cur = conn.cursor()
      
      cur.execute('''
      CREATE TABLE IF NOT EXISTS Locations (address TEXT, geodata TEXT)''')
      
      fh = open("where.data")
      count = 0
      for line in fh:
          if count > 200 : break
          address = line.strip()
          print ('')
          cur.execute("SELECT geodata FROM Locations WHERE address= ?", (buffer(address), ))
      
          try:
              data = cur.fetchone()[0]
              print ("Found in database ",address)
              continue
          except:
              pass
      
          print ('Resolving', address)
          url = serviceurl + urllib.urlencode({"sensor":"false", "address": address})
          print ('Retrieving', url)
          uh = urllib.urlopen(url, context=scontext)
          data = uh.read()
          print ('Retrieved',len(data),'characters',data[:20].replace('\n',' '))
          count = count + 1
          try: 
              js = json.loads(str(data))
              # print js  # We print in case unicode causes an error
          except: 
              continue
      
          if 'status' not in js or (js['status'] != 'OK' and js['status'] != 'ZERO_RESULTS') : 
              print ('==== Failure To Retrieve ====')
              print (data)
              break
      
          cur.execute('''INSERT INTO Locations (address, geodata) 
                  VALUES ( ?, ? )''', ( buffer(address),buffer(data) ) )
          conn.commit() 
          time.sleep(1)
      
      print ("Run geodump.py to read the data from the database so you can visualize it on a map.")
Viewing 1 reply thread
  • You must be logged in to reply to this topic.