Multiple parameters in MySQL "IN" query

Question

I'm clearly doing something wrong in the parameterization, but not sure what the proper syntax is.

Desired, but doesn't work: multiple conditions in where IN

data = ['lol', 'hi']
query = """ select word, count(1) from table where word in (%(ids)s) group by 1"""
pandas.read_sql_query(sql=query, con=db_engine, params={'ids':data})

Output:

InternalError: (pymysql.err.InternalError) (1241, 'Operand should contain 1 column(s)') [SQL: "select word, count(1) from table where word in (%(ids)s) group by 1 "] [parameters: {'ids': ('lol', 'hi')}]

Not desired, but works: single condition in where IN (it's fine with a list of length 1)

data = ['lol']
query = """ select word, count(1) from table where word in (%(ids)s) group by 1"""
pandas.read_sql_query(sql=query, con=db_engine, params={'ids':data})

Answer 1

Remove the brackets around the placeholder. As it is the query is comparing word against (('lol', 'hi')) after parameter substitution done by pymysql, or in other words a scalar against a tuple. A list of length 1 is fine because the result is (('hi')) and SQL actually treats the scalar word as a 1-column row when comparing, which the perhaps slightly non obvious error hints at.

So the query should look like:

query = """
    select word, count(1)
    from table
    where word in %(ids)s
    group by 1
    """