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
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions