鋇錸技術(shù)PLC網(wǎng)關(guān)采集設(shè)備數(shù)據(jù)Python寫(xiě)入數(shù)據(jù)庫(kù)

1、Modbus slave設(shè)置從機(jī)Modbus TCP參數(shù)

設(shè)置Modbus TCP協(xié)議和地址

模擬軟件設(shè)置的是 當(dāng)前電腦的，所以后面 配置Modbus TCP的從機(jī)時(shí)，就要填當(dāng)前電腦的ip地址，不要寫(xiě) bl系列設(shè)備的ip地址

設(shè)置數(shù)據(jù)參數(shù)

設(shè)備地址：22

功能碼：04

數(shù)據(jù)類(lèi)型：16

起始地址：0

數(shù)量：5個(gè)

2、設(shè)置BL102將Modbus slave從機(jī)數(shù)據(jù)點(diǎn)映射

設(shè)置WAN口為Modbus TCP Master模式

設(shè)置bl102設(shè)備和從機(jī)同個(gè)網(wǎng)段

添加從機(jī)

從機(jī)的ip地址，需要填寫(xiě)Modbus slave軟件的所在電腦的ip地址，不要填了 bl網(wǎng)關(guān)的ip地址

添加數(shù)據(jù)點(diǎn)

設(shè)置MQTT broker（MQTT服務(wù)器）信息

查看設(shè)備上下行狀態(tài)

查看下行連接設(shè)備和上行連接MQTT服務(wù)器狀態(tài)

3、MQTT客戶(hù)端設(shè)置訂閱

客戶(hù)端設(shè)置連接MQTT服務(wù)器參數(shù)

服務(wù)器ip、端口、ID、心跳等。

設(shè)置訂閱BL102設(shè)備的主題

剛才設(shè)置BL102設(shè)備的發(fā)布主題是 test，那么訂閱也是 test

查看已收到BL102設(shè)備的數(shù)據(jù)

4、Python采集并寫(xiě)入數(shù)據(jù)庫(kù)

# 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

#將數(shù)據(jù)出入數(shù)據(jù)庫(kù)
def create_table():
#連接數(shù)據(jù)庫(kù)，沒(méi)有則創(chuàng)建
conn = sqlite3.connect('plcdata')
print('連接數(shù)據(jù)庫(kù)成功')

#判斷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);
'''
#執(zhí)行創(chuàng)建數(shù)據(jù)表語(yǔ)句
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('寫(xiě)入數(shù)據(jù)庫(kù)成功')
conn.commit()
conn.close()

strlist = ''
def subscribe(client: mqtt_client):
def on_message(client, userdata, msg):
t = datetime.now()
print('當(dāng)前時(shí)間：',t)
#print(f"Received `{msg.payload.decode()}` from `{msg.topic}` topic")
json_data = msg.payload.decode() #解析訂閱主題信息，
# print(type(json_data)) #判斷data類(lèi)型，是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發(fā)布和訂閱
發(fā)布

# 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('當(dāng)前時(shí)間：',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()