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)