第二次作業
模擬 RFID(要按PB) 輸出UID至MQTT Broker 後給Python TKinter 暨 Node-Red (Sqlite資料庫) 另一路至Telegram
Telegram 安裝
參考 https://alex9ufoexploer.blogspot.com/2025/04/telegram.html
Telegram 完全教學攻略：從註冊、中文化、設定到各種應用技巧一次學會
https://www.techbang.com/posts/76688-telegram-complete-lying-on-teaching-strategies-from-registration-chinese-setting-stoking-to-a-variety-of-application-techniques-learned-at-once
iOS下載網址：https://reurl.cc/VaKlNQ
Android下載網址：https://reurl.cc/alQALY
PC/Mac/Linux下載網址：https://reurl.cc/lL8Ag6
macOS下載網址：https://reurl.cc/EKkeeK
網頁版連結：https://reurl.cc/mdaAAM
WOKWI硬體 利用PB壓下後產生亂數模擬RFID UID碼
(需修改 )
DB_PATH = r"D:\2025RFID\2025-09下學期\EX2\rfid.db"
請修改為您存放的路徑
Python程式
import tkinter as tk
from tkinter import ttk
import sqlite3
import paho.mqtt.client as mqtt
from datetime import datetime
# 資料庫路徑
DB_PATH = r"D:\2025RFID\2025-09下學期\EX2\rfid.db"
MQTT_BROKER = "broker.mqttgo.io"
MQTT_PORT = 1883
MQTT_TOPIC = "alex9ufo/esp32/RFID"
# 建立資料表（如果尚未存在）
def init_db():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS rfid_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uid TEXT NOT NULL,
date TEXT NOT NULL,
time TEXT NOT NULL
)
""")
conn.commit()
conn.close()
# 插入新資料
def insert_uid(uid):
now = datetime.now()
date_str = now.strftime("%Y-%m-%d")
time_str = now.strftime("%H:%M:%S")
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("INSERT INTO rfid_logs (uid, date, time) VALUES (?, ?, ?)", (uid, date_str, time_str))
conn.commit()
conn.close()
refresh_table()
# 讀取資料並更新表格
def refresh_table():
for row in tree.get_children():
tree.delete(row)
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT * FROM rfid_logs ORDER BY id DESC")
rows = cursor.fetchall()
for row in rows:
tree.insert("", tk.END, values=row)
conn.close()
# MQTT 訊息處理
def on_connect(client, userdata, flags, rc):
client.subscribe(MQTT_TOPIC)
def on_message(client, userdata, msg):
uid = msg.payload.decode()
insert_uid(uid)
# 建立 GUI
root = tk.Tk()
root.title("RFID 資料顯示")
root.geometry("600x400")
columns = ("id", "uid", "date", "time")
tree = ttk.Treeview(root, columns=columns, show="headings")
for col in columns:
tree.heading(col, text=col)
tree.column(col, width=100)
tree.pack(fill=tk.BOTH, expand=True)
# 初始化資料庫與表格
init_db()
refresh_table()
# 啟動 MQTT 客戶端
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.connect(MQTT_BROKER, MQTT_PORT, 60)
client.loop_start()
# 啟動 GUI 主迴圈
root.mainloop()
模擬一個 RFID 打卡記錄系統的桌面端監控程式。
MQTT 即時監聽 (Real-time Listener): 程式作為一個 MQTT 客戶端，持續連線到
broker.mqttgo.io:1883並訂閱alex9ufo/esp32/RFID主題。數據處理與儲存 (Data Logging): 一旦收到來自 MQTT 的訊息（被視為 RFID 卡號 UID），程式會自動獲取當前的日期和時間，將 UID、日期、時間 一起寫入本地的
rfid.db資料庫中的rfid_logs資料表。圖形化介面顯示 (GUI): 使用 Tkinter 創建一個簡單的視窗，內含一個表格 (
ttk.Treeview)，用於即時顯示資料庫中的所有打卡記錄。每次有新的記錄插入時，表格會自動更新，確保使用者看到最新的數據。
import tkinter as tk
from tkinter import ttk
import sqlite3
import paho.mqtt.client as mqtt
from datetime import datetime
# ==============================================================================
# 程式配置區塊 (Configuration Block)
# ==============================================================================
# 資料庫路徑
DB_PATH = r"D:\2025RFID\2025-09下學期\EX2\rfid.db"
# MQTT Broker 伺服器位址
MQTT_BROKER = "broker.mqttgo.io"
# MQTT Broker 連接埠 (標準未加密連線)
MQTT_PORT = 1883
# MQTT 訂閱及發佈的主題名稱
MQTT_TOPIC = "alex9ufo/esp32/RFID"
# ==============================================================================
# 資料庫操作函式 (Database Functions)
# ==============================================================================
# 建立資料表（如果尚未存在）
def init_db():
# 連接到 SQLite 資料庫檔案
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# 執行 SQL 語句：建立 rfid_logs 資料表
# 包含 id (主鍵, 自動遞增)、uid (卡號)、date (日期)、time (時間)
cursor.execute("""
CREATE TABLE IF NOT EXISTS rfid_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uid TEXT NOT NULL,
date TEXT NOT NULL,
time TEXT NOT NULL
)
""")
conn.commit() # 提交變更
conn.close() # 關閉連線
# 插入新資料 (由 MQTT 觸發)
def insert_uid(uid):
# 獲取當前時間
now = datetime.now()
# 格式化日期為 YYYY-MM-DD
date_str = now.strftime("%Y-%m-%d")
# 格式化時間為 HH:MM:SS
time_str = now.strftime("%H:%M:%S")
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# 執行 INSERT 語句，使用 '?' 作為佔位符防止 SQL 注入
cursor.execute("INSERT INTO rfid_logs (uid, date, time) VALUES (?, ?, ?)", (uid, date_str, time_str))
conn.commit()
conn.close()
# 插入新數據後，立即更新 GUI 表格顯示
refresh_table()
# 讀取資料並更新表格 (GUI 介面)
def refresh_table():
# 1. 清空 Treeview 中現有的所有行
for row in tree.get_children():
tree.delete(row)
# 2. 從資料庫讀取最新數據
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# 查詢所有數據，並按 ID 倒序排列 (DESC)，最新的記錄在最上面
cursor.execute("SELECT * FROM rfid_logs ORDER BY id DESC")
rows = cursor.fetchall() # 獲取所有結果
conn.close()
# 3. 將數據插入到 Treeview
for row in rows:
# tree.insert(父節點, 位置, 值)
tree.insert("", tk.END, values=row)
# ==============================================================================
# MQTT 客戶端回調函式 (MQTT Client Callbacks)
# ==============================================================================
# 連線成功時的回調函式
def on_connect(client, userdata, flags, rc):
# rc = 0 代表連線成功
if rc == 0:
# 連線成功後，訂閱指定的主題
client.subscribe(MQTT_TOPIC)
# 可以添加 print("MQTT 連線成功並已訂閱") 進行除錯
# 收到 MQTT 訊息時的回調函式
def on_message(client, userdata, msg):
# 將收到的二進制 payload 轉換為字串 (即 RFID UID)
uid = msg.payload.decode()
# 呼叫函式將此 UID 寫入資料庫
insert_uid(uid)
# ==============================================================================
# GUI 介面設定 (GUI Setup)
# ==============================================================================
# 建立主視窗
root = tk.Tk()
root.title("RFID 資料顯示")
root.geometry("600x400") # 設定視窗初始大小
# 定義表格的欄位名稱
columns = ("id", "uid", "date", "time")
# 建立 Treeview 表格組件
tree = ttk.Treeview(root, columns=columns, show="headings")
# 配置每個欄位
for col in columns:
tree.heading(col, text=col) # 設定欄位標題
tree.column(col, width=100) # 設定欄位寬度
# 將表格組件放置到主視窗中，fill=tk.BOTH 擴展填充，expand=True 允許隨視窗縮放
tree.pack(fill=tk.BOTH, expand=True)
# ==============================================================================
# 初始化與啟動 (Initialization and Start)
# ==============================================================================
# 執行資料庫初始化 (建立資料表)
init_db()
# 首次讀取資料庫數據並顯示在表格中
refresh_table()
# 啟動 MQTT 客戶端
client = mqtt.Client()
# 設定連線成功和收到訊息時的回調函式
client.on_connect = on_connect
client.on_message = on_message
# 連接到 Broker
client.connect(MQTT_BROKER, MQTT_PORT, 60)
# 以非阻塞方式在後台運行網路循環，確保 GUI 不會被 MQTT 循環阻塞
client.loop_start()
# 啟動 GUI 主迴圈 (程式從此進入事件驅動模式，等待用戶操作或 MQTT 訊息)
root.mainloop()
//========================================
Node-RED 安裝步驟暨執行node-red程式
https://alex9ufoexploer.blogspot.com/2025/07/node-red-node-red.html
Node-Red安裝 與 dashboard 安裝與使用範例
https://alex9ufoexploer.blogspot.com/2023/10/node-red-dashboard_18.html
Node-Red安裝 與 dashboard 安裝
https://sites.google.com/site/wenyunotify/12-node-red/01-%E5%85%A5%E9%96%80%E7%AF%87
DB Browser for SQLite 的安裝
https://alex9ufoexploer.blogspot.com/2025/07/db-browser-for-sqlite.html
(安裝node-red , dashboard , sqlitedb, telegrambot 節點 才能匯入程式 )
留意 rfid.db 存放位置 D:\2025RFID\2025-09下學期\EX2\rfid.db
請修改為您存放的路徑
Node-Red程式
[
{
"id": "ec87aef83806bf77",
"type": "tab",
"label": "RFID_2025",
"disabled": false,
"info": "",
"env": []
},
{
"id": "5318e15b20b2d057",
"type": "mqtt in",
"z": "ec87aef83806bf77",
"name": "RFID UID In",
"topic": "alex9ufo/esp32/RFID",
"qos": "1",
"datatype": "auto-detect",
"broker": "450dc18180d64bb4",
"nl": false,
"rap": true,
"rh": 0,
"inputs": 0,
"x": 110,
"y": 380,
"wires": [
[
"43e89ebe38071896",
"d189cd151fb44415"
]
]
},
{
"id": "09f57e8989839b3f",
"type": "ui_text",
"z": "ec87aef83806bf77",
"group": "01def7a22d7e0b47",
"order": 1,
"width": 4,
"height": 1,
"name": "UID",
"label": "UID",
"format": "{{msg.payload}} ",
"layout": "row-left",
"className": "",
"style": false,
"font": "",
"fontSize": "",
"color": "#000000",
"x": 390,
"y": 420,
"wires": []
},
{
"id": "a5f9d8b1a41e041a",
"type": "ui_text",
"z": "ec87aef83806bf77",
"group": "01def7a22d7e0b47",
"order": 2,
"width": 4,
"height": 1,
"name": "Current Time",
"label": "",
"format": "<span style=\"font-weight:bold;\">{{msg.payload}}</span>",
"layout": "row-spread",
"className": "",
"style": false,
"font": "",
"fontSize": "",
"color": "#000000",
"x": 530,
"y": 660,
"wires": []
},
{
"id": "3276e8ef114117e3",
"type": "inject",
"z": "ec87aef83806bf77",
"name": "Current Time",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": true,
"onceDelay": "0.1",
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 660,
"wires": [
[
"b69f3cf69db70f15"
]
]
},
{
"id": "b69f3cf69db70f15",
"type": "function",
"z": "ec87aef83806bf77",
"name": "Format Date/Time",
"func": "const now = new Date(msg.payload);\nconst date = now.toLocaleDateString();\nconst time = now.toLocaleTimeString();\nmsg.payload = `${date} ${time}`;\nreturn msg;",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 330,
"y": 660,
"wires": [
[
"a5f9d8b1a41e041a"
]
]
},
{
"id": "c43ba373c649a667",
"type": "function",
"z": "ec87aef83806bf77",
"name": "Log UID",
"func": "const now = new Date();\nconst rdate = now.toISOString().split('T')[0];\nconst rtime = now.toTimeString().split(' ')[0];\nvar rtemp= msg.payload ;\nmsg.topic = \"INSERT INTO rfid_logs (uid ,date, time) VALUES ($temp , $date, $time )\";\nmsg.payload = [rtemp ,rdate, rtime ];\nreturn msg;\n\n//CREATE TABLE IF NOT EXISTS rfid_logs \n//(id INTEGER PRIMARY KEY AUTOINCREMENT,\n//uid TEXT NOT NULL,\n//date TEXT NOT NULL,\n//time TEXT NOT NULL)",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 400,
"y": 380,
"wires": [
[
"eaf68880f7e1e8a3"
]
]
},
{
"id": "121608f3028a5a08",
"type": "sqlite",
"z": "ec87aef83806bf77",
"mydb": "9c0419f225af3721",
"sqlquery": "msg.topic",
"sql": "{{msg.topic}}",
"name": "RFID SQLite DB",
"x": 500,
"y": 320,
"wires": [
[
"306c76656d27a7b8"
]
]
},
{
"id": "5616bc8197f9d0b3",
"type": "inject",
"z": "ec87aef83806bf77",
"name": "Refresh DB",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": true,
"onceDelay": "0.1",
"topic": "SELECT id, date, time, event FROM events ORDER BY id DESC",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 560,
"wires": [
[
"0963eb3d0d4dcbd0"
]
]
},
{
"id": "9fad4c0dccccf994",
"type": "ui_table",
"z": "ec87aef83806bf77",
"group": "01def7a22d7e0b47",
"name": "Database View",
"order": 3,
"width": 8,
"height": 12,
"columns": [
{
"field": "id",
"title": "ID碼",
"width": "",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "uid",
"title": "UID號碼",
"width": "",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "date",
"title": "日期",
"width": "",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "time",
"title": "時間",
"width": "",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
}
],
"outputs": 0,
"cts": false,
"x": 700,
"y": 560,
"wires": []
},
{
"id": "0963eb3d0d4dcbd0",
"type": "sqlite",
"z": "ec87aef83806bf77",
"mydb": "9c0419f225af3721",
"sqlquery": "msg.topic",
"sql": "{{msg.topic}}",
"name": "RFID Query DB",
"x": 480,
"y": 560,
"wires": [
[
"9fad4c0dccccf994"
]
]
},
{
"id": "f3f1c42856e97243",
"type": "inject",
"z": "ec87aef83806bf77",
"name": "Create Table Once",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": true,
"onceDelay": "2",
"topic": "CREATE TABLE IF NOT EXISTS rfid_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,uid TEXT NOT NULL,date TEXT NOT NULL,time TEXT NOT NULL)",
"payload": "",
"payloadType": "date",
"x": 170,
"y": 320,
"wires": [
[
"121608f3028a5a08"
]
]
},
{
"id": "306c76656d27a7b8",
"type": "debug",
"z": "ec87aef83806bf77",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 710,
"y": 320,
"wires": []
},
{
"id": "eaf68880f7e1e8a3",
"type": "sqlite",
"z": "ec87aef83806bf77",
"mydb": "9c0419f225af3721",
"sqlquery": "msg.topic",
"sql": "{{msg.topic}}",
"name": "RFID SQLite DB",
"x": 560,
"y": 380,
"wires": [
[
"306c76656d27a7b8"
]
]
},
{
"id": "e7e7a5822097d8ec",
"type": "inject",
"z": "ec87aef83806bf77",
"name": "Current Time",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "5",
"crontab": "",
"once": true,
"onceDelay": "0.1",
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 600,
"wires": [
[
"d1656423a801e0d6"
]
]
},
{
"id": "d1656423a801e0d6",
"type": "function",
"z": "ec87aef83806bf77",
"name": "function query",
"func": "msg.topic = \"SELECT id, uid, date, time FROM rfid_logs ORDER BY id DESC\";\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 300,
"y": 600,
"wires": [
[
"0963eb3d0d4dcbd0"
]
]
},
{
"id": "c35bdfe07b11b681",
"type": "comment",
"z": "ec87aef83806bf77",
"name": "RFID_uid",
"info": "CREATE TABLE IF NOT EXISTS rfid_logs (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n uid TEXT NOT NULL,\n date TEXT NOT NULL,\n time TEXT NOT NULL\n)\n\nCREATE TABLE \"rfid_logs\" (\n\t\"id\"\tINTEGER,\n\t\"uid\"\tTEXT NOT NULL,\n\t\"date\"\tTEXT NOT NULL,\n\t\"time\"\tTEXT NOT NULL,\n\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n);",
"x": 480,
"y": 280,
"wires": []
},
{
"id": "43e89ebe38071896",
"type": "debug",
"z": "ec87aef83806bf77",
"name": "debug 345",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 210,
"y": 480,
"wires": []
},
{
"id": "d189cd151fb44415",
"type": "json",
"z": "ec87aef83806bf77",
"name": "",
"property": "payload",
"action": "str",
"pretty": false,
"x": 250,
"y": 380,
"wires": [
[
"c43ba373c649a667",
"09f57e8989839b3f",
"fd411fc6c641a651"
]
]
},
{
"id": "fd411fc6c641a651",
"type": "debug",
"z": "ec87aef83806bf77",
"name": "debug 346",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 430,
"y": 480,
"wires": []
},
{
"id": "450dc18180d64bb4",
"type": "mqtt-broker",
"name": "broker.mqttgo.io",
"broker": "broker.mqttgo.io",
"port": "1883",
"clientid": "",
"autoConnect": true,
"usetls": false,
"protocolVersion": "4",
"keepalive": "60",
"cleansession": true,
"autoUnsubscribe": true,
"birthTopic": "",
"birthQos": "1",
"birthPayload": "",
"birthMsg": {},
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"closeMsg": {},
"willTopic": "",
"willQos": "0",
"willPayload": "",
"willMsg": {},
"userProps": "",
"sessionExpiry": ""
},
{
"id": "01def7a22d7e0b47",
"type": "ui_group",
"name": "Default",
"tab": "2fcd675506c05c36",
"order": 1,
"disp": true,
"width": 8,
"collapse": false,
"className": ""
},
{
"id": "9c0419f225af3721",
"type": "sqlitedb",
"db": "D:\\2025RFID\\2025-09下學期\\EX2\\rfid.db",
"mode": "RWC"
},
{
"id": "2fcd675506c05c36",
"type": "ui_tab",
"name": "RFID",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
