Pass List as Argument with python-mysql-connector Feb 2nd 2021 Words: 151

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