alex9ufo 聰明人求知心切: Using SQLite with Node-RED on Windows (3)

2023年6月25日星期日

Using SQLite with Node-RED on Windows (3)

Using SQLite with Node-RED on Windows (3)

[{"id":"71880c62adda6431","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":21,"width":"3","height":"1","passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":150,"y":80,"wires":[["c6e5fbb3eda3586b"]]},{"id":"cf5b2d9905f6b383","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":43,"width":"3","height":"1","passthru":false,"label":"新增一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":160,"y":120,"wires":[["0451109ef9349e7f"]]},{"id":"092523eb0d1f32d2","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":45,"width":"3","height":"1","passthru":false,"label":"檢視資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":140,"y":220,"wires":[["6b85cb70330bbdf9"]]},{"id":"7d2e6c10da47e995","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":47,"width":"3","height":"1","passthru":false,"label":"刪除所有資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":160,"y":420,"wires":[["3b7049510b362b0d"]]},{"id":"fd30947ba235f3ec","type":"sqlite","z":"3d0a5d2e336b4432","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":650,"y":80,"wires":[["6726fa3cbd7f53f0"]]},{"id":"c6e5fbb3eda3586b","type":"function","z":"3d0a5d2e336b4432","name":"CREATE　DATABASE","func":"//CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT,\n//temperature NUMERIC, \n//humidity NUMERIC, \n//currentdate DATE, \n//currenttime TIME,\n//device TEXT)\nmsg.topic = \"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":80,"wires":[["fd30947ba235f3ec"]]},{"id":"6726fa3cbd7f53f0","type":"debug","z":"3d0a5d2e336b4432","name":"debug 81","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":800,"y":80,"wires":[]},{"id":"0451109ef9349e7f","type":"function","z":"3d0a5d2e336b4432","name":"INSERT","func":"var 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 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//return msg;\n\nvar myTemp = Math.floor(Math.random()*s);\nvar max1=100;\nvar min1= 20;\nvar myHumi = Math.round(Math.random() * (max1 - min1) + min1);\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":340,"y":120,"wires":[["fd30947ba235f3ec"]]},{"id":"3b7049510b362b0d","type":"function","z":"3d0a5d2e336b4432","name":"刪除所有資料","func":"//DELETE from dhtreadings\nmsg.topic = \"DELETE from dhtreadings\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":420,"wires":[["b09d4e1fae51223f"]]},{"id":"b09d4e1fae51223f","type":"sqlite","z":"3d0a5d2e336b4432","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":590,"y":400,"wires":[["c6b9f83df88fd703"]]},{"id":"fdf7eb5b084ed346","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":47,"width":"3","height":"1","passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":150,"y":460,"wires":[["c6ee1005b671ddfa"]]},{"id":"c6ee1005b671ddfa","type":"function","z":"3d0a5d2e336b4432","name":"刪除資料庫","func":"//DROP TABLE dhtreadings\nmsg.topic = \"DROP TABLE dhtreadings\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":460,"wires":[["b09d4e1fae51223f"]]},{"id":"c67ed6035a88c52c","type":"ui_template","z":"3d0a5d2e336b4432","group":"92b4e639.d05558","name":"","order":0,"width":"12","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>id</th> \n <th>database_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].id}}</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":760,"y":220,"wires":[[]]},{"id":"c6b9f83df88fd703","type":"debug","z":"3d0a5d2e336b4432","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":770,"y":400,"wires":[]},{"id":"6b85cb70330bbdf9","type":"function","z":"3d0a5d2e336b4432","name":"檢視資料","func":"//SELECT * FROM dhtreadings ORDER BY id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM dhtreadings ORDER BY id DESC LIMIT 50\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":220,"wires":[["095efd36c9ee91b7"]]},{"id":"095efd36c9ee91b7","type":"sqlite","z":"3d0a5d2e336b4432","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":570,"y":220,"wires":[["c67ed6035a88c52c","7906a7f8668a5649"]]},{"id":"f40b210b62ab0d0e","type":"function","z":"3d0a5d2e336b4432","name":"SELECT ALL","func":"var del_idtemp=msg.payload;\nflow.set(\"idtemp\", del_idtemp);\n\nmsg.topic = \"SELECT * FROM dhtreadings \";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":380,"wires":[["b09d4e1fae51223f","ad7234c6f874962c"]]},{"id":"c3df9e9aeb892b78","type":"function","z":"3d0a5d2e336b4432","name":"確認刪除","func":"var del_id = flow.get(\"idtemp\");\n\n\nmsg.topic = \"DELETE FROM dhtreadings WHERE id= ($del_id) \" ;\nmsg.payload = [del_id]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":320,"wires":[["111a3b6f5cd2a8dd"]]},{"id":"10146e650715a95b","type":"ui_numeric","z":"3d0a5d2e336b4432","name":"","label":"刪除的database_id","tooltip":"","group":"92b4e639.d05558","order":30,"width":"4","height":"1","wrap":false,"passthru":true,"topic":"topic","topicType":"msg","format":"{{value}}","min":"1","max":"100","step":1,"className":"","x":170,"y":380,"wires":[["f40b210b62ab0d0e"]]},{"id":"111a3b6f5cd2a8dd","type":"sqlite","z":"3d0a5d2e336b4432","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"DELETE FROM dhtreadings WHERE id = VALUES ($theid)","name":"My_sensor","x":570,"y":320,"wires":[["7906a7f8668a5649"]]},{"id":"7906a7f8668a5649","type":"debug","z":"3d0a5d2e336b4432","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":770,"y":320,"wires":[]},{"id":"c5b57163e624ce27","type":"ui_button","z":"3d0a5d2e336b4432","name":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":160,"y":320,"wires":[["c3df9e9aeb892b78"]]},{"id":"ad7234c6f874962c","type":"debug","z":"3d0a5d2e336b4432","name":"debug 82","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":540,"y":360,"wires":[]},{"id":"92b4e639.d05558","type":"ui_group","name":"溫度","tab":"a769a2ac.25aff","order":1,"disp":true,"width":"15","collapse":false,"className":""},{"id":"dd7f8134e015a1cb","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\my_sensors.db","mode":"RWC"},{"id":"a769a2ac.25aff","type":"ui_tab","name":"Python","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

沒有留言:

張貼留言

訂閱：張貼留言 (Atom)