SQL Query Where in list

OK my brain is hurting and I can’t seem to figure this out.

I’ve got a list of strings and I’m trying to do a query using the WHERE IN operator. This is in MySQL.

Basically

list = [item1,item2,item3,item4]
sqlQuery = 'update table set valid = true where item in (?)'
data = system.db.runPrepQuery(query=sqlQuery,args=list)

But I’m not getting any data back. If I directly query the database I get data, but when I do it through Ignition nothing.

Any ideas?

I believe you have to have a ? for each parameter.

So assuming that the list size is static

sqlQuery = 'update table set valid = true where item in (?,?,?,?)'

Also, I notice that you’re using an update query but not using system.db.runPrepUpdate()

If the list size is dynamic then you could build the query string dynamically.

system.db.runPrepUpdate(query = 'update table set valid = true where item in (%s)' % (", ".join(['?'] * len(list))), args = list)
2 Likes

BTW, don’t use python built-in function names as variable names.

1 Like

It’s not… It’s just a quick example. I had to sanitize for the customer.

That’s what I thought.

Thanks. I’ll test when I get back to the office.