locations-jinja.py

import cherrypy
import jinja2
import sqlite3 as sql
import math, os
import time

JINJA_ENVIRONMENT = jinja2.Environment(loader=jinja2.FileSystemLoader(os.path.join(os.path.dirname(__file__),'templates')),extensions=['jinja2.ext.autoescape'])

DB = 'locations.db'  

class LocationsWebsite(object):
    @cherrypy.expose
    @cherrypy.tools.gzip()
    def index(self):
        template = JINJA_ENVIRONMENT.get_template('index.html')
        template_values = {'locations': self.get_locations()} #template_values is a dict. key is 'locations', values is a list of lists of two items (long, lat)
        return template.render(template_values) #make and serve the webpage
           
    def get_locations(self):
        locations = []
        latitudes = self.get_latitudes()
        longitudes = self.get_longitudes()
        dates = self.get_dates()
        #times = [time[:-7] for time in self.get_times()]
        tids = self.get_tids()
        times = self.get_report_times()
        for i in range(len(latitudes)):
            locations.append([longitudes[i],latitudes[i],dates[i],times[i],tids[i]])  #make list of lists to enable jinja render as columns 
        return locations
  
    def get_latitudes(self):
        latitudes = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT latitude FROM Location ORDER BY tid DESC;''')
            for latitude, in results:
                latitudes.append(str(latitude))
        return latitudes

    def get_longitudes(self):
        longitudes = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT longitude FROM Location ORDER BY tid DESC;''')
            for longitude, in results:
                longitudes.append(str(longitude))
        return longitudes
   
    def get_dates(self):
        dates = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT date FROM Location ORDER BY tid DESC;''')
            for date, in results:
                readabledate = time.strftime('%Y-%m-%d', time.localtime(date))
                dates.append(str(readabledate))
        return dates
   
    def get_times(self):
        times = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT time FROM Location ORDER BY tid DESC;''')
            for time, in results:
                times.append(str(time))
        return times
   
    def get_tids(self):
        tids = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT tid FROM Location ORDER BY tid DESC;''')
            for tid, in results:
                tids.append(str(tid))
        return tids
   
    def get_report_times(self):
        times = []
        with sql.connect(DB) as cur:
            results = cur.execute('''SELECT time FROM Location ORDER BY tid DESC;''')
            for Time, in results:
                readabletime = time.strftime('%H:%M:%S', time.localtime(Time))
                times.append(str(readabletime))
        return times

if __name__ == '__main__':
    cherrypy.config.update({'server.socket_host': '0.0.0.0'})
    cherrypy.config.update({'server.socket_port': 3000})
    cherrypy.quickstart(LocationsWebsite(), '/', {'/': {'tools.gzip.on': True}})