Zugriff auf eigene MySQL-Datenbank

Kamikaze

Hallo,

ich habe ein kleines Scriptiering -Problem. Ich möchte auf eine beliebige MySQL-Datenbank via javascript zugreifen und entsprechende SQL-Aktivitäten durchführen.

Aktueller Hintergrund ist, dass ich in einer bestehenden DB bereits Messwerte von Stromverbäuchen habe. Dies sind ca. 50000 Zeilen. Diese Messwerte müssen aber noch aufbereitet werden, um dann die gewünschten Grafiken daraus zu erstellen, daher muß ich z.B. Datensätze für einen bestimmtenZeitraum einlesen, die Werte verarbeiten und notwenige Änderungen zurückschreiben.

Ein weiterer Anwendungsfall wäre der Zugriff auf die Kodi-Film-DB um einen Film direkt von IOBroker auswählbar zu machen, oder Musik für eine Party abzuspielen.

Also nun zu meiner konkreten Frage:

Wie kann ich aus einem javascript-Programm auf die unterschiedlichen Datenbanken/Tabellen zugreifen?

Wie erstelle ich entsprechende Queries und wie erhalte ich das Ergebnis zurück?

Hoffentlich hat jemand einen konstruktiven Vorschlag?

frankjoke

Hatte mir mal 'n script geschrieben um auf das MySQL History zurückzugreifen (und von einem anderen System Daten zu importieren).

`var myServer = {
        host     : 'localhost',
        port     : 0,
        user     : 'iobroker',
        password : 'iobroker',
        database : 'iobroker',
        mydebug  : true,
    };

function MySQL(options) {
    if (!(this instanceof MySQL)) return new MySQL(options);
    if (!options) throw 'MySQL configuration options not set!';

    this.opt = options;
    this.connection = null;
    this.sqt = ["ts_number","ts_string","ts_bool"];
    this.sqd = null;
    this.sqf = null;

    var that = this;

    that.end = function(callback) {
        if (that.connection) {
            that.connection.end(callback);
            that.connection = null;
        } else
            log("No connection to stop!");
    };

    that.getConn = function() { return that.connection; };
    that.getSqt = function() { return that.sqt; };
    that.getSqd = function() { return that.sqd; };
    that.getSqf = function() { return that.sqf; };
    that.getOpt = function() { return that.opt; };

    that.convArr = function(arr,name) {
        o = {};
        for (var i=0; i<arr.length; 0/++i)/{/var/e="arr[i];" if/(e[name])/o[e[name]]="e;" }/return/o;/};/that.start="function" (callback)/conn="mysql.createConnection(that.opt);" (that.connection)/that.stopconnection();/conn.connect(function/(err)/that.connection="null;" callback/&&/callback(err);/else/that.querymysql("select/*/from/sources",/function(rows,fields)/(!rows)/callback(fields);/that.sqf="that.convArr(rows,&quot;name&quot;);" if(that.opt.mydebug)/logj("from/table",that.sqf);/datapoints",/that.sqd="that.convArr(rows,&quot;name&quot;);" logj("datapoints/table",that.sqd);/callback(null);/});/that.querymysql="function(query,callback)" if(!that.connection)/logj("no/connection/to/mysql!",that.opt,"warn");/null;/(query.substr(-1,1)/!="=" ';')/query="query" +/";";/logs("start/query:/"+/query,"debug2","darkgreen");/nexttick();/that.connection.query(query,/function(err,/rows,/fields)/logj("query/sql/err:",err);/callback(null,err);/f="fields" ||/[];/r="rows.length" 0;/logs("success/returned/"+f.length/"/colums/and/datasets","debug2","green");/console.log('the/solution/is:/'+/rows[0].name);/callback(rows,fields);/that.getidn="function(ids)" if(!that.sqd)/logs("no/sqd/data/found/in/mysql.getidn()!");/idn="that.sqd[ids];" (!idn)/logs("id/not/found,/findentry:"+ids,"warn");/idn;/that.gettyp="function(idn)" if(!idn)/mysql.gettyp()!");/table="that.sqt[idn.type];" (!table)/tt="[&quot;number&quot;,&quot;boolean&quot;,&quot;string&quot;].indexOf(idn.type);" (tt<0)/logj("type/findentry:",idn,"warn");/table;/that.findentry="function(ids,ts,callback,opt)" (!callback/!ids/!ts)/logs("wrong/arguments/mysql.findentry(id,ts,callback,opt)!");/(idn)/logs("type/findentry:"+idn.ytpe,"warn");/+"/where/ts=" + ts +" id="+idn.id;
            that.queryMySQL(sql,function(rows,fields) {
                if (callback &amp;&amp; rows &amp;&amp; fields &amp;&amp; rows.length>0) {
                    var cnt = rows[0]['COUNT(*)'];
//                    logs(" rows/was/+cnt);/(!cnt/cnt="==0)" callback(null,opt);/callback(ids,opt);/get/history/optionally/limited/by/fro0m/timestamts/timestamps./no/then/until/now,/anf/all/that.gethistory="function(ids,callback,from_ts,to_ts)" if(!callback/!ids)/mysql.gethistory(id,ts,callback,opt)!");/idt="that.getTyp(idn);">=" + from_ts;
            else {
                if(from_ts > to_ts) {
                    var i = from_ts;
                    from_ts = to_ts;
                    to_ts = i;
                }
            sql += " AND ts>="+ from_ts + " AND ts<=" + to_ts;
            }
        }
        that.queryMySQL(sql, callback);
    };

    that.addToHistory = function(ids,item,callback) {
        if(!callback || !item)
            return logs("Wrong arguments in MySQL.addToHistory(ids,item,callback)!");
        if(that.opt.mydebug)
            logj("MySQL.addToHistory "+ ids ,item,"debug2","darkgreen");
        var idn = that.getIdn(ids);
        var idt = that.getTyp(item);
        var idf = that.sqf[item.from] || 1;
        var query = "INSERT INTO "+ idt +" (id, ts, val, ack, _from, q) VALUES(" 
                + idn.id + ", "             // id
                + item.ts + ", "       
                + item.val + ", " 
                + (item.ack ? item.ack : 0) + ", "
                + (idf.id ? idf.id : 1)  + ", "                 // _from, in my case 1 =: system.adapter.javascript.0, 5 = .1!
                + (item.q ? item.q : 0) + ") ON DUPLICATE KEY UPDATE val="
                + item.val + ", ack=" 
                + (item.ack ? item.ack : 0) +", q="
                + (item.q ? item.q : 0) + ", _from="
                + (idf.id ? idf.id : 1) + ";";
        that.queryMySQL(query,function(rows, fields) {
            if (!rows) {
                logj("addToHistory err with query "+query,fields,"warn");
                callback && callback(null,fields);
            } else
                callback && callback(rows,fields);

        });

    };
}</arr.length;>` 

Im Prinzip sollte vieles andere auch funktionieren...

Ah, ja, die options sind MyServer.... meiner Testmaschine müssen adaptiert werden.[/tt][/i]

Kamikaze

Vielen Dank für die schnelle Antwort.

Da ich noch nicht so richtig viel Erfahrung mit javascript habe, muß ich noch eine Frage hinterher schieben.

Hast du auch noch ein Beispiel für die Nutzung dieser Routinen? Den grundlegenden Aufbau habe ich verstanden, aber die jeweilige Nutzung aufgrund der Objekt-Orientierung noch nicht.

Danke

frankjoke

Kamikaze,

Das script ist ziemlich auf die sql-history zuigeschnitten.

Am wichtigsten ist:

	// start code
        var conn = mysql.createConnection(that.opt);

        if (that.connection) 
            that.stopConnection();

        conn.connect(function (err) {
            if (err) {
                that.connection = null;
                return callback && callback(err);
            } else {
                that.connection = conn;
            }
   // Der Rest von start ist history-spezifisch und 'callback && callback(null)'  könnte schon hier aufgerufen werden

 // und dann Query

     that.queryMySQL = function(query,callback) {

        if(!that.connection) {
            logj("No connection to MySQL!",that.opt,"warn");
            return null;
        }

        if (query.substr(-1,1) !== ';')
            query = query + ";";
        if(that.opt.mydebug)
            logs("Start Query: "+ query,"debug2","darkgreen");
//        nextTick();
        that.connection.query(query, function(err, rows, fields) {
            if (err) {
                if(that.opt.mydebug)
                    logj("Query SQL Err:",err);
                callback(null,err);
            } else {
                if(that.opt.mydebug) {
                    var f = fields || [];
                    var r = rows.length || 0;
                    logs("Success returned "+f.length + " colums and " + 0 + " datasets","debug2","green");
                }
                //console.log('The solution is: '+ rows[0].name);
                callback(rows,fields);
            }
        });

    };

Alle Abfragen/Änderungen/… werden mit der Query und SQL-Befehlen gemacht.

p.s.: logj= wandelt das 2. Argument in ein JSON-String an und hängts dann an den log-Text an...

In der Funktion addHistory, getHistory oder findEntry siehst du wie ein SQL-Statement aufgebaut wird das entweder Daten in die Datenbank speichert oder herausholt.

Meine Scripts haben nur diese drei Routinen aufgerufen um die Historydaten zu manipulieren, alle anderen Aufrufe waren nur mit

    var mySql = new MySQL(myServerURL);
    mySql.start(function(err) {
    	// mach hier was wenn kein Err
    	// z.B
    	mySql.queryMySQL (query, function (rows,fields) {
        // bearbeite Ergebnis von query 
        });
    });

Auf dem git vom mysql npm-package kannst du mehr über das Interface nachlesen welche ich verwendet habe: https://github.com/mysqljs/mysql

Kamikaze

Hallo,

jetzt habe ich alles was ich brauche. Vielen Dank für deine Hilfe. Der Hinweis mit git hat mir doch sehr geholfen. Jetzt habe ich die erste Verbindung herstellen können und auf die Tabellen zugreifen können. Die Basis steht. Jetzt geht es an die Einzelheiten.

Da deine Lösung sehr auf die History getrimmt ist, werde ich diese zwar nicht verwenden, ich konnte mir aber alle wichtigen Vorgehensweisen herausziehen.

Grüsse

MadTrinity

Hallo,

möchte auch gerne aus meiner mySQL Datenbank werte auslesen um diese dann in Vis anzuzeigen.

Leider habe ich Null erfahrung in Sachen javascript Ibroker und so, ich habe obiges script kopiert unter myServer die Daten meiner Datenbank eingegeben, beim Ausführen steht im Log nur startMySQL. Bin davon ausgegangen das die Tabellen und deren Inhalte jetzt unter Objekte zu verfügung stehen.

Kann mir bitte einer erklären wie ich auf meine Daten auslesen kann?

dtp

Sagt mal, wieso macht ihr das denn nicht mit dem Flot-Adapter?

Mich würde aber mal interessieren, ob es möglich ist, per Skript die Werte eines bestimmten Datenpunkts der SQL-Datenbank für ein definierbares Zeitfenster zu löschen, denn das geht ja meines Wissens leider nicht direkt über den SQL-Adpater, oder?

Dutchman

Doch geht, siehe docu :

 Custom queries

The user can execute custom queries on tables from javascript adapter:

sendTo('sql.0', 'query', 'SELECT * FROM datapoints', function (result) {
    if (result.error) {
        console.error(result.error);
    } else {
        // show result
         console.log('Rows: ' + JSON.stringify(result.result));
    }
});
Or get entries for the last hour for ID=system.adapter.admin.0.memRss

sendTo('sql.0', 'query', 'SELECT id FROM datapoints WHERE name="system.adapter.admin.0.memRss"', function (result) {
    if (result.error) {
        console.error(result.error);
    } else {
        // show result
        console.log('Rows: ' + JSON.stringify(result.result));
        var now = new Date();
        now.setHours(-1);
        sendTo('sql.0', 'query', 'SELECT * FROM ts_number WHERE ts >= ' + now.getTime() + ' AND id=' + result.result[0].id, function (result) {
            console.log('Rows: ' + JSON.stringify(result.result));
        });
    }
});

Musst dir halt nur selber den query zusammen basteln

https://github.com/ioBroker/ioBroker.sq … /README.md

Sent from my iPhone using Tapatalk

MadTrinity

Hi,

kann mir bitte einer ein Beispiel posten wie ich die Daten aus meiner MySQL Datenbank in Iobroker Objekte nutzen kann.

Meine mySQL Daten wären folgende

mysqlhost = localhost

mysqluser = me

mysqlpwd = passwort

mysqldb = Haus

jetzt würde ich gerne "wirkleistung" From "strom_snapshot" abfragen und dann bei IObroker unter Objekte speichern um die Werte dann unter Vis anzuzeigen.

Die Daten schreibe ich meist mit Phyton in die Datenbank, kann man nicht schon von dort aus die Daten an Iobroker senden habe irgenwas mit curl gelesene aber bekomme das ohne hilfe leider nicht hin.

Vielen Dank vorab

Dutchman

Da du schon mit Python schreibst schau dir das hier Mal an damit könntest du direkt mit deinem Python Script den api-call ausführen

https://github.com/ioBroker/ioBroker.si … /README.md

Sent from my iPhone using Tapatalk

MadTrinity

Wunderbar das funktioniert.

Schönen Dank

NEWS

Zugriff auf eigene MySQL-Datenbank

Support us

884
Online

31.6k
Users

79.6k
Topics

1.3m
Posts

NEWS

Zugriff auf eigene MySQL-Datenbank

Support us

884Online

31.6kUsers

79.6kTopics

1.3mPosts

884
Online

31.6k
Users

79.6k
Topics

1.3m
Posts