tortoise / tortoise/tortoise-orm
Raw SQL with parameters does not work with IN Operator
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
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
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start at the connection path used by execute_query_dict and reproduce the MySQL example with one value and multiple values for the IN operator. Trace how the parameter list is escaped and bound; done means the multi-value query returns both matching IDs without breaking the single-value case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100