Wokwi ESP32 MQTT 傳輸 + Python (Tkinter + SQLite) 門禁管理後台

 

 Wokwi ESP32 MQTT 傳輸 + Python (Tkinter + SQLite) 門禁管理後台 

ESP32 會連接至免費公共測試服務器 broker.hivemq.com，並在刷卡時將帶有 AA: (進) 與 BB: (出)
 前綴的卡號發布至主題 wokwi/rfid/access


wokwi

程式 
#include <WiFi.h>
#include <PubSubClient.h>
#include <SPI.h>
#include <MFRC522.h>
// Wi-Fi 與 MQTT 伺服器設定 (Wokwi 虛擬 Wi-Fi)
const char* ssid = "Wokwi-GUEST";
const char* password = "";
const char* mqtt_server = "broker.hivemq.com";
const char* mqtt_topic = "wokwi/rfid/access";
WiFiClient espClient;
PubSubClient client(espClient);
// RFID 腳位定義
#define SS_1_PIN   5
#define RST_1_PIN 21
#define SS_2_PIN   4
#define RST_2_PIN 22
MFRC522 rfid1(SS_1_PIN, RST_1_PIN); // CARD1 (入口)
MFRC522 rfid2(SS_2_PIN, RST_2_PIN); // CARD2 (出口)
void setup_wifi() {
  delay(10);
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("\nWiFi 已連線");
}
void reconnect() {
  while (!client.connected()) {
    String clientId = "ESP32-RFID-" + String(random(0xffff), HEX);
    if (client.connect(clientId.c_str())) {
      Serial.println("MQTT 已連線");
    } else {
      delay(2000);
    }
  }
}
void readAndPublish(MFRC522 &mfrc522, String prefix) {
  if (!mfrc522.PICC_IsNewCardPresent() || !mfrc522.PICC_ReadCardSerial()) {
    return;
  }
  String fullCode = prefix;
  for (byte i = 0; i < mfrc522.uid.size; i++) {
    if (i > 0) fullCode += ":";
    if (mfrc522.uid.uidByte[i] < 0x10) fullCode += "0";
    fullCode += String(mfrc522.uid.uidByte[i], HEX);
  }
  fullCode.toUpperCase();
  Serial.print("刷卡發送: ");
  Serial.println(fullCode);
  // 發送卡號至 MQTT 主題
  client.publish(mqtt_topic, fullCode.c_str());
  mfrc522.PICC_HaltA();
  mfrc522.PCD_StopCrypto1();
  delay(1000);
}
void setup() {
  Serial.begin(115200);
  SPI.begin();
  setup_wifi();
  client.setServer(mqtt_server, 1883);
  rfid1.PCD_Init();
  rfid2.PCD_Init();
  Serial.println("雙 MFRC522 + MQTT 系統已啟動");
}
void loop() {
  if (!client.connected()) {
    reconnect();
  }
  client.loop();
  readAndPublish(rfid1, "AA:"); // 入口
  readAndPublish(rfid2, "BB:"); // 出口
  delay(20);
}


執行此 Python 腳本後，會自動建立 access_control.db 資料庫，
訂閱 MQTT 主題，並透過視窗顯示即時進出日誌與當前場內的所有卡號與進場時間。




import sqlite3
import threading
import tkinter as tk
from datetime import datetime
from tkinter import ttk
import paho.mqtt.client as mqtt

# --- 1. SQLite 資料庫初始化 ---
def init_db():
    conn = sqlite3.connect("access_control.db")
    cursor = conn.cursor()
    # 建立歷史刷卡日誌資料表
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS access_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            card_uid TEXT,
            action TEXT,
            timestamp TEXT
        )
    """
    )
    # 建立場內人員清單資料表
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS inside_cards (
            card_uid TEXT PRIMARY KEY,
            entry_time TEXT
        )
    """
    )
    conn.commit()
    conn.close()


init_db()


# --- 2. GUI 主介面類別 ---
class AccessControlApp:

    def __init__(self, root):
        self.root = root
        self.root.title("RFID 門禁與場內即時監控系統")
        self.root.geometry("750x450")

        # 頂部狀態列
        self.status_label = tk.Label(
            root,
            text="場內當前總人數: 0 人",
            font=("Helvetica", 16, "bold"),
            bg="#2c3e50",
            fg="white",
            pady=10,
        )
        self.status_label.pack(fill=tk.X)

        # 頁籤容器
        notebook = ttk.Notebook(root)
        notebook.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # 分頁 1: 當前場內卡號
        self.inside_frame = ttk.Frame(notebook)
        notebook.add(self.inside_frame, text="場內卡號清單")

        self.inside_tree = ttk.Treeview(
            self.inside_frame,
            columns=("UID", "EntryTime"),
            show="headings",
        )
        self.inside_tree.heading("UID", text="卡片 UID")
        self.inside_tree.heading("EntryTime", text="入場時間")
        self.inside_tree.column("UID", width=250, anchor="center")
        self.inside_tree.column("EntryTime", width=350, anchor="center")
        self.inside_tree.pack(fill=tk.BOTH, expand=True)

        # 分頁 2: 歷史進出日誌
        self.log_frame = ttk.Frame(notebook)
        notebook.add(self.log_frame, text="歷史進出紀錄")

        self.log_tree = ttk.Treeview(
            self.log_frame,
            columns=("ID", "UID", "Action", "Time"),
            show="headings",
        )
        self.log_tree.heading("ID", text="編號")
        self.log_tree.heading("UID", text="卡片 UID")
        self.log_tree.heading("Action", text="動作")
        self.log_tree.heading("Time", text="記錄時間")
        self.log_tree.column("ID", width=60, anchor="center")
        self.log_tree.column("UID", width=200, anchor="center")
        self.log_tree.column("Action", width=120, anchor="center")
        self.log_tree.column("Time", width=250, anchor="center")
        self.log_tree.pack(fill=tk.BOTH, expand=True)

        # 載入初始資料
        self.refresh_ui()

    # 處理卡片刷卡邏輯
    def process_swipe(self, raw_data):
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        conn = sqlite3.connect("access_control.db")
        cursor = conn.cursor()

        if raw_data.startswith("AA:"):  # 入口
            uid = raw_data[3:]
            # 紀錄歷史
            cursor.execute(
                "INSERT INTO access_logs (card_uid, action, timestamp) VALUES (?, ?, ?)",
                (uid, "IN (進場)", now_str),
            )
            # 新增至場內清單 (若未在場內)
            cursor.execute(
                "INSERT OR REPLACE INTO inside_cards (card_uid, entry_time) VALUES (?, ?)",
                (uid, now_str),
            )

        elif raw_data.startswith("BB:"):  # 出口
            uid = raw_data[3:]
            # 紀錄歷史
            cursor.execute(
                "INSERT INTO access_logs (card_uid, action, timestamp) VALUES (?, ?, ?)",
                (uid, "OUT (出場)", now_str),
            )
            # 從場內清單移除
            cursor.execute(
                "DELETE FROM inside_cards WHERE card_uid = ?", (uid,)
            )

        conn.commit()
        conn.close()

        # 更新 UI 畫面
        self.root.after(0, self.refresh_ui)

    # 刷新表格與統計人數
    def refresh_ui(self):
        conn = sqlite3.connect("access_control.db")
        cursor = conn.cursor()

        # 1. 刷新場內表格
        for item in self.inside_tree.get_children():
            self.inside_tree.delete(item)
        cursor.execute(
            "SELECT card_uid, entry_time FROM inside_cards ORDER BY entry_time DESC"
        )
        inside_records = cursor.fetchall()
        for row in inside_records:
            self.inside_tree.insert("", tk.END, values=row)

        # 2. 刷新歷史紀錄
        for item in self.log_tree.get_children():
            self.log_tree.delete(item)
        cursor.execute(
            "SELECT id, card_uid, action, timestamp FROM access_logs ORDER BY id DESC LIMIT 50"
        )
        for row in cursor.fetchall():
            self.log_tree.insert("", tk.END, values=row)

        # 3. 更新計數
        count = len(inside_records)
        self.status_label.config(text=f"場內當前總人數: {count} 人")

        conn.close()


# --- 3. MQTT 接收執行緒 ---
def start_mqtt(app):
    def on_connect(client, userdata, flags, rc):
        print("已成功連線至 MQTT Broker，開始監聽刷卡事件...")
        client.subscribe("wokwi/rfid/access")

    def on_message(client, userdata, msg):
        payload = msg.payload.decode("utf-8")
        print(f"收到 MQTT 訊息: {payload}")
        app.process_swipe(payload)

    client = mqtt.Client()
    client.on_connect = on_connect
    client.on_message = on_message
    client.connect("broker.hivemq.com", 1883, 60)
    client.loop_forever()


if __name__ == "__main__":
    root = tk.Tk()
    app = AccessControlApp(root)

    # 將 MQTT 放在 background thread 避免主介面凍結
    mqtt_thread = threading.Thread(target=start_mqtt, args=(app,), daemon=True)
    mqtt_thread.start()

    root.mainloop()



雙 MFRC522 + MQTT 系統已啟動
MQTT 已連線
刷卡發送: AA:AA:11:22:33
刷卡發送: BB:BB:44:55:66
刷卡發送: BB:11:22:33:44
刷卡發送: AA:11:22:33:44
刷卡發送: AA:11:22:33:44
刷卡發送: BB:11:22:33:44
刷卡發送: BB:11:22:33:44
刷卡發送: BB:11:22:33:44
刷卡發送: BB:BB:44:55:66
刷卡發送: BB:BB:44:55:66
刷卡發送: BB:BB:44:55:66
刷卡發送: AA:11:22:33:44
刷卡發送: AA:AA:11:22:33
刷卡發送: AA:55:66:77:88
刷卡發送: AA:AA:BB:CC:DD
刷卡發送: AA:04:11:22:33
刷卡發送: AA:C0:FF:EE:99
刷卡發送: BB:BB:44:55:66
刷卡發送: BB:11:22:33:44
刷卡發送: BB:11:22:33:44
刷卡發送: BB:11:22:33:44
刷卡發送: BB:55:66:77:88
刷卡發送: BB:55:66:77:88