Von Shelly zu MySQL: Datenübertragung mit PowerShell automatisieren (2024)

Im letzten Beitrag habe ich dir gezeigt wie du die Daten von einem Shelly Plus PM Mini mit HTTP-Requests via VBA auslesen kannst. In diesem Beitrag soll es nun darum gehen wie du die Daten automatisiert vom Shelly mit PowerShell in eine MySQL Datenbank ablegen kannst.

Ich werde dir in den nächsten Abschnitten Schritt-für-Schritt erläutern, wie du:

• die MySQL Datenbank auf einem Microsoft Windows PC installierst,
• das PowerShell Skript in Visual Studio Code programmierst und ausführst

Schaltung mit dem Shelly Plus 1PM Mini

Für diesen Beitrag verwende ich eine kleine Schaltung mit dem Shelly Plus 1PM Mini, an welchen ich lediglich eine Lötstation über eine Steckdose angeklemmt habe.

Installieren von XAMPP

Das Paket XAMPP enthält neben einem Apache2 Server, Php auch eine MariaDB welche mit MySQL kompatibel ist und kannst du dir kostenfrei unter https://www.apachefriends.org/de/download.html herunterladen.

In meinem Fall lade ich mir die Version 8.2.4-0-VS16 herunter.

Wenn XAMPP erfolgreich installiert wurde, dann öffnet sich das XAMPP Control Panel aus welchem wir die Server / Dienste starten können.

Bedenke, dass die Server / Dienste einen Port deines PCs belegen und somit ggf. andere Tools, welche diesen auch verwenden, in einen Konflikt geraten können.

Starten der Dienste für die MySQL Datenbank

Im nächsten Schritt starten wir nun das Modul “Apache” sowie “MySQL” indem wir auf die jeweilige Schaltfläche “Starten” klicken.

Bei XAMPP ist das gute, dass dieses bereits alles Vorkonfiguriert ist und wir nur in Spezialfällen etwas ändern müssen!

Einrichten der Datenbank

Wenn wir XAMPP installiert haben müssen wir als Nächstes die Datenbank einrichten, das können wir entweder über die Konsole oder bequem per phpMyAdmin erledigen. In meinem Fall mache ich das per phpMyAdmin welches im Browser unter http://localhost/phpmyadmin/ aufgerufen werden kann. Auf der Konsole geht dieses auch, nur hier muss man die gesamten SQL Statements manuell eingeben.

Am Ende dieses Abschnitts stelle ich dir eine SQL-Datei zum Download bereit, welche das von mir erstellte Schema enthält, sodass du dieses nur importieren musst.

Schritt 1 – Erstellen der Datenbank

Im ersten Schritt erstellen wir die Datenbank und klicken auf den Reiter “Datenbanken” (1). Danach vergeben wir unserer Datenbank einen Namen, in meinem Fall nehme ich die Bezeichnung “shelly_db” (2). Dieses bestätigen wir mit der Schaltfläche “Anlegen” (3).

Schritt 2 – Erstellen der Tabelle für die Messwerte

Im zweiten Schritt legen wir uns eine Tabelle an, um dort später die Messwerte abzulegen. Dazu vergeben wir im Reiter “Struktur” (5) im Feld Tabellenname den Namen der neuen Tabelle, in meinem Fall gebe ich hier “messdaten” (6) ein. Initial soll diese Tabelle 6 Spalten (7) haben. Diese Auswahl bestätige ich mit der Schaltfläche “Anlegen” (8).

Im nächsten Schritt legen wir die Spalten an, hier sollten wir im Vorausplanen, welche Messdaten und in welchem Format (Datentyp) wir aufnehmen wollen.

Der Shelly Plus 1PM Mini liefert nachfolgende Werte:

• die Leistungsaufnahme (in Watt pro Stunde),
• die Spannung (in Volt),
• die Frequenz (in Herz),
• die Stromaufnahme (in Ampere), sowie
• einen Zeitstempel (Sekunden seit 01.01.1970)

Zusätzlich erhalten wir auch die Temperatur in Grad Celsius sowie in Fahrenheit. Diese interessieren mich in diesem Fall jedoch nicht und daher lasse ich diese außen vor.

Alle Werte außer der Zeitstempel werden als Gleitkommazahlen ausgegeben und daher legen wir für jeden Wert eine Spalte mit dem Datentyp DOUBLE an. Sowie noch eine Spalte ID welche als INT und mit “auto increment” angelegt wird. Die MySQL Datenbank legt automatisch auf diese Spalte einen Primärschlüssel.

Download des Shema als ZIP-Datei

Wenn du dir diese Schritte sparen möchtest, dann kannst du dir das Schema auch bequem als SQL-Datei herunterladen und importieren.

Zum Importieren dieser SQL-Datei navigierst du über den Reiter “Importieren” (1) und wählst zunächst die Datei (2) aus. Danach scrollst du das Fenster nach unten und wählst die Schaltfläche “Importieren” (3).

Anlegen eines Benutzers für die Datenbank

Es ist nie ratsam mit dem Benutzer root zu arbeiten da dieser sehr weitreichende Rechte hat. Über die Oberfläche von phpMyAdmin können wir uns einen Benutzer anlegen und diesem die benötigten Rechte für die Datenbank “shelly_db” geben.

Erstellen des PowerShell-Skriptes in Visual Studio Code

Das benötigte PowerShell Skript kannst du auch in einem einfachen Editor schreiben, jedoch bietet eine Entwicklungsumgebung noch zusätzliche Features, welche uns bei der Programmierung unterstützen.

Das Programm Visual Studio Code kannst du dir unter https://code.visualstudio.com/download kostenfrei herunterladen und installieren. Wie du dieses machst, habe ich dir bereits im Beitrag zur Programmiersprache Rust erläutert.

Schritt 1 – Installieren der Erweiterung für PowerShell in VSC

Ich gehe nun davon aus, dass bei dir Visual Studio Code installiert ist. Somit können wir beginnen und die Erweiterung für PowerShell installieren. Dazu klicken wir in der linken Menüleiste auf “Extensions” (1) und suchen dann nach “PowerShell” (2) in meinem Fall wähle ich die offizielle Erweiterung von Microsoft und klicke dort auf die Schaltfläche “Install” (3).

Dem ganzen muss man noch zustimmten und die Installation startet sobald man auf “Trus Workspace & Install” (4) geklickt hat.

Schritt 2 – Verbindung zur MySQL Datenbank

Damit wir eine Verbindung zur MySQL Datenbank aufbauen können, müssen wir zunächst ein Modul installieren, für diesen Beitrag verwende ich SimplySql. Zu diesem Modul gibt es eine sehr schöne Wiki-Seite https://github.com/mithrandyr/SimplySql/wiki/, auf welcher du die Funktionen mit Beispielen findest.

Schritt 2.1 – Installieren von SimplySql

Für die Installation muss ein PowerShell Terminal mit Administratorrechten geöffnet werden.

Im neuen Terminal gibt man nun den nachfolgenden Befehl ein.

Install-Module -Name SimplySql

Un bestätigt wärend dieser Installation mit “J”. Wenn dieses Abgeschlossen ist, können wir das Terminalfenster schließen.

Schritt 2.2 – Importieren von SimplySql

Nun kehren wir in Visual Studio Code zurück und dort importieren wir dieses Modul.

#Modul SimplySql importierenImport-Module SimplySql

Schritt 2.3 – Aufbauen der Datenbankverbindung

Danach bauen wir die Datenbank verbindung auf, hierzu nutzen wir den Benutzer welchen wir zuvor eingerichtet haben.

#Aufbauen einer Verbindung zur DatenbankOpen-MySqlConnection -server '127.0.0.1' -connectionname 'localhost' -database 'shelly_db' -Username benutzer1 -Password passwort1 

Schritt 2.4 – SQL-Insert-Statement vorbereiten

Für unsere Messdaten bereiten wir ein SQL-Insert-Statement vor. Als Values verwende ich hier gleich entsprechende Variablen welche im nächsten Schritt befüllt werden.

$testconnection = Test-SqlConnection -ConnectionName 'localhost'#Wenn die Verbindung erfolgreich aufgebaut wurde,...if ($testconnection) { #SQL Statement $InsertQuery = "INSERT INTO shelly_db.messdaten (ID, apower, voltage, freq, current, minute_ts) VALUES (NULL, '$apower', '$voltage', '$freq', '$current', '$minute_ts');" #Ausführen des Statements Invoke-SqlUpdate -connectionname 'localhost' -Query $InsertQuery #Schließen der Datenbankverbindung Close-SqlConnection -connectionname 'localhost'}

Wichtig ist, das am Ende die Datenbankverbindung geschlossen wird!

Schritt 3 – Abrufen der Messdaten vom Shelly

Die Messdaten vom Shelly lesen wir mit einem einfachen Aufruf per WebRequest. Hier prüfe ich zusätzlich ob die Antwort länger als 0 Zeichen ist, ansonsten verlasse ich das Skript mit einer Fehlermeldung.

#Abrufen der Daten vom Shelly$response = Invoke-WebRequest -URI http://192.168.178.35/rpc/Switch.GetStatus?id=0 -UseBasicParsing#Wenn die länge des Textes gleich 0 ist dann soll das Skript verlassen werden!if ($response.Length -eq 0) { "Es wurde keine Antwort empfangen!" exit}

Ansonsten wird die Anwort in ein JSON-Format geparst und in die Variablen geschrieben.

#Umwandeln der Antwort in ein JSON-Format$data = $response | ConvertFrom-Json#Auslesen der Werte$apower = $data.apower$voltage = $data.voltage$freq = $data.freq$current = $data.current$minute_ts = $data.aenergy.minute_ts

Ausführen des PowerShell Skriptes zum Speichern der Daten eines Shelly in die MySQL Datenbank

Aus Visual Studio Code kannst du das Skript per klick auf “Run” oben rechts das Skript ausführen.

Alternativ kannst du auch ein Terminal öffnen und zu dem Ordner navigieren und dort das Skript manuell ausführen.

Ansicht in phpMyAdmin

Wenn wir das Skript erfolgreich ausgeführt wurde, dann sehen wir in der Tabelle “messdaten” eine neue Zeile mit den aktuellen Messdaten.

Fertiges PowerShell Skript zum speichern von Messdaten eines Shelly Plus 1PM Mini in eine MySQL Datenbank

Hier nun das fertige PowerShell Skript um die Daten eine Shelly in die MySQL Datenbank zu schreiben.

#Modul SimplySql importierenImport-Module SimplySql#Abrufen der Daten vom Shelly$response = Invoke-WebRequest -URI http://192.168.178.35/rpc/Switch.GetStatus?id=0 -UseBasicParsing#Wenn die länge des Textes gleich 0 ist dann soll das Skript verlassen werden!if ($response.Length -eq 0) { "Es wurde keine Antwort empfangen!" exit}#Umwandeln der Antwort in ein JSON-Format$data = $response | ConvertFrom-Json#Auslesen der Werte$apower = $data.apower$voltage = $data.voltage$freq = $data.freq$current = $data.current$minute_ts = $data.aenergy.minute_ts#Aufbauen einer Verbindung zur DatenbankOpen-MySqlConnection -server '127.0.0.1' -connectionname 'localhost' -database 'shelly_db' -Username benutzer1 -Password passwort1 $testconnection = Test-SqlConnection -ConnectionName 'localhost'#Wenn die Verbindung erfolgreich aufgebaut wurde,...if ($testconnection) { #SQL Statement $InsertQuery = "INSERT INTO shelly_db.messdaten (ID, apower, voltage, freq, current, minute_ts) VALUES (NULL, '$apower', '$voltage', '$freq', '$current', '$minute_ts');" #Ausführen des Statements Invoke-SqlUpdate -connectionname 'localhost' -Query $InsertQuery #Schließen der Datenbankverbindung Close-SqlConnection -connectionname 'localhost'}

Troubleshooting

Fehler beim Ausführen von PowerShell Skripte

In meinem Fall bekam ich beim ersten Ausführen des PowerShell Skriptes einen Fehler, dass dieses auf meinem System nicht genehmigt ist.

PS D:\PowerShell\ShellyFetchData> .\shellyFetchData.ps1.\shellyFetchData.ps1 : Die Datei "D:\PowerShell\ShellyFetchData\shellyFetchData.ps1" kann nicht geladen werden, da die Ausführung von Skripts auf diesem System deaktiviert ist. Weitere Informationen finden Sie unter "about_Execution_Policies" (https:/go.microsoft.com/fwlink/?LinkID=135170).In Zeile:1 Zeichen:1+ .\shellyFetchData.ps1+ ~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : Sicherheitsfehler: (:) [], PSSecurityException + FullyQualifiedErrorId : UnauthorizedAccess

Nachfolgend die Schritte, um das Ausführen von PowerShell Skripte unter Microsoft Windows 10 zu erlauben:

Fehler “Cannot create file” beim Beenden von XAMPP

Wenn XAMPP nicht mit Administratorrechten gestartet wurde, dann erscheint beim Beenden von XAMPP nachfolgender Fehler.

Dieser Fehler führt dazu, dass das XAMPP Control Panell nurnoch über den Taskmanager beendet werden kann.

Die Lösung ist hier das XAMPP Control Panel mit Administratorrechten zu starten.

Index der Datenzeilen auf 0 zurücksetzen

Wenn du mit dem Skript herumspielst, dann erhöht sich der Wert der Spalte “ID” automatisch. Um dann im Live Betrieb wieder mit einem sauberen Index von 0 zu starten, kannst du das nachfolgende SQL-Statement ausführen:

ALTER TABLE messdaten AUTO_INCREMENT=0;

Fazit & Ausblick

Damit möchte ich nun diesen Beitrag abschließen in welchem ich dir gezeigt habe wie man die Daten eines Shelly in eine MySQL Datenbank per PowerShell speichern kann. Das schreiben eines PowerShell Skriptes ist recht einfach da so ziemlich alles irgendwo beschrieben und mit Beispielen hinterlegt ist.
In meinem Fall habe ich mich an den Beispielen von den nachfolgenden Seiten gehalten:

• https://davidhamann.de/2019/04/12/powershell-invoke-webrequest-by-example/,
• https://github.com/mithrandyr/SimplySql/wiki/,

Da wir nun die Daten in der Datenbank haben, können wir diese nun auf Webseiten, Excelmappen etc. weiterverarbeiten und diese möchte ich dir in den nächsten Beiträgen zeigen.