Newer
Older
Jesus Miguel Zubia
committed
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, monthly_cost1_FEB, monthly_cost1_MAR, monthly_cost1_JAN, monthly_power1_JAN, monthly_power1_FEB, monthly_power1_MAR, monthly_cost2_FEB, monthly_cost2_JAN, monthly_cost2_MAR, monthly_water_usg_MAR, monthly_water_usg_JAN, monthly_water_usg_FEB, monthly_power2_FEB, monthly_power2_JAN, monthly_power2_MAR, monthly_temp_watts_JAN, monthly_temp_watts_FEB, monthly_temp_watts_MAR, monthly_temp_ct_JAN, monthly_temp_ct_FEB, monthly_temp_ct_MAR
# 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 (
Jesus Miguel Zubia
committed
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 (
Jesus Miguel Zubia
committed
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
#################### This is for
# # create a new table for app1_cost_power
# conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
# cur = conn.cursor()
# cur.execute("""
# DROP TABLE IF EXISTS app1_cost_power;
# CREATE TABLE app1_cost_power (
# month TEXT,
# watts FLOAT,
# cost FLOAT
# )
# """)
# conn.commit()
# # Insert the monthly power and cost values into the table
# cur.execute("""
# INSERT INTO app1_cost_power (month, watts, cost)
# VALUES ('January', %s, %s),
# ('February', %s, %s),
# ('March', %s, %s)
# """, (monthly_power1_JAN, monthly_cost1_JAN,
# monthly_power1_FEB, monthly_cost1_FEB,
# monthly_power1_MAR, monthly_cost1_MAR))
# # Commit the changes
# conn.commit()
# # create a new table for app2_cost_power
# conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
# cur = conn.cursor()
# cur.execute("""
# DROP TABLE IF EXISTS app2_cost_power;
# CREATE TABLE app2_cost_power (
# month TEXT,
# watts FLOAT,
# water FLOAT,
# cost FLOAT
# )
# """)
# conn.commit()
# # Insert the monthly power and cost values into the table
# cur.execute("""
# INSERT INTO app2_cost_power (month, watts, water, cost)
# VALUES ('January', %s, %s, %s),
# ('February', %s, %s, %s),
# ('March', %s, %s, %s)
# """, (monthly_power2_JAN, monthly_water_usg_JAN, monthly_cost2_JAN,
# monthly_power2_FEB, monthly_water_usg_FEB, monthly_cost2_FEB,
# monthly_power2_MAR, monthly_water_usg_MAR, monthly_cost2_MAR))
# # Commit the changes
# conn.commit()
# # create a new table for temp_cost_watts
# conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
# cur = conn.cursor()
# cur.execute("""
# DROP TABLE IF EXISTS temp_cost_watts;
# CREATE TABLE temp_cost_watts (
# month TEXT,
# watts FLOAT,
# cost FLOAT
# )
# """)
# conn.commit()
# # Insert the monthly power and cost values into the table
# cur.execute("""
# INSERT INTO temp_cost_watts (month, watts, cost)
# VALUES ('January', %s, %s),
# ('February', %s, %s),
# ('March', %s, %s)
# """, (monthly_temp_watts_JAN, monthly_temp_ct_JAN,
# monthly_temp_watts_FEB, monthly_temp_ct_FEB,
# monthly_temp_watts_MAR, monthly_temp_ct_MAR))
# # Commit the changes
# conn.commit()
# create a new table for total_cost_watts
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur = conn.cursor()
cur.execute("""
DROP TABLE IF EXISTS total_cost_watts;
CREATE TABLE total_cost_watts (
month TEXT,
watts FLOAT,
cost FLOAT
)
""")
conn.commit()
# Insert the monthly power and cost values into the table
cur.execute("""
INSERT INTO total_cost_watts (month, watts, cost)
VALUES ('January', %s, %s),
('February', %s, %s),
('March', %s, %s)
""", (monthly_temp_watts_JAN+monthly_power2_JAN+monthly_power1_JAN, monthly_temp_ct_JAN+monthly_cost2_JAN+monthly_cost1_JAN,
monthly_temp_watts_FEB+monthly_power2_FEB+monthly_power1_FEB, monthly_temp_ct_FEB+monthly_cost2_FEB+monthly_cost1_FEB,
monthly_temp_watts_MAR+monthly_power2_MAR+monthly_power1_MAR, monthly_temp_ct_MAR+monthly_cost2_MAR+monthly_cost1_MAR))
# Commit the changes
conn.commit()