SQLite 新增 (Create)、查詢 (Read)、更新 (Update)、刪除 (Delete) 資料

SQLite 新增 (Create)、查詢 (Read)、更新 (Update)、刪除 (Delete) 資料

SQLite 是一種輕量級、檔案型（單一檔案）的關聯式資料庫，非常適合嵌入式系統、行動應用程式（Android/iOS）和桌面軟體。其基本功能包含建立資料庫、建立資料表、新增 (Create)、查詢 (Read)、更新 (Update)、刪除 (Delete) 資料，簡稱 CRUD。 

以下是使用 Python sqlite3 模組的具體基本功能範例：

1. 建立/連接資料庫

SQLite 不需要啟動伺服器，直接開啟檔案即可。 

python

import sqlite3

# 連接資料庫，若檔案不存在會自動建立
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

2. 建立資料表 (Create Table)

python

# 建立一個使用者資料表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit() # 提交事務

3. 新增資料 (Create)

python

# 插入單筆資料
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")

# 插入多筆資料
users = [('Bob', 30), ('Charlie', 35)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)

conn.commit()

4. 查詢資料 (Read)

python

# 查詢所有資料
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 查詢特定條件
cursor.execute("SELECT * FROM users WHERE age > ?", (30,))
print(cursor.fetchall())

5. 更新資料 (Update)

python

# 更新 Bob 的年齡
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, 'Bob'))
conn.commit()

6. 刪除資料 (Delete)

python

# 刪除 Alice
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))
conn.commit()

7. 關閉連接

python

conn.close()

常用工具與資源

• 瀏覽器擴充功能： SQLite Browser 可用於查看和管理 SQLite 資料庫檔案。
• 特點： 遵守 ACID 原則，檔案單一化，便於備份。

在 Node-RED 中使用 SQLite，通常會透過 node-red-node-sqlite 這個節點來達成。

以下是如何在 Node-RED 中實現 CRUD 操作的完整指南。

---

1. 環境準備

首先，你需要在 Node-RED 的「選單」>「管理調色盤」中搜尋並安裝： node-red-node-sqlite

安裝後，你會看到一個 sqlite 節點。你需要點擊該節點並設定一個 Database 檔案路徑（例如：/data/mydb.db）。

---

2. 建立資料表 (Setup)

在執行 CRUD 之前，我們需要一張表。通常會使用一個 Inject 節點觸發一個 Function 節點。

SQL 指令：

SQL

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER
)

---

3. CRUD 操作範例

你可以將 msg.topic 設為 SQL 語句，然後傳遞給 SQLite 節點。

C - 新增 (Create)

將資料寫入資料庫。

• Function 節點內容：

  JavaScript

  msg.topic = "INSERT INTO users (name, age) VALUES ('Alice', 25)";
  return msg;
  

R - 查詢 (Read)

取出資料庫中的資料。

• Function 節點內容：

  JavaScript

  msg.topic = "SELECT * FROM users WHERE age > 20";
  return msg;
  

  結果會以 Array (陣列) 形式出現在 msg.payload。

U - 更新 (Update)

修改現有資料。

• Function 節點內容：

  JavaScript

  msg.topic = "UPDATE users SET age = 26 WHERE name = 'Alice'";
  return msg;
  

D - 刪除 (Delete)

移除資料。

• Function 節點內容：

  JavaScript

  msg.topic = "DELETE FROM users WHERE id = 1";
  return msg;
  

---

4. 進階技巧：預防 SQL 注入 (Prepared Statements)

直接在字串中拼接變數是很危險的。SQLite 節點支援使用陣列來帶入參數，這樣更安全且專業。

安全做法範例：

1. Function 節點：

   JavaScript

   msg.topic = "INSERT INTO users (name, age) VALUES (?, ?)";
   msg.payload = ["Bob", 30]; // 依序對應問號
   return msg;
   

---

總結流程圖

1. Inject 節點（觸發動作）

2. Function 節點（撰寫 msg.topic 的 SQL 語句）

3. SQLite 節點（執行資料庫讀寫）

4. Debug 節點（查看執行結果）

[{"id":"60dcc3509d8bcea7","type":"inject","z":"2ec92e8e481b31c7","name":"建立資料表 (Setup)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":120,"wires":[["a8293005df7095ac"]]},{"id":"a8293005df7095ac","type":"function","z":"2ec92e8e481b31c7","name":"SETUP Table function  ","func":"msg.topic=\"CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":120,"wires":[["d517a660eaef169d"]]},{"id":"d517a660eaef169d","type":"sqlite","z":"2ec92e8e481b31c7","mydb":"1cb2040a1b54cb0d","sqlquery":"msg.topic","sql":"","name":"CRUD","x":650,"y":380,"wires":[["a969087543becd87"]]},{"id":"a969087543becd87","type":"debug","z":"2ec92e8e481b31c7","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":380,"wires":[]},{"id":"42bedc9b11726e3f","type":"inject","z":"2ec92e8e481b31c7","name":"新增一筆 (Create)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":200,"wires":[["cd426b5bf1c7752a"]]},{"id":"cd426b5bf1c7752a","type":"function","z":"2ec92e8e481b31c7","name":"Create function  ","func":"msg.topic = \"INSERT INTO users (name, age) VALUES ('Alice', 25)\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":200,"wires":[["d517a660eaef169d"]]},{"id":"3ac57f7d2bbaedac","type":"comment","z":"2ec92e8e481b31c7","name":"資料表Table","info":"CREATE TABLE IF NOT EXISTS users (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n   name TEXT,\n   age INTEGER\n)\n","x":410,"y":80,"wires":[]},{"id":"210afd8e2601a3ce","type":"inject","z":"2ec92e8e481b31c7","name":"查詢 (Read)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":360,"wires":[["3d3b9c38c623355c"]]},{"id":"3d3b9c38c623355c","type":"function","z":"2ec92e8e481b31c7","name":"Read function  ","func":"msg.topic = \"SELECT * FROM users WHERE age > 20\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":360,"wires":[["d517a660eaef169d"]]},{"id":"9b8b4d857042e43a","type":"inject","z":"2ec92e8e481b31c7","name":"更新 (Update)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":440,"wires":[["39f05041fd414bc1"]]},{"id":"39f05041fd414bc1","type":"function","z":"2ec92e8e481b31c7","name":"Update function  ","func":"msg.topic = \"UPDATE users SET age = 26 WHERE name = 'Alice'\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":440,"wires":[["d517a660eaef169d"]]},{"id":"bf89364e88cea868","type":"inject","z":"2ec92e8e481b31c7","name":"刪除 (Delete)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":520,"wires":[["a6d59667ce09088a"]]},{"id":"a6d59667ce09088a","type":"function","z":"2ec92e8e481b31c7","name":"Delete  function  ","func":"msg.topic = \"DELETE FROM users WHERE id = 1\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":520,"wires":[["d517a660eaef169d"]]},{"id":"989491797b337a03","type":"inject","z":"2ec92e8e481b31c7","name":"新增一筆 (Create)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":280,"wires":[["4ac024a48fa44490"]]},{"id":"4ac024a48fa44490","type":"function","z":"2ec92e8e481b31c7","name":"Create function  ","func":"msg.topic = \"INSERT INTO users (name, age) VALUES (?, ?)\";\nmsg.payload = [\"Bob\", 30]; // 依序對應問號\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":280,"wires":[["d517a660eaef169d"]]},{"id":"9483f811f3a38c69","type":"inject","z":"2ec92e8e481b31c7","name":"DROP (刪除資料表)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":600,"wires":[["cda3a22250276ed5"]]},{"id":"cda3a22250276ed5","type":"function","z":"2ec92e8e481b31c7","name":"DROP function  ","func":"msg.topic = \"DROP TABLE IF EXISTS users\";\nreturn msg;\n\n//SQL 指令：\n//CREATE TABLE IF NOT EXISTS users (\n//    id INTEGER PRIMARY KEY AUTOINCREMENT,\n//    name TEXT,\n//    age INTEGER\n//)\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":600,"wires":[["d517a660eaef169d"]]},{"id":"1cb2040a1b54cb0d","type":"sqlitedb","db":"CRUD.db","mode":"RWC"}]

[{"id":"940528c3e3697601","type":"sqlite","z":"6175eaf79545705a","mydb":"bc23914c8cab28bb","sqlquery":"msg.topic","sql":"","name":"SQLite 資料庫 (test.db)","x":670,"y":160,"wires":[["6c89c25ba1b79a98","c79aea60f978db5e"]]},{"id":"6c89c25ba1b79a98","type":"debug","z":"6175eaf79545705a","name":"SQLite 執行結果","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":900,"y":140,"wires":[]},{"id":"b320e61d0a2d30a5","type":"ui_button","z":"6175eaf79545705a","name":"CREATE (建立資料表)","group":"1963df87ae08178b","order":1,"width":3,"height":1,"passthru":false,"label":"(建立資料表)","tooltip":"建立 my_data 資料表","color":"","bgcolor":"green","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":140,"y":80,"wires":[["5943ddf87dc315ac"]]},{"id":"5943ddf87dc315ac","type":"change","z":"6175eaf79545705a","name":"SQL: CREATE (建立)","rules":[{"t":"set","p":"topic","pt":"msg","to":"CREATE TABLE IF NOT EXISTS my_data (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, number INTEGER)","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":80,"wires":[["940528c3e3697601","606aeeb344c86ebb"]]},{"id":"f8fdbe63af332cf9","type":"ui_button","z":"6175eaf79545705a","name":"INSERT (插入)","group":"1963df87ae08178b","order":2,"width":3,"height":1,"passthru":false,"label":"INSERT (插入一筆)","tooltip":"插入一筆隨機資料","color":"","bgcolor":"blue","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":140,"wires":[["3d023e070064238f"]]},{"id":"3d023e070064238f","type":"function","z":"6175eaf79545705a","name":"產生 INSERT SQL","func":"// 獲取目前時間\nconst now = new Date();\n// 格式化日期：YYYY-MM-DD\nconst date = now.getFullYear() + '-' + ('0' + (now.getMonth() + 1)).slice(-2) + '-' + ('0' + now.getDate()).slice(-2);\n// 格式化時間：HH:mm:ss\nconst time = ('0' + now.getHours()).slice(-2) + ':' + ('0' + now.getMinutes()).slice(-2) + ':' + ('0' + now.getSeconds()).slice(-2);\n\n// 產生 0 到 100 之間的亂數\nconst randomNumber = Math.floor(Math.random() * 101);\n\n// 建構 INSERT INTO SQL 語句\nmsg.topic = `INSERT INTO my_data (date, time, number) VALUES ('${date}', '${time}', ${randomNumber})`;\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":140,"wires":[["940528c3e3697601"]]},{"id":"d61eed9277aa032d","type":"ui_button","z":"6175eaf79545705a","name":"SELECT (查詢)","group":"1963df87ae08178b","order":3,"width":3,"height":1,"passthru":false,"label":"SELECT (查詢)","tooltip":"查詢所有資料並顯示","color":"","bgcolor":"orange","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":200,"wires":[["0925d7f1e6bf7d57"]]},{"id":"0925d7f1e6bf7d57","type":"change","z":"6175eaf79545705a","name":"SQL: SELECT (查詢)","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT id, date, time, number FROM my_data","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":200,"wires":[["940528c3e3697601"]]},{"id":"c79aea60f978db5e","type":"ui_table","z":"6175eaf79545705a","group":"216515e83bf6ead4","name":"資料表格 (ui-table)","order":1,"width":10,"height":8,"columns":[{"field":"id","title":"序號","width":"40%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"date","title":"日期","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"time","title":"時間","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"number","title":"亂數 (0-100)","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":910,"y":200,"wires":[]},{"id":"2650d6e9ad63a5c5","type":"ui_button","z":"6175eaf79545705a","name":"DELETE (刪除)","group":"1963df87ae08178b","order":4,"width":3,"height":1,"passthru":false,"label":"刪除 number < 50  ","tooltip":"刪除 number < 50 的紀錄，並自動刷新表格","color":"","bgcolor":"red","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":300,"wires":[["14f91b0687ef609b"]]},{"id":"14f91b0687ef609b","type":"change","z":"6175eaf79545705a","name":"SQL: DELETE (刪除)","rules":[{"t":"set","p":"topic","pt":"msg","to":"DELETE FROM my_data WHERE number < 50","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":300,"wires":[["940528c3e3697601","0925d7f1e6bf7d57"]]},{"id":"0c96e812e72f3f84","type":"ui_button","z":"6175eaf79545705a","name":"DROP(銷毀資料表)","group":"1963df87ae08178b","order":5,"width":3,"height":1,"passthru":false,"label":"DROP(銷毀資料表)","tooltip":"刪除 my_data 資料表 (請謹慎操作)","color":"","bgcolor":"darkred","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":130,"y":400,"wires":[["ebd49403e7375a09"]]},{"id":"ebd49403e7375a09","type":"change","z":"6175eaf79545705a","name":"SQL: DROP (銷毀)","rules":[{"t":"set","p":"topic","pt":"msg","to":"DROP TABLE IF EXISTS my_data","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":400,"wires":[["940528c3e3697601"]]},{"id":"a0e788235c2d259a","type":"comment","z":"6175eaf79545705a","name":"","info":"DELETE FROM my_data WHERE number < 50","x":330,"y":340,"wires":[]},{"id":"51c5c320038f5c68","type":"comment","z":"6175eaf79545705a","name":"","info":"DROP TABLE IF EXISTS my_data","x":330,"y":440,"wires":[]},{"id":"bac6457e8cc2c3c5","type":"comment","z":"6175eaf79545705a","name":"","info":"CREATE TABLE IF NOT EXISTS my_data \n(\nid INTEGER PRIMARY KEY AUTOINCREMENT, \ndate TEXT, \ntime TEXT, \nnumber INTEGER\n)","x":370,"y":40,"wires":[]},{"id":"d005ea88d8cac469","type":"ui_button","z":"6175eaf79545705a","name":"刪除","group":"1963df87ae08178b","order":8,"width":3,"height":1,"passthru":false,"label":"DELETE by ID","tooltip":"執行單筆 ID 刪除，並刷新表格","color":"","bgcolor":"darkred","className":"","icon":"fa-eraser","payload":"1","payloadType":"num","topic":"","topicType":"str","x":90,"y":540,"wires":[["24cb44a4c6383834"]]},{"id":"9b934b2d46e35ffc","type":"function","z":"6175eaf79545705a","name":"function  (DELETE ID SQL)","func":"// 這個節點從 Dashboard 的輸入和按鈕接收訊息\n// 步驟一：儲存輸入的 ID\n// 如果 msg.topic 是 'deleteId' (來自輸入框)，則儲存 payload (ID)\nif (msg.topic === 'deleteId') {\n    flow.set('deleteID', msg.payload);\n    // 不繼續傳遞，因為還沒按刪除按鈕\n    return msg;\n}\n\nreturn msg; // 其他情況不執行操作","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":480,"wires":[[]]},{"id":"333ccf32fcdfbf92","type":"ui_text_input","z":"6175eaf79545705a","name":"刪除一筆 id=","label":"刪除 ID = ","tooltip":"","group":"1963df87ae08178b","order":6,"width":3,"height":1,"passthru":true,"mode":"number","delay":0,"topic":"deleteId","sendOnBlur":true,"className":"","topicType":"str","x":110,"y":480,"wires":[["9b934b2d46e35ffc"]]},{"id":"24cb44a4c6383834","type":"function","z":"6175eaf79545705a","name":"function  (DELETE ID SQL)","func":"// 這個節點從 Dashboard 的輸入和按鈕接收訊息\n\n// 步驟二：執行刪除操作（來自按鈕）\n// 如果 msg.payload 是 1 (來自按鈕)，則執行刪除\nif (msg.payload === 1) {\n    const idToDelete = flow.get('deleteID');\n    \n    // 檢查是否有有效的 ID\n    if (idToDelete && !isNaN(parseInt(idToDelete))) {\n        // 創建 DELETE SQL 語句\n        msg.topic = `DELETE FROM my_data WHERE id = ${parseInt(idToDelete)}`;\n        msg.payload = {}; // 清空 payload，確保後續操作正常\n        return msg;\n    } else {\n        node.warn(\"請先在輸入框中輸入有效的 ID 號碼！\");\n        return null; // 無效 ID，不執行操作\n    }\n}\n\nreturn msg; // 其他情況不執行操作","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":540,"wires":[["940528c3e3697601"]]},{"id":"ae02d51a98a2c189","type":"inject","z":"6175eaf79545705a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":40,"wires":[["5943ddf87dc315ac"]]},{"id":"606aeeb344c86ebb","type":"debug","z":"6175eaf79545705a","name":"debug 369","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":60,"wires":[]},{"id":"bc23914c8cab28bb","type":"sqlitedb","db":"1111.db","mode":"RWC"},{"id":"1963df87ae08178b","type":"ui_group","name":"Default","tab":"1871b13b611a78bb","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"216515e83bf6ead4","type":"ui_group","name":"SQLite 資料庫功能示範","tab":"1871b13b611a78bb","order":1,"disp":true,"width":10,"collapse":false,"className":""},{"id":"1871b13b611a78bb","type":"ui_tab","name":"資料庫示範面板","icon":"dashboard","order":1,"disabled":false,"hidden":false}]