def getandsaveDataWeekly(db, x, s, e, w):
cur = db.cursor()
sql = "SELECT socket_id, watt_cons FROM power_daily WHERE socket_id=%s AND date>=%s AND date<=%s"
cur.execute(sql, (x, s, e))
results = cur.fetchall()
f = 0.0000
for row in results:
f = f + float(row[1])
cur.close()
curinsert = db.cursor()
sql = "INSERT INTO power_weekly(socket_id, watt_cons, date_from, date_to, week_number) VALUES (%s, %s, %s, %s, %s)"
try:
curinsert.execute(sql, (x, f, s, e, w))
db.commit()
except:
db.rollback()
def saveDate(db, x, d):
cur = db.cursor()
sql = "SELECT socket_id, watt_cons FROM power_con WHERE CAST(date_time as DATE)=%s AND socket_id=%s"
cur.execute(sql, (d, x))
count = cur.rowcount
results = cur.fetchall()
f = 0.000
for row in results:
f = f + float(row[1])
if count != 0:
f = (f / count * ((count / 60) / 60))
cur.close()
curinsert = db.cursor()
sql = "INSERT INTO power_daily(socket_id, watt_cons, date) VALUES (%s, %s, %s)"
try:
curinsert.execute(sql, (x, f, d))
db.commit()
except Exception as e:
db.rollback()
curinsert.close()
def saveAppliance(db, t, s):
cur = db.cursor()
sql = """UPDATE socket SET appliance=%s WHERE id=%s"""
cur.execute(sql, (t, s))
db.commit()
def deleteForgot(db, id):
cur = db.cursor()
sql = "DELETE FROM forgot_table WHERE id="+ str(id)
cur.execute(sql)
db.commit()
cur.close()
def deleteSched(db, pid, socket_id):
cur = db.cursor()
sql = "DELETE FROM schedule WHERE id=%s AND socket_id=%s"
cur.execute(sql, (pid, socket_id))
db.commit()
cur.close()