how to pass variable with in operator in mysql?

Question

In sql commed, where condition i'm using in operator along with a variable, now i've to pass value in this variable, what would be right syntax for this:

 select col1 from testtable where col2 in :testval

mysql version 5.1

Answer 1

You can't. Parametrised SQL only works on single values. Use this:

values = ["foo", "bar", "baz"]
variables = { "var%d" % i: value for i, value in enumerate(values) }
placeholders = ", ".join(":%s" % var for var in variables.keys())
sql = "select col1 from testtable where col2 in (" + placeholders + ")"

You can then use sql with the variables bindings.

Answer 2

You can use an ORM library for a higher-level access instead of plain SQL.

For example (taken from https://stackoverflow.com/a/8603129/196206):

session.query(MyUserClass).filter(MyUserClass.id.in_((123,456))).all()

# alternative - without the ORM part, just query builder:

session.execute(
    select(
        [MyUserTable.c.id, MyUserTable.c.name], 
        MyUserTable.c.id.in_((123, 456))
    )
).fetchall()

Answer 3

It should be like : select col1 from testtable where col2 in (var1, var2, ...)

Sample

Table Name : Test

Id  Name          Roll_No
1  R. Nikhil           34
2  Ashish Rao      25
3  Joseph             45
4  Mohammed     35
5  Jatin Gupta      39

Mysql Query : select Name from Test where Roll_No IN (25, 35, 45);

and the result will be :

Ashish Rao
Joseph
Mohammed