Pulling SQL Query Data into Tags

Ignition
, ,
1

Hello Everyone,

I am pretty new to ignition and scripting in general and have been trying to get this to work for a few hours now and I am not sure what I am doing wrong. Basically I am running a named query to pull data out of a SQL database. I need that data to then be written to multiple tags (19 in total). This is what i have so far but I am struggling

def runAction(self, event):
	values =[]
	data = system.db.runNamedQuery("Read_Motor_Data" , {"Motor_Model":self.getSibling("TextField_13").props.text})
	tagPath=['[Test_Well]Test_Information/Motor_Make','[Test_Well]Test_Information/Motor_Model','[Test_Well]Test_Information/Motor_Part_Number','[Test_Well]VSD_Tags/Motor_Nominal_Power','[Test_Well]VSD_Tags/Motor_Pole_Count','[Test_Well]VSD_Tags/Nominal_Motor_Current','[Test_Well]VSD_Tags/Motor_Thermal_Current','[Test_Well]VSD_Tags/Current_Limit','[Test_Well]VSD_Tags/Nominal_Motor_Voltage','[Test_Well]VSD_Tags/Nominal_Motor_Freq','[Test_Well]VSD_Tags/Nominal_Motor_Speed','[Test_Well]VSD_Tags/Motor_Power_Factor_PF1','[Test_Well]VSD_Tags/Motor_Power_Factor_PF2','[Test_Well]VSD_Tags/Motor_Eff_1_1','[Test_Well]VSD_Tags/Motor_Eff_3_4','[Test_Well]Test_Information/Motor_Diameter','[Test_Well]Test_Information/Min_Motor_Cooling','[Test_Well]VSD_Tags/Accel_Time','[Test_Well]VSD_Tags/Decel_Time']
	
	#length = data.getColumnCount()
	for row in data:
		for col in data:
			values.append(row[1])
	system.tag.writeBlocking(tagPath,values)

I am passing a value from a drop down box in, which is the motor model, into the query and it then gives me the parameters for that motor model (This is confirmed by using a table). From what I have worked out I believe I need to strip the data out of the dataset as I only need the values and not the column titles. This does not seem to be working for me. Can anyone please point me in the right direction?

Kind Regards,
Sam Logan

2

Hi Sam,

Datasets have a built-in function (not very well documented, unless you dig into the javadocs) called getColumnAsList(). Assuming the data comes in a tall format, and the row from the query are consistent, that should do what you're looking for.

def runAction(self, event):
	values =[]
	data = system.db.runNamedQuery("Read_Motor_Data" , {"Motor_Model":self.getSibling("TextField_13").props.text})
	tagPath=['[Test_Well]Test_Information/Motor_Make','[Test_Well]Test_Information/Motor_Model','[Test_Well]Test_Information/Motor_Part_Number','[Test_Well]VSD_Tags/Motor_Nominal_Power','[Test_Well]VSD_Tags/Motor_Pole_Count','[Test_Well]VSD_Tags/Nominal_Motor_Current','[Test_Well]VSD_Tags/Motor_Thermal_Current','[Test_Well]VSD_Tags/Current_Limit','[Test_Well]VSD_Tags/Nominal_Motor_Voltage','[Test_Well]VSD_Tags/Nominal_Motor_Freq','[Test_Well]VSD_Tags/Nominal_Motor_Speed','[Test_Well]VSD_Tags/Motor_Power_Factor_PF1','[Test_Well]VSD_Tags/Motor_Power_Factor_PF2','[Test_Well]VSD_Tags/Motor_Eff_1_1','[Test_Well]VSD_Tags/Motor_Eff_3_4','[Test_Well]Test_Information/Motor_Diameter','[Test_Well]Test_Information/Min_Motor_Cooling','[Test_Well]VSD_Tags/Accel_Time','[Test_Well]VSD_Tags/Decel_Time']
	
	values = data.getColumnAsList(1)
	system.tag.writeBlocking(tagPath,values)
1 Like
3

And to make it all a bit more readable and easily maintained, here are some free line breaks.

def runAction(self, event):
	values = []
	data = system.db.runNamedQuery("Read_Motor_Data", 
		{"Motor_Model": self.getSibling("TextField_13").props.text}
	)
	tagPath=['[Test_Well]Test_Information/Motor_Make',
		'[Test_Well]Test_Information/Motor_Model',
		'[Test_Well]Test_Information/Motor_Part_Number',
		'[Test_Well]VSD_Tags/Motor_Nominal_Power',
		'[Test_Well]VSD_Tags/Motor_Pole_Count',
		'[Test_Well]VSD_Tags/Nominal_Motor_Current',
		'[Test_Well]VSD_Tags/Motor_Thermal_Current',
		'[Test_Well]VSD_Tags/Current_Limit',
		'[Test_Well]VSD_Tags/Nominal_Motor_Voltage',
		'[Test_Well]VSD_Tags/Nominal_Motor_Freq',
		'[Test_Well]VSD_Tags/Nominal_Motor_Speed',
		'[Test_Well]VSD_Tags/Motor_Power_Factor_PF1',
		'[Test_Well]VSD_Tags/Motor_Power_Factor_PF2',
		'[Test_Well]VSD_Tags/Motor_Eff_1_1',
		'[Test_Well]VSD_Tags/Motor_Eff_3_4',
		'[Test_Well]Test_Information/Motor_Diameter',
		'[Test_Well]Test_Information/Min_Motor_Cooling',
		'[Test_Well]VSD_Tags/Accel_Time',
		'[Test_Well]VSD_Tags/Decel_Time'
	]
	
	values = data.getColumnAsList(1)
	system.tag.writeBlocking(tagPath, values)
4

@JordanCClark

Thank you for your reply. I have implement it and I am still having issues. I am getting the below error.

File "<function:runAction>", line 8, in runAction ValueError: Length of values does not match length of paths.

The data that I am getting in is a single row that is 19 columns long. From doing some debugging printing to the console as to the length of values after it has run, it is only showing it as 1, which I am guessing is causing the above. Apparently getRowAsList does not exist. Do I need to add this into a for loop and cycle through each one?

Kind Regards,
Sam Logan

5

Hey everyone,

I have managed to sort it. I was not doing a few things correctly but its working now. This is how I did it.

def runAction(self, event):
	Motor_Model = system.tag.readBlocking("[Test_Well]Test_Information/Motor_Model")[0].value

	values =[]
	data = system.db.runNamedQuery("Read_Motor_Data" , {"Motor_Model":Motor_Model})
	tagPath=['[Test_Well]Test_Information/Motor_Make',
	'[Test_Well]Test_Information/Motor_Model',
	'[Test_Well]Test_Information/Motor_Part_Number',
	'[Test_Well]VSD_Tags/Nominal_Motor_Power',
	'[Test_Well]VSD_Tags/Motor_Pole_Count',
	'[Test_Well]VSD_Tags/Nominal_Motor_Current',
	'[Test_Well]VSD_Tags/Motor_Thermal_Current',
	'[Test_Well]VSD_Tags/Current_Limit',
	'[Test_Well]VSD_Tags/Nominal_Motor_Voltage',
	'[Test_Well]VSD_Tags/Nominal_Motor_Freq',
	'[Test_Well]VSD_Tags/Nominal_Motor_Speed',
	'[Test_Well]VSD_Tags/Motor_Power_Factor_PF1',
	'[Test_Well]VSD_Tags/Motor_Power_Factor_PF2',
	'[Test_Well]VSD_Tags/Motor_Eff_1_1',
	'[Test_Well]VSD_Tags/Motor_Eff_3_4',
	'[Test_Well]Test_Information/Motor_Diameter',
	'[Test_Well]Test_Information/Min_Motor_Cooling',
	'[Test_Well]VSD_Tags/Accel_Time',
	'[Test_Well]VSD_Tags/Decel_Time']
	
	for row in range(data.getRowCount()):
	    for col in range(data.getColumnCount()):
	       #system.perspective.print(data.getValueAt(row, col))
	       values.insert(col,data.getValueAt(row, col))
	system.tag.writeBlocking(tagPath,values)

Kind Regards,
Sam Logan

6

This is more pythonic and more performant:

def runAction(self, event):
	Motor_Model = system.tag.readBlocking("[Test_Well]Test_Information/Motor_Model")[0].value

	data = system.db.runNamedQuery("Read_Motor_Data" , {"Motor_Model":Motor_Model})
	tagPath=['[Test_Well]Test_Information/Motor_Make',
	'[Test_Well]Test_Information/Motor_Model',
	'[Test_Well]Test_Information/Motor_Part_Number',
	'[Test_Well]VSD_Tags/Nominal_Motor_Power',
	'[Test_Well]VSD_Tags/Motor_Pole_Count',
	'[Test_Well]VSD_Tags/Nominal_Motor_Current',
	'[Test_Well]VSD_Tags/Motor_Thermal_Current',
	'[Test_Well]VSD_Tags/Current_Limit',
	'[Test_Well]VSD_Tags/Nominal_Motor_Voltage',
	'[Test_Well]VSD_Tags/Nominal_Motor_Freq',
	'[Test_Well]VSD_Tags/Nominal_Motor_Speed',
	'[Test_Well]VSD_Tags/Motor_Power_Factor_PF1',
	'[Test_Well]VSD_Tags/Motor_Power_Factor_PF2',
	'[Test_Well]VSD_Tags/Motor_Eff_1_1',
	'[Test_Well]VSD_Tags/Motor_Eff_3_4',
	'[Test_Well]Test_Information/Motor_Diameter',
	'[Test_Well]Test_Information/Min_Motor_Cooling',
	'[Test_Well]VSD_Tags/Accel_Time',
	'[Test_Well]VSD_Tags/Decel_Time']
	
	values = [row[col] for row in system.dataset.toPyDataSet(data) for col in row]
	system.tag.writeBlocking(tagPath,values)

Just make sure that the order you have the tag paths in the tagPath list matches the order of the columns returned from the named query. Otherwise you'll have data being placed in the wrong tags.

2 Likes