钡铼技术PLC网关采集设备数据Python写入数据库

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采集并写入数据库

# 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 %dn", 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'
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 %dn", 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 %dn", 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()