Adding list elements to WHERE clause

Question

I want to add another condition to this WHERE clause:

stmt = 'SELECT account_id FROM asmithe.data_hash WHERE percent < {};'.format(threshold)

I have the variable juris which is a list. The value of account_id and juris are related in that when an account_id is created, it contains the substring of a juris.

I want to add to the query the condition that it needs to match anyone of the juris elements. Normally I would just add ...AND account_id LIKE '{}%'".format(juris) but this doesn't work because juris is a list.

How do I add all elements of a list to the WHERE clause?

Taher Rahgooy · Accepted Answer · 2015-08-31T03:40:32.053

1

Use Regex with operator ~:

 juris = ['2','7','8','3']

'select * from tbl where id ~ \'^({})\''.format('|'.join(juris))

which leads to this query:

select * from tbl where id ~ '^(2|7|8|3)'

This brings the rows which their id start with any of 2,7,8 or 3. Here is a fiddle for it.

If you want the id start with 2783 use:

select * from tbl where id ~ '^2783'

and if id contains any of 2,7,8 or 3

select * from t where id ~ '.*(2|7|8|3).*'

edited Aug 31 '15 at 03:40

answered Aug 30 '15 at 20:28

Taher Rahgooy

6,528
3
19
30

How do you combine this with a regular expression? – Celeritas Aug 30 '15 at 20:33
No but it would be any row where id *started* with 2,7,8 or 3. That's what I mean by using LIKE and regular expressions. – Celeritas Aug 30 '15 at 20:37
No, you want records that their `account_id` is in `juris` , right? – Taher Rahgooy Aug 30 '15 at 20:37
Not like that. I mean the string account_id contains the string juris. So if it does return the row. So account_id is always composed of taking the juris and appending more characters to it. – Celeritas Aug 30 '15 at 20:38
Ah, I got it now, I changed the answer. – Taher Rahgooy Aug 30 '15 at 20:45
I think your space in `'| '` breaks it – Celeritas Aug 31 '15 at 03:35

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

I am hoping juris is a list of strings? If so, this might help:

myquery = ("SELECT accountid FROM asmithe.data_hash "
           "WHERE percent in (%s)" % ",".join(map(str,juris)))

See these links:

edited May 23 '17 at 12:15

Community

1
1

answered Aug 30 '15 at 20:35

Ahem, SQL injection? Please never, ever provide example code like this, it's horrible insecure. – Craig Ringer Aug 31 '15 at 00:43

score 0 · Answer 3 · answered Aug 31 '15 at 00:41

Stop using string formatting with SQL. Right now. Understand?

OK now. There's a construct, ANY in SQL, that lets you take an operator and apply it to an array. psycopg2 supports passing a Python list as an SQL ARRAY[]. So in this case you can just

curs.execute('SELECT account_id FROM asmithe.data_hash WHERE percent LIKE ANY (%s)', (thelist,))

Note here that %s is the psycopg2 query-parameter placeholder. It's not actually a format specifier. The second argument is a tuple, the query parameters. The first (and only) parameter is the list.

There's also ALL, which works like ANY but is true only if all the matches are true, not just if one or more is true.

Adding list elements to WHERE clause

3 Answers3