Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
import psycopg2
# import CS_499_backend
from CS_499_backend import Door_Window, Appliance_1, Appliance_with_Water, Temperature_data
# create a new database
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur = conn.cursor()
cur.execute("CREATE DATABASE Team3 ")
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()
# insert the data into the Door_Window_History table
for i in Door_Window_History:
cur.execute("""
INSERT INTO Door_Window_History (
day, door_status, door_time, window_status, window_time)
VALUES (%s, %s, %s, %s, %s);
""", (
Door_Window_History[i].day, Door_Window_History[i].Door_status, Door_Window_History[i].Door_time,
Door_Window_History[i].Window_status, Door_Window_History[i].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")
cur.execute("""
CREATE TABLE App1_History (
id SERIAL PRIMARY KEY,
day VARCHAR(255),
micro_pwr FLOAT,
stove_pwr FLOAT,
oven_pwr FLOAT,
Lrtv_pwr FLOAT,
Brtv_pwr FLOAT,
fridge_pwr FLOAT,
lights_pwr FLOAT,
micro_ct FLOAT,
stove_ct FLOAT,
oven_ct FLOAT,
Lrtv_ct FLOAT,
Brtv_ct FLOAT,
fridge_ct FLOAT,
lights_ct FLOAT
);
""")
conn.commit()
# insert the data into the App1_History table
for i in App1_History:
cur.execute("""
INSERT INTO App1_History (
day, micro_pwr, stove_pwr, oven_pwr, Lrtv_pwr, Brtv_pwr,
fridge_pwr, lights_pwr, micro_ct, stove_ct, oven_ct, Lrtv_ct,
Brtv_ct, fridge_ct, lights_ct
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
""", (
App1_History[i].day, App1_History[i].micro_pwr, App1_History[i].stove_pwr,
App1_History[i].oven_pwr, App1_History[i].Lrtv_pwr, App1_History[i].Brtv_pwr,
App1_History[i].fridge_pwr, App1_History[i].lights_pwr, App1_History[i].micro_ct,
App1_History[i].stove_ct, App1_History[i].oven_ct, App1_History[i].Lrtv_ct,
App1_History[i].Brtv_ct, App1_History[i].fridge_ct, App1_History[i].lights_ct
))
# create a new table for appliance_with_water
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur.execute("""
CREATE TABLE appliance_with_water (
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
);
""")
# insert data into the App2_History table
for timestamp, data in App2_History.items():
cur.execute("""
INSERT INTO appliance_with_water (
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
))
# create a new table for Temperature_data
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur.execute("""
CREATE TABLE Temperature_data (
id SERIAL PRIMARY KEY,
temp_power FLOAT,
temp_cost FLOAT
);
""")
# insert data into the App3_History Table
for i in App3_Histroy:
cur.execute("""
INSERT INTO temperature_data (
temp_power, temp_cost
)
VALUES (%s, %s);
""", (
App3_History[i].temp_power, App3_History[i].temp_cost
))
# Commit changes
conn.commit()
cur.close()
conn.close()