Raw SQL with parameters does not work with IN Operator

[saintlyzero]

Description Passing multiple values to the IN operator does not work

Steps To Reproduce

from tortoise import fields, models, Tortoise

class User(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=30)

# dummy data
bob = await User.create(name="bob")
alice = await User.create(name="alice")
jhon = await User.create(name="jhon")

# manual query MySQL
conn = Tortoise.get_connection("default")

#1 this works
await conn.execute_query_dict("SELECT id FROM user WHERE name IN (%s) ", ["alice"])

#2 this does not work
await conn.execute_query_dict("SELECT id FROM user WHERE name IN (%s) ", ["'alice', 'bob'"])

Expected behavior Ideally, #2 should return [{"id":"1"}, {"id":"2"}], but it returns an empty resultset instead.
The most probable reason seems that, the conn.escape(args) converts 'alice', 'bob' to ''alice', 'bob'', and the IN operator treats it as a single element

[long2ice]

what about

await conn.execute_query_dict("SELECT id FROM user WHERE name IN (%s) ", ['alice', 'bob'])

[saintlyzero]

Nope, that does not work either Raises error at line 238

~/.ven/lib/python3.8/site-packages/aiomysql/cursors.py in execute(self, query, args) 236 237 if args is not None: 238 query = query % self._escape_args(args, conn) 239 240 await self._query(query) TypeError: not all arguments converted during string formatting

[babzo007]

Hello, I know it's been years from now but did any one catched a solution to this ? I have been searching the Internet but it seems no one has.

[miladmahmoodi]

Yes, the IN command only takes one value. To work around this issue, you need to place a %s for each value you have so that your problem gets resolved. For example:

await conn.execute_query_dict(
    "SELECT id FROM user WHERE name IN ({','.join(['%s'] * len(str_items))})",
    [item for item in str_items]
)

You can easily write a dynamic query with the above code by having a list of values.