def get_weather_timeseries(city_id, start_date, end_date):
import os, sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import psycopg2
from db_functions import db_connection, db_close
sql = f"""
SELECT
DATE("timestamp") AS "date",
ROUND(AVG("temp"), 2) AS "temp",
ROUND(MAX("high_temp"), 2) AS "high_temp",
CEILING(AVG("humidity")) AS "humidity",
CEILING(AVG("weather_severity")) AS "severity"
FROM weather
WHERE DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
AND city_id = {city_id}
GROUP BY DATE("timestamp")
ORDER BY "date" ASC
"""
try:
conn, cur = db_connection()
cur.execute(sql)
daily_timeseries = cur.fetchall()
db_close(conn)
return daily_timeseries
except (Exception, psycopg2.DatabaseError) as error:
print("get_checks error: ", error)
db_close(conn)
def get_pos_timeseries(account_id, start_date, end_date):
import os, sys
sys.path.append(os.path.dirname(os.path.dirname(
os.path.abspath(__file__))))
from db_functions import db_connection, db_close
import psycopg2
sql = f"""
SELECT
DATE("dining_date") AS "date",
SUM("guests") AS "guests",
SUM("total") AS "total_sales",
COUNT(distinct "check_id") AS "check_count"
FROM checks
WHERE DATE("dining_date") BETWEEN '{start_date}' AND '{end_date}'
AND account_id = {account_id}
GROUP BY DATE("dining_date")
ORDER BY "date" ASC
"""
try:
conn, cur = db_connection()
cur.execute(sql)
daily_timeseries = cur.fetchall()
db_close(conn)
return daily_timeseries
except (Exception, psycopg2.DatabaseError) as error:
db_close(conn)
raise error
def get_forecasts(account_id, target_variable, model):
# import sys
# import os
#
# sys.path.append(
# "/Users/jerome/Documents/NYU/Capstone/DashOfData/Model/PreProcessing"
# )
# sys.path.append("Model/Models")
# sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from db_functions import db_connection, db_close
import datetime
from psycopg2.extensions import AsIs
# from dod_model import posData
# from datetime import timedelta
import numpy as np
# import os, sys
# sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
# from db_functions import db_connection, db_close
import psycopg2
sql = f"""
SELECT
"forecast_date" AS "date",
"guests" AS "guests"
FROM "public"."model_forecasts"
WHERE "model" = '{model}' AND "target_variable" = '{target_variable}' AND "account_id" = '{account_id}'
ORDER BY "forecast_date" ASC
"""
try:
conn, cur = db_connection()
cur.execute(sql)
daily_timeseries = cur.fetchall()
db_close(conn)
return daily_timeseries
except (Exception, psycopg2.DatabaseError) as error:
db_close(conn)
raise error
def get_params(model_category, seasons, account_id):
import psycopg2
import pandas as pd
import os, sys
sys.path.append(os.path.dirname(os.path.dirname(
os.path.abspath(__file__))))
from db_functions import db_connection, db_close
conn, cur = db_connection()
sql = f"""
SELECT "p_", "d_", "q_", "p", "d", "q", "s", "parameter_id" FROM "public"."model_parameters"
WHERE "model_category" = '{model_category}'
AND "s" = {seasons}
AND "account_id" = {account_id}
ORDER BY "date" DESC
LIMIT 1
"""
try:
cur.execute(sql)
parameters = cur.fetchall()
parameters = pd.DataFrame(
parameters,
columns=["p", "d", "q", "P", "D", "Q", "s", "parameter_id"])
p = parameters["p"][0]
d = parameters["d"][0]
q = parameters["q"][0]
P = parameters["P"][0]
D = parameters["D"][0]
Q = parameters["Q"][0]
s = int(parameters["s"][0])
parameter_id = int(parameters["parameter_id"][0])
return p, d, q, P, D, Q, s, parameter_id
except (Exception, psycopg2.DatabaseError) as error:
raise error
finally:
db_close(conn, cur)
import os, sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from db_functions import db_connection, db_close
from config_model import start_date, end_date
import psycopg2
import pandas
conn, cur = db_connection()
#Query DB for index quotes and return a list of tuples
def get_economy():
sql = f"""
SELECT "gdp", "date" FROM "public"."food_services_gdp"
WHERE "state" = 'ny'
AND DATE("date") BETWEEN '{start_date}' AND '{end_date}';
"""
try:
cur.execute(sql)
economy = cur.fetchall()
return economy
except (Exception, psycopg2.DatabaseError) as error:
raise error
econData = pandas.DataFrame(get_economy(), columns=["gdp", "date"])
econData.gdp = econData.gdp.astype(int)
def save_forecast(forecasts,
target_variable,
model_name,
account_id,
parameter_id=None):
# import sys
# import os
#
# sys.path.append(
# "/Users/jerome/Documents/NYU/Capstone/DashOfData/Model/PreProcessing"
# )
# sys.path.append("Model/Models")
# sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from db_functions import db_connection, db_close
import datetime
from psycopg2.extensions import AsIs
from dod_model import posData
from datetime import timedelta
import numpy as np
print("********VAR NAME**********: ", target_variable)
conn, cur = db_connection()
last_week_index = forecasts.index - timedelta(7)
try:
if target_variable == "guests_log_diff":
forecasts = forecasts + posData["guests_log"].loc[last_week_index].values
forecasts = np.exp(forecasts)
if target_variable == "guests_diff":
forecasts = forecasts + posData["guests"].loc[last_week_index].values
if target_variable == "guests_log_diff_val":
forecasts = forecasts + posData["guests_log"].loc[last_week_index].values
forecasts = np.exp(forecasts)
# print("*****ACTUAL*****: ", posData["guests"].loc[last_week_index].values)
# print("*****FORECAST*****: ", forecasts)
for forecast_date, guest_prediction in forecasts.iteritems():
sql = f"""INSERT INTO %s(account_id,
parameter_id,
model,
guests,
sales,
forecast_date,
date_produced,
target_variable)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"""
cur.execute(sql,
(AsIs('model_forecasts'),
account_id,
parameter_id,
model_name,
guest_prediction,
None,
forecast_date,
datetime.datetime.now(),
target_variable))
conn.commit()
except Exception as error:
raise error
else:
db_close(conn, cur)
def get_quotes(start_date='2014-01-02', end_date='2018-05-01'):
conn, cur = db_connection()
sql = [
f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'IYK'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'RHS'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'FSTA'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'VDC'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'PBJ'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'XLY'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'FXG'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
""", f"""
SELECT "timestamp", "index_name", "symbol", "close"
FROM "public"."quotes"
WHERE "symbol" = 'QQQ'
AND DATE("timestamp") BETWEEN '{start_date}' AND '{end_date}'
"""
]
try:
quote_lists = []
for sql in sql:
cur.execute(sql)
index = cur.fetchall()
quote_lists.append(index)
db_close(conn)
return quote_lists
except (Exception, psycopg2.DatabaseError) as error:
db_close(conn)
raise error