CS499_database2.py

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()