I try to use in-query in python. But I got different exceptions about it.

First try 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 error like: Failed processing pyformat-parameters; Python 'tuple' cannot be converted to a MySQL type

Second try is:

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 error but it doesn’t work.

Any suggestion?

CodePudding user response：

You can use f-string 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"

CodePudding user response：

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 aim here is to build up an in (%s, %s, %s, ..., %s) clause with one %s placeholder for each value in ids.

There are a couple of things to be aware of:

• There may be an upper limit on the number of placeholders in an IN clause. For more information, see MySQL IN condition limit.
• This query will not be valid if ids is empty. You might already have some logic for handling the case of an empty ids list, or your code might never be called with ids empty. If not, you will need to handle this case.