SQL Query Where in list

Ignition
,
1

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?

2

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)
3 Likes
3

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

1 Like
4

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

5

That’s what I thought.

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

6

While trying to do this, I was still getting errors so I just did this and it worked for me.

names = [n.replace("'", "\\'") for n in names]

system.db.runUpdataQuery("DELETE FROM [table name] WHERE name IN ('{}')".format(", ".join(names)))
1 Like
7

When you're using system.db.runUpdateQuery() then the query string needs to be constructed as valid SQL, meaning that you can't use the ? for parameter substitution.

You should use the system.db.runPrepUpdate() to protect yourself from any SQL Injection risks, particularly if you are accepting user input as the parameters.

6 Likes
8

Try this instead:

q = "delete from [table name] where name in ({})".format(','.join('?' * len(names)))
system.db.runPrepUpdate(q, names)

For the reasons @lrose mentioned, you shouldn't use runUpdateQuery for this.

3 Likes