I’m attempting to create a gateway script that will log all of the tags of a folder into the MySQL table on a time interval. This script does not report any errors when operating, but nothing appears in the SQL table. I’m stuck on what the issue is with the script and was hoping to find some help.
def logDB(tagPath):
import system
from datetime import datetime
tpName = str(tagPath)+'.Name'
#tpDoc = str(tagPath)+'.documentation'
#tpID = str(tagPath)+'.Tooltip'
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
TStampNow = system.db.dateFormat(now, "yyyy-MM-dd HH:mm:ss")
TStampNow_frmt = datetime.strptime(TStampNow, fmt)
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
#Desc = system.tag.read(tpDoc).value
#CatID = system.tag.read(tpID).value
# 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 ],["MySQL_Conn"])
tags = system.tag.browseTags("Hydraulic_Pressure")
for tag in tags:
logDB(tag.path)
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?
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.
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.
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.
You should be using system.tag.readBlocking() and system.tag.writeBlocking() in place of the now deprecated system.tag.read() and system.tag.write()
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
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?
Don’t make unnecessary type conversion calls. If the tag type is a float, then you’ll get a float value back.
Don’t make blanket except blocks, instead be specific about the error you’re trying to catch.
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)
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
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?
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