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-31 03:40:32Z

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, 2015 at 3:40

answered Aug 30, 2015 at 20:28

Taher Rahgooy

6,7163 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Celeritas Over a year ago

How do you combine this with a regular expression?

Celeritas Over a year ago

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.

Taher Rahgooy Over a year ago

No, you want records that their account_id is in juris , right?

Celeritas Over a year ago

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.

Taher Rahgooy Over a year ago

Ah, I got it now, I changed the answer.

|

Craig Ringer · Accepted Answer · 2015-08-31 00:41:52Z

0

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.

answered Aug 31, 2015 at 0:41

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:15:16Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Aug 30, 2015 at 20:35

user4398985

1 Comment

Craig Ringer Over a year ago

Ahem, SQL injection? Please never, ever provide example code like this, it's horrible insecure.

Collectives™ on Stack Overflow

Adding list elements to WHERE clause

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related