14
\$\begingroup\$

I recently finished the Using Databases with Python course. To figure out whether I really understood what was being taught, I build an application which does roughly the same but uses a different formatting.

The idea is to have a list with addresses, look up the corresponding coordinates using the Google Maps API, store name/address/coordinates in a SQLite database, retrieve the data from the database and show the retrieved locations on a map.

To show my colleagues what keeps me busy during the weekends, I inserted their addresses, plotted a map and showed them the might of Python. Of-course, all data here is example data and not the actual dataset. The real set is less than 200 addresses though, so the difference shouldn't matter much. All code is real.

geoload.py queries the API for all addresses provided in locations.data. The results will be stored in geodata.sqlite. All data previously encountered will be skipped instead of retrieved again to allow multi-stage loading (which is helpful in cases of rate limiting). geodump.py will retrieve the data from geodata.sqlite and put data ready for display in JSON format in locations.js. locations.html will display a map with markers placed on the locations provided. Hovering over the markers will tell who's stationed there.

locations.data

Headquarters, Havenmeesterweg 1, Haarlemmermeer
Alice, Spoorstraat 2, Leeuwarden
Bob, Stadskanaal
Charlie, Maastricht
Devon, Woerden
Eddy, P.J. Jongstraat, Lutjebroek
Freddy, Roosendaal
Giles, Almere
Harry, Spoorstraat 4, Winterswijk
Igor, Middelburg
Janine, Terschelling

locations.js

locations = [
['Headquarters',52.3046539,4.7588565],
['Alice',53.1975889,5.8055371],
['Bob',52.9919853,6.9462217],
['Charlie',50.8513682,5.6909725],
['Devon',52.0798287,4.8627239],
['Eddy',52.6979589,5.2007523],
['Freddy',51.535849,4.4653213],
['Giles',52.3507849,5.2647016],
['Harry',51.9698835,6.7204984],
['Igor',51.4987962,3.610998],
['Janine',53.3978747,5.3466786]
];

geoload.py

import urllib
import sqlite3
import json


def main():
    SERVICE_URL = "http://maps.googleapis.com/maps/api/geocode/json?"

    conn = sqlite3.connect('geodata.sqlite')
    cur = conn.cursor()

    cur.execute('''
    CREATE TABLE IF NOT EXISTS Locations (target TEXT,
            address TEXT, geodata TEXT)''')

    filehandle = open("locations.data")
    count = 0
    for line in filehandle:
        target, _, address = line.partition(', ')
        address = address.strip()
        cur.execute(
                "SELECT geodata FROM Locations WHERE target= ?",
                (buffer(target), ))

        try:
            data = cur.fetchone()[0]
            print "Found in database ", address
            continue
        except:
            pass

        print 'Resolving', address
        url = SERVICE_URL + urllib.urlencode(
            {"sensor": "false", "address": address})
        print 'Retrieving', url
        urlhandle = urllib.urlopen(url)
        data = urlhandle.read()
        print 'Retrieved', len(data), \
              'characters', data[:20].replace('\n', ' ')
        count = count + 1
        try:
            js = json.loads(str(data))
        except:
            continue

        if 'status' not in js or (
                js['status'] != 'OK' and js['status'] != 'ZERO_RESULTS'):
            print '==== Failure To Retrieve ====', \
                  data
            break

        cur.execute('''INSERT INTO Locations (target, address, geodata)
                VALUES ( ?, ?, ? )''', (
                buffer(target), buffer(address), buffer(data)))
        conn.commit()

if __name__ == '__main__':
    main()

geodump.py

import sqlite3
import json
import codecs


def main():
    OUTPUT_FILE = 'locations.js'

    conn = sqlite3.connect('geodata.sqlite')
    cur = conn.cursor()

    cur.execute('SELECT * FROM Locations')
    filehandle = codecs.open(OUTPUT_FILE, 'w', "utf-8")
    filehandle.write("locations = [\n")
    count = 0
    for row in cur:
        '''
        row[0]: target
        row[1]: address
        row[2]: lat & long
        '''
        target = str(row[0]).split(',')[0]
        data = str(row[2])
        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
        loc = js['results'][0]['formatted_address'].replace("'", "")
        try:
            print target, loc, lat, lng

            count = count + 1
            if count > 1:
                filehandle.write(",\n")
            output = "['"+target+"',"+str(lat)+","+str(lng)+"]"
            filehandle.write(output)
        except:
            continue

    filehandle.write("\n];\n")
    cur.close()
    filehandle.close()
    print count, "records written to", OUTPUT_FILE

if __name__ == '__main__':
    main()

locations.html

<html>
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
    <meta charset="utf-8">
    <title>Employee location overview</title>
    <script src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
    <script src="http://google-maps-utility-library-v3.googlecode.com/svn/trunk/markerclusterer/src/markerclusterer_compiled.js"></script>
    <script src="locations.js"></script>
    <script>
      function initialize() {
        // 51.0, 4.0 is roughly the center of The Netherlands
        const Lat = 51.0;
        const Lng = 4.0;
        var mapOptions = {
          // 7 keeps the country roughly full-screen, adjust if it doesn't
          zoom: 7,
          center: new google.maps.LatLng(Lat,Lng),
          mapTypeId: google.maps.MapTypeId.ROADMAP
        }
        var map = new google.maps.Map(document.getElementById('map_canvas'), mapOptions);

        i = 0;
        var markers = [];
        for ( pos in locations ) {
            i = i + 1;
            var row = locations[pos];
            window.console && console.log(row);
            console.log(row)
            var currentCoord = new google.maps.LatLng(row[1], row[2]);
            var marker = new google.maps.Marker({
                position: currentCoord,
                map: map,
                title: row[0]
            });
            markers.push(marker);
        }
      }
    </script>
  </head>
  <body onload="initialize()">
    <div id="map_canvas" style="height: 100%"></div>
  </body>
</html>

I think the viewer is written in such a way I won't need Bootstrap to make it mobile-ready. However, I haven't tested it on mobile yet. It's quite straight-forward, but I might have some pitfalls there.

Now, I'm no JavaScript guru, but for some reason it breaks when I add 'use strict'. So I'm probably doing something hazardous, wrong, or both. For now it works, though.

I'm aware there is no argument handling on the Python side of things. That's on purpose. It's easier in this particular situation. If it ever gets upgraded into something more complex, there'll probably be a web server involved so I'm pretty certain there will never be a need for argument handling. The input should be relatively well handled. If it doesn't produce anything sensible it should get discarded.

I'm quite unhappy with the number of magic numbers, but finding a sensible solution for them was harder than expected.

Viewer in action:

Map with markers

\$\endgroup\$
  • \$\begingroup\$ Yeah but: why do you write a JS file instead of a plain JSON one? \$\endgroup\$ – Zorgatone Nov 28 '16 at 17:32
  • \$\begingroup\$ @Zorgatone Would it be more appropriate to write to a .json? If so, feel free to make that an answer. Feel free to review the viewer as well, it hasn't been touched yet :-) \$\endgroup\$ – Mast Nov 28 '16 at 18:45
  • \$\begingroup\$ I'm not too familiar with Python. I'll try later maybe. But yeah you're saying that you're using JSON but that is just plain JavaScript. I'd use Ajax on the client to load and parse JSON from the server \$\endgroup\$ – Zorgatone Nov 28 '16 at 19:02
  • \$\begingroup\$ I'm talking about the locations.js file anyway \$\endgroup\$ – Zorgatone Nov 28 '16 at 19:57
  • \$\begingroup\$ Wrong: you have an assignment and it's not valid Json. JSON can't contain executable code. You can only have one root element (which is an array or object with quotes propertie names) \$\endgroup\$ – Zorgatone Nov 28 '16 at 20:46
6
\$\begingroup\$

One function per file? I know you can do better than that.

Compare geoload.py with:

import urllib
import sqlite3
import json


SERVICE_URL = "http://maps.googleapis.com/maps/api/geocode/json?"


def main(dbfile='geodata.sqlite', datafile='locations.data'):
    conn = get_connexion(dbfile)

    with open(datafile) as filehandle:
        for line in filehandle:
            target, _, address = line.partition(', ')
            if exists_in_db(target, conn):
                continue
            address = address.strip()
            geo_data = locate(address)
            if geo_data is not None:
                save_location(target, address, geo_data, conn)

    conn.close()

I’ll let you fill in the blanks based on your current code. But a few things to notice:

  • close your resources: conn.close() was missing in your code, wraps open (or codecs.open) in a with clause.
  • you conn.commit() but in case something went wrong, you want to use conn.rollback() as well. Luckily, conn is a context manager that can wrap it automatically:

    with conn:
        with conn.cursor() as cur:
            cur.execute(...)
        # cur is auto closed at the end of the with block
    # conn.commit() or conn.rollback() is called depending if there is a pending exception or not
    
  • buffer is unneccessary, plain str are enough when dealing with TEXT variables in SQLite

  • You can simplify retrieving data from a dictionary using the dict.get method: if js.get('status') not in {'OK', 'ZERO_RESULTS'}:
  • You can simplify writing json to a file:

    with <whatever> as f:
        f.write('location = ')
        json.dump(locations)
        f.write(';')
    

    all you need to do is build the locations list (using a list-comp, preferably):

    def parse_location(location):
        target, _, data = location
        target = target.split(',')[0]
        try:
            js = json.loads(data)
        except ValueError:
            return
    
        if js.get('status') != 'OK':
            return
    
        lat = js["results"][0]["geometry"]["location"]["lat"]
        lng = js["results"][0]["geometry"]["location"]["lng"]
        if lat == 0 or lng == 0:
            return
    
        loc = js['results'][0]['formatted_address'].replace("'", "")
        # print target, loc, lat, lon
        return target, lat, lon
    
    def main():
        ...
        locations = filter(None, (parse_location(l) for l in cur.execute('SELECT * FROM Locations')))
        f.write('location = ')
        json.dump(locations)
        f.write(';')
    
  • Do not use bare excepts. json.loads can raise a subclass of ValueError when decoding failed. At least catch that.

\$\endgroup\$
7
\$\begingroup\$

In geoload.py use with..as to ensure it is closed, even if any code in-between raises an exception:

with open("locations.data") as filehandle:
    ...

In geodump.py, this is a code-smell:

for row in cur:
        '''
        row[0]: target
        row[1]: address
        row[2]: lat & long
        '''

It just asks for you to do:

for target, _, data in cur:
    ...

I used _ because you never use the address here. This still makes sense, even though you do some more processing on it afterwards.

Regarding that, a value returned from a db is usually already a string, so there should be no need to cast target = str(...).

For your output, I would use str.format, which makes it a lot easier to read:

output = "['{}',{},{}]".format(target, lat, lng)
\$\endgroup\$
7
\$\begingroup\$

You can simplify geoload by using the requests package. In fact it says this in the urllib documentation:

See also: The Requests package is recommended for a higher-level HTTP client interface.

Doing this single handedly changes:

print 'Resolving', address
url = SERVICE_URL + urllib.urlencode(
    {"sensor": "false", "address": address})
print 'Retrieving', url
urlhandle = urllib.urlopen(url)
data = urlhandle.read()
print 'Retrieved', len(data), \
      'characters', data[:20].replace('\n', ' ')
count = count + 1
try:
    js = json.loads(str(data))
except:
    continue

to:

r = requests.get(SERVICE_URL, params={"sensor": "false", "address": address})
js = r.json()
if js is None:
    continue

After this you can simplify your status check. First you can default to None if status isn't in the dictionary with js.get. Then you can check if the status is not in a list, tuple or set. Simplifying your status check to:

if js.get('status') not in ('OK', 'ZERO_RESULTS'):

There are only a couple more changes I'd make in geoload:

  • Use with for both conn and filehandle. You don't close conn, and if there's an unexpected error both aren't signaled to close.
  • Rather than using partition you can use split with it's second argument, maxsplit. Allowing you to remove the code to throw away the separator:

    target, address = line.split(', ', 1)
    

In geodump, I'd split the code out more. You don't need to interact with the file and the SQL database in the same function. You can yield data from the database and then use it with the file. This makes your code more re-usable and less WET.

When moving the SQL only code into it's own function I'd keep to changing as little as possible. By only normalizing the data. Take:

def read_records():
    conn = sqlite3.connect('geodata.sqlite')
    with conn:
        cur = conn.cursor()
        cur.execute('SELECT * FROM Locations')
        for row in cur:
            '''
            row[0]: target
            row[1]: address
            row[2]: lat & long
            '''
            target = str(row[0]).split(',', 1)[0]
            data = str(row[2])
            try:
                js = json.loads(data)
            except:
                continue
            yield target, row[1], js

After this I'd filter bad data and then write to the file. As memory isn't too much of a concern with about 200 users in your set, you could build a list, and use ',\n'.join. Or carry on with the way that you have it at the moment, as it should work ok with way more people.

Finally you seem to have a lot of debugging print statements in your code. You could instead use logging. And you should specify the exception you're guarding against when using except.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.