Skip to content
Snippets Groups Projects
CS_499_database.py 5.04 KiB
Newer Older
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
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()