Gateway timer script to log folder of tags to SQL

Just a code comment, but can you avoid the imports by using system.date functions?
See system.date - Ignition User Manual 8.1 - Ignition Documentation. What are you really trying to do there?

I am not sure if it will make a difference, but try changing this:

system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ],["MySQL_Conn"])

To this:

system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ], datasources=["MySQL_Conn"])

It’s not getting stuck in the SF queue is it?

1 Like

Apologies my responses have been disappearing. It seems to do that when editing posts.

Here is the code based on comments above. I’ve realized a typo in SQL name but that did not fix it still.

def logDB(tagPath):
	import system
	tpName = str(tagPath)+'.Name'
	tpTStamp = str(tagPath)+'.LastChange'
	tpPrevValue = str(tagPath)+'.Tooltip'
	
	Name = system.tag.read(tpName).value
	Value = round(float(system.tag.read(tagPath).value),3)
	
	fmt = '%Y-%m-%d %H:%M:%S'
	#now = system.tag.read("Internal/DateTime_Now").value
	now = system.date.now()
	TStampNow = system.db.dateFormat(now, "yyyy-MM-dd HH:mm:ss")
	
	try:
		prevValue = round(float(system.tag.read(tpPrevValue).value),3)
	except:
		prevValue = Value
		system.tag.write(tpPrevValue,Value)
		
	if abs(prevValue) > 0.1: 
		val_diff = abs((Value - prevValue)/float(prevValue))
	else:
		val_diff = abs(Value - prevValue) # avoid divide by zero  

	# The following condition ensures that if the tag value becomes NULL due to loss of communications
	# then we do not log this tag to DB. 
	if((Value is not None) and  (val_diff > 0.1) ): 
		system.tag.write(tpPrevValue,Value)
		my_query = "INSERT INTO `ride_activity_trends` (`Name`, `TagValue`, `t_stamp`) VALUES (?,?,?) "
		print system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ], datasources=["MySQL"])

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag.path)

I’m fairly new to using Ignition so do not know how to check if this issue is occurring.

On the gateway > status > Store & Forward :
Aggregate Throughput shows as 0.0 per second. I suppose that means it is not making it through the S&F queue?

Also, I would check your wrapper log.

I don’t think that this:

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag.path)

Does what you’re expecting.

From the manual, system.db.browse() returns a list of tag dictionaries system.tag.browse - Ignition User Manual 8.1 - Ignition Documentation

So the proper way to write that code would be:

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag['path'])

There are other optimizations that could be suggested here, but I think this is the bug you’re looking for.

Just in the event this is useful (and perhaps you’re implementing it this way because you don’t have access to SQL Bridge module in an existing project, etc), I feel that I should mention consideration of SQL Bridge (Transaction Groups) as it is explicitly designed to do what you’re shooting for in script.

3 Likes

I will have a read, thank you!

I tried this change and it produced an error.

Thinking the data was not reaching store & forward, I removed the IF statement and it started writing to the SQL table. It seems that logic was not working how I expected it to.

I will have to re-evaluate that logic.

It would show in quarantine in S+F status. Normally, when scripting something new, I won’t use SF initially, until I know the script works. Also, if lots of data, I initially use a limit x (MariaDB) or top x (MS SQL) so I don’t choke the gateway with millions of rows.

What was the error?

Some things to consider when reworking your code:

  1. You should be using system.tag.readBlocking() and system.tag.writeBlocking() in place of the now deprecated system.tag.read() and system.tag.write()

  2. You should take advantage of the ability of these new functions to read and write multiple tags at the same time. I like to read all tags as soon as possible and write all tags as late as possible, doing all of the processing between those two calls

  3. You should consider condensing your insert query such that you insert all rows with one call to the db. There is a limit to how many rows you can insert in one call but why make 10 calls when you can make 1?

  4. Don’t make unnecessary type conversion calls. If the tag type is a float, then you’ll get a float value back.

  5. Don’t make blanket except blocks, instead be specific about the error you’re trying to catch.

  6. Don’t check for a value one way and then use it another way. Specifically checking a value for zero by using the absolute value. Consider:

val = -0.2

if abs(val) > 0.1:
    print round(val)
  1. print in a gateway script doesn’t necessarily output where you think it does. Consider using a logger.

Thank you for the advice! I will consider and try to use it to the best of my abilities.

So answer your question about changing the for loop to:

for tag in tags:
	logDB(tag['path'])

The following error was received.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 37, in TypeError: ‘com.inductiveautomation.ignition.common.script.builtin.ialabs.BrowseTag’ object is unsubscriptable

Huh, seems like they changed something between 8.0 and 8.1.

I believe in 8.0 this will do the trick then.

for tag in tags.getResults():
    logDB(tag['path'])

limit x (MariaDB) or top x (MS SQL)
I unfortunately have not come across what you’re referring to to understand it and it’s implementation. Would you mind describing it further?
Is this a function to write in the script or an S&F config?

It’s SQL.

It can be used to Limit the number of rows affected or returned by a query.

1 Like

The following produces the error below. line 38 is the for loop.

for tag in tags.getResults():
    logDB(tag['path'])

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 38, in AttributeError: ‘array.array’ object has no attribute ‘getResults’

EDIT: What specific version of Ignition are you using? That hasn’t been stated, yet.

getResults() should work regardless. Let’s start small and work our way up. This is a sample using my server, change to your tag path and see if we can print some values from the script console.

tagPath = '[default]Assembly/3425/Production'

# Browse for tags in a Folder
tags = system.tag.browse(tagPath, {'tagType' : 'AtomicTag'}).getResults()

# Initialize list of tags to read
tagReadList = []

# Add fullpath of each tag from the browse to the list 
for tag in tags:
	# fullpaths are objects so we must coerce them to strings.
	tagReadList.append(str(tag['fullPath']))

# Read the tags. Note that these are qualified values, not just the values.
qvalues = system.tag.readBlocking(tagReadList)

# Print the results. zip() lets us iterate through multiple lists at once.
for tagName, qvalue in zip(tagReadList, qvalues):
	value = qvalue.value
	t_stamp = qvalue.timestamp
	quality = qvalue.quality
	print tagName, value, t_stamp, quality

output

[default]Assembly/3425/Production/actual 696 Mon Jun 27 14:48:29 EDT 2022 Good
[default]Assembly/3425/Production/Efficiency 0.633879780769 Mon Jun 27 14:48:30 EDT 2022 Good
[default]Assembly/3425/Production/Efficiency 60 0.538461506367 Mon Jun 27 14:48:27 EDT 2022 Good
[default]Assembly/3425/Production/goal 1098 Mon Jun 27 14:48:13 EDT 2022 Good
[default]Assembly/3425/Production/total 769 Mon Jun 27 14:48:29 EDT 2022 Good
1 Like

Version 8.0.16
Your script worked using script console to run for a folder of tags.

output

[default]Hydraulic_Pressure/base1_acc_pressure 1438.41418457 Mon Jun 27 21:14:45 CEST 2022 Good
[default]Hydraulic_Pressure/base1_cb_pressure 1390.67675781 Mon Jun 27 21:14:57 CEST 2022 Good
[default]Hydraulic_Pressure/base2_acc_pressure 1452.05358887 Mon Jun 27 21:14:57 CEST 2022 Good
[default]Hydraulic_Pressure/base2_cb_pressure 1392.25048828 Mon Jun 27 21:14:57 CEST 2022 Good
[default]Hydraulic_Pressure/base3_acc_pressure 1448.38146973 Mon Jun 27 21:14:56 CEST 2022 Good
[default]Hydraulic_Pressure/base3_cb_pressure 1418.4798584 Mon Jun 27 21:14:56 CEST 2022 Good

Does the browse operation need to be recursive? In other words if the search finds a folder, do those tags also need to be included?

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