Python Mqtt+Mysql+Json处理简单数据初试

目的：
接收Topic为mqttqc的Json数据，读取client值，并把相应内容提交至mysql数据库

#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql
import json
import paho.mqtt.client as mqtt

import logging

host = "mqtt.eclipse.org"
client_id = "yk003"
keepalive = 60
port = 1883
password = "123456"
topic = "mqttqc"
username = "yk003"
verbose = False
mysqlhost="localhost"
mysqluser="python"
mysqlpassword="python" 
mysqlport=3306
mysqldatabase="python"
mysqlcharset='utf8'
#增加数据库记录的函数
def mysql_add(mqttc,msg):
    db = pymysql.connect(mysqlhost, 
                     mysqluser, 
                     mysqlpassword, 
                     port=mysqlport,# 端口  
                     database=mysqldatabase, 
                     charset=mysqlcharset)
    cursor = db.cursor()
    payload=str(msg.payload,encoding='utf-8')
    jsonpayload=json.loads(payload)
    sql = """INSERT INTO ceshi(clientid,
         topic, payload)
         VALUES ('"""+jsonpayload['clientid']+"""', '"""+msg.topic+"""','"""+payload+"""')"""
    print(sql)
    try:
        cursor.execute(sql)
    # 提交到数据库执行
        db.commit()
    except:
    # 发生错误时回滚
        db.rollback()
    cursor.close()
    db.close()
# 连接的回调函数
def on_connect(mqttc, obj, flags, rc):
    print(f"Connected with result code {rc}")
   
    
# 收到消息的回调函数
def on_message(mqttc, obj, msg):
    print(msg.topic+" "+str(msg.payload))
    mysql_add(mqttc,msg)
debug = False

logging.basicConfig(level=logging.DEBUG)
mqttc = mqtt.Client(client_id)
mqttc.username_pw_set(username, password)
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)
mqttc.on_message = on_message
mqttc.on_connect = on_connect
#mqttc.on_publish = on_publish
#mqttc.on_subscribe = on_subscribe
mqttc.enable_logger(logger)
mqttc.connect("127.0.0.1", 1883, 60)
mqttc.subscribe("mqttqc", 0)
mqttc.loop_forever()

测试结果

[root@localhost lqj]# python mqtt.py
DEBUG:__main__:Sending CONNECT (u1, p1, wr0, wq0, wf0, c1, k60) client_id=b'yk003'
DEBUG:__main__:Sending SUBSCRIBE (d0, m1) [(b'mqttqc', 0)]
DEBUG:__main__:Received CONNACK (0, 0)
Connected with result code 0
DEBUG:__main__:Received SUBACK
DEBUG:__main__:Received PUBLISH (d0, q0, r0, m0), 'mqttqc', ...  (32 bytes)
mqttqc b'{\n    "clientid": "9087654312"\n}'
INSERT INTO ceshi(clientid,
         topic, payload)
         VALUES ('9087654312', 'mqttqc','{
    "clientid": "9087654312"
}')
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP
DEBUG:__main__:Sending PINGREQ
DEBUG:__main__:Received PINGRESP