Newer
Older
from CS_499_backend import Door_Window, Appliance_1, Appliance_with_Water, Temperature_data, Door_Window_History, App1_History, App2_History, App3_History, time_stamp, time_stamp2, time_stamp3, time_stamp4
# create a new database
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur = conn.cursor()
conn.close()
# create a new table for Door_Window_History
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur = conn.cursor()
cur.execute("""
CREATE TABLE Door_Window_History (
id SERIAL PRIMARY KEY,
day VARCHAR(255),
door_status INTEGER,
door_time FLOAT,
window_status INTEGER,
window_time FLOAT
);
""")
conn.commit()
for time_stamp, data in Door_Window_History.items():
cur.execute("""
INSERT INTO Door_Window_History (
day, door_status, door_time, window_status, window_time)
data.day, data.Door_status, data.Door_time,
data.Window_status, data.Window_time
))
conn.commit()
# create a new table for App1_History
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
DROP TABLE IF EXISTS Appliance_1_History;
CREATE TABLE Appliance_1_History (
id SERIAL PRIMARY KEY,
stove_pwr FLOAT,
oven_pwr FLOAT,
Lrtv_pwr FLOAT,
Brtv_pwr FLOAT,
fridge_pwr FLOAT,
micro_ct FLOAT,
stove_ct FLOAT,
oven_ct FLOAT,
Lrtv_ct FLOAT,
Brtv_ct FLOAT,
INSERT INTO Appliance_1_History (
day, micro_pwr, stove_pwr, oven_pwr, Lrtv_pwr, Brtv_pwr, fridge_pwr,
micro_ct, stove_ct, oven_ct, Lrtv_ct, Brtv_ct, fridge_ct, lights_pwr, lights_ct)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
""", (
data.day, data.micro_pwr, data.stove_pwr, data.oven_pwr, data.Lrtv_pwr, data.Brtv_pwr,
data.fridge_pwr, data.micro_ct, data.stove_ct, data.oven_ct, data.Lrtv_ct, data.Brtv_ct,
data.fridge_ct, data.lights_pwr, data.lights_ct
))
conn.commit()
# create a new table for appliance_with_water
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
id SERIAL PRIMARY KEY,
day TEXT,
water_heat_pwr FLOAT,
dishwasher_pwr FLOAT,
clothes_wash_pwr FLOAT,
clothes_dry_pwr FLOAT,
bath_fans_pwr FLOAT,
water_heat_ug FLOAT,
bath_water_ug FLOAT,
shower_water_ug FLOAT,
dishwasher_ug FLOAT,
clothes_wash_ug FLOAT,
water_heat_ct FLOAT,
dishwasher_ct FLOAT,
clothes_wash_ct FLOAT,
clothes_dry_ct FLOAT,
bath_fans_ct FLOAT,
water_ct FLOAT,
bath INT,
shower INT
for time_stamp3, data in App2_History.items():
cur = conn.cursor()
cur.execute("""
day, water_heat_pwr, dishwasher_pwr, clothes_wash_pwr,
clothes_dry_pwr, bath_fans_pwr, water_heat_ug, bath_water_ug,
shower_water_ug, dishwasher_ug, clothes_wash_ug, water_heat_ct,
dishwasher_ct, clothes_wash_ct, clothes_dry_ct, bath_fans_ct,
water_ct, bath, shower
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
""", (
data.day, data.Water_Heat_pwr, data.DishWasher_pwr, data.Clothes_Wash_pwr,
data.Clothes_dry_pwr, data.bath_fans_pwr, data.Water_Heat_ug, data.Bath_water_ug,
data.Shower_water_ug, data.DishWasher_ug, data.Clothes_Wash_ug, data.Water_Heat_ct,
data.DishWasher_ct, data.Clothes_Wash_ct, data.Clothes_dry_ct, data.bath_fans_ct,
data.Water_ct, data.bath, data.shower
))
conn.commit()
# create a new table for Temperature_data
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
CREATE TABLE Temperature_data (
id SERIAL PRIMARY KEY,
temp_power FLOAT,
temp_cost FLOAT
);
""")
for time_stamp4, data in App3_History.items():
cur = conn.cursor()
cur.execute("""
INSERT INTO temperature_data (