Use system.db.runUpdateQuery with variable value counts

Ignition
1

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:

#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:

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’)

2

I think this post may help you.

1 Like
3

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.

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.

2 Likes
5

@lrose

It works like this, this query:

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!

6

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.

1 Like