Isolated SQL Functions, Passed JS Data to Flask, time series in a separate function

8 files changed, 123 insertions, 84 deletions

diff --git a/__pycache__/app.cpython-38.pyc b/__pycache__/app.cpython-38.pyc
index 205b647..8c35454 100644
--- a/__pycache__/app.cpython-38.pyc
+++ b/__pycache__/app.cpython-38.pyc
diff --git a/__pycache__/sql_functions.cpython-38.pyc b/__pycache__/sql_functions.cpython-38.pyc
new file mode 100644
index 0000000..9956f8f
--- /dev/null
+++ b/__pycache__/sql_functions.cpython-38.pyc
diff --git a/__pycache__/time_series_model.cpython-38.pyc b/__pycache__/time_series_model.cpython-38.pyc
new file mode 100644
index 0000000..8c3621b
--- /dev/null
+++ b/__pycache__/time_series_model.cpython-38.pyc
diff --git a/app.py b/app.py
index c443e68..431bd66 100644
--- a/app.py
+++ b/app.py
@@ -1,35 +1,34 @@
 from flask import Flask, render_template
-from flask import Markup
-import sqlite3 as sql
+from flask import request
 import pandas as pd
+from sql_functions import run_sql_pandas, get_list_of_dict
+from time_series_model import plot_data
 
 app = Flask(__name__)
 
-
-def execute_sql_statement(sql_statement, conn):
-    cur = conn.cursor()
-    cur.execute(sql_statement)
-    rows = cur.fetchall()
-    return rows
-
-def get_list_of_dict(keys, list_of_tuples):
-     list_of_dict = [dict(zip(keys, values)) for values in list_of_tuples]
-     return list_of_dict
-
 def get_cities_list():
-    conn = sql.connect("database.db")
     sql_statement = 'SELECT DISTINCT(City_Name) as City_Name, Lat, Long FROM City_table join Loc_Table on City_Table.City_Id = Loc_table.City_Id'
-    df=pd.read_sql_query(sql_statement, conn).to_records(index=False)
-    return df
+    return run_sql_pandas(sql_statement)
 
 @app.route("/")
 @app.route("/index")
 def index():
-
     cities_list = get_cities_list()
     keys = ("city", "latitude", "longitude")
     cities_list = get_list_of_dict(keys, cities_list)
     return render_template("index.html", cities_list=cities_list)
 
+
[email protected]("/receiveDates", methods=["POST"])
+def receive_dates():
+    start_date = request.form["start_date"]
+    end_date = request.form["end_date"]
+    dates_unmf = pd.date_range(start_date, end_date, freq='d')
+    dates = []
+    for i in dates_unmf:
+        dates.append(i.strftime('%Y-%m-%d'))
+    plot_data(dates)
+    return 'OK'
+
 if __name__ == '__main__':
     app.run(debug=True)
diff --git a/sql_functions.py b/sql_functions.py
new file mode 100644
index 0000000..313c449
--- /dev/null
+++ b/sql_functions.py
@@ -0,0 +1,18 @@
+import sqlite3 as sql
+import pandas as pd
+
+def execute_sql_statement(sql_statement):
+    conn = sql.connect("database.db")
+    cur = conn.cursor()
+    cur.execute(sql_statement)
+    rows = cur.fetchall()
+    return rows
+
+def run_sql_pandas(sql_statement):
+    conn = sql.connect("database.db")
+    df=pd.read_sql_query(sql_statement, conn).to_records(index=False)
+    return df
+
+def get_list_of_dict(keys, list_of_tuples):
+     list_of_dict = [dict(zip(keys, values)) for values in list_of_tuples]
+     return list_of_dict
\ No newline at end of file
diff --git a/templates/index.html b/templates/index.html
index 170de98..19fc873 100644
--- a/templates/index.html
+++ b/templates/index.html
@@ -39,11 +39,11 @@
                         </div>
                     </div>
                     <p class="flex items-center space-x-2 py-2 px-4"><span><strong>Start Date: </strong></span><input
-                            type="date" class="rounded bg-gray-600 w-full" /><span></p>
+                            type="date" class="rounded bg-gray-600 w-full" id="startDate" /><span></p>
                     <p class="flex items-center space-x-2 py-2 px-4"><strong>End Date: </strong></span><input
-                            type="date" class="rounded bg-gray-600 w-full" /></p>
+                            type="date" class="rounded bg-gray-600 w-full" id="endDate" /></p>
                     <p class="flex items-center space-x-2 py-2 px-4">
-                        <button
+                        <button id="displayData"
                             class="text-center px-6 py-2 border border-transparent text-base font-medium rounded-md text-white bg-indigo-600 hover:bg-indigo-700 w-full">
                             Display Data
                         </button>
@@ -71,6 +71,7 @@
         </main>
     </div>
 </body>
+<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
 <script type="text/javascript">
     const cities = JSON.parse('{{cities_list | tojson}}');
 </script>
@@ -78,4 +79,28 @@
 <script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>
 <script src="../static/js/search.js" defer></script>
 
+<script>
+    var displayDataButton = document.getElementById('displayData');
+    var startDate = document.getElementById('startDate');
+    var endDate = document.getElementById('endDate');
+
+    displayDataButton.addEventListener('click', e => {
+        e.preventDefault();
+        var startDateValue = startDate.value;
+        var endDateValue = endDate.value;
+        if (startDateValue == '' || endDateValue == '') {
+            alert('Please enter a start and end date');
+        } else if (endDateValue < startDateValue) {
+            alert('Please enter a valid date range');
+        } else {
+            $.post("/receiveDates", {
+                start_date: startDateValue,
+                end_date: endDateValue
+            }, data => {
+                console.log(data);
+            });
+        }
+    })
+</script>
+
 </html>
\ No newline at end of file
diff --git a/time_serie_model.py b/time_serie_model.py
deleted file mode 100644
index 1e05543..0000000
--- a/time_serie_model.py
+++ /dev/null
@@ -1,64 +0,0 @@
-import numpy as np
-import pandas as pd
-import matplotlib.pyplot as plt
-import seaborn as sns
-from statsmodels.tsa.arima_model import ARIMA
-import statsmodels.api as sm
-from app import execute_sql_statement
-import sqlite3 as sql
-import pmdarima as pm
-
-conn = sql.connect("database.db")
-
-sql_stmt = "select date, cast(avg_temperature as real) as temp from temperature limit 100"
-
-result = execute_sql_statement(sql_stmt, conn)
-data = pd.DataFrame(result, columns=["date","temp"])
-
-data.set_index('date', inplace=True)
-print(data)
-new_model = pm.auto_arima(data.temp, start_p=1, start_q=1,
-                      test='adf',       # use adftest to find optimal 'd'
-                      max_p=3, max_q=3, # maximum p and q
-                      m=5,              # frequency of series
-                      d=None,           # let model determine 'd'
-                      seasonal=False,   # No Seasonality
-                      start_P=0, 
-                      D=0, 
-                      trace=True,
-                      error_action='ignore',  
-                      suppress_warnings=True, 
-                      stepwise=True)
-
-print(new_model.summary())
-
-new_model.plot_diagnostics(figsize=(10,8))
-# plt.show()
-
-n_periods = 10
-fc, confint = new_model.predict(n_periods = n_periods, return_conf_int = True)
-print(fc)
-
-n_years = ['1958-05-01', '1958-06-01','1958-07-01','1958-08-01','1958-09-01','1958-10-01','1958-11-01','1958-12-01','1959-01-01','1959-02-01']
-fc_ind = pd.Series(n_years)
-
-fc_series = pd.Series(fc, index=fc_ind)
-lower_series = pd.Series(confint[:, 0], index=fc_ind)
-upper_series = pd.Series(confint[:, 1], index=fc_ind)
-
-plt.figure(figsize=(12, 5))
-plt.plot(np.log10(data.temp))
-plt.plot(fc_series, color="darkred")
-# plt.xlabel("Year")
-# plt.ylabel(data. + " Rate")
-plt.fill_between(lower_series.index, 
-                     lower_series, 
-                     upper_series, 
-                     color="k", alpha=.35)
-# plt.xticks(np.arange(min(data.index), max(upper_series.index)+3, 3.0))
-# plt.title("Final Forecast of Crude Death Rate")
-# plt.legend(("past", "forecast", "95% confidence interval"), loc="upper right")
-plt.show()
-
-
-
diff --git a/time_series_model.py b/time_series_model.py
new file mode 100644
index 0000000..2896381
--- /dev/null
+++ b/time_series_model.py
@@ -0,0 +1,61 @@
+import numpy as np
+import pandas as pd
+import matplotlib.pyplot as plt
+import seaborn as sns
+from statsmodels.tsa.arima_model import ARIMA
+import statsmodels.api as sm
+import sqlite3 as sql
+import pmdarima as pm
+from sql_functions import execute_sql_statement
+
+def plot_data(dates):
+    sql_stmt = "select date, cast(avg_temperature as real) as temp from temperature"
+    result = execute_sql_statement(sql_stmt)
+    data = pd.DataFrame(result, columns=["date","temp"])
+    data.set_index('date', inplace=True)
+    # print(data)
+    new_model = pm.auto_arima(data.temp, start_p=1, start_q=1,
+                        test='adf',       # use adftest to find optimal 'd'
+                        max_p=3, max_q=3, # maximum p and q
+                        m=5,              # frequency of series
+                        d=None,           # let model determine 'd'
+                        seasonal=False,   # No Seasonality
+                        start_P=0, 
+                        D=0, 
+                        trace=True,
+                        error_action='ignore',  
+                        suppress_warnings=True, 
+                        stepwise=True)
+
+    # print(new_model.summary())
+
+    # new_model.plot_diagnostics(figsize=(10,8))
+    # # plt.show()
+
+    n_periods =30
+    fc, confint = new_model.predict(n_periods = n_periods, return_conf_int = True)
+    # print(fc)
+
+    n_years = ['1960-12-02', '1960-12-03', '1960-12-04', '1960-12-05', '1960-12-06', '1960-12-07', '1960-12-08', '1960-12-09', '1960-12-10', '1960-12-11', '1960-12-12', '1960-12-13', '1960-12-14', '1960-12-15', '1960-12-16', '1960-12-17', '1960-12-18', '1960-12-19', '1960-12-20', '1960-12-21', '1960-12-22', '1960-12-23', '1960-12-24', '1960-12-25', '1960-12-26', '1960-12-27', '1960-12-28', '1960-12-29', '1960-12-30', '1960-12-31']
+    fc_ind = pd.Series(n_years)
+
+    fc_series = pd.Series(fc, index=fc_ind)
+    lower_series = pd.Series(confint[:, 0], index=fc_ind)
+    upper_series = pd.Series(confint[:, 1], index=fc_ind)
+
+    plt.figure(figsize=(12, 5))
+    # # # plt.plot(np.log10(data.temp))
+    # plt.plot(fc_series, color="darkred")
+    # # # plt.xlabel("Year")
+    # # # plt.ylabel(data. + " Rate")
+    # # plt.fill_between(lower_series.index, 
+    # #                     lower_series, 
+    # #                     upper_series, 
+    # #                     color="k", alpha=.35)
+    # # # plt.xticks(np.arange(min(data.index), max(upper_series.index)+3, 3.0))
+    # # # plt.title("Final Forecast of Crude Death Rate")
+    # # # plt.legend(("past", "forecast", "95% confidence interval"), loc="upper right")
+    plt.show()
+
+
+