KeyError: 2

[stsxbel]

Good evening, the second day I struggle with the code, then there is no entry in the database, then the error is KeyError: 2

import struct
import sys
import time
import datetime
import pymysql.cursors
from pymodbus.client.sync import ModbusSerialClient
from pymodbus.exceptions import ModbusIOException
from datetime import datetime

connection = pymysql.connect(host='127.0.0.1', user='root', password='passwd', db='scales', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

unit = 5
client = ModbusSerialClient(method='rtu', port='/dev/ttyUSB1', parity='N', baudrate=9600, bytesize=8, stopbits=1, timeout=1, strict=False)


#---------------------------Продукт 1-------------------------
 
while True:
	reply = client.write_registers(52, 1, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result1 = request.registers
	print(result1)

	reply = client.write_registers(52, 2, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result2 = request.registers
	print(result2)

	reply = client.write_registers(52, 3, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result3= request.registers
	print(result3)
	
	reply = client.write_registers(52, 1, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result4 = request.registers
	print(result4)

	reply = client.write_registers(52, 2, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result5 = request.registers
	print(result5)

	reply = client.write_registers(52, 3, unit=unit) 
	reply = client.write_registers(53, 1, unit=unit) 
	reply = client.write_registers(5, 2020, unit=unit) #2020 - Total managment
	request = client.read_holding_registers(51, 1, unit=unit)
	result6= request.registers
	print(result6)
	if (result1 == result4 and result2 == result5 and result3 == result6):
		
		written_as = result1[0] * 0.1, result2[0] * 0.1, result3[0] * 0.1
		print(result1, result2, result3)
	    
#---------Write DB--------------------
		with connection: 
 
			cur = connection.cursor()
			cur.execute("SELECT * FROM wl5 ORDER BY id DESC LIMIT 1")
 
			rows = cur.fetchall()
 
			for row in rows:

			
				print("{0}".format(row[2]))
				print("{0}".format(row[3]))
				print("{0}".format(row[4]))
			
				try:
					a1 =  float(result1[0] * 0.1)
					b1 =  float(row[2]) 
					silos14 = b1 - a1 - bool(b1 > a1) * 6553.5
					print ("Расход:"f'{silos14:.1f}')

					a2 =  float(result2[0] * 0.1)
					b2 =  float(row[3]) 
					silos15 = b2 - a2 - bool(b2 > a2) * 6553.5
					print ("Расход:"f'{silos15:.1f}')

					a3 =  float(result3[0] * 0.1)
					b3 =  float(row[4])
					silos16 = b3 - a3 - bool(b3 > a3) * 6553.5
					print ("Расход:"f'{silos16:.1f}')

				except:
#---------write db result-----
					with connection.cursor() as cursor:
        # Create a new record
						sql = "INSERT INTO `line5` (`date`, `silos14`, silos15, silos16) VALUES (%s, %s, %s, %s)"
						today = datetime.datetime.today()
						newHireDate = today.strftime("%Y-%m-%d-%H.%M")
						cursor.execute(sql, (newHireDate, silos14, silos15, silos16))


#------Write db wl5-------
						sql = "INSERT INTO `wl5` (`date`, `product1`, `product2`, `product3`) VALUES (%s, %s, %s, %s)"
						today = datetime.datetime.today()
						newHireDate = today.strftime("%Y-%m-%d-%H.%M")
						cursor.execute(sql, (newHireDate, result1[0] * 0.1, result2[0] * 0.1,result3[0] * 0.1))

    			connection.commit()

 	
		break

Traceback (most recent call last):
File "/home/pokip/test.py", line 76, in <module>
print("{0}".format(row[2]))
KeyError: 2

[deanhystad]

Your error message says that "row" is a dictionary and 2 is not a key in the dictionary. I would print "rows" to see what was returned.

To get where your program throws a key error means that you must have something in the database.

[ibreeden]

Apparently "row" does not contain what you expect. So in such cases always add a print statement just before the line where the error occurs.

print(row)

What does it show?


Furtheron it is good practice to mention the columns you want instead of "select * from ...". Now you choose all columns and only use row[2], row[3] and row[4]. I is better to specify the columns by name so you can be sure you can use row[0], row[1] and row[2]

[stsxbel]

Apparently "row" does not contain what you expect. So in such cases always add a print statement just before the line where the error occurs.

print(row)

What does it show?


Furtheron it is good practice to mention the columns you want instead of "select * from ...". Now you choose all columns and only use row[2], row[3] and row[4]. I is better to specify the columns by name so you can be sure you can use row[0], row[1] and row[2]

{'id': 1, ‘date’: ‘22.05.2022’, ‘product1’: Decimal('0.0'), ‘product2’: Decimal('3.0'), ‘product3’: Decimal('0.0')}
Traceback (most recent call last):
File “/home/pokip/test.py”, line 77, in <module>
print(“{0}”.format(row))
KeyError: 3

[deanhystad]

According to your print row is a dictioany, not a list. You can use row["id"] or row["date"] or row["product1"]. You cannot do row[2] or row[3]. Maybe that should be row["product2"] and row["product3].

Why do you do this "print("{0}".format(row[2]))" when it does the exact same thing as "print(row[2])"?

[stsxbel]

According to your print row is a dictioany, not a list. You can use row["id"] or row["date"] or row["product1"]. You cannot do row[2] or row[3]. Maybe that should be row["product2"] and row["product3].

Why do you do this "print("{0}".format(row[2]))" when it does the exact same thing as "print(row[2])"?

Traceback (most recent call last):
  File "/home/pokip/test.py", line 76, in <module>
    print(row[2])
KeyError: 2

[stsxbel]

Thanks, figured it out. You need to use row["product1"]. But the problem remained in another :-( everything works as it should, only the result is not written to the database for some reason :-(

[15877]
[2178]
[15877]
[34338]
[2178]
[15877]
[34338]
[2178]
[15877]
[34338]
[2178]
[15877]
[34338] [2178] [15877]
0.0
3.0
0.0
load1:-3433.8
load2:-214.8
load3:-1587.7

[deanhystad]

That would be because you never execute an code that tries to add items to the database. This only gets called if an exception is raised.

                    with connection.cursor() as cursor:
        # Create a new record
                        sql = "INSERT INTO `line5` (`date`, `silos14`, silos15, silos16) VALUES (%s, %s, %s, %s)"
                        today = datetime.datetime.today()
                        newHireDate = today.strftime("%Y-%m-%d-%H.%M")
                        cursor.execute(sql, (newHireDate, silos14, silos15, silos16))
 
 
#------Write db wl5-------
                        sql = "INSERT INTO `wl5` (`date`, `product1`, `product2`, `product3`) VALUES (%s, %s, %s, %s)"
                        today = datetime.datetime.today()
                        newHireDate = today.strftime("%Y-%m-%d-%H.%M")
                        cursor.execute(sql, (newHireDate, result1[0] * 0.1, result2[0] * 0.1,result3[0] * 0.1))

Get rid of the try/except and see what happens.

[stsxbel]

oops. Thank you. Everything is working :-)