Using a list of strings in a python MySQL IN clause with parameters

Question

I try to use in-query in python. But I have different exceptions to this.

The first attempt is:

query = """select keyword
               from keyword 
               where keyword in %(ids)s and country = %(country)s"""
cursor.execute(query, {'ids': tuple(ids), 'country': country})

It gives the following error: Failedprocessing pyformat-parameters; Python 'tuple' cannot be converted to MySQL type

The second attempt was:

str_keywords = ",".join(tuple("'" + str(i) + "'" for i in ids))

query = """select keyword
           from keyword 
           where keyword in (%s) and country = %s"""
cursor.execute(query, (str_keywords, country))

This doesn't give an error but doesn't work.

Any suggestions?

Answer 1

You can use f strings with tuples:

ids = ('1,2,3','54','67')
code = 'BR'
query = f"""select keyword
           from keyword 
           where keyword in {ids} and country_code = {code}
           and brand_app is not null"""
query

Output:

"select keyword\n           from keyword \n           where keyword in ('1,2,3', '54', '67') and country_code = BR\n           and brand_app is not null"

Answer 2

Try the following:

params = ",".join(["%s"] * len(ids))
query = f"""select keyword
               from keyword 
               where keyword in ({params}) and country = %s"""
cursor.execute(query, (*ids, country))

The goal here is to construct a in (%s, %s, %s, ..., %s) clause for each value in ids, It contains a %s placeholder.

There are several points to note:

• IN There may be an upper limit on the number of placeholders in a clause. For more information, see MySQL IN Conditional Limitations.
• If ids is empty, this query is invalid. You may already have some logic to handle the case of an empty ids list, or your code may never be called if ids is empty. If not, you will need to work on the case.