diff options
| -rw-r--r-- | mqtt_listener.service (renamed from queue2pgsql.service) | 6 | ||||
| -rw-r--r-- | queue_runner.service (renamed from mqtt2queue.service) | 6 | ||||
| -rw-r--r-- | requirements.txt | 21 | ||||
| -rwxr-xr-x | run.sh | 11 | ||||
| -rw-r--r-- | scripts/common/__init__.py (renamed from common/__init__.py) | 0 | ||||
| -rw-r--r-- | scripts/common/postgres.py (renamed from common/postgres.py) | 0 | ||||
| -rw-r--r-- | scripts/common/queue.py (renamed from common/queue.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/elvia.py (renamed from elvia2pgsql.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/elvia_gridtariff.py (renamed from elvia_gridtariff2pgsql.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/entsoe.py (renamed from entsoe2pgsql.py) | 0 | ||||
| -rw-r--r-- | scripts/mqtt_listener.py (renamed from mqtt2queue.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/nb.py (renamed from nb2pgsql.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/neohub.py (renamed from neohub2pgsql.py) | 0 | ||||
| -rw-r--r-- | scripts/queue_runner.py (renamed from queue2pgsql.py) | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/tibber_consumption.py (renamed from tibber_consumption2pgsql.py) | 44 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/tibber_prices.py (renamed from tibber_prices2pgsql.py) | 39 | ||||
| -rw-r--r--[-rwxr-xr-x] | scripts/yr.py (renamed from yr2pgsql.py) | 0 | ||||
| -rw-r--r-- | todb.py | 22 |
18 files changed, 51 insertions, 98 deletions
diff --git a/queue2pgsql.service b/mqtt_listener.service index 49e4066..1ae79ca 100644 --- a/queue2pgsql.service +++ b/mqtt_listener.service @@ -1,5 +1,5 @@ [Unit] -Description=Simple service to start python-listener +Description=Simple service to start mqtt-listener StartLimitIntervalSec=100 StartLimitBurst=5 @@ -7,11 +7,11 @@ StartLimitBurst=5 Type=simple EnvironmentFile=%h/energyscripts/.env WorkingDirectory=%h/energyscripts -ExecStart=%h/energyscripts/venv/bin/python3 -u queue2pgsql.py +ExecStart=%h/energyscripts/venv/bin/python3 -u scripts/mqtt_listener.py Restart=on-failure TimeoutStopSec=70 RestartSec=30 -SyslogIdentifier=queue2pgsql +SyslogIdentifier=%N [Install] WantedBy=default.target diff --git a/mqtt2queue.service b/queue_runner.service index a63c994..6b78068 100644 --- a/mqtt2queue.service +++ b/queue_runner.service @@ -1,5 +1,5 @@ [Unit] -Description=Simple service to start python-listener +Description=Simple service to start queue-runner StartLimitIntervalSec=100 StartLimitBurst=5 @@ -7,11 +7,11 @@ StartLimitBurst=5 Type=simple EnvironmentFile=%h/energyscripts/.env WorkingDirectory=%h/energyscripts -ExecStart=%h/energyscripts/venv/bin/python3 -u mqtt2queue.py +ExecStart=%h/energyscripts/venv/bin/python3 -u scripts/queue_runner.py Restart=on-failure TimeoutStopSec=70 RestartSec=30 -SyslogIdentifier=mqtt2queue +SyslogIdentifier=%N [Install] WantedBy=default.target diff --git a/requirements.txt b/requirements.txt index 0dd3ee4..e2d36d3 100644 --- a/requirements.txt +++ b/requirements.txt @@ -1,13 +1,32 @@ +astroid==2.14.1 async-property==0.2.1 -async-timeout==4.0.2 certifi==2022.12.7 charset-normalizer==3.0.1 +DateTime==5.0 +dill==0.3.6 idna==3.4 +isort==5.12.0 +lazy-object-proxy==1.9.0 litequeue==0.5 +mccabe==0.7.0 neohubapi==1.0 paho-mqtt==1.6.1 +platformdirs==2.6.2 psycopg==3.1.8 +psycopg2-binary==2.9.5 +pylint==2.16.0 +python-dateutil==2.8.2 +pytz==2022.7.1 +pytz-deprecation-shim==0.1.0.post0 requests==2.28.2 +six==1.16.0 +tomli==2.0.1 +tomlkit==0.11.6 +typing_extensions==4.4.0 +tzdata==2022.7 +tzlocal==4.2 urllib3==1.26.14 websockets==10.4 +wrapt==1.14.1 xmltodict==0.13.0 +zope.interface==5.5.2 @@ -7,4 +7,13 @@ cd ${0:h} setopt all_export source .env unsetopt all_export -./venv/bin/python3 $1 || exit 1 + +if [[ -f $1 ]]; then + ./venv/bin/python3 $1 +elif [[ -f scripts/$1 ]]; then + ./venv/bin/python3 scripts/$1 +else + print -u2 "Computer says no" && exit 1 +fi + +exit $? diff --git a/common/__init__.py b/scripts/common/__init__.py index 89977d2..89977d2 100644 --- a/common/__init__.py +++ b/scripts/common/__init__.py diff --git a/common/postgres.py b/scripts/common/postgres.py index bff0720..bff0720 100644 --- a/common/postgres.py +++ b/scripts/common/postgres.py diff --git a/common/queue.py b/scripts/common/queue.py index e567fb4..e567fb4 100644 --- a/common/queue.py +++ b/scripts/common/queue.py diff --git a/elvia2pgsql.py b/scripts/elvia.py index be5eaae..be5eaae 100755..100644 --- a/elvia2pgsql.py +++ b/scripts/elvia.py diff --git a/elvia_gridtariff2pgsql.py b/scripts/elvia_gridtariff.py index bcf4bd7..bcf4bd7 100755..100644 --- a/elvia_gridtariff2pgsql.py +++ b/scripts/elvia_gridtariff.py diff --git a/entsoe2pgsql.py b/scripts/entsoe.py index 2597a98..2597a98 100755..100644 --- a/entsoe2pgsql.py +++ b/scripts/entsoe.py diff --git a/mqtt2queue.py b/scripts/mqtt_listener.py index 24ee207..24ee207 100644 --- a/mqtt2queue.py +++ b/scripts/mqtt_listener.py diff --git a/nb2pgsql.py b/scripts/nb.py index 1901182..1901182 100755..100644 --- a/nb2pgsql.py +++ b/scripts/nb.py diff --git a/neohub2pgsql.py b/scripts/neohub.py index 5c62606..5c62606 100755..100644 --- a/neohub2pgsql.py +++ b/scripts/neohub.py diff --git a/queue2pgsql.py b/scripts/queue_runner.py index 436dcb8..436dcb8 100644 --- a/queue2pgsql.py +++ b/scripts/queue_runner.py diff --git a/tibber_consumption2pgsql.py b/scripts/tibber_consumption.py index e710b8f..a411562 100755..100644 --- a/tibber_consumption2pgsql.py +++ b/scripts/tibber_consumption.py @@ -1,14 +1,14 @@ -#!/bin/python3 +#!/usr/bin/env python3 +''' import energy consumption from tibber ''' import os import sys -import json -import psycopg2 -import requests - from datetime import datetime from datetime import timedelta from tzlocal import get_localzone +import requests + +from common import dbi # variables @@ -34,9 +34,9 @@ try: 'Content-Type': 'application/json', } - body = {"query":"""{ - viewer { - homes { + body = {"query":"""{ + viewer { + homes { consumption(resolution: HOURLY, last:100) { nodes { from @@ -73,30 +73,4 @@ for item in data["data"]["viewer"]["homes"][0]["consumption"]["nodes"]: if item["consumption"] is not None: values.append((item["from"],item["to"],item["consumption"],item["consumptionUnit"],item["cost"],item["unitPrice"],item["unitPriceVAT"])) -# connect to db -conn = psycopg2.connect(database=pg_db, host=pg_host) -cur = conn.cursor() - -# count rows before we start -cur.execute("SELECT COUNT(*) FROM " + pg_table) -before = cur.fetchone() - -# insert data -try: - cur.executemany("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (startTime,endTime) DO NOTHING", values) - conn.commit() -except Exception as e: - conn.rollback() - raise e - -# count rows after we finnish -cur.execute("SELECT COUNT(*) FROM " + pg_table) -after = cur.fetchone() - -# count *new* rows -newRows = after[0] - before[0] - -# close connection -conn.close() - -print("Successfully inserted " + str(newRows) + " records into the database") +dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (startTime,endTime) DO NOTHING", values, verbose=True) diff --git a/tibber_prices2pgsql.py b/scripts/tibber_prices.py index 046072f..81b3afb 100755..100644 --- a/tibber_prices2pgsql.py +++ b/scripts/tibber_prices.py @@ -1,14 +1,14 @@ -#!/bin/python3 +#!/usr/bin/env python3 +''' import energy prices from tibber ''' import os import sys -import json -import psycopg2 -import requests - from datetime import datetime from datetime import timedelta from tzlocal import get_localzone +import requests + +from common import dbi # variables @@ -73,31 +73,4 @@ for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo prices.append((item["startsAt"],item["total"],item["energy"],item["tax"],item["level"])) -# connect to db -conn = psycopg2.connect(database=pg_db, host=pg_host) -cur = conn.cursor() - -# count rows before we start -cur.execute("SELECT COUNT(*) FROM " + pg_table) -before = cur.fetchone() - -# insert data -try: - cur.executemany("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s) ON CONFLICT (startsat) DO NOTHING", prices) - conn.commit() -except Exception as e: - conn.rollback() - raise e - -# count rows after we finnish -cur.execute("SELECT COUNT(*) FROM " + pg_table) -after = cur.fetchone() - -# count *new* rows -newRows = after[0] - before[0] - -# close connection -conn.close() - - -print("Successfully inserted " + str(newRows) + " records into the database") +dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s) ON CONFLICT (startsat) DO NOTHING", prices, verbose=True) diff --git a/yr2pgsql.py b/scripts/yr.py index 9c3ae5e..9c3ae5e 100755..100644 --- a/yr2pgsql.py +++ b/scripts/yr.py diff --git a/todb.py b/todb.py deleted file mode 100644 index 7426ace..0000000 --- a/todb.py +++ /dev/null @@ -1,22 +0,0 @@ -#!/usr/bin/env python3 - -import os -import psycopg - -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_user = os.environ['el_pg_user'] -pg_pass = os.environ['el_pg_pass'] - -def todb(sql, values): - with psycopg.connect(dbname=pg_db, host=pg_host, user=pg_user, password=pg_pass) as conn: - if type(values) == list: - cur = conn.cursor() - cur.executemany(sql, values) - return cur.rowcount - elif type(values) == tuple: - conn.execute(sql, values) - return 1 - else: - print("OH SHIT") - exit() |