Node-RED SQLite產生資料庫 新增一筆 資料 讀取資料庫

The tutorial will cover the basics of:

• Setting up the dblite (SQLite) node and creating a DB table
• Generating random data and storing in the SQLite DB
• Reading from the SQLite DB
• Graphing the data from the DB using the standard Node-RED UI nodes

[{"id":"4d641f65.581ad","type":"comment","z":"58734155.f17b4","name":"","info":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)\n\nINSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")\n\nSELECT * FROM dhtreadings\n","x":100,"y":460,"wires":[]},{"id":"30043f2c.ba265","type":"inject","z":"58734155.f17b4","name":"CREATE","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":100,"y":520,"wires":[["a8a68516.7d69b8"]]},{"id":"13b1a3c6.fa9a1c","type":"inject","z":"58734155.f17b4","name":"INSERT","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":100,"y":580,"wires":[["a8a68516.7d69b8"]]},{"id":"43ebb8db.077628","type":"inject","z":"58734155.f17b4","name":"View Records","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":660,"wires":[["b0b9faea.c4bb38"]]},{"id":"86689a7b.795978","type":"debug","z":"58734155.f17b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":490,"y":540,"wires":[]},{"id":"130e63c1.a5a27c","type":"ui_template","z":"58734155.f17b4","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,"templateScope":"local","x":480,"y":640,"wires":[[]]},{"id":"b0b9faea.c4bb38","type":"sqlite","z":"58734155.f17b4","mydb":"af927aba.0dc108","sqlquery":"msg.topic","sql":"","name":"SQLite","x":310,"y":640,"wires":[["130e63c1.a5a27c"]]},{"id":"a8a68516.7d69b8","type":"sqlite","z":"58734155.f17b4","mydb":"af927aba.0dc108","sqlquery":"msg.topic","sql":"","name":"SQLite","x":310,"y":540,"wires":[["86689a7b.795978"]]},{"id":"b165cbf1.03d608","type":"ui_group","z":"","name":"Data","tab":"6946f4ef.f94eec","order":3,"disp":true,"width":"6","collapse":false},{"id":"af927aba.0dc108","type":"sqlitedb","z":"","db":"db_003.db","mode":"RWC"},{"id":"6946f4ef.f94eec","type":"ui_tab","z":"","name":"Sqlit_4","icon":"photo_camera","disabled":false,"hidden":false}]

NODE-RED + LINE NOTIFY MASSAGE

NODE-RED + LINE NOTIFY MASSAGE

http://noobembedded.blogspot.com/2018/07/node-red-line-api.html

• create inject  by  create massage  string for send to Line Notify 

• create function Set Line API

• For format Line massage Header
  'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer OKW0QlpccUbnkzyVKMqCwnPUELrxfVbPMs7LfGQjkSv'
  
  By OKW0QlpccUbnkzyVKMqCwnPUELrxfVbPMs7LfGQjkSv is API KEY
  can be generate by  https://notify-bot.line.me/en/
  
  And create massage to  msg.payload
  msg.payload = {"message":msg.payload};

•  crate http request to flow

• by setting POST Method , URL to https://notify-api.line.me/api/notify

•  create debug for view massage response

• if  function complete  Line Notify will show massage  and debug
   status":200,"message":"ok

[{"id":"53b696d9.a48d08","type":"inject","z":"58734155.f17b4","name":"","topic":"","payload":"Node-red send Line Massage","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":220,"wires":[["6bd49f9.c916d6"]]},{"id":"6bd49f9.c916d6","type":"function","z":"58734155.f17b4","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer cEI4hx24xyopKGAArgZcKJNHE1V7KeeIi4Lzny3dDNO'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;","outputs":1,"noerr":0,"x":250,"y":220,"wires":[["75e93e42.4fae8"]]},{"id":"75e93e42.4fae8","type":"http request","z":"58734155.f17b4","name":"","method":"POST","ret":"txt","url":"https://notify-api.line.me/api/notify","tls":"","x":450,"y":220,"wires":[["a3fd0ba1.ae8e48"]]},{"id":"a3fd0ba1.ae8e48","type":"debug","z":"58734155.f17b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":650,"y":220,"wires":[]}]

• 
 
 
• Java
 
• Python
 
• Ruby
 
• MySQL
 
 

位置：首頁 > 數據庫 > SQLite基礎教程 > SQLite 創建數據庫

SQLite基礎教程SQLite 是什麼？SQLite 安裝SQLite 命令SQLite 語法SQLite 數據類型SQLite 創建數據庫SQLite 附加數據庫SQLite 分離數據庫SQLite 創建表SQLite 刪除表SQLite 插入查詢SQLite SELECT查詢SQLite 運算符SQLite 表達式SQLite WHERE子句SQLite AND/OR操作符SQLite UPDATE 查詢SQLite LIKE 子句SQLite GLOB子句SQLite LIMIT 子句SQLite ORDER BY 子句SQLite GROUP BYSQLite HAVING 子句SQLite DISTINCT 關鍵字SQLite 約束SQLite JOINS連接

高級基礎教程SQLite PRAGMASQLite UNIONS 子句SQLite ALIAS/彆名SQLite TRIGGERS/觸發器SQLite 索引SQLite INDEXED BYSQLite ALTER TABLE命令SQLite truncate table命令SQLite VIEW/視圖SQLite TRANSACTIONS/事務SQLite 子查詢SQLite AUTO INCREMENT/自動遞增SQLite INJECTION/注入SQLite EXPLAIN/解釋SQLite VACUUM/真空SQLite Date&Time/日期時間SQLite 常用函數SQLite C/C++SQLite JavaSQLite PHPSQLite PerlSQLite Python

SQLite 創建數據庫

 上一篇下一篇  

被用來創建新的SQLite數據庫 sqlite3 的命令。不需要有任何特殊的權限，創建一個數據庫。

語法:

sqlite3 命令的基本語法如下：

$sqlite3 DatabaseName.db

一如既往，數據庫名稱應該是唯一的在RDBMS內。

例子：

如果想創建新的數據庫<testDB.db>，然後SQLITE3語句如下：

$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

上麵的命令將在當前目錄下創建一個文件 testDB.db。此文件將被用作數據庫通過SQLite的引擎。如果已經注意到sqlite3命令將創建數據庫的同時，提供一個sqlite>提示符後成功創建數據庫文件。

一旦創建數據庫時，可以檢查它在列表中使用SQLite的數據庫.databases命令如下：

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db

使用SQLite命令quit 退出sqlite提示如下：

sqlite>.quit
$

.dump 命令

可以使用 .dump 命令導出完整的數據庫在一個文本文件中使用SQLite命令，在命令提示符如下：

$sqlite3 testDB.db .dump > testDB.sql

A上麵的命令將轉換整個內容testDB.db數據庫到SQLite的語句並將其轉儲到ASCII文本文件testDB.sql。可以做恢複從生成testDB.sql 簡單的方法如下：

$sqlite3 testDB.db < testDB.sql

此時的數據庫是空的，所以可以嘗試上述兩個程序，一旦有幾張表和數據庫中的數據。現在，讓我們繼續下一章。

極客書 © 2015-2015 . 京ICP備15058027號-1 Email:        站长统计

Node-Red flow範例 << 模擬隨機值實現Script。>>

Analog Random Values ​​Implementation Script. |  Node-Red Flow Example

//===setting=== 
var hpoint = 100;
var lpoint = 0;
var cent = 10; //centralize number
//===============

var mpoint = 0;
var output = context.get('output')||0;
var addpoint = context.get('addpoint')||0;

//count
var count = context.get('count')||0;
count += 1;
context.set('count',count);

//rcount
var rcount = context.get('rcount')||0;
if(count >= rcount){
    for(i=0;i<cent;i++){        //centralize
        mpoint = mpoint + Math.floor(Math.random() * hpoint);
    }
    rcount = parseInt(Math.floor(Math.random() * 100)/5);
    addpoint = ((mpoint/cent)-output)/rcount;
    context.set('addpoint',addpoint);
    context.set('rcount',rcount);
    context.set('count',0);
}else{
    output = parseFloat((output + addpoint).toFixed(1));
    context.set('output',output);
}
msg.payload = output;
return msg;

Flow JSON Sourcecode


[{"id":"59e7f383.6f34ec","type":"comment","z":"35516ee7.622d92","name":"Random","info":"","x":220,"y":100,"wires":[]},{"id":"a38db82b.383e78","type":"inject","z":"35516ee7.622d92","name":"","topic":"","payload":"","payloadType":"date","repeat":"2","crontab":"","once":false,"onceDelay":0.1,"x":240,"y":160,"wires":[["f49a2ffd.11127"]]},{"id":"a99544a8.554ed8","type":"debug","z":"35516ee7.622d92","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":220,"wires":[]},{"id":"f49a2ffd.11127","type":"function","z":"35516ee7.622d92","name":"random","func":"//===setting=== \nvar hpoint = 100;\nvar lpoint = 0;\nvar cent = 10; //centralize number\n//===============\n\nvar mpoint = 0;\nvar output = context.get('output')||0;\nvar addpoint = context.get('addpoint')||0;\n\n//count\nvar count = context.get('count')||0;\ncount += 1;\ncontext.set('count',count);\n\n//rcount\nvar rcount = context.get('rcount')||0;\nif(count >= rcount){\n    for(i=0;i<cent;i++){        //centralize\n        mpoint = mpoint + Math.floor(Math.random() * hpoint);\n    }\n    rcount = parseInt(Math.floor(Math.random() * 100)/5);\n    addpoint = ((mpoint/cent)-output)/rcount;\n    context.set('addpoint',addpoint);\n    context.set('rcount',rcount);\n    context.set('count',0);\n}else{\n    output = parseFloat((output + addpoint).toFixed(1));\n    context.set('output',output);\n}\nmsg.payload = output;\nreturn msg;","outputs":1,"noerr":0,"x":420,"y":160,"wires":[["a99544a8.554ed8","7f105c44.bf22e4"]]},{"id":"7f105c44.bf22e4","type":"ui_chart","z":"35516ee7.622d92","name":"","group":"7784c75b.8caac8","order":0,"width":"6","height":"6","label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"200","removeOlder":1,"removeOlderPoints":"50","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":610,"y":160,"wires":[[],[]]},{"id":"7784c75b.8caac8","type":"ui_group","z":"","name":"Default","tab":"ef07f6d1.ceaea8","disp":true,"width":"6","collapse":false},{"id":"ef07f6d1.ceaea8","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

使用NodeRed貨幣轉換器API

使用NodeRed貨幣轉換器API

提供的API

https://rate.bot.com.tw/xrt?Lang=zh-TW  台灣銀行

https://www.esunbank.com.tw/bank/personal/deposit/rate/forex/foreign-exchange-rates    玉山銀行

檢查 網頁原始碼

<td data-table="本行即期賣出" class="rate-content-sight text-right print_hide" data-hide="phone">0.2817</td>


Select 的地方填入 .rate-content-cash.text-right.print_hide ，注意這邊的 Select 是 CSS 選擇器，就跟我們在寫 CSS 是一樣，例如我們要抓取的 class 在 HTML 呈現是 class=”rate-content-cash text-right print_hide”，選擇器就變成 .rate-content-cash.text-right.print_hide。




https://www.esunbank.com.tw/bank/personal/deposit/rate/forex/foreign-exchange-rates

<td data-name="現金賣出匯率" class="even lastTd">0.2837</td>

Select 的地方填入 .evev.lattTd

[{"id":"6ea74d13.929d74","type":"debug","z":"acf5c35f.665d7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":790,"y":320,"wires":[]},{"id":"36bda223.9e1a7e","type":"comment","z":"acf5c35f.665d7","name":"Currency converter","info":"","x":130,"y":100,"wires":[]},{"id":"82a341fb.59f69","type":"inject","z":"acf5c35f.665d7","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":180,"wires":[["dccf7518.3a5178"]]},{"id":"d2678ccd.2002e","type":"debug","z":"acf5c35f.665d7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":180,"wires":[]},{"id":"dccf7518.3a5178","type":"http request","z":"acf5c35f.665d7","name":"Exchange","method":"GET","ret":"txt","url":"https://rate.bot.com.tw/xrt?Lang=zh-TW","tls":"","x":280,"y":180,"wires":[["7e9d89.07d0e278"]]},{"id":"fbbc192b.b0eda8","type":"comment","z":"acf5c35f.665d7","name":"https://rate.bot.com.tw/xrt?Lang=zh-TW","info":"","x":200,"y":140,"wires":[]},{"id":"497090d8.c1b0f","type":"http request","z":"acf5c35f.665d7","name":"Exchange","method":"GET","ret":"obj","url":"https://www.esunbank.com.tw/bank/personal/deposit/rate/forex/foreign-exchange-rates","tls":"","x":280,"y":320,"wires":[["82c39bc.5a6ef68"]]},{"id":"4bd25fa9.6468e","type":"inject","z":"acf5c35f.665d7","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":320,"wires":[["497090d8.c1b0f"]]},{"id":"dd51d6d5.9c7f48","type":"comment","z":"acf5c35f.665d7","name":"https://www.esunbank.com.tw/bank/personal/deposit/rate/forex/foreign-exchange-rates","info":"","x":350,"y":280,"wires":[]},{"id":"7e9d89.07d0e278","type":"html","z":"acf5c35f.665d7","name":"過濾 filter","property":"payload","outproperty":"payload","tag":".rate-content-cash.text-right.print_hide","ret":"text","as":"single","x":440,"y":180,"wires":[["d2678ccd.2002e","f52b4326.34eac"]]},{"id":"f52b4326.34eac","type":"function","z":"acf5c35f.665d7","name":"擷取 Capture","func":"var jp = msg.payload[15];\nvar date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n    h = '0'+h;\n}\nif(m<10){\n    m = '0' + m;\n}\nif(s<10){\n    s = '0' + s;\n}\nmsg.payload = '(' + h + ':' + m + ':' + s + ')\\n'+\n'日幣匯率：' + jp ;\nreturn msg;\n","outputs":1,"noerr":0,"x":600,"y":220,"wires":[["d2678ccd.2002e"]]},{"id":"82c39bc.5a6ef68","type":"html","z":"acf5c35f.665d7","name":"過濾 filter","property":"payload","outproperty":"payload","tag":".even.lastTd","ret":"text","as":"single","x":440,"y":320,"wires":[["6ea74d13.929d74","fe715575.76af58"]]},{"id":"fe715575.76af58","type":"function","z":"acf5c35f.665d7","name":"擷取 Capture","func":"var jp = msg.payload[3];\nvar date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n    h = '0'+h;\n}\nif(m<10){\n    m = '0' + m;\n}\nif(s<10){\n    s = '0' + s;\n}\nmsg.payload = '(' + h + ':' + m + ':' + s + ')\\n'+\n'日幣匯率：' + jp ;\nreturn msg;\n","outputs":1,"noerr":0,"x":620,"y":360,"wires":[["6ea74d13.929d74"]]}]

使用MQTT節點

使用MQTT節點

[{"id":"a6793e55.92b1e","type":"comment","z":"6357831a.d111ac","name":"MQTT Broker","info":"","x":130,"y":60,"wires":[]},{"id":"20a133ae.c53b4c","type":"comment","z":"6357831a.d111ac","name":"MQTT IN","info":"","x":120,"y":120,"wires":[]},{"id":"81bff40f.4db528","type":"comment","z":"6357831a.d111ac","name":"MQTT OUT","info":"","x":130,"y":300,"wires":[]},{"id":"6dee416.21f19c","type":"debug","z":"6357831a.d111ac","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":60,"wires":[]},{"id":"ba293462.4bae38","type":"mqtt in","z":"6357831a.d111ac","name":"","topic":"alex9ufo/sensor/sensor1","qos":"0","broker":"40bf4d5e.0395f4","x":370,"y":120,"wires":[["58b863c9.47236c"]]},{"id":"58b863c9.47236c","type":"debug","z":"6357831a.d111ac","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":120,"wires":[]},{"id":"8c811d5.a9a62e","type":"inject","z":"6357831a.d111ac","name":"","topic":"","payload":"1","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":330,"y":300,"wires":[["fd430b5a.695fe8"]]},{"id":"fd430b5a.695fe8","type":"mqtt out","z":"6357831a.d111ac","name":"","topic":"alex9ufo/sensor/sensor1","qos":"0","retain":"","broker":"40bf4d5e.0395f4","x":650,"y":300,"wires":[]},{"id":"d2d13a4e.e1e338","type":"inject","z":"6357831a.d111ac","name":"","topic":"","payload":"2","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":330,"y":360,"wires":[["720c30bb.ca207"]]},{"id":"720c30bb.ca207","type":"mqtt out","z":"6357831a.d111ac","name":"","topic":"alex9ufo/sensor/sensor2","qos":"0","retain":"","broker":"40bf4d5e.0395f4","x":650,"y":360,"wires":[]},{"id":"2803f112.95532e","type":"mqtt in","z":"6357831a.d111ac","name":"","topic":"alex9ufo/sensor/#","qos":"0","broker":"40bf4d5e.0395f4","x":350,"y":180,"wires":[["147c069d.d35869"]]},{"id":"147c069d.d35869","type":"debug","z":"6357831a.d111ac","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":180,"wires":[]},{"id":"f9183f36.d4eed","type":"mqtt in","z":"6357831a.d111ac","name":"","topic":"alex9ufo/#","qos":"0","broker":"40bf4d5e.0395f4","x":320,"y":60,"wires":[["6dee416.21f19c"]]},{"id":"40bf4d5e.0395f4","type":"mqtt-broker","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":15,"cleansession":true,"birthQos":"0","willQos":"0"}]

Node-red 節點Node 安裝方式

Node-red 節點Node 安裝方式 (增加節點)

Node-RED本身只包含了一些核心的基本節點，但還有大量來自於Node-RED項目和廣大社區的其他節點可以使用。

你可以在Node-RED 代碼庫或npm倉庫中尋找所需要的節點。

使用編輯器

從0.15版開始，你可以直接通過編輯器安裝節點。具體做法是，在右上角的功能表中選擇管理節點面板，然後在出現的面板中選擇安裝標籤，這樣就可以搜索安裝新的節點，並啟用或禁用已有節點了。

安裝由npm包裝的節點

既可以將npm包裝的節點安裝到保存使用者資料的本地目錄中(預設為$HOME/.node-red)：

cd $HOME/.node-red

npm install <npm-package-name>

也可以安裝到和Node-RED在一起的全域目錄中:

sudo npm install -g <npm-package-name>

但是這樣你需要重啟Node-RED，以便它能夠獲取到新的節點。

安裝單獨的節點檔

將.js和.html檔安裝到保存使用者資料目錄中的nodes子目錄，也是安裝節點的一個可行方式。如果這些節點中存在一些npm依賴項，那麼也必須將其安裝到使用者資料目錄中。但建議只是在開發階段使用這種方式。增加節點

Node-RED本身只包含了一些核心的基本節點，但還有大量來自於Node-RED項目和廣大社區的其他節點可以使用。

你可以在Node-RED代碼庫或npm倉庫中尋找所需要的節點。

使用編輯器

從0.15版開始，你可以直接通過編輯器安裝節點。具體做法是，在右上角的功能表中選擇管理節點面板，然後在出現的面板中選擇安裝標籤，這樣就可以搜索安裝新的節點，並啟用或禁用已有節點了。

安裝由npm包裝的節點

既可以將npm包裝的節點安裝到保存使用者資料的本地目錄中(預設為$HOME/.node-red)：

cd $HOME/.node-red

npm install <npm-package-name>

也可以安裝到和Node-RED在一起的全域目錄中:

sudo npm install -g <npm-package-name>

但是這樣你需要重啟Node-RED，以便它能夠獲取到新的節點。

安裝單獨的節點檔

將.js和.html檔安裝到保存使用者資料目錄中的nodes子目錄，也是安裝節點的一個可行方式。如果這些節點中存在一些npm依賴項，那麼也必須將其安裝到使用者資料目錄中。但建議只是在開發階段使用這種方式。

以安裝simpletime為例

1)  manage palette

2)  利用npm 來設定 

cd $HOME/.node-red
npm install <npm-package-name>

cmd進入命令模式

cd  .node-red

npm i --unsafe-perm node-red-contrib-simpletime

安裝後

cd ~/.node-red
npm rebuild