tortoise / tortoise/tortoise-orm

bulk_update raises syntax error when fields contains a foreign key

Open
#1,335 1 comment 1 reaction 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.