0

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?

Sha
  • 921
  • 17
  • 46

2 Answers2

1

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.
Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
0

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"
1extralime
  • 606
  • 3
  • 6
  • what about sql injection? – Sha Jul 29 '22 at 18:08
  • If you are worried about your python variables being altered, then its a concern. Its a good question though. I am not an expert at injection, but on the surface, you would only send the string as the query, there is nothing to bind. Without further context of where this script executes, and who executes it, i cannot fully address the concern. – 1extralime Jul 29 '22 at 18:14