Pymodbus read and save to database

[stewietopg]

Hello,

in our company we will install multiple energy meters (abb m4m ethernet 20). This energy meter works via modbus tcp/ip.

I have a working python script for reading registers but i would like to know how can i save values to mysql database? I want to save each measurement for every meter (we will have 25 of them).

Also if anyone has any better code for reading values from all the meters at once please tell me.

DB table - meter:

measurement_id  
meter - name of meter (M01, M02,...)
power 
energy 
date_of_reading

Current code:

import mysql.connector
from pymodbus.client import ModbusTcpClient
from datetime import datetime

mydb = mysql.connector.connect(
  host="192.168.25.2",
  user="xxxx",
  password="xxxx",
  database="energetics"
)

mycursor = mydb.cursor()

M01 = '192.168.30.156' #LOCATION 1
M02 = '192.168.22.95' #LOCATION 2

ips = [M01, M02]

date_of_reading = datetime.now()

meters = []

for ip in ips:
    meters.append(ModbusTcpClient(ip))

    power_register = []
    energy_register = []

for meter in meters:
    power_register.append(meter.read_holding_registers(23322, 2))
    energy_register.append(meter.read_holding_registers(20480, 4))

    power_calculation = []
    energy_calculation = []

for i in range(len(meters)):
    power_calculation.append(round(((power_register[i].registers[0] * pow(2, 16) + power_register[i].registers[1]) * 0.01 / 1000), 2))
    energy_calculation.append(round(((energy_register[i].registers[2] * pow(2, 16) + energy_register[i].registers[3]) * 0.01), 2))

for i in range(len(meters)):
	sql = ("INSERT INTO meter (meter, power, energy, date_of_reading) VALUES (%s, %s, %s, %s)",
    (meters[i], power_calculation[i], energy_calculation[i], date_of_reading))

mycursor.execute(*sql)

mydb.commit()

print("Success !")

mycursor.close()

This is the code but it works only until part where i want to save to sql.

[rob101]

[removed].

Sorry, I'm wrong, but you may want to do a so-called 'print debug': print(*sql) before the execute, so that you can see how it will be iterated by the *sql

[buran]

for i in range(len(meters)):
    sql = ("INSERT INTO meter (meter, power, energy, date_of_reading) VALUES (%s, %s, %s, %s)",
    (meters[i], power_calculation[i], energy_calculation[i], date_of_reading))
 
mycursor.execute(*sql)

This will insert just the values for the last meter. You either move the execute inside the loop, or look at changing your code to using executemany

---

Something like this (code not tested):

import mysql.connector
from pymodbus.client import ModbusTcpClient
from datetime import datetime

 
M01 = '192.168.30.156' #LOCATION 1
M02 = '192.168.22.95' #LOCATION 2
 
ips = [M01, M02]
 
date_of_reading = datetime.now()
meters = [ModbusTcpClient(ip) for ip in ips]
readings = []
 
for meter in meters:
    power_register = meter.read_holding_registers(23322, 2)
    energy_register = meter.read_holding_registers(20480, 4)
    power_calculation = round((power_register.registers[0] * pow(2, 16) + power_register.registers[1]) * 0.01 / 1000, 2)
    energy_calculation = round((energy_register.registers[2] * pow(2, 16) + energy_register.registers[3]) * 0.01, 2)
    reading = meter, power_calculation, energy_calculation, date_of_reading
    readings.append(reading)
 
sql = "INSERT INTO meter (meter, power, energy, date_of_reading) VALUES (%s, %s, %s, %s)"

mydb = mysql.connector.connect(
  host="192.168.25.2",
  user="xxxx",
  password="xxxx",
  database="energetics"
)
 
mycursor = mydb.cursor()
mycursor.executemany(sql, readings)
mydb.commit()
mycursor.close()

print("Success !")

From here you can improve the code - using e.g. named tuple for readings, make your code use functions and/or OOP, etc.

[stewietopg]

for i in range(len(meters)):
    sql = ("INSERT INTO meter (meter, power, energy, date_of_reading) VALUES (%s, %s, %s, %s)",
    (meters[i], power_calculation[i], energy_calculation[i], date_of_reading))
 
mycursor.execute(*sql)

This will insert just the values for the last meter. You either move the execute inside the loop, or look at changing your code to using executemany

---

Something like this (code not tested):

import mysql.connector
from pymodbus.client import ModbusTcpClient
from datetime import datetime

 
M01 = '192.168.30.156' #LOCATION 1
M02 = '192.168.22.95' #LOCATION 2
 
ips = [M01, M02]
 
date_of_reading = datetime.now()
meters = [ModbusTcpClient(ip) for ip in ips]
readings = []
 
for meter in meters:
    power_register = meter.read_holding_registers(23322, 2)
    energy_register = meter.read_holding_registers(20480, 4)
    power_calculation = round((power_register.registers[0] * pow(2, 16) + power_register.registers[1]) * 0.01 / 1000, 2)
    energy_calculation = round((energy_register.registers[2] * pow(2, 16) + energy_register.registers[3]) * 0.01, 2)
    reading = meter, power_calculation, energy_calculation, date_of_reading
    readings.append(reading)
 
sql = "INSERT INTO meter (meter, power, energy, date_of_reading) VALUES (%s, %s, %s, %s)"

mydb = mysql.connector.connect(
  host="192.168.25.2",
  user="xxxx",
  password="xxxx",
  database="energetics"
)
 
mycursor = mydb.cursor()
mycursor.executemany(sql, readings)
mydb.commit()
mycursor.close()

print("Success !")

From here you can improve the code - using e.g. named tuple for readings, make your code use functions and/or OOP, etc.

Thank you for this, i've test it and it's working ! This is my first time with modbus and python and i'm not familiar with functions and OOP but i will try to implement them... i'm learning slowly