# Use system.db.runUpdateQuery with variable value counts

**URL:** <https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658>\
**Category:** Ignition\
**Created:** [January 27, 2021, 3:39pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658 "2021-01-27T15:39:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rusty](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rusty/32/19859_2.png) [@Rusty](https://forum.inductiveautomation.com/u/Rusty)\
**Post date:** [January 27, 2021, 3:39pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658/1 "2021-01-27T15:39:27Z")

</div>

Hello,

I’m running system.db.runUpdateQuery with variables, which works, however, I’d also like to make the number of VALUES variable, which isn’t working for me. This is what I have so far:

```auto
#Variables########################################
SqlLabels = ( #Add SQL column labels here
	'Line',
	'Spool',
	'HeightMin',
	'HeightMax',
	'HeightAvg',
	'HeightDev',
	'HeightCpk'
	)
	
SqlValues = ( #Add values for SQL here, these are not hard coded in real program
	' 100',
	' 200',
	' 300',
	' 400',
	' 500',
	' 600',
	' 700'
	)	

#Definitions##################################################

def convertTuple(tup): #converts tuple to string
    str = ','.join(tup) 
    return str

def transactionInsert(Labels, Values): #runs SQL transaction, adds row and populates columns with data
	import system
	system.db.runUpdateQuery("INSERT INTO DataTable (" + Labels + ") VALUES (%s, %s, %s, %s, %s, %s, %s)" %Values)

#Program##################################################	

Headers = convertTuple(SqlLabels) #converts SqlLabels tuple into string for transaction
	
transactionInsert(Headers, SqlValues) #runs transaction

```

How can I make this part variable length? VALUES (%s, %s, %s, %s, %s, %s, %s)  
I can make a string, but it doesn’t seem to work with a string. If I convert the string to a tuple or list, it comes out in the wrong format too:

```auto
Str = ""
for x in range(len(SqlLabels)):
	Str += " %s,"
	print Str
Str = Str[:-1]
#Str = Str + ")"
print Str	
Lis = Str.split(',')
print Lis
Tup = Str.partition(',')
print Tup

```

results in:  
%s,  
%s, %s,  
%s, %s, %s,  
%s, %s, %s, %s,  
%s, %s, %s, %s, %s,  
%s, %s, %s, %s, %s, %s,  
%s, %s, %s, %s, %s, %s, %s,  
%s, %s, %s, %s, %s, %s, %s  
[’ %s’, ’ %s’, ’ %s’, ’ %s’, ’ %s’, ’ %s’, ’ %s’]  
(’ %s’, ‘,’, ’ %s, %s, %s, %s, %s, %s’)

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [January 27, 2021, 4:04pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658/2 "2021-01-27T16:04:19Z")

</div>

I think this post may help you.

> [@Update with IN condition](http://forum.inductiveautomation.com/t/update-with-in-condition/11679/2):
>
> Hi Andrea, By array, do you mean a Python list? This will be my assumption unless you tell me differently. I’d say the problem is that SQL doesn’t understand lists. Try converting the list into a string: system.db.runPrepUpdate("UPDATE TEST SET FL= 1 WHERE ID IN (?)", [str(y)[1:-1]], "dscada\_main") Not that converting a list to string keeps the brackets, so the [1:-1] will remove them. [attachment=0]2016-05-17\_8-14-43.png[/attachment]

---

<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 27, 2021, 5:17pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658/3 "2021-01-27T17:17:19Z")

</div>

Instead of inserting a ‘%s’ for each value that you want, build a single string representing the value list that you need. Similar to how you converted the Tuple to a string.

```auto
def transactionInsert(Labels, Values):
    system.db.runUpdateQuery('INSERT INTO DataTable (%s) VALUES (%s) ' % (','.join(Labels),','.join(Values))

```

There is no need to import system, that is done automatically for you.

Be careful here, as this approach can be open to SQL Injection.

---

<div class="post-metadata">

**Author:** ![Rusty](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rusty/32/19859_2.png) [@Rusty](https://forum.inductiveautomation.com/u/Rusty)\
**Post date:** [January 27, 2021, 9:57pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658/5 "2021-01-27T21:57:25Z")

</div>

@lrose

It works like this, this query:

```auto
system.db.runUpdateQuery('INSERT INTO DataTable (%s) VALUES (%s) ' % ((Labels),','.join(Values)))

```

produces this:

system.db.runUpdateQuery(INSERT INTO DataTable (Line,Spool,HeightMin,HeightMax,HeightAvg,HeightDev,HeightCpk) VALUES ( 100, 200, 300, 400, 500, 600, 700) , , , false)

Thanks so much for your help!

---

<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 28, 2021, 3:00pm UTC](https://forum.inductiveautomation.com/t/use-system-db-runupdatequery-with-variable-value-counts/42658/6 "2021-01-28T15:00:32Z")

</div>

Yeah, I assumed that Labels was some type of a data structure like a list or tuple not a string.

Either way, glad you got it working.