Node-Red與 Sqlite
參考來源 https://funprojects.blog/2019/12/26/sqlite-and-node-red/
1) 安裝SQLite
https://flows.nodered.org/node/node-red-node-sqlite .
節點管理
2. 產生一個資料庫與資料表Table
下載 DB Browser for SQLite https://sqlitebrowser.org/dl/
產生一個資料庫名稱sqlite_0.db 資料表 temps
並將資料庫copy 到目錄 C:\Users\{使用者alex}\AppData\Roaming\npm 下
(AppData\Roaming須將隱藏目錄開啟)
產生一個資料庫與資料表Table的另一方式
例如 資料庫名稱 sqlte_1.db , 資料表Table為temp_table
直接由function 產生
msg.topic = ' CREATE TABLE "temp_table" ("thetime" Timestamp,"thetemp" integer)';
return msg;
到目錄C:\Users\{使用者alex}\AppData\Roaming\npm 下檢視
CREATE TABLE "temp_table" ("thetime" Timestamp,"thetemp" integer)
此為 function的內容
3. 插入一筆記錄 record到資料庫內
function內容
// Create a Params variable
// with a time and value component
//
msg.params = { $thetime:Date.now(), $thevalue:msg.payload }
return msg;
SQLite 設定
insert into temp_table (thetime,thetemp) values ($thetime,$thevalue);
4. 查詢資料庫的內容
按一下 時間戳 即可 插入一筆記錄到資料庫 可以使用 DB Browser檢視
另一種 查詢方式
function內容
msg.topic ='select * from temp_table';
return msg;
5.自定義折線圖
function內容
msg.payload = [{
"series": ["A"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 6 }
]
],
"labels": ["Data Values"]
}];
return msg;
6.折線圖中的Sqlite數據
SQL select statement function內容
msg.topic = 'select * from temp_table LIMIT 8 OFFSET (SELECT COUNT(*) FROM temp_table)-8';
return msg;
format for the Line Char function內容
//
// Create a data variable
//
var series = ["temp DegC"];
var labels = ["Data Values"];
var data = "[[";
for (var i=0; i < msg.payload.length; i++) {
data += '{ "x":' + msg.payload[i].thetime + ', "y":' + msg.payload[i].thetemp + '}';
if (i < (msg.payload.length - 1)) {
data += ","
} else {
data += "]]"
}
}
var jsondata = JSON.parse(data);
msg.payload = [{"series": series, "data": jsondata, "labels": labels}];
return msg;
[{"id":"e22f5e71.9cbba","type":"inject","z":"4b313a3e.f07594","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":100,"wires":[["cf154dfc.2903e"]]},{"id":"e3f04b63.a39198","type":"sqlite","z":"4b313a3e.f07594","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"SQLite_1","x":520,"y":100,"wires":[[]]},{"id":"cf154dfc.2903e","type":"function","z":"4b313a3e.f07594","name":"產生資料庫TABLE","func":"msg.topic = ' CREATE TABLE \"temp_table\" (\"thetime\" Timestamp,\"thetemp\" integer)';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":100,"wires":[["e3f04b63.a39198"]]},{"id":"5dd7a32f.947adc","type":"inject","z":"4b313a3e.f07594","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"2","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":200,"wires":[["fd4dcd3e.0e2fc"]]},{"id":"fd4dcd3e.0e2fc","type":"random","z":"4b313a3e.f07594","name":"","low":1,"high":10,"inte":"true","property":"payload","x":280,"y":200,"wires":[["a3c6dc15.43244"]]},{"id":"6a4dca3c.b7e704","type":"sqlite","z":"4b313a3e.f07594","mydb":"19f59ce9.3edc23","sqlquery":"prepared","sql":"insert into temp_table (thetime,thetemp) values ($thetime,$thevalue);\n","name":"","x":470,"y":240,"wires":[["df17d29e.105b5"]]},{"id":"df17d29e.105b5","type":"debug","z":"4b313a3e.f07594","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":240,"wires":[]},{"id":"a3c6dc15.43244","type":"function","z":"4b313a3e.f07594","name":"","func":"// Create a Params variable\n// with a time and value component\n//\nmsg.params = { $thetime:Date.now(), $thevalue:msg.payload }\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":450,"y":200,"wires":[["6a4dca3c.b7e704","d83b830d.13f44"]]},{"id":"d83b830d.13f44","type":"debug","z":"4b313a3e.f07594","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":180,"wires":[]},{"id":"84c0e643.ac8ca8","type":"inject","z":"4b313a3e.f07594","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":320,"wires":[["44fed989.edfb68"]]},{"id":"44fed989.edfb68","type":"function","z":"4b313a3e.f07594","name":"","func":"msg.topic ='select * from temp_table';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":290,"y":320,"wires":[["58a6c538.f62bdc"]]},{"id":"58a6c538.f62bdc","type":"sqlite","z":"4b313a3e.f07594","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"SQLite_1","x":460,"y":320,"wires":[["6a74d365.f0666c"]]},{"id":"6a74d365.f0666c","type":"debug","z":"4b313a3e.f07594","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":320,"wires":[]},{"id":"bf03d5f2.bf1508","type":"ui_chart","z":"4b313a3e.f07594","name":"","group":"d5da3e9b.46abf","order":2,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"x":530,"y":400,"wires":[[]]},{"id":"753a0289.37d6fc","type":"inject","z":"4b313a3e.f07594","name":"1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":400,"wires":[["c0d2b7b6.380cb8"]]},{"id":"c0d2b7b6.380cb8","type":"function","z":"4b313a3e.f07594","name":"","func":"msg.payload = [{\n\"series\": [\"A\"],\n\"data\": [\n[{ \"x\": 1577229315152, \"y\": 5 },\n{ \"x\": 1577229487133, \"y\": 4 },\n{ \"x\": 1577232484872, \"y\": 6 }\n]\n],\n\"labels\": [\"Data Values\"]\n}];\n \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":290,"y":400,"wires":[["bf03d5f2.bf1508"]]},{"id":"45c0675c.5a1e38","type":"inject","z":"4b313a3e.f07594","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"20","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":480,"wires":[["9ac8c84d.66b498"]]},{"id":"9ac8c84d.66b498","type":"function","z":"4b313a3e.f07594","name":"SQL select statement","func":"msg.topic = 'select * from temp_table LIMIT 8 OFFSET (SELECT COUNT(*) FROM temp_table)-8';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":480,"wires":[["9969c47e.b3f568"]]},{"id":"6a845511.aa0dbc","type":"function","z":"4b313a3e.f07594","name":"format for the Line Char","func":" // \n // Create a data variable \n // \n var series = [\"temp DegC\"]; \n var labels = [\"Data Values\"]; \n var data = \"[[\"; \n \n for (var i=0; i < msg.payload.length; i++) { \n data += '{ \"x\":' + msg.payload[i].thetime + ', \"y\":' + msg.payload[i].thetemp + '}'; \n if (i < (msg.payload.length - 1)) { \n data += \",\" \n } else { \n data += \"]]\" \n } \n } \n var jsondata = JSON.parse(data); \n msg.payload = [{\"series\": series, \"data\": jsondata, \"labels\": labels}]; \n \n \n return msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","x":510,"y":560,"wires":[["dd96ec63.fdda1","bbde99b8.0ebf88"]]},{"id":"9969c47e.b3f568","type":"sqlite","z":"4b313a3e.f07594","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"SQLite_1","x":300,"y":560,"wires":[["6a845511.aa0dbc"]]},{"id":"dd96ec63.fdda1","type":"ui_chart","z":"4b313a3e.f07594","name":"","group":"d5da3e9b.46abf","order":2,"width":0,"height":0,"label":"SQLite Data","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"x":710,"y":520,"wires":[[]]},{"id":"bbde99b8.0ebf88","type":"debug","z":"4b313a3e.f07594","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":580,"wires":[]},{"id":"19f59ce9.3edc23","type":"sqlitedb","db":"sqlite_1.db","mode":"RWC"},{"id":"d5da3e9b.46abf","type":"ui_group","name":"SQLite_1","tab":"d76149c6.626598","order":2,"disp":true,"width":"6","collapse":false},{"id":"d76149c6.626598","type":"ui_tab","name":"SQLite","icon":"dashboard","disabled":false,"hidden":false}]
6.折線圖中的Sqlite數據如何改為二條
回覆刪除