# Pulling SQL Query Data into Tags

**URL:** <https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473>\
**Category:** Ignition\
**Tags:** database, scripting, perspective\
**Created:** [January 18, 2024, 9:21pm UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473 "2024-01-18T21:21:48Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sam\_Logan](https://avatars.discourse-cdn.com/v4/letter/s/cdc98d/32.png) [@Sam\_Logan](https://forum.inductiveautomation.com/u/Sam_Logan)\
**Post date:** [January 18, 2024, 9:21pm UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/1 "2024-01-18T21:21:48Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 18, 2024, 9:36pm UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/2 "2024-01-18T21:36:54Z")

</div>

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.

```python
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)

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 18, 2024, 9:43pm UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/3 "2024-01-18T21:43:37Z")

</div>

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

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![Sam\_Logan](https://avatars.discourse-cdn.com/v4/letter/s/cdc98d/32.png) [@Sam\_Logan](https://forum.inductiveautomation.com/u/Sam_Logan)\
**Post date:** [January 18, 2024, 10:57pm UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/4 "2024-01-18T22:57:08Z")

</div>

@JordanCClark

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

```auto
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

---

<div class="post-metadata">

**Author:** ![Sam\_Logan](https://avatars.discourse-cdn.com/v4/letter/s/cdc98d/32.png) [@Sam\_Logan](https://forum.inductiveautomation.com/u/Sam_Logan)\
**Post date:** [January 19, 2024, 2:28am UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/5 "2024-01-19T02:28:18Z")

</div>

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.

```auto
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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 19, 2024, 4:20am UTC](https://forum.inductiveautomation.com/t/pulling-sql-query-data-into-tags/84473/6 "2024-01-19T04:20:03Z")

</div>

This is more pythonic and more performant:

```python
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.