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

Anyone have experience with Sphinx speech recognition?

speech-recognition ivr cmusphinx

Has anyone used the Sphinx speech recognition stack to build IVR applications? I am looking for open source alternatives to the expensive and somewhat limiting choices from MSFT and others. I hav...

Hibernate - unique column constraint being ignored

java mysql hibernate

I have a MySQL table to hold tags (i.e. like those used here on Stack Overflow). It simply has an id (pk) and a tag column to hold the tag itself.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.