tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.66k stars 387 forks source link

Bulk creation with on_conflict fails with CharField PK #1722

Open lnxsr opened 1 month ago

lnxsr commented 1 month ago

Describe the bug Bulk creation with on_conflict set to primary key works for IntField PKs. After switching to CharField it fails with:

tortoise.exceptions.OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

To Reproduce

class Tournament(Model):
    # id = fields.IntField(primary_key=True)
    id = fields.CharField(primary_key=True, max_length=48)
    field1 = fields.CharField(max_length=10)

async def run():
    await Tortoise.init(db_url=f"sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()
    await Tournament.bulk_create([Tournament(id="123", field1="Some value")], update_fields=["id", "field1"], on_conflict=["id"])

Expected behavior Bulk creation works with CharField PK as well.

Additional context Generated SQL / Debug output:

2024-09-28 13:53:47 - tortoise.db_client:84 - DEBUG - Created connection <Connection(Thread-1, started 139755117758144)> with params: filename=:memory: journal_mode=WAL journal_size_limit=16384 foreign_keys=ON
2024-09-28 13:53:47 - tortoise.db_client:164 - DEBUG - CREATE TABLE IF NOT EXISTS "tournament" (
    "id" VARCHAR(48) NOT NULL  PRIMARY KEY,
    "field1" VARCHAR(10) NOT NULL
);
2024-09-28 13:53:47 - tortoise.db_client:132 - DEBUG - INSERT INTO "tournament" ("id","field1") VALUES (?,?) ON CONFLICT ("id", "id") DO UPDATE SET "id"=EXCLUDED."id","id"=EXCLUDED."id","field1"=EXCLUDED."field1","field1"=EXCLUDED."field1": [['123', 'Some value']]
2024-09-28 13:53:47 - tortoise.db_client:94 - DEBUG - Closed connection <Connection(Thread-1, started 139755117758144)> with params: filename=:memory: journal_mode=WAL journal_size_limit=16384 foreign_keys=ON
henadzit commented 2 weeks ago

Hey @lnxsr, this is caused by a bug in pypika-tortoise. I just submitted a PR which should fix the issue https://github.com/tortoise/pypika-tortoise/pull/14