Skip to content

bulk_update raises syntax error when fields contains a foreign key #1335

@rajeee

Description

@rajeee

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions