-
Notifications
You must be signed in to change notification settings - Fork 3
/
save_modbus_to_db.py
113 lines (95 loc) · 3.58 KB
/
save_modbus_to_db.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
#!/usr/bin/env python3
# MIT License
#
# Copyright (c) 2020 aattww (https://github.com/aattww/)
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
# Simple example (very simplified) Python script to read data from a Sensors gateway and save
# it to MySQL database. Use this as a base for your own needs.
#
# Remember to change the correct port and slave address and database settings below before use.
#
# Also remember to install the needed libraries:
# minimalmodbus: https://pypi.org/project/minimalmodbus/
# mysql.connector: https://dev.mysql.com/doc/connector-python/en/
##################
# BEGIN SETTINGS #
##################
# Serial settings
serial_device = "/dev/serial0" # This should work with Raspberry if gateway is connected directly to headers
modbus_address = 1 # Gateway slave address
# Database settings
db_user = "user"
db_password = "password"
db_host = "localhost"
db_database = "database"
################
# END SETTINGS #
################
import minimalmodbus
import mysql.connector
from datetime import datetime
# Helper function to convert 16 bit unsigned values from gateway to signed 16 bit values
# so that negative values are handled correctly.
#
# value: unsigned value to be converted
#
# returns: signed value
#
def to_signed(value):
if (value >= 2**15):
return (value - 2**16)
else:
return (value)
# Function to save one value to database.
#
# Automatically adds current date and time.
#
# table: database table name
# column: database column
# value: value to be saved
#
# returns: no
#
def save_to_db(table, column, value):
try:
cnx = mysql.connector.connect(user=db_user, password=db_password, host=db_host, database=db_database)
cursor = cnx.cursor()
dt = datetime.now().replace(second=0, microsecond=0)
add_reading = ("INSERT INTO " + table + "(time, " + column + ") VALUES (%s, %s) ON DUPLICATE KEY UPDATE time=VALUES(time), " + column + "=VALUES(" + column + ")")
data_reading = (dt, str(value))
cursor.execute(add_reading, data_reading)
cnx.commit()
except Exception as e:
print(e)
finally:
cursor.close()
cnx.close()
gateway = minimalmodbus.Instrument(serial_device, modbus_address)
gateway.serial.baudrate = 38400
# Read node 1 modbus data
try:
mb_data = gateway.read_registers(100, 8)
# If we had correct amount of data, save it to database
if len(mb_data) == 8:
save_to_db("node_1", "temperature", to_signed(mb_data[5]))
save_to_db("node_1", "humidity", to_signed(mb_data[6]))
save_to_db("node_1", "pressure", to_signed(mb_data[7]))
except Exception:
print("Error saving node 1 data to database")