Issue: bulk_update() generated SQL with high number of SQL variables
Author: CarstenGrohmann
State: closed
Created: 2020-09-02 09:57:25

Hello Charles,

I'm still working on my mass updates. Now I've trouble with the maximum numbers of variables in a SQLite statement.

If I use `Model.bulk_update(batch_size=800)` my code raises ```OperationalError: too many SQL variables```. If I use `Model.update()` with same number of items all works fine.

I looked at the generated SQL statements and found that `bulk_update()` uses 9 SQL variables and `update()` only three. I'm surprised about the difference even if I'm about that I work with programatically generated SQL statements.

Currently `bulk_update()` is usable in show scenario with small chunk sizes only. Is this behavior desired?

Is it possible to rework the query with `bulk_update()` so that it needs significantly less variables?

## Statement with Model.update()
```
>>> query = TestInStmt.update(num=2).where(TestInStmt.text.in_(['A', 'B', 'C']))
DEBUG:peewee:('UPDATE "testinstmt" SET "num" = ? WHERE ("testinstmt"."text" IN (?, ?, ?))', [2, u'A', u'B', u'C'])
```

## Statement with Model.bulk_update()
```
>>> TestInStmt.bulk_update([b1, b2, b3], [TestInStmt.num])
DEBUG:peewee:('UPDATE "testinstmt" SET "num" = CASE "testinstmt"."id" WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END WHERE ("testinstmt"."id" IN (?, ?, ?))', [16, 42, 17, 4711, 18, 1111, 16, 17, 18])
```

## Minimal example:
```
from peewee import *
import logging
logging.basicConfig(level=logging.DEBUG)

db = SqliteDatabase('test.sqlite')


class TestInStmt(Model):
    class Meta:
        database = db

    num = IntegerField()
    text = TextField()


db.create_tables([TestInStmt])

b1 = TestInStmt.create(num=1, text='A')
b2 = TestInStmt.create(num=2, text='B')
b3 = TestInStmt.create(num=3, text='C')

query = TestInStmt.update(num=2).where(TestInStmt.text.in_(['A', 'B', 'C']))
query.execute()

b1.num = 42
b2.num = 4711
b3.num = 1111
TestInStmt.bulk_update([b1, b2, b3], [TestInStmt.num])
```
```
DEBUG:peewee:('CREATE TABLE IF NOT EXISTS "testinstmt" ("id" INTEGER NOT NULL PRIMARY KEY, "num" INTEGER NOT NULL, "text" TEXT NOT NULL)', [])
DEBUG:peewee:('INSERT INTO "testinstmt" ("num", "text") VALUES (?, ?)', [1, u'A'])
DEBUG:peewee:('INSERT INTO "testinstmt" ("num", "text") VALUES (?, ?)', [2, u'B'])
DEBUG:peewee:('INSERT INTO "testinstmt" ("num", "text") VALUES (?, ?)', [3, u'C'])
DEBUG:peewee:('UPDATE "testinstmt" SET "num" = ? WHERE ("testinstmt"."text" IN (?, ?, ?))', [2, u'A', u'B', u'C'])
DEBUG:peewee:('UPDATE "testinstmt" SET "num" = CASE "testinstmt"."id" WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END WHERE ("testinstmt"."id" IN (?, ?, ?))', [19, 42, 20, 4711, 21, 1111, 19, 20, 21])
```

Regards,
Carsten

This is why the `batch_size` is exposed. Performing bulk updates necessarily involves a lot of variables because you essentially have to provide a mapping from each primary-key to the values that are to be updated. I don't know of any other way to do this when each row may have a different value.

Not a bug, that's simply how it works.

With sqlite you can probably just as efficiently open a transaction and apply the updates one-at-a-time.