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?
lrose
January 29, 2021, 9:19pm
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
bschroeder:
list = [
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.
ToMakPo
November 28, 2023, 6:31pm
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
lrose
November 28, 2023, 6:59pm
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
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