rewrite to update data explicitely1.5.1

2 files changed, 37 insertions, 40 deletions

diff --git a/api.py b/api.py
index c733649..eda5592 100644
--- a/api.py
+++ b/api.py
@@ -2,8 +2,6 @@
 
 import os
 import psycopg2
-import psycopg2.pool
-import psycopg2.extras
 from flask import Flask, g, render_template, send_from_directory
 from werkzeug.contrib.cache import SimpleCache
 
@@ -18,41 +16,40 @@ db_config = {
     "dbname": os.environ.get("POSTGRESQL_DEST_DB") or "vainsocial-web"
 }
 
-def dbpool():
-    if not hasattr(g, "db_pool"):
-        g.db_pool = psycopg2.pool.ThreadedConnectionPool(
-            **db_config, minconn=1, maxconn=10)
-    return g.db_pool
+data = {}
 
-@app.teardown_appcontext
-def db_disconnect(err):
-    if hasattr(g, "db_pool"):
-        g.db_pool.closeall()
+def update_vals():
+    con = psycopg2.connect(**db_config)
+    c = con.cursor()
 
-@app.context_processor
-def sql_processor():
     def sql_val(query):
-        val = cache.get(query)
-        if val is not None:
-            return val.get(0)
-
-        con = dbpool().getconn()
-        c = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
-
         c.execute(query)
-        val = c.fetchone()
-        cache.set(query, val, timeout=60*60)  # seconds
+        return c.fetchone()[0]
 
-        c.close()
-        dbpool().putconn(con)
+    data["turrets_rebuilt"] = sql_val("SELECT SUM(turret_kills) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL")
+    data["minions_for_nothing"] = sql_val("SELECT SUM(minion_kills) FROM participant WHERE NOT winner AND created_at > NOW() - '1 day'::INTERVAL")
+    data["minions_total"] = sql_val("SELECT SUM(minion_kills) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL")
+    data["fountains_wasted"] = sql_val("SELECT COUNT((item_grants->>'*1045_Item_FountainOfRenewal*')::INT)-COUNT((item_uses->>'*1045_Item_FountainOfRenewal*')::INT) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL")
+    data["crystal_sentries"] = sql_val("SELECT SUM(participant.crystal_mine_captures) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL")
+    data["flares_sold"] = sql_val("SELECT SUM((participant.item_sells->>'*1038_Item_Flare*')::INT) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL")
+    data["minions_per_kraken"] = sql_val("SELECT (SUM(match.duration)/60*5) / SUM(roster.kraken_captures) FROM match JOIN roster ON roster.match_api_id=match.api_id WHERE match.created_at > NOW() - '1 day'::INTERVAL")
+    data["minion_candies"] = sql_val("SELECT SUM((item_uses->>'*1041_Item_MinionCandy*')::INT) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL")
+    data["infusions_min"] = sql_val("SELECT SUM(match.duration)/60 / (SUM((participant.item_uses->>'*1052_Item_WeaponInfusion*')::INT) + SUM((participant.item_uses->>'*1053_Item_CrystalInfusion*')::INT)) FROM participant JOIN roster ON participant.roster_api_id=roster.api_id JOIN match ON roster.match_api_id=match.api_id WHERE participant.created_at > NOW() - '1 day'::INTERVAL")
+    data["scout_traps"] = sql_val("SELECT SUM((item_uses->>'*1054_Item_ScoutTrap*')::INT) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL")
+    data["saw_turrets"] = sql_val("SELECT SUM(turret_kills) FROM participant WHERE hero='*SAW*' AND created_at > NOW() - '1 day'::INTERVAL")
+    data["matches"] = sql_val("SELECT COUNT(*) FROM match WHERE created_at > NOW() - '1 day'::INTERVAL")
+    data["minions_min"] = sql_val("SELECT SUM(minion_kills)/(24*60*60)::FLOAT FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL")
 
-        return val.get(0)
-
-    return dict(sql_val=sql_val)
+    c.close()
 
 @app.route("/")
 def index():
-    return render_template("index.html")
+    return render_template("index.html", data=data)
+
+@app.route("/update")
+def update():
+    update_vals()
+    return render_template("index.html", data=data)
 
 @app.route("/assets/<path:path>")
 def send_assets(path):
diff --git a/templates/index.html b/templates/index.html
index e6b6ee1..4b96767 100644
--- a/templates/index.html
+++ b/templates/index.html
@@ -68,7 +68,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM(turret_kills) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL") }}<b class="currency"><br/></b></h4>
+                            <h4>{{ data.turrets_rebuilt }}<b class="currency"><br/></b></h4>
                         </div>
                         <p class="description">rebuilt by workers every day</p>      
                     </div> 
@@ -83,7 +83,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM(minion_kills) FROM participant WHERE NOT winner AND created_at > NOW() - '1 day'::INTERVAL") }} <b class="currency"><br/>out of {{ sql_val("SELECT SUM(minion_kills) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL") }}<br/></b></h4>
+                            <h4>{{ data.minions_for_nothing }} <b class="currency"><br/>out of {{ data.minions_total }}<br/></b></h4>
                         </div>
                         <p class="description">died for nothing</p>      
                     </div> 
@@ -98,7 +98,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT COUNT((item_grants->>'*1045_Item_FountainOfRenewal*')::INT)-COUNT((item_uses->>'*1045_Item_FountainOfRenewal*')::INT) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL") }}</h4>
+                            <h4>{{ data.fountains_wasted }}</h4>
                         </div>
                         <p class="description">bought for the looks</p>      
                     </div> 
@@ -113,7 +113,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM(participant.crystal_mine_captures) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL") }}</h4>
+                            <h4>{{ data.crystal_sentries }}</h4>
                         </div>
                         <p class="description">slaughtered</p>      
                     </div> 
@@ -128,7 +128,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM((participant.item_sells->>'*1038_Item_Flare*')::INT) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL") }}</h4>
+                            <h4>{{ data.flares_sold }}</h4>
                         </div>
                         <p class="description">thrown in trash</p>      
                     </div> 
@@ -143,7 +143,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT (SUM(match.duration)/60*5) / SUM(roster.kraken_captures) FROM match JOIN roster ON roster.match_api_id=match.api_id WHERE match.created_at > NOW() - '1 day'::INTERVAL") }}</h4>
+                            <h4>{{ data.minions_per_kraken }}</h4>
                         </div>
                         <p class="description">are needed to unleash a Kraken</p>      
                     </div> 
@@ -158,7 +158,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM((item_uses->>'*1041_Item_MinionCandy*')::INT) FROM participant WHERE created_at > NOW() - '1 day'::INTERVAL") }}</h4>
+                            <h4>{{ data.minion_candies }}</h4>
                         </div>
                         <p class="description">Minion parties</p>      
                     </div> 
@@ -173,7 +173,7 @@
                     <div class="content">
                         <div class="price">
                             <h6>every</h6>
-                            <h4>{{ sql_val("SELECT SUM(match.duration)/60 / (SUM((participant.item_uses->>'*1052_Item_WeaponInfusion*')::INT) + SUM((participant.item_uses->>'*1053_Item_CrystalInfusion*')::INT)) FROM participant JOIN roster ON participant.roster_api_id=roster.api_id JOIN match ON roster.match_api_id=match.api_id WHERE participant.created_at > NOW() - '1 day'::INTERVAL") }}<b class="currency"><br/>minutes</b></h4>
+                            <h4>{{ data.infusions_min }}<b class="currency"><br/>minutes</b></h4>
                         </div>
                         <p class="description">a hero gets high</p>      
                     </div> 
@@ -189,7 +189,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM((item_uses->>'*1054_Item_ScoutTrap*')::INT) FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL") }}<b class="currency"><br/></b></h4>
+                            <h4>{{ data.scout_traps }}<b class="currency"><br/></b></h4>
                         </div>
                         <p class="description">made the Fold a Dangerous Place</p>      
                     </div> 
@@ -205,7 +205,7 @@
                     <div class="content">
                         <div class="price">
                             <h6></h6>
-                            <h4>{{ sql_val("SELECT SUM(turret_kills) FROM participant WHERE hero='*SAW*' AND created_at > NOW() - '1 day'::INTERVAL") }}<b class="currency"><br/></b></h4>
+                            <h4>{{ data.saw_turrets }}<b class="currency"><br/></b></h4>
                         </div>
                         <p class="description">were pushed by SAW in the last 24h</p>      
                     </div> 
@@ -222,7 +222,7 @@
                         <p class="description">
                             Based on Vainsocial users' data from the past 24 hours that by no means is representative to the whole player base of Vainglory.
                         </p>
-			<!-- {{ sql_val("SELECT COUNT(*) FROM match WHERE created_at > NOW() - '1 day'::INTERVAL") }} matches are taken into account for the calculations. -->
+                        <!-- {{ data.matches }} matches are taken into account for the calculations. -->
                     </div>
                     <div class="filter"></div>
                 </div>
@@ -261,7 +261,7 @@
     
 
     <script type="text/javascript">
-        var minions_per_second = {{ sql_val("SELECT SUM(minion_kills)/(24*60*60)::FLOAT FROM participant WHERE participant.created_at > NOW() - '1 day'::INTERVAL") }};
+        var minions_per_second = {{ data.minions_min }};
         var minions = 0;
         // sum(minions killed) / (seconds per day)
         setInterval(() => {