From cc137038fa8c1db5e24a2ba103c7dbee79affa95 Mon Sep 17 00:00:00 2001 From: Dennis Eriksen Date: Sat, 4 Feb 2023 21:53:54 +0100 Subject: adding location and rooms updating yr to add location added coule tables to schema I forgot --- schema.sql | 133 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++- scripts/yr.py | 16 +++++-- 2 files changed, 145 insertions(+), 4 deletions(-) diff --git a/schema.sql b/schema.sql index 9fbb3a2..66fbd08 100644 --- a/schema.sql +++ b/schema.sql @@ -65,6 +65,69 @@ CREATE TABLE public.entsoe ( ); +-- +-- Name: location; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.location ( + id smallint NOT NULL, + name character varying(255) NOT NULL, + description text, + lon real, + lat real, + created timestamp with time zone DEFAULT now() NOT NULL, + updated timestamp with time zone +); + + +-- +-- Name: location_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +ALTER TABLE public.location ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.location_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: mqtt_han; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.mqtt_han ( + id bigint NOT NULL, + name text, + current real, + power real, + voltage real, + linkquality smallint, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + + +-- +-- Name: mqtt_han_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE public.mqtt_han_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: mqtt_han_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.mqtt_han_id_seq OWNED BY public.mqtt_han.id; + + -- -- Name: mqtt_temps; Type: TABLE; Schema: public; Owner: - -- @@ -152,6 +215,34 @@ CREATE SEQUENCE public.neohub_id_seq ALTER SEQUENCE public.neohub_id_seq OWNED BY public.neohub.id; +-- +-- Name: room; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.room ( + id smallint NOT NULL, + location_id smallint NOT NULL, + name character varying(255) NOT NULL, + description text, + created timestamp with time zone DEFAULT now() NOT NULL, + updated timestamp with time zone +); + + +-- +-- Name: room_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +ALTER TABLE public.room ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.room_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + -- -- Name: tibber_consumption; Type: TABLE; Schema: public; Owner: - -- @@ -188,6 +279,7 @@ CREATE TABLE public.tibber_prices ( CREATE TABLE public.yr ( "time" timestamp with time zone NOT NULL, + location smallint NOT NULL, air_temperature real, air_pressure_at_sea_level real, cloud_area_fraction real, @@ -199,6 +291,13 @@ CREATE TABLE public.yr ( ); +-- +-- Name: mqtt_han id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_han ALTER COLUMN id SET DEFAULT nextval('public.mqtt_han_id_seq'::regclass); + + -- -- Name: mqtt_temps id; Type: DEFAULT; Schema: public; Owner: - -- @@ -237,6 +336,22 @@ ALTER TABLE ONLY public.entsoe ADD CONSTRAINT entsoe_pkey PRIMARY KEY (starttime, zone); +-- +-- Name: location location_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.location + ADD CONSTRAINT location_pkey PRIMARY KEY (id); + + +-- +-- Name: mqtt_han mqtt_han_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_han + ADD CONSTRAINT mqtt_han_pkey PRIMARY KEY (id); + + -- -- Name: mqtt_temps mqtt_temps_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -261,6 +376,14 @@ ALTER TABLE ONLY public.neohub ADD CONSTRAINT neohub_pkey PRIMARY KEY (id); +-- +-- Name: room room_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.room + ADD CONSTRAINT room_pkey PRIMARY KEY (id); + + -- -- Name: tibber_consumption tibber_consumption_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -282,7 +405,15 @@ ALTER TABLE ONLY public.tibber_prices -- ALTER TABLE ONLY public.yr - ADD CONSTRAINT yr_pkey PRIMARY KEY ("time"); + ADD CONSTRAINT yr_pkey PRIMARY KEY ("time", location); + + +-- +-- Name: room fk_location; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.room + ADD CONSTRAINT fk_location FOREIGN KEY (location_id) REFERENCES public.location(id); -- diff --git a/scripts/yr.py b/scripts/yr.py index f6b654c..243f531 100644 --- a/scripts/yr.py +++ b/scripts/yr.py @@ -7,6 +7,7 @@ import requests import common +location = int(os.environ['el_location']) lat = str(os.environ['el_yr_lat']) lon = str(os.environ['el_yr_lon']) @@ -42,6 +43,7 @@ for item in data["properties"]["timeseries"]: details = item["data"]["instant"]["details"] values.append(( item["time"], + location, details["air_temperature"], details["air_pressure_at_sea_level"], details["cloud_area_fraction"], @@ -50,9 +52,17 @@ for item in data["properties"]["timeseries"]: details["wind_speed"])) # SQL -sql = """ INSERT INTO yr - VALUES(%s, %s, %s, %s, %s, %s, %s) - ON CONFLICT (time) DO UPDATE SET +sql = """ INSERT INTO yr ( + time, + location, + air_temperature, + air_pressure_at_sea_level, + cloud_area_fraction, + relative_humidity, + wind_from_direction, + wind_speed) + VALUES(%s, %s, %s, %s, %s, %s, %s, %s) + ON CONFLICT (time, location) DO UPDATE SET air_temperature=EXCLUDED.air_temperature, air_pressure_at_sea_level=EXCLUDED.air_pressure_at_sea_level, cloud_area_fraction=EXCLUDED.cloud_area_fraction, -- cgit v1.2.3