如何将MQTT Mosquitto发布事件存储到MySQL中？

最新推荐文章于 2026-01-06 00:09:57 发布

原创最新推荐文章于 2026-01-06 00:09:57 发布 · 1.6k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了一种利用Paho MQTT库与MySQL数据库整合的方法，通过实时收集气象站数据并将其发布到MQTT Broker，再由Python脚本接收并保存到MariaDB中，实现了数据的有效流转。

1 介绍

如果您熟悉Python，那么Paho MQTT库很简单，可以节省资源，并且可以与Mosquitto很好地交互。要使用它，只需订阅该主题并设置一个回调即可使用peewee将有效负载传递给MySQL

2 需求

用pywws实时收集气象站数据
使用pywws.service.mqtt发布到mqtt-Broker
NAS上的python-script收集数据并写入MariaDB

3 代码

#!/usr/bin/python -u

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

mariadb_connection = mariadb.connect(user='USER', password='PW', database='MYDB')
cursor = mariadb_connection.cursor()

# MQTT Settings
MQTT_Broker = "192.XXX.XXX.XXX"
MQTT_Port = 1883
Keep_Alive_Interval = 60
MQTT_Topic = "/weather/pywws/#"

# Subscribe
def on_connect(client, userdata, flags, rc):
mqttc.subscribe(MQTT_Topic, 0)

def on_message(mosq, obj, msg):
# Prepare Data, separate columns and values
msg_clear = msg.payload.translate(None, '{}""').split(", ")
msg_dict = {}
for i in range(0, len(msg_clear)):
msg_dict[msg_clear[i].split(": ")[0]] = msg_clear[i].split(": ")[1]

# Prepare dynamic sql-statement
placeholders = ', '.join(['%s'] * len(msg_dict))
columns = ', '.join(msg_dict.keys())
sql = "INSERT INTO pws ( %s ) VALUES ( %s )" % (columns, placeholders)

# Save Data into DB Table
try:
cursor.execute(sql, msg_dict.values())
except mariadb.Error as error:
print("Error: {}".format(error))
mariadb_connection.commit()

def on_subscribe(mosq, obj, mid, granted_qos):
pass

mqttc = mqtt.Client()

# Assign event callbacks
mqttc.on_message = on_message
mqttc.on_connect = on_connect
mqttc.on_subscribe = on_subscribe

# Connect
mqttc.tls_set(ca_certs="ca.crt", tls_version=ssl.PROTOCOL_TLSv1_2)
mqttc.connect(MQTT_Broker, int(MQTT_Port), int(Keep_Alive_Interval))

# Continue the network loop & close db-connection
mqttc.loop_forever()
mariadb_connection.close()