4G MQTT采集RS485协议传感器到数据库

1、Modbus slave设置从机Modbus TCP参数

设置Modbus TCP协议和地址

模拟软件设置的是当前电脑的，所以后面配置Modbus TCP的从机时，就要填当前电脑的ip地址，不要写 bl系列设备的ip地址

设置数据参数

设备地址：22
功能码：04
数据类型：16
起始地址：0
数量：5个

2、设置BL102将Modbus slave从机数据点映射

设置WAN口为Modbus TCP Master模式

设置bl102设备和从机同个网段

添加从机

从机的ip地址，需要填写Modbus slave软件的所在电脑的ip地址，不要填了 bl网关的ip地址

添加数据点

设置MQTT broker（MQTT服务器）信息

查看设备上下行状态

查看下行连接设备和上行连接MQTT服务器状态

3、MQTT客户端设置订阅

客户端设置连接MQTT服务器参数

服务器ip、端口、ID、心跳等。

设置订阅BL102设备的主题

刚才设置BL102设备的发布主题是 test，那么订阅也是 test

查看已收到BL102设备的数据

4、Python采集并写入数据库

其他语言也有对应MQTT客户端库，根据对应语言查找即可，这里演示用python。

# python3.6
import json
import sqlite3

import random
from datetime import datetime

from paho.mqtt import client as mqtt_client


broker = '192.168.1.165'
#broker = 'broker-cn.emqx.io'
port = 1883
topic = "test"
# generate client ID with pub prefix randomly
client_id = f'python-mqtt-{random.randint(0, 100)}'


def connect_mqtt() -> mqtt_client:
    def on_connect(client, userdata, flags, rc):
        if rc == 0:
            print("Connected to MQTT Broker!")
        else:
            print("Failed to connect, return code %d\n", rc)

    client = mqtt_client.Client(client_id)
    client.on_connect = on_connect
    client.connect(broker, port)
    return client


#将数据出入数据库
def create_table():
    #连接数据库，没有则创建
    conn = sqlite3.connect('plcdata') 
    print('连接数据库成功')
    
    #判断iot表是否存在，不存在则新建。 CREATE TABLE IF NOT EXISTS iot
    try:
        create_tb_cmd='''
        CREATE TABLE IF NOT EXISTS iot
        (lat TEXT,
        lng TEXT,
        signal_strength  INT,
        REG001 INT,
        REG002 INT,
        REG003 INT,
        REG004 INT,
        REG005 INT);
        '''
        #执行创建数据表语句
        conn.execute(create_tb_cmd)
    except:
        print("Create table failed")
        return False
    
    insert_dt_cmd='INSERT INTO iot (lat,lng,signal_strength,REG001,REG002,REG003,REG004,REG005) VALUES '+ strlist + ';'
    print(insert_dt_cmd)
#     insert_dt_cmd='''
#     INSERT INTO iot (lat,lng,signal_strength,REG001,REG002,REG003,REG004,REG005) VALUES (0.000000,0.000000,0,33,8538,234,22,9046);
#     '''
    
    conn.execute(insert_dt_cmd)
    print('写入数据库成功')
    conn.commit()
    conn.close()

strlist = ''
def subscribe(client: mqtt_client):
    def on_message(client, userdata, msg):
        t = datetime.now()
        print('当前时间：',t)
        #print(f"Received `{msg.payload.decode()}` from `{msg.topic}` topic")
        json_data = msg.payload.decode() #解析订阅主题信息，
#         print(type(json_data)) #判断data类型，是json字符串
        dict_json = json.loads(json_data)
        #print(type(dict_json))
        #print(dict_json)
        
        sensordatas = dict_json['sensorDatas']
        list_data =[]
        for i in sensordatas:
            #print(type(i),i)
            #print(i['flag'],type(i['flag']))


            if i['flag'] == 'GPS':
                list_data.append(i['lat'])
                list_data.append(i['lng'])
                #print(list_data)

            else:
                #print(i['flag'],i['value']) 
                list_data.append(i['value'])
                #print(list_data)
        global strlist
        strlist = str(tuple(list_data))
        print(strlist)
        create_table()
        
        
    client.subscribe(topic)
    client.on_message = on_message


def run():
    client = connect_mqtt()
    subscribe(client)
    client.loop_forever()


if __name__ == '__main__':
    run()

其他：python发布和订阅

发布

# python 3.6

import random
import time

from paho.mqtt import client as mqtt_client


#broker = '192.168.1.165'
broker = 'broker-cn.emqx.io'
port = 1883
topic = "test"
# generate client ID with pub prefix randomly
client_id = f'python-mqtt-{random.randint(0, 1000)}'


def connect_mqtt():
    def on_connect(client, userdata, flags, rc):
        if rc == 0:
            print("Connected to MQTT Broker!")
        else:
            print("Failed to connect, return code %d\n", rc)

    client = mqtt_client.Client(client_id)
    client.on_connect = on_connect
    client.connect(broker, port)
    return client


def publish(client):
    msg_count = 0
    while True:
        time.sleep(1)
        msg = f"messages: {msg_count}"
        result = client.publish(topic, msg)
        # result: [0, 1]
        status = result[0]
        if status == 0:
            print(f"Send `{msg}` to topic `{topic}`")
        else:
            print(f"Failed to send message to topic {topic}")
        msg_count += 1


def run():
    client = connect_mqtt()
    client.loop_start()
    publish(client)


if __name__ == '__main__':
    run()

# python3.6

import random
from datetime import datetime

from paho.mqtt import client as mqtt_client


#broker = '192.168.1.165'
broker = 'broker-cn.emqx.io'
port = 1883
topic = "test"
# generate client ID with pub prefix randomly
client_id = f'python-mqtt-{random.randint(0, 100)}'


def connect_mqtt() -> mqtt_client:
    def on_connect(client, userdata, flags, rc):
        if rc == 0:
            print("Connected to MQTT Broker!")
        else:
            print("Failed to connect, return code %d\n", rc)

    client = mqtt_client.Client(client_id)
    client.on_connect = on_connect
    client.connect(broker, port)
    return client


def subscribe(client: mqtt_client):
    def on_message(client, userdata, msg):
        t = datetime.now()
        print('当前时间：',t)
        print(f"Received `{msg.payload.decode()}` from `{msg.topic}` topic")
        

    client.subscribe(topic)
    client.on_message = on_message


def run():
    client = connect_mqtt()
    subscribe(client)
    client.loop_forever()


if __name__ == '__main__':
    run()

文章来源地址https://uudwc.com/A/vmpVj