SQLite + Node-Red (新建資料庫 新增一筆 更正一筆 刪除一筆 刪除整個資料 刪除資料庫 查詢所有的資料)
[{"id":"64628d19d15ca84a","type":"ui_button","z":"b523f70629843071","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":170,"y":180,"wires":[["c0690b4a2df519e0"]]},{"id":"e7885c7997027678","type":"ui_button","z":"b523f70629843071","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":180,"y":220,"wires":[["25f8e6da3b8afdb2"]]},{"id":"acd3ca90398d4526","type":"ui_button","z":"b523f70629843071","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":160,"y":280,"wires":[["8fcac30d10886753"]]},{"id":"d8d8920c3b90eaf8","type":"ui_button","z":"b523f70629843071","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":180,"y":440,"wires":[["a58121fdd908c059"]]},{"id":"04d239b02fda9510","type":"sqlite","z":"b523f70629843071","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":670,"y":220,"wires":[["39afc66c9a7f60d1"]]},{"id":"c0690b4a2df519e0","type":"function","z":"b523f70629843071","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":380,"y":180,"wires":[["04d239b02fda9510"]]},{"id":"39afc66c9a7f60d1","type":"debug","z":"b523f70629843071","name":"debug 83","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":860,"y":240,"wires":[]},{"id":"25f8e6da3b8afdb2","type":"function","z":"b523f70629843071","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":360,"y":220,"wires":[["04d239b02fda9510"]]},{"id":"a58121fdd908c059","type":"function","z":"b523f70629843071","name":"刪除所有資料","func":"//DELETE from dhtreadings\nmsg.topic = \"DELETE from dhtreadings\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":440,"wires":[["bfcd5f5bba2c1e5b"]]},{"id":"bfcd5f5bba2c1e5b","type":"sqlite","z":"b523f70629843071","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":610,"y":420,"wires":[["86db2960eddcd0a5"]]},{"id":"4b361bb9aacfb131","type":"ui_button","z":"b523f70629843071","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":170,"y":480,"wires":[["da5420a58c78abf3"]]},{"id":"da5420a58c78abf3","type":"function","z":"b523f70629843071","name":"刪除資料庫","func":"//DROP TABLE dhtreadings\nmsg.topic = \"DROP TABLE dhtreadings\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":480,"wires":[["bfcd5f5bba2c1e5b"]]},{"id":"2680b1e0277ec348","type":"ui_template","z":"b523f70629843071","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>Time</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":780,"y":280,"wires":[[]]},{"id":"86db2960eddcd0a5","type":"debug","z":"b523f70629843071","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":420,"wires":[]},{"id":"8fcac30d10886753","type":"function","z":"b523f70629843071","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":380,"y":280,"wires":[["eedecd735d45d190"]]},{"id":"eedecd735d45d190","type":"sqlite","z":"b523f70629843071","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":590,"y":280,"wires":[["2680b1e0277ec348","5d4865cce77f8669"]]},{"id":"c5939c395eea4cf9","type":"function","z":"b523f70629843071","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":390,"y":400,"wires":[["bfcd5f5bba2c1e5b","5019f388e3fdee8f"]]},{"id":"4d5a2b9bc355919c","type":"function","z":"b523f70629843071","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":380,"y":340,"wires":[["4b238c9755188432"]]},{"id":"1277634f62803f5f","type":"ui_numeric","z":"b523f70629843071","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":190,"y":400,"wires":[["c5939c395eea4cf9"]]},{"id":"4b238c9755188432","type":"sqlite","z":"b523f70629843071","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"DELETE FROM dhtreadings WHERE id = VALUES ($theid)","name":"My_sensor","x":590,"y":340,"wires":[["5d4865cce77f8669"]]},{"id":"5d4865cce77f8669","type":"debug","z":"b523f70629843071","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":340,"wires":[]},{"id":"8791438dd747443e","type":"ui_button","z":"b523f70629843071","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":180,"y":340,"wires":[["4d5a2b9bc355919c"]]},{"id":"5019f388e3fdee8f","type":"debug","z":"b523f70629843071","name":"debug 84","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":380,"wires":[]},{"id":"778fec5dfbbd81a2","type":"ui_numeric","z":"b523f70629843071","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":190,"y":540,"wires":[["068fb17d314c0427"]]},{"id":"98e42a654f5d689c","type":"sqlite","z":"b523f70629843071","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_sensor","x":570,"y":840,"wires":[["29fda65eb0dadc7d"]]},{"id":"29fda65eb0dadc7d","type":"debug","z":"b523f70629843071","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":840,"wires":[]},{"id":"f6ca8357d5a47b28","type":"ui_text_input","z":"b523f70629843071","name":"","label":"修改-Temperature","tooltip":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":190,"y":580,"wires":[["279069e1ea67f9df"]]},{"id":"c2c03a04e02098ec","type":"ui_text_input","z":"b523f70629843071","name":"","label":"修改-Humidity","tooltip":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":180,"y":620,"wires":[["fa6ce17d1e7c55e2"]]},{"id":"0dcf1e1df60b4b0a","type":"ui_text_input","z":"b523f70629843071","name":"","label":"修改-Date","tooltip":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":160,"y":660,"wires":[["9f9f023565cb0d28"]]},{"id":"9573502e12e59919","type":"ui_text_input","z":"b523f70629843071","name":"","label":"修改-Time","tooltip":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":160,"y":700,"wires":[["bd4dcce9a13dc49c"]]},{"id":"833b1d8d3a578bfd","type":"ui_text_input","z":"b523f70629843071","name":"","label":"修改-Device","tooltip":"","group":"92b4e639.d05558","order":44,"width":"3","height":"1","passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":170,"y":740,"wires":[["841ea1ba6d0ecc32"]]},{"id":"068fb17d314c0427","type":"function","z":"b523f70629843071","name":"STORE 更新參數 id","func":"var update_temp=msg.payload;\nflow.set(\"update_id\", update_temp);","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":540,"wires":[["e538444e051d73f7"]]},{"id":"279069e1ea67f9df","type":"function","z":"b523f70629843071","name":"STORE 更新參數 temperature","func":"var update_temp=msg.payload.trim();\nflow.set(\"update_temperature\", update_temp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":580,"wires":[["41aa50c6e9b67abc"]]},{"id":"fa6ce17d1e7c55e2","type":"function","z":"b523f70629843071","name":"STORE 更新參數 humidity","func":"var update_temp=msg.payload.trim();\nflow.set(\"update_humidity\", update_temp);","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":620,"wires":[["fd822f25e62b8e04"]]},{"id":"9f9f023565cb0d28","type":"function","z":"b523f70629843071","name":"STORE 更新參數 currentdate","func":"var update_temp=msg.payload.trim();\nflow.set(\"update_date\", update_temp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":660,"wires":[["c84f073269ed8749"]]},{"id":"bd4dcce9a13dc49c","type":"function","z":"b523f70629843071","name":"STORE 更新參數 currenttime","func":"var update_temp=msg.payload.trim();\nflow.set(\"update_time\", update_temp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":700,"wires":[["8ca8c1e66f383948"]]},{"id":"841ea1ba6d0ecc32","type":"function","z":"b523f70629843071","name":"STORE 更新參數 device","func":"var update_temp=msg.payload.trim();\nflow.set(\"update_device\", update_temp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":740,"wires":[["788cea56deb5e2eb"]]},{"id":"10b0abd1bca721cd","type":"ui_button","z":"b523f70629843071","name":"","group":"92b4e639.d05558","order":44,"width":"4","height":"1","passthru":false,"label":"更新update","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":170,"y":840,"wires":[["0ee6b514644194fe","061e806ad7b89de7"]]},{"id":"0ee6b514644194fe","type":"function","z":"b523f70629843071","name":"確認 更新update","func":"var id = flow.get(\"update_id\");\nvar temperature = flow.get(\"update_temperature\");\nvar humidity = flow.get(\"update_humidity\");\nvar date = flow.get(\"update_date\");\nvar time = flow.get(\"update_time\");\nvar device = flow.get(\"update_device\");\n\n\nmsg.topic = \"UPDATE dhtreadings SET temperature=($temperature) ,humidity =($humidity) , currentdate = ($date) , currenttime = ($time), device = ($device) WHERE id is ($id) \" ;\nmsg.payload = [temperature,humidity,date,time,device , id ];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":840,"wires":[["98e42a654f5d689c"]]},{"id":"e538444e051d73f7","type":"debug","z":"b523f70629843071","name":"debug 85","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":540,"wires":[]},{"id":"41aa50c6e9b67abc","type":"debug","z":"b523f70629843071","name":"debug 86","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":580,"wires":[]},{"id":"fd822f25e62b8e04","type":"debug","z":"b523f70629843071","name":"debug 87","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":620,"wires":[]},{"id":"c84f073269ed8749","type":"debug","z":"b523f70629843071","name":"debug 88","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":660,"wires":[]},{"id":"8ca8c1e66f383948","type":"debug","z":"b523f70629843071","name":"debug 89","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":700,"wires":[]},{"id":"788cea56deb5e2eb","type":"debug","z":"b523f70629843071","name":"debug 90","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":740,"wires":[]},{"id":"ec131a68158df252","type":"function","z":"b523f70629843071","name":"function 25","func":"var temp=\"\";\nmsg.payload=temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":150,"y":780,"wires":[["4febb92411de750b"]]},{"id":"4febb92411de750b","type":"link out","z":"b523f70629843071","name":"link out 5","mode":"link","links":["15943826864e6952"],"x":435,"y":780,"wires":[]},{"id":"15943826864e6952","type":"link in","z":"b523f70629843071","name":"link in 7","links":["4febb92411de750b"],"x":55,"y":600,"wires":[["f6ca8357d5a47b28","c2c03a04e02098ec","0dcf1e1df60b4b0a","9573502e12e59919","833b1d8d3a578bfd"]]},{"id":"061e806ad7b89de7","type":"delay","z":"b523f70629843071","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":300,"y":800,"wires":[["ec131a68158df252"]]},{"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}]
沒有留言:
張貼留言