def update_ski_area(data):
print("\n\n[DEBUG] db.update_ski_area() data:", data)
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
"""UPDATE ski_areas SET cur_temp = %(cur_temp)s, cur_depth = %(cur_depth)s, ytd = %(ytd)s, \
wind_dir = %(wind_dir)s, wind_speed = %(wind_speed)s, new_snow_12 = %(new_snow_12)s, \
new_snow_24 = %(new_snow_24)s, new_snow_48 = %(new_snow_48)s, ts = curdate() \
WHERE name = %(name)s;""", {
"cur_temp": data["cur_temp"],
"cur_depth": data["cur_depth"],
"ytd": data["ytd"],
"wind_dir": data["wind_dir"],
"wind_speed": data["wind_speed"],
"new_snow_12": data["new_snow_12"],
"new_snow_24": data["new_snow_24"],
"new_snow_48": data["new_snow_48"],
"name": data["name"]
})
db.commit()
print("[DEBUG] Updated {}\n\n".format(data["name"]))
return True
except:
utils.print_error_message("Error updating {}".format(data["name"]))
return False
def update_monthly_data(data):
print("\n\n[DEBUG] db.update_monthly_data() snow_data:", data)
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
""" UPDATE monthly_data SET total_new_snow = %(total_new_snow)s, snow_depth = %(snow_depth)s, \
avg_temp = %(avg_temp)s, ytd = %(ytd)s WHERE (ski_area_name = %(ski_area_name)s AND month = %(month)s AND \
year = %(year)s);""", {
"total_new_snow": data["total_new_snow"],
"snow_depth": data["snow_depth"],
"avg_temp": data["avg_temp"],
"ytd": data["ytd"],
"ski_area_name": data["ski_area_name"],
"month": data["month"],
"year": data["year"]
})
db.commit()
print("[DEBUG] Updated monthly data for {}\n\n".format(
data["ski_area_name"]))
return True
except:
utils.print_error_message("Error updating monthly data for {}".format(
data["ski_area_name"]))
return False
def update_sa():
for ski_area in const.SKI_AREAS:
try:
sa = SkiArea(name=ski_area)
sa.update_ski_areas()
except:
utils.print_error_message(
"Error scraping and updating {}".format(ski_area))
def create_new_month():
for ski_area in const.SKI_AREAS:
try:
data = resort_scraper.get_data(ski_area)
sa = SkiArea(data[0], data[1], data[2], data[3], data[4], data[5],
data[6], data[7], data[8])
sa.create_new_month()
except:
utils.print_error_message(
"SkiAreas error creating monthly snow_data for {}".format(
ski_area))
def delete_user(data):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute("""DELETE from users where username=%(username)s;""",
{"username": data["username"]})
db.commit()
return True
except:
utils.print_error_message("delete_user mysql error")
return False
def check_password(data):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
""" SELECT password FROM users WHERE username=%(username)s; """,
{"username": data["username"]})
pw_hash = cursor.fetchone()
return check_password_hash(pw_hash[0], data["password"])
except:
utils.print_error_message("check_password mysql error")
return False
def get_avg_temp(data):
print("\n\n[DEBUG] db.get_avg_temp()")
try:
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute("use snow_db")
cursor.execute(
"""SELECT * FROM avg_temps WHERE ski_area_name=%(ski_area_name)s;""",
{"ski_area_name": data["name"]})
res = cursor.fetchone()
return res["avg_temp"]
except:
utils.print_error_message("Error getting avg_temp from db")
def update_email(data):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
""" UPDATE users SET email=%(email)s WHERE username=%(username)s; """,
{
"email": data["new_email"],
"username": data["username"]
})
db.commit()
return True
except:
utils.print_error_message("update_email mysql error")
return False
def reset_avg_temp(data):
print("\n\n[DEBUG] db.reset_avg_temps()")
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
""" UPDATE avg_temps SET avg_temp="0", total_temp="0", count="0" WHERE ski_area_name = \
%(ski_area_name)s; """, {"ski_area_name": data["name"]})
db.commit()
print("[DEBUG] Reset avg temp for {}\n\n".format(data["name"]))
return True
except:
utils.print_error_message("Error resetting avg temp for {}\n\n".format(
data["name"]))
return False
def update_password(data):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
hashed_pwd = generate_password_hash(data["new_password"])
cursor.execute(
""" UPDATE users SET password=%(password)s WHERE username=%(username)s; """,
{
"password": hashed_pwd,
"username": data["username"]
})
db.commit()
return True
except:
utils.print_error_message("update_password mysql error")
return False
def update_forecast(name, forecast):
print("\n\n[DEBUG] db.update_forecast")
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
"""UPDATE ski_areas SET forecast = %(forecast)s WHERE name = %(name)s;""",
{
"forecast": json.dumps(forecast),
"name": name
})
db.commit()
print("[DEBUG] Updated {} Forecast\n\n".format(name))
return True
except:
utils.print_error_message("Error updating {} Forecast".format(name))
return False
def set_content(new_content, ski_area):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
"""UPDATE websites SET content=%(content)s WHERE ski_area_name=%(ski_area_name)s;""",
{
"content": new_content,
"ski_area_name": ski_area
})
db.commit()
print("DEBUG commited")
return True
except:
utils.print_error_message(
"Error setting content for {}".format(ski_area))
return False
def check_website_change():
for ski_area, value in const.SKI_AREAS.items():
for key, url in value.items():
old_content = db.get_content(ski_area)
new_content = website_parser.get_plain_text(url)
# compare old content to new content and update the db with the new content
if old_content:
if website_parser.compare_plain_texts(old_content,
new_content) < 90:
try:
utils.send_email(
"{} has updated their website".format(ski_area))
except:
utils.print_error_message("Error sending email")
updated = db.set_content(new_content, ski_area)
if not updated:
utils.print_error_message("Error updating website change db")
def create_user(data):
print("\n\n[DEBUG] db.create_user() snow_data:", data)
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
hashed_pwd = generate_password_hash(data["password"])
cursor.execute(
"""INSERT INTO users (username, email, password, api_key, api_count) VALUES (%(username)s, \
%(email)s, %(password)s, %(api_key)s, 0);""", {
"username": data["username"],
"email": data["email"],
"password": hashed_pwd,
"api_key": data["api_key"]
})
db.commit()
return True
except:
utils.print_error_message("create_user mysql error")
return False
def update_avg_temp(data):
print("\n\n[DEBUG] db.update_avg_temps()")
try:
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute("use snow_db")
cursor.execute(
""" SELECT * FROM avg_temps WHERE ski_area_name=%(ski_area_name)s; """,
{"ski_area_name": data["name"]})
res = cursor.fetchone()
new_total_temp = float(res["total_temp"]) + float(data["cur_temp"])
new_count = res["count"] + 1
new_avg_temp = int(new_total_temp / new_count)
except:
utils.print_error_message("Error getting temps")
return False
if new_avg_temp and new_total_temp and new_count:
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
""" UPDATE avg_temps SET avg_temp=%(avg_temp)s, total_temp=%(total_temp)s, count=%(count)s \
WHERE ski_area_name = %(ski_area_name)s; """, {
"avg_temp": new_avg_temp,
"total_temp": new_total_temp,
"count": new_count,
"ski_area_name": data["name"]
})
db.commit()
print("[DEBUG] Updated average temp for {}\n\n".format(
data["name"]))
return True
except:
utils.print_error_message(
"Error updating average temp for {}\n\n".format(data["name"]))
return False
def create_new_month(data):
try:
db = get_db()
cursor = db.cursor()
cursor.execute("use snow_db")
cursor.execute(
"""INSERT INTO monthly_data (month, year, ski_area_name, total_new_snow, snow_depth, avg_temp, \
ytd) VALUES (%(month)s, %(year)s, %(ski_area_name)s, %(total_new_snow)s, %(snow_depth)s, %(avg_temp)s, \
%(ytd)s);""", {
"month": data["month"],
"year": data["year"],
"ski_area_name": data["ski_area_name"],
"total_new_snow": data["total_new_snow"],
"snow_depth": data["snow_depth"],
"avg_temp": data["avg_temp"],
"ytd": data["ytd"]
})
db.commit()
print("[DEBUG] Updated monthly data for {}\n\n".format(
data["ski_area_name"]))
return True
except:
utils.print_error_message("DB error creating new month")
return False