Trails.py

import bottle
from bottle import route, run, template, Bottle
from bottle import request, post, redirect
import cgitb, csv
import random
from random import choice
import os
from sqlalchemy import create_engine
import MySQLdb

THIS_FOLDER = os.path.dirname(os.path.abspath(__file__))
cgitb.enable()

dbhost='StephenBroomell.mysql.pythonanywhere-services.com'
dbuser='StephenBroomell'
dbpass='L@bW3b31'
dbname='StephenBroomell$trial_vars'

#sqlalchemy and mysql setup
mysql_connect_string = 'mysql+mysqldb://%s:%s@%s/%s?charset=utf8' % (dbuser, dbpass, dbhost, dbname)

engine = create_engine(mysql_connect_string)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.bind = engine
from sqlalchemy import Column, Integer, String, Float, SmallInteger
from sqlalchemy.orm import sessionmaker
DBSession = sessionmaker()
DBSession.bind = engine

#sqlalchemy model class for table 'nevek'
class Variable(Base):
    __tablename__ = 'vars'
    # Here we define columns for the table nevek
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    value = Column(String(250), nullable=False)

#get records from table nevek with sqlalchemy
def get_vars_from_db():
    session = DBSession()
    result = session.query(Variable).all()
    session.close()
    return result

#get records from table nevek
def get_raw_sql(sql):
    conn = MySQLdb.connect(host=dbhost, user=dbuser,
        passwd=dbpass, db=dbname, charset='utf8')
    cur = conn.cursor()
    cur.execute(sql)
    res = cur.fetchall()
    cur.close()
    conn.close()
    return res

def get_var(table, name):
    return table[table[0:][1].index(name)][2]

def add_vars(name, value):
    session = DBSession()
    var = Variable(name=name, value=value)
    session.add(var)
    session.commit()



def set_vars_value(name, value):
    conn = MySQLdb.connect(host=dbhost, user=dbuser,
        passwd=dbpass, db=dbname, charset='utf8')
    cursor = conn.cursor()
    cursor.execute ("""
       UPDATE vars
       SET name=%s, value=%s
       WHERE ID=%s
    """, (name, value, vars[0:][1].index(name)))

context = {}

@route('/location', method=['POST'])
def do_location():

    global context
    city = int(request.forms.get('location'))
    unit = request.forms.get('unit')

    get_raw_sql('TRUNCATE TABLE vars')

    context = {}

    add_vars("city",city)
    add_vars("unit",unit)
    add_vars("current_trial",1)
    #add_vars('context',context)
    if (city > 51):
        return template('city_select.html', locErr="Please Select a city from the list", page_name="- Location", **context)
    else:
        redirect('/trials')

@route('/trials')
def trials():

    global context
    #response.set_cookie("visited", "yes");
    dates = ["na"]*7
    highs = ["na"]*7
    lows = ["na"]*7

    vars = get_raw_sql("SELECT *  FROM vars")
    unit = context['unit']
    city = context['city']
    trialsPassed = []
    trialsNum = 1
    trialsLeft = []

    file = os.path.join(THIS_FOLDER, 'data/'+str(city)+'_web.csv')
    cityData = list(csv.reader(open(file)))

    trialsLeft = range(len(cityData))
    rowNum = random.choice(trialsLeft)
    trialsPassed.append(rowNum)
    trialsLeft.remove(rowNum)
    datefile = os.path.join(THIS_FOLDER,'data/Dates.csv')
    dates = list(csv.reader(open(datefile)))[rowNum]
    highs = cityData[rowNum][0:7]
    lows = cityData[rowNum][7:14]

    if (unit == "C"):
        highs = [int((int(x)-32)*(5.0/9)) for x in highs]
        lows = [int((int(x)-32)*(5.0/9)) for x in lows]

    colours = ["red", "orange","yellow","green","blue", "indigo","violet"]


    context = {

        "colour":colours[trialsNum/8],
        "page_name":"Trials",
        "current_trial" : trialsNum,
        "city": city,
        "cityData" : cityData,
        "day_1" : dates[0],
        "day_2" : dates[1],
        "day_3" : dates[2],
        "day_4" : dates[3],
        "day_5" : dates[4],
        "day_6" : dates[5],
        "day_7" : dates[6],
        "high_1" : highs[0],
        "high_2" : highs[1],
        "high_3" : highs[2],
        "high_4" : highs[3],
        "high_5" : highs[4],
        "high_6" : highs[5],
        "high_7" : highs[6],
        "low_1" : lows[0],
        "low_2" : lows[1],
        "low_3" : lows[2],
        "low_4" : lows[3],
        "low_5" : lows[4],
        "low_6" : lows[5],
        "low_7" : lows[6],
        "trial_num" : (trialsNum/52*100),
        "trials_passed" : trialsPassed,
        "trials_left" : trialsLeft,
        "unit":unit,
        "week" : rowNum,
        "row" : []

    }

    return template('trials.html', **context)

@route('/trials', method=['POST'])
def do_trials():

    normal = request.forms.get('normalcheck')
    confidence = request.forms.get('confidencecheck')

    global context

    if (get_var(vars,'current_trial') == 52):
        with open('results.csv', 'w+') as resultFile:
            wr = csv.writer(resultFile, dialect='excel')
            id = len(list(csv.reader(resultFile)))+1
            wr.writerow([id, context['city']] + context['row'])
        redirect('/')
    else:
        dates = ["na"]*7
        highs = ["na"]*7
        lows = ["na"]*7

        colours = ["red", "orange","yellow","green","blue", "indigo","violet"]

        cityData = context['cityData']
        rowNum = choice(context['trials_left'])
        context['trials_passed'].append(rowNum)
        context['trials_left'].remove(rowNum)
        dates = list(csv.reader(open(os.path.join(THIS_FOLDER,'data/Dates.csv'))))[rowNum]
        highs = cityData[rowNum][0:7]
        #print highs
        lows = cityData[rowNum][7:14]
        #print lows

        context['current_trial'] += 1
        colours = ["red", "orange","yellow","green","blue", "indigo","violet"]


        context["colour"] = colours[context['current_trial']/8]
        context["page_name"] = "Trials"
        context["day_1"] = dates[0]
        context["day_2"] = dates[1]
        context["day_3"] = dates[2]
        context["day_4"] = dates[3]
        context["day_5"] = dates[4]
        context["day_6"] = dates[5]
        context["day_7"] = dates[6]
        context["high_1"] = highs[0]
        context["high_2"] = highs[1]
        context["high_3"] = highs[2]
        context["high_4"] = highs[3]
        context["high_5"] = highs[4]
        context["high_6"] = highs[5]
        context["high_7"] = highs[6]
        context["low_1"] = lows[0]
        context["low_2"] = lows[1]
        context["low_3"] = lows[2]
        context["low_4"] = lows[3]
        context["low_5"] = lows[4]
        context["low_6"] = lows[5]
        context["low_7"] = lows[6]
        context["trial_num"] = round(context['current_trial']/52.0*100)
        context["row"] = context['row'] + [rowNum,normal,confidence]



    return template('trials.html', **context)