# 建立一個使用者資料表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER
)
''')
conn.commit() # 提交事務
[{"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}]
