Skip to content
Snippets Groups Projects
CS_499_database.py 5.28 KiB
Newer Older
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
import psycopg2

# import CS_499_backend
Anthony Gobble's avatar
Anthony Gobble 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
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed

# create a new database
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
cur = conn.cursor()
Anthony Gobble's avatar
Anthony Gobble committed
#cur.execute("CREATE DATABASE Team3 ")
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
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("""
Anthony Gobble's avatar
Anthony Gobble committed
    DROP TABLE IF EXISTS Door_Window_History;
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
    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()

Anthony Gobble's avatar
Anthony Gobble committed
#insert the data into the Door_Window_History table
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
for i in Door_Window_History:
Anthony Gobble's avatar
Anthony Gobble committed
    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) 
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
            VALUES (%s, %s, %s, %s, %s);
        """, (
Anthony Gobble's avatar
Anthony Gobble committed
            data.day, data.Door_status, data.Door_time,
            data.Window_status, data.Window_time
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        ))
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")
Anthony Gobble's avatar
Anthony Gobble committed
cur = conn.cursor()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
cur.execute("""
Anthony Gobble's avatar
Anthony Gobble committed
    DROP TABLE IF EXISTS Appliance_1_History;
    CREATE TABLE Appliance_1_History (
        id SERIAL PRIMARY KEY, 
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        day VARCHAR(255), 
Anthony Gobble's avatar
Anthony Gobble committed
        micro_pwr FLOAT, 
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        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, 
Anthony Gobble's avatar
Anthony Gobble committed
        fridge_ct FLOAT,
        lights_pwr FLOAT,
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        lights_ct FLOAT
        );
    """)

Anthony Gobble's avatar
Anthony Gobble committed
for time_stamp2, data in App1_History.items():
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
    cur.execute("""
Anthony Gobble's avatar
Anthony Gobble committed
        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) 
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
Anthony Gobble's avatar
Anthony Gobble committed
        """, (
            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()

Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed


# create a new table for appliance_with_water
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
Anthony Gobble's avatar
Anthony Gobble committed
cur = conn.cursor()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
cur.execute("""
Anthony Gobble's avatar
Anthony Gobble committed
    DROP TABLE IF EXISTS appliance_with_water;
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
    CREATE TABLE appliance_with_water (
Anthony Gobble's avatar
Anthony Gobble committed
       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,
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        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
Anthony Gobble's avatar
Anthony Gobble committed
  );
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
""")
Anthony Gobble's avatar
Anthony Gobble committed
conn.commit()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed

# insert data into the App2_History table
Anthony Gobble's avatar
Anthony Gobble committed
for time_stamp3, data in App2_History.items():
   cur = conn.cursor()
   cur.execute("""
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        INSERT INTO appliance_with_water (
Anthony Gobble's avatar
Anthony Gobble committed
           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
       )
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        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,
Anthony Gobble's avatar
Anthony Gobble committed
       data.Water_ct, data.bath, data.shower
   ))
   conn.commit()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed

# create a new table for Temperature_data
conn = psycopg2.connect(database="Team3DB", user="Team3", password="team3", host="138.26.48.83", port="5432")
Anthony Gobble's avatar
Anthony Gobble committed
cur = conn.cursor()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
cur.execute("""
Anthony Gobble's avatar
Anthony Gobble committed
    DROP TABLE IF EXISTS Temperature_data;
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
    CREATE TABLE Temperature_data (
        id SERIAL PRIMARY KEY,
        temp_power FLOAT,
        temp_cost FLOAT
    );
""")
Anthony Gobble's avatar
Anthony Gobble committed
conn.commit()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed

# insert data into the App3_History Table
Anthony Gobble's avatar
Anthony Gobble committed
for time_stamp4, data in App3_History.items():
    cur = conn.cursor()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
    cur.execute("""
        INSERT INTO temperature_data (
Anthony Gobble's avatar
Anthony Gobble committed
           temp_power, temp_cost
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
        )
        VALUES (%s, %s);
    """, (
Anthony Gobble's avatar
Anthony Gobble committed
        data.temp_power, data.temp_cost
Anthony Gobble's avatar
Anthony Gobble committed
    conn.commit()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed

# Commit changes
Anthony Gobble's avatar
Anthony Gobble committed
#conn.commit()
Jesus Miguel Zubia's avatar
Jesus Miguel Zubia committed
cur.close()
conn.close()