-
francoisdillinger authoredf00271eb
CS499_database2.py 12.35 KiB
import psycopg2
# import CS_499_backend
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, TC_ct_MAR_lrtv, TC_ct_MAR_brtv, TC_pwr_MAR_brtv, TC_pwr_MAR_lrtv, TC_shower_cost2_MAR, TC_shower_power2_MAR, TC_shower_water_usg_MAR
# 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("""
DROP TABLE IF EXISTS Door_Window_History;
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:
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)
VALUES (%s, %s, %s, %s, %s);
""", (
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")
cur = conn.cursor()
cur.execute("""
DROP TABLE IF EXISTS Appliance_1_History;
CREATE TABLE Appliance_1_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,
micro_ct FLOAT,
stove_ct FLOAT,
oven_ct FLOAT,
Lrtv_ct FLOAT,
Brtv_ct FLOAT,
fridge_ct FLOAT,
lights_pwr FLOAT,
lights_ct FLOAT
);
""")
for time_stamp2, data in App1_History.items():
cur.execute("""
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")
cur = conn.cursor()
cur.execute("""
DROP TABLE IF EXISTS appliance_with_water;
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
);
""")
conn.commit()
# insert data into the App2_History table
for time_stamp3, data in App2_History.items():
cur = conn.cursor()
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
))
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")
cur = conn.cursor()
cur.execute("""
DROP TABLE IF EXISTS Temperature_data;
CREATE TABLE Temperature_data (
id SERIAL PRIMARY KEY,
temp_power FLOAT,
temp_cost FLOAT
);
""")
conn.commit()
# insert data into the App3_History Table
for time_stamp4, data in App3_History.items():
cur = conn.cursor()
cur.execute("""
INSERT INTO temperature_data (
temp_power, temp_cost
)
VALUES (%s, %s);
""", (
data.temp_power, data.temp_cost
))
conn.commit()
#################### 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()
######################################################################################################################################
######################################################################################################################################
############################################ TEMPERATURE DATA TABLE WATTS & COST BY MONTH ############################################
# 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 ('Temperature Data (January):', %s, %s),
('Temperature Data (February):', %s, %s),
('Temperature Data (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()
# create a new table for power_testcases
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 power_testcases;
CREATE TABLE power_testcases (
id SERIAL PRIMARY KEY,
test_case TEXT,
test_case_pwr FLOAT,
test_case_cost FLOAT
)
""")
conn.commit()
# Insert the two test case for living room TV & bedroom tv power and cost values into the table
cur.execute("""
INSERT INTO power_testcases (test_case, test_case_pwr, test_case_cost)
VALUES ('Test case 1 (Living Room TV):', %s, %s),
('Test case 2 (Bedroom TV):', %s, %s)
""", (TC_pwr_MAR_lrtv, TC_ct_MAR_lrtv,
TC_pwr_MAR_brtv, TC_ct_MAR_brtv))
# Commit the changes
conn.commit()
#####################################################################################################################
#####################################################################################################################
############################################ WATER TEST CASE SHOWER CODE ############################################
# create a new table for water_testcase
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 water_testcase;
CREATE TABLE water_testcase (
id SERIAL PRIMARY KEY,
test_case TEXT,
test_case_pwr FLOAT,
test_case_cost FLOAT,
test_case_usg FLOAT
)
""")
conn.commit()
# Insert a test case for shower and display power, cost, and water used into the table
cur.execute("""
INSERT INTO water_testcase (test_case, test_case_pwr, test_case_cost, test_case_usg)
VALUES ('Test case (Shower):', %s, %s, %s)
""", (TC_shower_power2_MAR, TC_shower_cost2_MAR, TC_shower_water_usg_MAR))
# Commit the changes
conn.commit()
#####################################################################################################################
#####################################################################################################################
#####################################################################################################################
# Commit changes
#conn.commit()
cur.close()
conn.close()