Gateway timer script to log folder of tags to SQL

Ignition
,
25

Taking some inspiration from @JordanCClark I did my best to refactor your code to accomplish what you’re attempting to do.

First here’s a little helper function which comes in handy for things like this. Technically you don’t need a function, but I find that grouper(2,zip(list1,list2)) is much more readable than
zip(*[iter(zip(list1,list2))] * 2). Some may disagree.

def grouper(n, iterable):
    return zip(*[iter(iterable)] * n)

This will group elements of an iterable in into groups of ‘n’ size. So
grouper(2,'ABCDEF') --> [('A','B'),('C','D'),('E','F')]

Here is what I came up with:

import numbers

def logDB_new(tags):
	tagReadList = []
	
	# Add fullpath of each tag, and each tag's Tooltip from the browse to the list.
	for tag in tags:
		tagReadList.append(str(tag['fullPath']))
		tagReadList.append(str(tag['fullPath']) + '.Tooltip')
	
	#Now that we have all of the paths we want, read all of the values in one go.
	qValues = system.tag.readBlocking(tagReadList)
	
	#create lists to hold the paths and values that we want to write after processing.
	writeList = []
	writeValues = []
	
	#create the base part of the query string.  This part will not change.
	query = "INSERT INTO 'ride_activeity_trends' ('Name','TagValue','t_stamp') VALUES "
	#create a list to hold the values to be inserted into the table.
	queryVals = []
	
	#Loop through the current and previous values.
	#The ZIP function puts the paths with the values, and the grouper function puts the currentPath and Value
	#together with the prevPath and value.  So you get something like (((currentPath,currentValue),(prevPath,prevValue)),...)
	for currentValues, prevValues in grouper(2,zip(tagReadList, qValues)):
		name, curQValue = currentValues  #break apart the currentValues into the path(name) and current qualified value
		prevName, prevQValue = prevValues#break apart the prevValues into the path(prevName) and previous qualified value
		
		#this insures that the value is a type of number. This prevents an error from trying to round a string or some other non-numeric type.		
		if isinstance(prevQValue.value,numbers.Number):
			
			prevValue = round(prevQValue.value,3)
			curValue = curQValue.value
			
			#if the absolute value of prevValue is less then 0.1 or the current value is None then move on to the next itteration
			if abs(prevValue) <= 0.1 or not curValue:
				continue
			
			val_diff = abs((curQValue.value - prevValue)/prevValue)
			
			if val_diff > 0.1:
				#note that this is concatenating two lists resulting in a single list
				queryVals += [name,curValue,system.date.now()]
				#add the prevValue name and value to the lists to be written.
				writeList.append(prevName)
				writeValues.append(curQValue.value)
				
	#now that we have processed all of the tags we can construct the query.
	#the str.join() function returns a string by joining all elements of an iterable
	#the ['(?,?,?)'] * (len(queryVals) / 3 will dynamically create a list of ['(?,?,?)','(?,?,?)',...]
	#the number of elements in that list will be the length of queryVals divided by 3.
	#This will result in the syntax needed to insert the rows for all tags in one call.		
	query += ','.join(['(?,?,?)'] * (len(queryVals) / 3))
	
	#only run the query if queryVals is not empty.
	if queryVals:
		system.db.runSFPrepUpdate(query,queryVals,datasources=['MySQL'])
	
	#write all of the tags in one go.
	system.tag.writeBlocking(writeList,writeValues)

tagPath = '[default]Hydraulic_Pressure'

tags = system.tag.browse(tagPath,{'tagType' : 'AtomicTag'}).getResults()

logDB_new(tags)
1 Like