SQLite with Node-RED 

參考來源
https://randomnerdtutorials.com/sqlite-with-node-red-and-raspberry-pi/

database 的位置 在c:/Users/使用者/sqlite_2020_0720.db

利用 DB Browser for SQLite 來檢視資料庫的內容

https://sqlitebrowser.org/dl/

[{"id":"7b121f95.b0616","type":"inject","z":"ea7c92f3.931c3","name":"創建一個新表(只能執行一次)","topic":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":80,"wires":[["7b2d78fb.d09568"]]},{"id":"a57530ed.20168","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":120,"wires":[["7b2d78fb.d09568"]]},{"id":"8d9142a8.5e744","type":"inject","z":"ea7c92f3.931c3","name":"選擇一筆資料","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":160,"wires":[["7b2d78fb.d09568"]]},{"id":"ccba0fc8.f0199","type":"inject","z":"ea7c92f3.931c3","name":"刪除所有的資料","topic":"DELETE from dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["7b2d78fb.d09568"]]},{"id":"cd3a09d5.cb0708","type":"inject","z":"ea7c92f3.931c3","name":"SQLite 刪除表","topic":"DROP TABLE dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":240,"wires":[["7b2d78fb.d09568"]]},{"id":"b6ea767d.af7298","type":"debug","z":"ea7c92f3.931c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":580,"y":180,"wires":[]},{"id":"7b2d78fb.d09568","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":410,"y":180,"wires":[["b6ea767d.af7298"]]},{"id":"7ef18041.5341b","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料(2)","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(26.4, 68, date('now'), time('now'), \"manual2\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":300,"wires":[["7b2d78fb.d09568"]]},{"id":"e77b6c54.4f02","type":"sqlitedb","z":"","db":"sqlite_2020_0720.db","mode":"RWC"}]

========================新增檢視資料庫 View Data=====================

[{"id":"ea7c92f3.931c3","type":"tab","label":"SQlite","disabled":false,"info":""},{"id":"7b121f95.b0616","type":"inject","z":"ea7c92f3.931c3","name":"創建一個新表(只能執行一次)","topic":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":80,"wires":[["7b2d78fb.d09568"]]},{"id":"a57530ed.20168","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":120,"wires":[["7b2d78fb.d09568"]]},{"id":"ccba0fc8.f0199","type":"inject","z":"ea7c92f3.931c3","name":"刪除所有的資料","topic":"DELETE from dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":160,"wires":[["7b2d78fb.d09568"]]},{"id":"cd3a09d5.cb0708","type":"inject","z":"ea7c92f3.931c3","name":"SQLite 刪除表","topic":"DROP TABLE dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":200,"wires":[["7b2d78fb.d09568"]]},{"id":"b6ea767d.af7298","type":"debug","z":"ea7c92f3.931c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":580,"y":180,"wires":[]},{"id":"7b2d78fb.d09568","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":410,"y":180,"wires":[["b6ea767d.af7298"]]},{"id":"7ef18041.5341b","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料(2)","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(26.4, 68, date('now'), time('now'), \"manual2\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":240,"wires":[["7b2d78fb.d09568"]]},{"id":"3f661adb.08c156","type":"ui_template","z":"ea7c92f3.931c3","group":"b165cbf1.03d608","name":"UI Table","order":2,"width":"6","height":"3","format":"<table style=\"width:100%\">\n  <tr>\n    <th>Time</th> \n    <th>Temp</th> \n    <th>Hum</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:120\">\n    <td>{{msg.payload[$index].currenttime}}</td>\n    <td>{{msg.payload[$index].temperature}}</td> \n    <td>{{msg.payload[$index].humidity}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":480,"y":300,"wires":[[]]},{"id":"6e90c476.4aa5fc","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":310,"y":300,"wires":[["3f661adb.08c156"]]},{"id":"14a97083.ef542f","type":"inject","z":"ea7c92f3.931c3","name":"選擇一筆資料","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":300,"wires":[["6e90c476.4aa5fc"]]},{"id":"e77b6c54.4f02","type":"sqlitedb","z":"","db":"sqlite_2020_0720.db","mode":"RWC"},{"id":"b165cbf1.03d608","type":"ui_group","z":"","name":"Data","tab":"7a15eb23.c05f34","order":3,"disp":true,"width":"6","collapse":false},{"id":"7a15eb23.c05f34","type":"ui_tab","z":"","name":"SQlite_2020_0720","icon":"dashboard","disabled":false,"hidden":false}]

<table style="width:100%">
  <tr>
    <th>Time</th>
    <th>Temp</th>
    <th>Hum</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:120">
    <td>{{msg.payload[$index].currenttime}}</td>
    <td>{{msg.payload[$index].temperature}}</td>
    <td>{{msg.payload[$index].humidity}}</td>
  </tr>
</table>

CREATE inject node as follows

CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)

Configure your INSERT inject node

INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), "manual")

Configure your SELECT inject node with

SELECT * FROM dhtreadings

Configure your DROP inject node

DROP TABLE dhtreadings

Configure your DELETE inject node as follows

DELETE from dhtreadings