# Find my colleagues

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
Charlie, Maastricht
Devon, Woerden
Eddy, P.J. Jongstraat, Lutjebroek
Freddy, Roosendaal
Giles, Almere
Harry, Spoorstraat 4, Winterswijk
Igor, Middelburg
Janine, Terschelling


### locations.js

locations = [
['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]
];


import urllib
import sqlite3
import json

def main():

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

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

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

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

url = SERVICE_URL + urllib.urlencode(
print 'Retrieving', url
urlhandle = urllib.urlopen(url)
print 'Retrieved', len(data), \
'characters', data[:20].replace('\n', ' ')
count = count + 1
try:
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 ( ?, ?, ? )''', (
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[2]: lat & long
'''
target = str(row[0]).split(',')[0]
data = str(row[2])
try:
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
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>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
<meta charset="utf-8">
<title>Employee location overview</title>
<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,
}
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]);
position: currentCoord,
map: map,
title: row[0]
});
markers.push(marker);
}
}
</script>
<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:

• Yeah but: why do you write a JS file instead of a plain JSON one? – Zorgatone Nov 28 '16 at 17:32
• @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 :-) – Mast Nov 28 '16 at 18:45
• 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 – Zorgatone Nov 28 '16 at 19:02
• I'm talking about the locations.js file anyway – Zorgatone Nov 28 '16 at 19:57
• 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) – Zorgatone Nov 28 '16 at 20:46

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

Compare geoload.py with:

import urllib
import sqlite3
import 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
if geo_data is not None:

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:
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

# 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.

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[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)


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(
print 'Retrieving', url
urlhandle = urllib.urlopen(url)
print 'Retrieved', len(data), \
'characters', data[:20].replace('\n', ' ')
count = count + 1
try:
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[2]: lat & long
'''
target = str(row[0]).split(',', 1)[0]
data = str(row[2])
try:

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.