tortoise / tortoise/tortoise-orm
bulk_update raises syntax error when fields contains a foreign key
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
Describe the bug
Returns the following syntax error when calling bulk_update with foreign key field.
Exception has occurred: OperationalError
near "<": syntax error
sqlite3.OperationalError: near "<": syntax error
To Reproduce
from tortoise import run_async
import tortoise
class Post(tortoise.models.Model):
id = tortoise.fields.IntField(pk=True)
title = tortoise.fields.CharField(max_length=255)
content = tortoise.fields.TextField()
class Comment(tortoise.models.Model):
id = tortoise.fields.IntField(pk=True)
post = tortoise.fields.ForeignKeyField('models.Post', related_name='comments')
content = tortoise.fields.TextField()
run_async(tortoise.Tortoise.init(
db_url='sqlite://dbtest.sqlite3',
modules={'models': ['__main__']},
))
run_async(tortoise.Tortoise.generate_schemas())
async def check_bulk_update():
post1 = Post(title="My first post", content="Hello world!")
await post1.save() # will create id in post1
post2 = Post(title="My first post", content="Hello world!")
await post2.save() # will create id in post2
comment1 = Comment(post=post1, content="Nice post!")
comment2 = Comment(post=post1, content="I did not like it.")
await Comment.bulk_create([comment1, comment2]) # works fine, but comment1 and comment2 don't have ids
c1 = await Comment.get(id=1) # fetch comment1 properly with id
c2 = await Comment.get(id=2) # fetch comment2 properly with id
# Update both comments with bulk_update
c1.content = "Nice post! Updated"
c2.content = "I did not like it. Updated"
c1.post = post2
c2.post = post2
await Comment.bulk_update([c1, c2], fields=['content', 'post']) # throws syntax error
await Comment.bulk_update([c1, c2], fields=['content', 'post_id']) # Works!
run_async(check_bulk_update())
run_async(tortoise.Tortoise.close_connections())
Expected behavior
Either it should have worked or given a more helpful error.
Additional context
Only tested with sqlite. The generated sql looks like this:
`> Comment.bulk_update([c1, c2], fields=['content', 'post']).sql()
UPDATE "comment" SET "content"=CASE WHEN "id"=1 THEN 'Nice post! Updated' WHEN "id"=2 THEN 'I did not like it. Updated' END,"post"=CASE WHEN "id"=1 THEN WHEN "id"=2 THEN END WHERE "id" IN (1,2) AND "id" IN (1,2)
`
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 by running the provided SQLite reproduction and inspect the bulk_update SQL-generation path, especially how the foreign-key field is rendered versus post_id. Compare the generated SQL for fields=['post'] and fields=['post_id']; done means the foreign-key case executes correctly or produces a clear error, with the regression behavior verified.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sqlite
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100