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})

Ilja Everilä · Accepted Answer · 2019-03-06 21:10:57Z

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
    """

edited Mar 6, 2019 at 21:10

answered Mar 6, 2019 at 20:50

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

To clarify, you mean to remove the parentheses (not brackets) around the text in query, correct?

Collectives™ on Stack Overflow

Multiple parameters in MySQL "IN" query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related