CODEWITHSUNDEEP
pythonmysqllistwhere-clause
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

Unpack python list to pass to a MySQL WHERE clause?

I have a python list on average consisting of 5 strings: ['help', 'me', 'solve', 'this', 'problem']. Now, I wish to query my database as follows:

select blah from table where blah = blahblah and (text like 'help' or text like 'me' or text like 'solve' or text like 'this' or text like 'problem'); The corresponding python code would look something like:

cur.execute('select blah from table where blah = blahblah and (text like %s or text like %s or text like %s or text like %s)', ('help', 'me', 'solve', 'this', 'problem'))

Pretty much I want to select all rows where the text field contains one of the strings in my list. The issue is that I do not know exactly how many strings I will have in my list. I am unaware of how to solve this problem.

How do I unpack the elements of my list into the query with a variable list size?

Upvotes: 2

Views: 713

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1122392

You can generate the query with a little string magic:

query = 'select blah from table where blah = blahblah and ({})'.format(
    ' or '.join(['text like %s'] * len(text_list)))

and then just pass text_list as a parameter to the .execute() call:

cur.execute(query, text_list)

For your list of 5 elements, this produces:

>>> text_list = ['help', 'me', 'solve', 'this', 'problem']
>>> 'select blah from table where blah = blahblah and ({})'.format(
...     ' or '.join(['text like %s'] * len(text_list)))
'select blah from table where blah = blahblah and (text like %s or text like %s or text like %s or text like %s or text like %s)'

Upvotes: 3

Related Questions