Ziwen's code

import paho.mqtt.client as mqtt
import json
import mysql.connector
import threading
from mysql.connector import connect, Error
BROKER_ADDRESS = "127.0.0.1"
BROKER_PORT = 1883
CLIENT_ID = "client"

# 连接数据库
def connect_to_db():
    host = '127.0.0.1'
    user = 'root'
    password="Qq69dzw47"
    database = 'studentcard'

    return mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )

# 回调函数，当连接成功时调用
def on_connect(client, userdata, flags, rc):
    print(f"Connected with result code {str(rc)}")

def handle_user_and_card(idnumber, name, major, amount):
    client = mqtt.Client()
    client.on_connect = on_connect
    # 连接到MQTT代理
    client.connect(BROKER_ADDRESS, BROKER_PORT)
    try:
        with connect_to_db() as conn:
            cursor = conn.cursor()
            # 检查用户是否已经存在，如果存在则更新，不存在则插入
            check_user_query = "SELECT * FROM users WHERE idnumber = %s"
            cursor.execute(check_user_query, (idnumber,))
            user_exists = cursor.fetchone()
            if user_exists:
                # 更新用户信息
                update_user_query = "UPDATE users SET name = %s, major = %s WHERE idnumber = %s"
                cursor.execute(update_user_query, (name, major, idnumber))
            else:
                # 插入新用户信息
                insert_user_query = "INSERT INTO users (idnumber, name, major) VALUES (%s, %s, %s)"
                cursor.execute(insert_user_query, (idnumber, name, major))
                # 插入校园卡信息
                insert_card_query = "INSERT INTO campus_card(idnumber, name, money) VALUES (%s, %s, %s)"
                cursor.execute(insert_card_query, (idnumber, name, amount))
                # 提交事务
            conn.commit()
            user_and_card = {
                "id": idnumber,
                "name": name,
                "major": major,
                "balance":amount
            }
            user_and_card_json = json.dumps(user_and_card)
            client.publish("user_newcard", user_and_card_json, qos=1)
            print(f"用户 {idnumber} 和校园卡已成功添加或更新。")
    except Error as e:
        print(f"Error: {e}")
        # 发生错误时回滚事务
        if conn.is_connected():
            conn.rollback()

def recharge_card(idnumber, money):
    conn = connect_to_db()
    cursor = conn.cursor()
    # 连接到MQTT代理
    client.connect(BROKER_ADDRESS, BROKER_PORT)
    # 检查充值金额是否大于0
    if money <= 0:
        print("充值金额必须大于0！")
        return False

        # 尝试更新余额
    try:
        update_query = ("UPDATE campus_card SET money = %s "
                        "WHERE idnumber = %s")

        # 执行UPDATE语句
        cursor.execute(update_query, (money, idnumber,))
        conn.commit()
        card_recharge_data = {
            "idnumber": idnumber,
            "amount": money
        }
        card_recharge_json = json.dumps(card_recharge_data)
        client.publish("card_recharge", card_recharge_json, qos=1)
        print(f"校园卡 {idnumber} 充值成功，当前余额已更新。")
        conn.close()
        return True
    except mysql.connector.Error as e:
        print(f"充值校园卡时发生错误: {e}")
        return False

# 查询校园卡信息
def query_card(idnumber):
    conn = connect_to_db()
    cursor = conn.cursor()
    client.connect(BROKER_ADDRESS, BROKER_PORT)
    # 尝试查询校园卡信息
    try:
        cursor.execute("SELECT * FROM campus_card WHERE idnumber = %s", (idnumber,))
        card_info = cursor.fetchone()
        if card_info:
            card_query_data = {
                "idnumber": idnumber,
                "name":card_info[0],
                "balance": card_info[2]
            }
            card_query_json = json.dumps(card_query_data)
            client.publish("card_query", card_query_json, qos=1)
            print(f"校园卡 {idnumber} 信息：")
            print(f"持卡人：{card_info[0]}")
            print(f"余额：{card_info[2]} 元")
        else:
            print("无效的校园卡ID！")

    except mysql.connector.Error as e:
        print(f"查询校园卡时发生错误: {e}")
    finally:
        # 关闭数据库连接
        conn.close()

        # 断开MQTT连接
        client.disconnect()

user_and_card_topic = "user_newcard"
card_recharge_topic = "card_recharge"
card_query_topic = "card_query"

# MQTT回调函数，用于处理收到的消息
def on_message(client, userdata, msg):
    topic = msg.topic
    payload = msg.payload.decode('utf-8')

    # 根据不同的topic处理消息
    if topic == "user_and_card":
        handle_user_and_card(payload)
    elif topic == "card_recharge_topic":
        recharge_card(payload)
    elif topic == "card_query_topic":
        query_card(payload)

def on_disconnect(client, userdata, rc):
    if rc != 0:
        print(f"Unexpected disconnection. Will auto-reconnect")
        client.reconnect_delay_set(min_delay=1, max_delay=60)  # 设置重连延迟（可选）
        client.loop_start()  # 这可能不是最佳实践，因为它会启动一个新的网络循环
        client.connect(BROKER_ADDRESS, BROKER_PORT, 60)  # 尝试重新连接
    else:
        print("Normal disconnection. Shutting down...")
        client.loop_stop()  # 如果正常断开，则停止网络循环
#连接到MQTT代理
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.on_disconnect = on_disconnect

client.connect(BROKER_ADDRESS, BROKER_PORT, 60)

try:
    client.connect(BROKER_ADDRESS, BROKER_PORT, 60)
    # 开始网络循环，以便客户端可以处理网络流量
    client.loop_start()
except Exception as e:
    print(f"Could not connect to the broker {BROKER_ADDRESS}:{BROKER_PORT}, {e}")

# 阻塞主线程，以便程序不会立即退出
try:
    while True:
        # 等待用户中断
        pass
except KeyboardInterrupt:
    print("Exiting...")
    # 停止网络循环
    client.loop_stop()
    # 断开连接
    client.disconnect()

handle_user_and_card(789013,"张三","Math",0)

recharge_card(789013, 50)
query_card(789013)