mqtt消息保存到mysql

import paho.mqtt.client as mqtt
import mysql.connector

# MQTT连接参数
mqtt_broker = "mqtt.eclipse.org"
mqtt_port = 1883
mqtt_topic = "test/topic"

# MySQL连接参数
mysql_host = "localhost"
mysql_user = "user"
mysql_password = "password"
mysql_database = "testdb"

# 创建MySQL连接
db_connection = mysql.connector.connect(
    host=mysql_host,
    user=mysql_user,
    password=mysql_password,
    database=mysql_database
)
db_cursor = db_connection.cursor()

# 创建表
db_cursor.execute("""
CREATE TABLE IF NOT EXISTS mqtt_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")

# MQTT消息处理函数
def on_message(client, userdata, message):
    payload = message.payload.decode("utf-8")
    print(f"Received message: {payload}")
    
    # 将消息保存到MySQL数据库
    db_cursor.execute("INSERT INTO mqtt_messages (message) VALUES (%s)", (payload,))
    db_connection.commit()

# 创建MQTT客户端并连接到代理
client = mqtt.Client()
client.on_message = on_message
client.connect(mqtt_broker, mqtt_port, 60)
client.subscribe(mqtt_topic)

# 启动MQTT客户端循环
client.loop_forever()