Using SQLite with Node-RED on Windows (2)
(1) Create DataBase
C:\Users\User\.node-red\2023sqlite_db002.db
(Read-Write-Create)
SQL Query : Fixed statement
CREATE TABLE
dhtreadings
(id INTEGER PRIMARY KEY AUTOINCREMENT,
temperature NUMERIC,
humidity NUMERIC,
currentdate DATE,
currenttime TIME,
device TEXT)
產生2各亂數 模擬 溫度 與 濕度
產生欄位
id INTEGER PRIMARY KEY AUTOINCREMENT,
temperature NUMERIC,
humidity NUMERIC,
currentdate DATE,
currenttime TIME,
device TEXT)
五個欄位的資料 (id 會自行產生並update)
// Create a Params variable
// with a time and value component
//
var myTemp = flow.get("tempC");
var myHumi = flow.get("humiD");
var Today = new Date();
var yyyy = Today.getFullYear(); //年
var MM = Today.getMonth()+1; //月
var dd = Today.getDate(); //日
var h = Today.getHours(); //時
var m = Today.getMinutes(); //分
var s = Today.getSeconds(); //秒
if(MM<10)
{
MM = '0'+MM;
}
if(dd<10)
{
dd = '0'+dd;
}
if(h<10)
{
h = '0'+h;
}
if(m<10)
{
m = '0' + m;
}
if(s<10)
{
s = '0' + s;
}
var var_date= yyyy + '/'+ MM + '/'+ dd ;
var var_time = h + ':' + m + ':' + s ;
var max= 40;
var min= 20;
var var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);
msg.params = { $thetempc: myTemp ,$thehumi: myHumi , $thedate: var_date, $thetime: var_time, $thedevice : var_device }
return msg;
C:\Users\User\.node-red\2023sqlite_db002.db
(Read-Write-Create)
SQL Query : Prepared statement
insert into dhtreadings (temperature, humidity ,currentdate , currenttime , device) values ($thetempc , $thehumi , $thedate , $thetime , $thedevice)
欄位 變數1 變數2
|
temperature |
$thetempc |
myTemp |
|
humidity |
$thehumi |
myHumi |
|
currentdate |
$thedate |
var_date |
|
currenttime |
$thetime, |
var_time |
|
device |
$thedevice |
var_device |
===============================================================
另一方法
使用msg.topic的方法
msg.topic = "INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) " ;
msg.payload = [myTemp, myHumi, var_date , var_time , var_device]
// Create a Params variable
// with a time and value component
//
var myTemp = flow.get("tempC");
var myHumi = flow.get("humiD");
var Today = new Date();
var yyyy = Today.getFullYear(); //年
var MM = Today.getMonth()+1; //月
var dd = Today.getDate(); //日
var h = Today.getHours(); //時
var m = Today.getMinutes(); //分
var s = Today.getSeconds(); //秒
if(MM<10)
{
MM = '0'+MM;
}
if(dd<10)
{
dd = '0'+dd;
}
if(h<10)
{
h = '0'+h;
}
if(m<10)
{
m = '0' + m;
}
if(s<10)
{
s = '0' + s;
}
var var_date= yyyy + '/'+ MM + '/'+ dd ;
var var_time = h + ';' + m + ';' + s ;
var max= 40;
var min= 20;
var var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);
//insert into dhtreadings
//(temperature, humidity ,currentdate , currenttime , device)
//msg.topic = 'INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime("now"));'
//msg.payload = ['SNSOR_3',23,'KW']
//return msg;
msg.topic = "INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) " ;
msg.payload = [myTemp, myHumi, var_date , var_time , var_device]
return msg;
[{"id":"eefd2e0d.1afec","type":"comment","z":"79f346c61c3c7374","name":"sqlite.org","info":"https://fred.sensetecnic.com/\nhttp://noderedguide.com/tutorial-sqlite-and-node-red/\nhttps://www.sqlite.org/lang.html\nhttps://www.sqlite.org/datatype3.html#expraff\n\nCREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)\n\n//INSERT\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;\n\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000\n\nSELECT COUNT(*) FROM RANDOMNUM\n\nUPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0\n\nSELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;\n<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>\n\n","x":100,"y":20,"wires":[]},{"id":"ce813a38.486e28","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"fixed","sql":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","name":"sqlite_db002","x":290,"y":60,"wires":[["4f3f3858.c6ea58"]]},{"id":"adb5271b.e49118","type":"inject","z":"79f346c61c3c7374","name":"CREATE DB","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":60,"wires":[["ce813a38.486e28"]]},{"id":"4f3f3858.c6ea58","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":60,"wires":[]},{"id":"885a944d.7a7838","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":730,"y":240,"wires":[]},{"id":"1edfd08.fcc9c3","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"","name":"sqlite_db002","x":370,"y":360,"wires":[["5b075b30.5b3014","852639d72678c06b"]]},{"id":"5b075b30.5b3014","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":360,"wires":[]},{"id":"1a6eb281.ee04dd","type":"inject","z":"79f346c61c3c7374","name":"COUNT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT COUNT(*) FROM dhtreadings","payload":"","payloadType":"date","x":110,"y":360,"wires":[["1edfd08.fcc9c3"]]},{"id":"4667ba0e.d67e64","type":"inject","z":"79f346c61c3c7374","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM dhtreadings ORDER BY id DESC LIMIT 50;","payload":"","payloadType":"date","x":120,"y":460,"wires":[["9e2dbd84.ab087"]]},{"id":"9e2dbd84.ab087","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"","name":"sqlite_db002","x":410,"y":460,"wires":[["df51560e.5729a8","95eec7a9.8e2a88"]]},{"id":"df51560e.5729a8","type":"ui_template","z":"79f346c61c3c7374","group":"d40ead3e.334ab","name":"","order":0,"width":"12","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>id</th> \n <th>Temperature</th>\n <th>Humidity</th>\n <th>Date</th>\n <th>Times</th>\n <th>DEVICE</th> \n </tr>\n \n <tr ng-repeat=\"x in msg.payload | limitTo:50\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].temperature}}</td>\n <td>{{msg.payload[$index].humidity}}</td>\n <td>{{msg.payload[$index].currentdate}}</td>\n <td>{{msg.payload[$index].currenttime}}</td>\n <td>{{msg.payload[$index].device}}</td> \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":620,"y":460,"wires":[[]]},{"id":"95eec7a9.8e2a88","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":500,"wires":[]},{"id":"a8f6a55b28067bff","type":"inject","z":"79f346c61c3c7374","name":"INSERT 1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":120,"wires":[["98abccc1dd83a597"]]},{"id":"98abccc1dd83a597","type":"random","z":"79f346c61c3c7374","name":"","low":"15","high":"45","inte":"true","property":"payload","x":260,"y":120,"wires":[["27e31fd9170a9eeb"]]},{"id":"ebb3db753f0f5365","type":"function","z":"79f346c61c3c7374","name":"msg.params","func":"// Create a Params variable\n// with a time and value component\n//\nvar myTemp = flow.get(\"tempC\");\nvar myHumi = flow.get(\"humiD\");\n\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\n\nvar var_date= yyyy + '/'+ MM + '/'+ dd ;\nvar var_time = h + ':' + m + ':' + s ;\nvar max= 40;\nvar min= 20;\nvar var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);\n\n\nmsg.params = { $thetempc: myTemp ,$thehumi: myHumi , $thedate: var_date, $thetime: var_time, $thedevice : var_device }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":180,"wires":[["1b7940afb0eb5b8b"]]},{"id":"1b7940afb0eb5b8b","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"prepared","sql":"\ninsert into dhtreadings (temperature, humidity ,currentdate , currenttime , device) values ($thetempc , $thehumi , $thedate , $thetime , $thedevice)","name":"INSERT INTO SQLITE","x":580,"y":180,"wires":[["466c109ae7e9d1b9"]]},{"id":"466c109ae7e9d1b9","type":"debug","z":"79f346c61c3c7374","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":180,"wires":[]},{"id":"cf3063fb14da9cda","type":"comment","z":"79f346c61c3c7374","name":"INSERT ","info":"\n//msg.params = { \n//$thetempc: msg.payload.rand1 \n//,$thehumi: msg.payload.rand2 , \n//$thedate: var_date, \n//$thetime: var_time,\n//$thedevice :'DEVICE1'\n\n//CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT,\n//temperature NUMERIC, \n//humidity NUMERIC, \n//currentdate DATE, \n//currenttime TIME, \n//device TEXT)","x":720,"y":140,"wires":[]},{"id":"27e31fd9170a9eeb","type":"function","z":"79f346c61c3c7374","name":"Store random 變數","func":"var temp1=msg.payload;\nflow.set(\"tempC\", temp1);\n\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nvar max=100;\nvar min= 20;\n//var humi1 = Math.round(Math.random() * (max - min) + min);\nvar humi1 = Math.floor(Math.random()*s);\n\n\nflow.set(\"humiD\", humi1);\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":120,"wires":[["ebb3db753f0f5365"]]},{"id":"784f99f25282d04e","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"SELECT * FROM sensors","name":"sqlite_db002","x":550,"y":240,"wires":[["885a944d.7a7838"]]},{"id":"cb106cd4d88195ca","type":"inject","z":"79f346c61c3c7374","name":"INSERT 2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":240,"wires":[["4ea45ad8c602047f"]]},{"id":"4ea45ad8c602047f","type":"function","z":"79f346c61c3c7374","name":"INSERT","func":"// Create a Params variable\n// with a time and value component\n//\n//var myTemp = flow.get(\"tempC\");\n//var myHumi = flow.get(\"humiD\");\n\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\n\nvar var_date= yyyy + '/'+ MM + '/'+ dd ;\nvar var_time = h + ';' + m + ';' + s ;\nvar max= 40;\nvar min= 20;\nvar var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);\n\n//insert into dhtreadings \n//(temperature, humidity ,currentdate , currenttime , device) \n\n//msg.topic = 'INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime(\"now\"));'\n//msg.payload = ['SNSOR_3',23,'KW']\n//return msg;\nvar myTemp = Math.floor(Math.random()*s);\nvar max=100;\nvar min= 20;\nvar myHumi = Math.round(Math.random() * (max - min) + min);\n\nmsg.topic = \"INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) \" ;\nmsg.payload = [myTemp, myHumi, var_date , var_time , var_device]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":240,"wires":[["784f99f25282d04e"]]},{"id":"780629e1b61b481d","type":"ui_text","z":"79f346c61c3c7374","group":"d40ead3e.334ab","order":1,"width":0,"height":0,"name":"","label":"資料庫目前筆數:","format":"{{msg.payload}}","layout":"row-left","className":"","x":740,"y":320,"wires":[]},{"id":"852639d72678c06b","type":"function","z":"79f346c61c3c7374","name":"function 25","func":"msg.payload=msg.payload[0];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":570,"y":320,"wires":[["780629e1b61b481d"]]},{"id":"3cc2697f190f3dee","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\2023sqlite_db002.db","mode":"RWC"},{"id":"d40ead3e.334ab","type":"ui_group","name":"Group 1","tab":"3c770aff.9ba2a6","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"3c770aff.9ba2a6","type":"ui_tab","name":"Main Tab","icon":"dashboard","order":1}]
沒有留言:
張貼留言