Insert Query with Dynamic Table Name,Colums and parameters

I am trying to write a dynamic SQL Insert Query in that all values are dynamic like This:
There will be any number of columns that is not fixed.

insertQuery=‘insert into ‘+str(Table)+’ (’+str(allCols)+’) values (?,?,?)’
print ‘insertQuery’,insertQuery

But it is throwing exception.Is it possible to do like this?
I have referred the solution given in forum but i didn’t get it.
Can anyone Help me out this.

Try modifying your script to something like this:

colValues = ['MuleSoft','','Max']
allCols = ['Company', 'Email', 'First_Name']
cols = ''
for col in allCols:
	cols += col + '=? '
table = 'Mapping_Info'
query = "update %s set %s" %(table, cols)
database = 'Ignition_Integration'
system.db.runPrepUpdate(query, colValues , database=database)

Thanks for the reply.
I have tried ,There is some syntax error in this but It will work for update query.
I am facing issue with insert query, can you please explain for insert.

@Anita.Shinde sorry about that.

Try this for an insert:

colValues = ['MuleSoft','','Max']
cols = 'Company, Email, First_Name'
args = '?,' * len(colValues)
args = args[:-1]
table = 'Mapping_Info'
query = "insert into %s (%s) VALUES (%s)" %(table, cols, args)
database = 'Ignition_Integration'
system.db.runPrepUpdate(query, colValues , database=database)

Thank You. It’s working.

1 Like