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 來檢視資料庫的內容
[{"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
沒有留言:
張貼留言