diff options
| -rw-r--r-- | schema.sql | 133 | ||||
| -rw-r--r-- | scripts/yr.py | 16 |
2 files changed, 145 insertions, 4 deletions
@@ -66,6 +66,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: - -- @@ -153,6 +216,34 @@ 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, @@ -200,6 +292,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: - -- @@ -238,6 +337,22 @@ ALTER TABLE ONLY public.entsoe -- +-- 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: - -- @@ -262,6 +377,14 @@ ALTER TABLE ONLY public.neohub -- +-- 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, |