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?

Answers


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)'

Need Your Help

Java DB Connection Pooling in Shell Script

java shell connection-pooling

Does connection pooling db connections make sense when java code is called from a shell script, or is it better to use individual connections? Doesn't the jvm exit after every call to the shell scr...

ASP.Net PagingBulletedList Extender For ASP.Net GridView

c# asp.net

Does any one know of a control that i can use with a ASP.Net gridview that provides the functionality of the ASP.Net Ajax Control PagingBulletedList. I want to provide the users with a easier way to