Problem
In python-mysql-connector, a SQL statement is assembled and executed with the internal formating function, for example:
1
| cursor.execute("SELECT * FROM user WHERE id = %s AND age = %s", ("john doe", 24))
|
The %s will be replaced by the escaped value from the tuple in the second parameter.
However, this only works for types like numbers or strings, for example, the following code will raise an exception:
1
| cursor.execute("SELECT * FROM user WHERE id IN (%s)", (["john doe", "jane doe", "john wick"],))
|
Solution
Imploding the list using f string in Python
1
2
3
4
5
6
| user_ids = ["john doe", "jane doe", "john wick"]
sql = f"SELECT * FROM user WHERE id IN ({','.join(['%s'] * len(user_ids))})"
cursor.execute(sql, tuple(user_ids))
|
Reference
imploding a list for use in a python MySQLDB IN clause